77 lines
2.4 KiB
Python
77 lines
2.4 KiB
Python
def __get_pkey__(engine,table_name,schema):
|
|
pk = engine.dialect.get_pk_constraint(engine,table_name=table_name,schema=schema)
|
|
return pk
|
|
|
|
def __get_dtype__(engine,table_name,schema):
|
|
cols = engine.dialect.get_columns(engine,table_name=table_name,schema=schema)
|
|
type_cols = {i['name']:i['type'] for i in cols}
|
|
return type_cols
|
|
|
|
def update_to_sql(df, con, table_name, schema_name, key_name):
|
|
# from sys import exit
|
|
|
|
a = []
|
|
b = []
|
|
table = table_name
|
|
schema = schema_name
|
|
primary_key = key_name
|
|
pkey = __get_pkey__(
|
|
con,table_name=table,schema=schema)
|
|
type_cols = __get_dtype__(
|
|
con,table_name=table,schema=schema)
|
|
|
|
if not all(item in pkey['constrained_columns'] for item in df.columns):
|
|
print('Le(s) champs clé primaire "%s" ne figure pas dans le DataFrame'%pkey['constrained_columns'])
|
|
Q = input('Voulez-vous continuer la mise à jour ? (y/n) ')
|
|
if Q.lower() == 'y':
|
|
pass
|
|
else :
|
|
return print('Données non mise à jour')
|
|
# if pkey not in df.columns:
|
|
# exit('Le champs clé primaire "%s" ne figure pas dans le DataFrame'%pkey)
|
|
|
|
if isinstance(primary_key, str):
|
|
primary_key = [primary_key]
|
|
|
|
for col in df.columns:
|
|
if col in primary_key:
|
|
b.append("t.{col}=f.{col}".format(col=col))
|
|
else:
|
|
dtype = type_cols[col]
|
|
if hasattr(dtype,'enums') :
|
|
dty = '.'.join([dtype.schema,dtype.name])
|
|
a.append("{col}=t.{col}::{typ}".format(col=col,typ=dty))
|
|
else:
|
|
a.append("{col}=t.{col}".format(col=col))
|
|
|
|
if isinstance(df, gpd.GeoDataFrame):
|
|
df.to_postgis(
|
|
name = 'temp_table',
|
|
con = con,
|
|
schema = schema,
|
|
if_exists = 'replace',
|
|
geom_col = df.geometry.name
|
|
)
|
|
else:
|
|
df.to_sql(
|
|
name = 'temp_table',
|
|
con = con,
|
|
schema = schema,
|
|
if_exists = 'replace',
|
|
index = False,
|
|
method = 'multi'
|
|
)
|
|
|
|
update_stmt_1 = "UPDATE {sch}.{final_table} f".format(sch=schema,final_table=table)
|
|
update_stmt_2 = " FROM {sch}.temp_table t".format(sch=schema)
|
|
update_stmt_6 = " WHERE %s"%' AND '.join(b)
|
|
update_stmt_3 = " SET "
|
|
update_stmt_4 = ", ".join(a)
|
|
update_stmt_5 = update_stmt_1 + update_stmt_3 + update_stmt_4 + update_stmt_2 + update_stmt_6 + ";"
|
|
drop_stmt = "DROP TABLE {sch}.temp_table ;".format(sch=schema)
|
|
with con.begin() as cnx:
|
|
cnx.execute(update_stmt_5)
|
|
cnx.execute(drop_stmt)
|
|
return print('END update')
|
|
|