'Update a register using sqlalchemy and executemany
I want to know if is possible update a row when the primary key exists using sqlalchemy
I am testing the below code:
import pyodbc
import sqlalchemy
from sqlalchemy import create_engine, event, DateTime, String, Integer, BigInteger
from urllib.parse import quote_plus
import math
import numpy as np
env = "DEV"
with open('Config.json','r') as file:
config = json.load(file)
conn = "DRIVER="+config[env]['DRIVER']+";SERVER="+config[env]['HOST']+";DATABASE="+config[env]['DATABASE']+";UID="+config[env]['USER']+";PWD="+config[env]['PASS']+";Authentication=ActiveDirectoryPassword"
quoted = quote_plus(conn)
new_con = 'mssql+pyodbc:///?odbc_connect={}'.format(quoted)
engine = create_engine(new_con)
def write_df_to_sql(df_name,schema_name, table_name, df_dtype):
chunksnumber=math.ceil(df.shape[0] / 10**4)
chunks = np.array_split(df, chunksnumber)
for chunk in chunks:
chunk.to_sql(table_name, engine, index=False, if_exists = 'append',schema=schema_name, dtype=df_dtype)
return True
@event.listens_for(engine, 'before_cursor_execute')
def receive_before_cursor_execute(conn, cursor, statement, params, context, executemany):
if executemany:
cursor.fast_executemany = True
dtype_dict_test={
'colunm1':sqlalchemy.Integer(),
'column2':sqlalchemy.BigInteger(),
'column3':sqlalchemy.String(64),
'column4':sqlalchemy.DateTime()
}
write_df_to_sql(df_test,'SCHEMA_TEST','TABLE_TEST',dtype_dict_test)
The issue happen when the primary key exist in database table, i need update some columns, but this code just try to insert.
I do not need change the param if_exists="replace", because i need update some columns and exist a constraint with this primary key
Can you suggest to me a method to update the data?
Thank and Regards
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
