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')