Update MSSQL table through SQLAlchemy using dataframes

Question

I'm trying to replace some old MSSQL stored procedures with python, in an attempt to take some of the heavy calculations off of the sql server. The part of the procedure I'm having issues replacing is as follows

UPDATE mytable
SET calc_value = tmp.calc_value
  FROM dbo.mytable mytable INNER JOIN 
       @my_temp_table tmp ON mytable.a = tmp.a AND mytable.b = tmp.b AND mytable.c = tmp.c
  WHERE (mytable.a = some_value)
    and (mytable.x = tmp.x)
    and (mytable.b = some_other_value)

Up to this point, I've made some queries with SQLAlchemy, stored those data in Dataframes, and done the requisite calculations on them. I don't know now how to put the data back into the server using SQLAlchemy, either with raw SQL or function calls. The dataframe I have on my end would essentially have to work in the place of the temporary table created in MSSQL Server, but I'm not sure how I can do that.

The difficulty is of course that I don't know of a way to join between a dataframe and a mssql table, and I'm guessing this wouldn't work so I'm looking for a workaround

Did you find any way to do this? I am try to do similar task.. — Manvi
– Manvi, Commented Jul 20, 2018 at 20:10

Jano · Accepted Answer · 2018-04-02 09:59:34Z

1

As the pandas doc suggests here :

from sqlalchemy import create_engine 
engine = create_engine("mssql+pyodbc://user:password@DSN", echo = False)
dataframe.to_sql('tablename', engine , if_exists = 'replace')

engine parameter for msSql is basically the connection string check it here

if_exist parameter is a but tricky since 'replace' actually drops the table first and then recreates it and then inserts all data at once.

by setting the echo attribute to True it shows all background logs and sql's.

answered Apr 2, 2018 at 9:59

Jano

3583 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dportology Over a year ago

Thanks so much for a reply. Looking at the to_sql documentation (pandas.pydata.org/pandas-docs/stable/generated/…) it seems there isn't an update option. Would there be any way you know of to update only records which match on certain columns, while keeping the rest of the records?

Collectives™ on Stack Overflow

Update MSSQL table through SQLAlchemy using dataframes

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related