Update panda dataframe to SQL Server though SQLAlchemy engine using python

Question

I have an existing SQL Server Database. I want to use python to read from a CSV file and update column values matching the TIMEID column into the SQL Server Table

If I do it in SQL Server I would load the the new CSV into a new table and then update using:

UPDATE R 
SET R.[PA]=P.[PA]
FROM [DATABASE_TABLE] AS R
INNER JOIN [NEW_CSV] AS P 
       ON R.[TIMEID] = P.[TIMEID] 
WHERE R.[TIMEID] like '20180201%' //i can survive now without the where, and update everything from the CSV.

Pretty new to python so pardon me. I have succeeded loading the CSV file into a panda dataframe and also I am able to insert new rows into the SQL Server but I am unable to manage an update (either into existing columns or null columns).

import pandas as pd 
from sqlalchemy import create_engine
engine = create_engine("BLOCKOUTFOR PASSWORD")
query="SELECT * FROM [DATABASE].[TABLE]"
df = pd.read_sql_query(query, engine)
display(df) #This is just to display the current data

    TIMEID  DATEID  HOUR    DOW FESTIVAL    PA  PB  PC  P31A    PX  PY  P_TOT
0   20180101H01 2018-01-01  01  2   N   0.4615  0.0570  0.4427  0.0153  None    None    0.9765
1   20180101H02 2018-01-01  02  2   N   0.4112  0.0516  0.4074  0.0154  None    None    0.8856

#Convert Type and Load CSV into df3
def dfReadCSV( Path, Ind):
    df =pd.read_csv(Path,dtype={'DATEID':str,'Hour':str},parse_dates= ['DATEID'])
    df1=df[Ind:]
    return df1
df3=dfReadCSV("C5Liq_2018Test.csv",0)

display(df3) #if there is a neater way to do this it be appreciated, but not critical 

    Attribute   TIMEID  DATEID  Hour    DOW 20A 20DHA   21A 21DHA   30A 31A PA  PB  PC  P31A    P_TOT
0   H01 20180101H01 2018-01-01  01  1   0.2953  0.0158  0.1662  0.0412  0.4427  0.0153  0.4615  0.0570  0.4427  0.0153  0.9765
1   H02 20180101H02 2018-01-01  02  1   0.2711  0.0160  0.1401  0.0356  0.4074  0.0154  0.4112  0.0516  0.4074  0.0154  0.8856

#Insert Function
connStr= engine.connect().connection
cursor = connStr.cursor()

for index,row in df3.iterrows():
    cursor.execute('INSERT INTO [DATABASE].[TABLE]([TIMEID],[DATEID],[Hour],[DOW]) values (?,?,?,?)', row['TIMEID'], row['DATEID'], row['Hour'], row['DOW']) 
    connStr.commit()

cursor.close()
connStr.close()

#Update Function. This is where i have problem.
connStr= engine.connect().connection
cursor = connStr.cursor()

for row in df3.iterrows():
    #sql = 'UPDATE [DATABASE].[TABLE] SET [DATEID]=? WHERE [TIMEID]=?'.format(tbl=[DATABASE].[TABLE])
   cursor.execute("UPDATE [DATABASE].[TABLE]  SET [DATEID] = ? WHERE [TIMEID] = ?", row[:,0],row[;,0])  

cursor.close()
connStr.close()

The Syntax is wrong and I couldn't figure it out. Preferable I like to have a similar method to update as above.Data in the CSV get updated and I want to update these info into my SQL Server table.

I have found a similiar thread but found no answer too: Update MSSQL table through SQLAlchemy using dataframes

As the threadstarter there, I too cannot drop the table because the new CSV that I load in a new column of data(example PX) might not have some info of the previous insert (PA).

Sushant Nair · Accepted Answer · 2019-08-06 05:01:49Z

0

There are two ways to make an update you want:

1) Directly on the database:

upd = (session.query(TABLE)
       .filter(TIMEID = row[:,0])
       .update({"DATEID": row[:,0]})
       )
print("# of updated rows = {}".format(upd))
# session.commit()

2) Load object(s), update the value, and commit the session

upd = (session.query(TABLE)
       .filter(TIMEID = row[:,0])
       )

# assuming there should be exactly one object for given TIMEID
DATEID= upd.one()
DATEID.time_out = datetime.datetime.now()
session.commit()

You can get more info

I don't recommend sqlachemy for updation. its good for batch insert

For sqlalchemy

import pandas as pd
from sqlalchemy import create_engine
engine = create_engine('postgresql+psycopg2://postgres:password@host:port/database')
print(engine)
truncate_query = "SELECT * from something.something"
df = pd.read_sql_query(truncate_query , engine)

edited Aug 6, 2019 at 5:01

answered Aug 5, 2019 at 12:07

Sushant Nair

837 bronze badges

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

4 Comments

user11883606 Over a year ago

Hi, thanks for the help. i started with the SQLACHEMY just because of the import create_engine,hoping to find a solution similiar to using Cursor.excute function. I tried your solution and franky have little knowledge of using session etc. Apart from the code you advised, I add the below in to the definition, after checking out the link //from sqlalchemy.orm import sessionmaker Session=sessionmaker(bind=engine) session = Session() // 1st solution 1) Directly on the database: //NameError: name 'TABLE' is not defined// I am finding difficulty to define the session.querry()?

user11883606 Over a year ago

I know it might have been a novice qns, but I am just confused by the diffierent ways to archieve one same thing.I like to understand how to make the querry.session work but at the same time I was thinking if there exist(maybe it not possible?) : to have the update similiar to insert in the line of: // connStr= engine.connect().connection cursor = connStr.cursor() for index,row in df3.iterrows(): cursor.execute(SOME UPDATE STATEMENT) connStr.commit() cursor.close() connStr.close() // Thanks everyone for any info , regards

Sushant Nair Over a year ago

NameError: name 'TABLE' is not defined// Here you have to replace with your table name

Sushant Nair Over a year ago

index is used for indexing of row data I don't say why loop is failing without index as it should not.

marc_s · Accepted Answer · 2021-01-24 17:04:23Z

0

I found an answer to my solution, after hours of searching:

Update function

connStr= engine.connect().connection
cursor = connStr.cursor()

for index, row in df3.iterrows():
    cursor.execute('''UPDATE [DATABASE].[TABLE] SET [Hour] = ? WHERE [TIMEID] = ?''', (row['Hour'],row['TIMEID']))  
    connStr.commit()
    

cursor.close()
connStr.close()

After hours of trying, its an straight forward syntax error.

I still like to hear about how i can have the solution using the session.query method.

And I am sure if there my above code could be better if some error checking. At the same time if some one can explain why the loop fails without the 'Index' and what it means?

for index, row in df3.iterrows():

Tired but excited.

edited Jan 24, 2021 at 17:04

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Aug 5, 2019 at 17:03

user11883606

291 gold badge1 silver badge5 bronze badges

Collectives™ on Stack Overflow

Update panda dataframe to SQL Server though SQLAlchemy engine using python

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related