2

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
  • 97,543
  • 17
  • 91
  • 116
paolof89
  • 1,243
  • 2
  • 16
  • 28

1 Answers1

10

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
  • 97,543
  • 17
  • 91
  • 116
howard roark
  • 585
  • 6
  • 26
  • 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
  • 1
    how to update all columns? Do I need to manually `col1 = t.col1`, `col2 = t.col2`? – cqcn1991 Jul 16 '19 at 11:34