bind pandas dataframe rows to sqlAlchemy custom query

Question

I have a existing data table named table below:

column_a column_b column_c

  1         2       "r1"
  4         5       "r2"
  0         0       "r3"

I want to update the data to table with a panda dataframe below:

column_a column_b column_c

  7         8       "r1"
  9         10      "r2"

How do pass the value to the parameters in the query I So the result should look like this below?

column_a column_b column_c

  7         8       "r1"
  9         10      "r2"
  0         0       "r3"

I can only manage to manually enter the value in the sqlAchemy function which show below, I do not know how to use python apply function to apply all the rows in the panda dataframe. Thanks in advance!

The SqlAlchemy custom query I'm using:

with engine.connect() as connection:
   connection.execute(text("UPDATE table SET column_a=:val1, column_b=:val2 WHERE column_c = :val3" ),
            {'val1': 7, 'val2': 8, 'val3': "r1"})

wwnde · Accepted Answer · 2021-12-15 21:56:06Z

1

Use upsert like you would just do normally.

conn =#Put here your database details

df = pd.read_csv()#Read the pandas table
df.to_sql('stagingTable', conn, if_exists='append', index = False)#Write pandas table as a staging table

#SQL Query    
sql = '''

declare @TrackingTable table(Change varchar(30))
MERGE TabletoUpdate  AS target
USING stagingTable AS source
ON (target.column_c = source.column_c)
WHEN NOT MATCHED BY target 
   THEN  INSERT (column_c, column_b, column_a)  
         VALUES (source.column_c, source.column_b, source.column_a)
WHEN MATCHED   
   THEN UPDATE SET 
        column_b      = source.column_b,
        column_a = source.column_a
OUTPUT $action into @TrackingTable;
COMMIT;
select Change, count(*) impacted
from @TrackingTable
group by Change

'''
conn.execute(sql)

answered Dec 15, 2021 at 21:56

wwnde

26.7k6 gold badges22 silver badges38 bronze badges

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

1 Comment

Sun Tianyi Over a year ago

I'm using mysql, not transection-sql, but I think I got your idea. I'll try to figure out how to do it in mysql query. It should use INSERT with ON DUPLICATE KEY UPDATE from what I researched.

Collectives™ on Stack Overflow

bind pandas dataframe rows to sqlAlchemy custom query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related