3

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?

Parfait
  • 104,375
  • 17
  • 94
  • 125
paolof89
  • 1,319
  • 5
  • 17
  • 31

1 Answers1

11

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)
Parfait
  • 104,375
  • 17
  • 94
  • 125
howard roark
  • 628
  • 7
  • 27
  • 1
    It seems that you can not use FROM in an UPDATE clause. SQL statement should be like: sql = "UPDATE final_table AS f INNER JOIN temp_table AS t ON f.id = t.id SET col1 = t.col1 WHERE f.id = t.id" – joaquintopiso Apr 09 '18 at 10:25
  • 2
    how to update all columns? Do I need to manually `col1 = t.col1`, `col2 = t.col2`? – ZK Zhao Jul 16 '19 at 11:34