44 lines
1.6 KiB
Python
44 lines
1.6 KiB
Python
from pycen import con_gn
|
|
from sqlalchemy.sql import text
|
|
|
|
def update_additional_values(
|
|
con,table='cor_counting_occtax',shema='pr_occtax',
|
|
jsonb_column='additional_fields',keyfield='code_atlas'):
|
|
sql = text('''
|
|
UPDATE %(sch)s.%(tab)s
|
|
SET "%(jsonb_c)s" = JSONB_SET(
|
|
"%(jsonb_c)s",
|
|
'{%(keyf)s}'::text[],
|
|
TO_JSONB(
|
|
ref_nomenclatures.get_nomenclature_label(("%(jsonb_c)s"::json#>>'{%(keyf)s}')::int,
|
|
'default')
|
|
),
|
|
false)
|
|
WHERE ("%(jsonb_c)s"::json#>>'{%(keyf)s}')::text ~ '^[0-9\.]+$'
|
|
AND ("%(jsonb_c)s"::json#>>'{%(keyf)s}')::int in (
|
|
SELECT id_nomenclature FROM ref_nomenclatures.t_nomenclatures t
|
|
JOIN ref_nomenclatures.bib_nomenclatures_types b USING (id_type)
|
|
WHERE lower(b.mnemonique) = '%(keyf)s'
|
|
)
|
|
'''%{'keyf':keyfield,'tab':table,'sch':shema,'jsonb_c':jsonb_column})
|
|
# print(sql)
|
|
|
|
with con.begin() as cnx:
|
|
cnx.execute(sql)
|
|
|
|
def update_additional_key(
|
|
con,table='cor_counting_occtax',shema='pr_occtax',jsonb_column='additional_fields',
|
|
keyfield='Strate de végétation',new_keyfield='strate_flore'
|
|
):
|
|
sql = text('''
|
|
UPDATE %(sch)s.%(tab)s
|
|
SET "%(jsonb_c)s" = "%(jsonb_c)s" - '%(keyf)s' || jsonb_build_object(
|
|
'%(new_keyf)s', "%(jsonb_c)s"->'%(keyf)s'
|
|
)
|
|
WHERE "%(jsonb_c)s" ? '%(keyf)s';
|
|
'''%{'keyf':keyfield,'new_keyf':new_keyfield,'tab':table,'sch':shema,'jsonb_c':jsonb_column})
|
|
# print(sql)
|
|
|
|
with con.begin() as cnx:
|
|
cnx.execute(sql)
|
|
|