How to update a db table from pandas dataset with sqlalchemy

Question

I need to update a table in a MSSQL database. The dimension of the table doesn't allow to load the table in memory, modify the dataframe and rewrite it back.

I also need to update only one column at a time so I cannot use the solution proposed in this topic (ie the solution proposes a delete operation of the interested rows, impossible for me cause I can update only one column at time)

So I need to perform something like an update-from query

Update mytable
set mycolumn = dfcolumn
from df
where mytable.key=df.key

in which mytable is a dbtable and df is a pandas Dataframe.

Is it possible to perform this kind of function with SQLALCHEMY?

Answer 1

Create a temp table with the key and the column you want to update in the ms sql database. And then make a update call to the server. The following is the code snippet using sqlalchemy

You can use the following way:

engine = create_engine('mssql+pymssql://scott:tiger@hostname:port/dbname')
df.to_sql('temp_table', engine, if_exists='replace')

sql = "UPDATE final_table AS f" + \
      " SET col1 = t.col1" + \
      " FROM temp_table AS t" + \
      " WHERE f.id = t.id"

with engine.begin() as conn:
   conn.execute(sql)