Pyodbc cursor to loop through a dataframe and insert in sql

Question

Below are my SQL TABLE and DATAFRAME:

    TXN_KEY SEND_AGENT  Pay_Agent
13273870    ANO080012   NULL
13274676    AUK359401   NULL
13274871    ACL000105   NULL
13275398    AED420319   NULL
13278566    ARA030210   NULL
13278955    AYM003098   NULL
13280334    AJ5020114   NULL
13280512    A11171047   NULL
13281278    AOG010045   NULL
13282118    AMX334165   NULL

In [212]: df
Out[212]:
TXN_KEY SEND_AGENT  PAY_AGENT
13273870  ANO080012  API352676
13274676  AUK359401  AED002782
13274871  ACL000105  ACB020203
13275398  AED420319  ASE094882
13278566  ARA030210  AII071196
13278955  AYM003098  AHX012817
13280334  AJ5020114  AED000438
13280512  A11171047  AEL051943
13281278  AOG010045  ADJ031448
13282118  AMX334165  APM033226

Below cursor statement is adding the first value of Pay_Agent in sql server just fine

    cursor.execute("""update result SET Pay_Agent = ? WHERE SEND_AGENT = ?""",df['PAY_AGENT'][0],df['SEND_AGENT'][0])

13273870    ANO080012   API352676
13274676    AUK359401   NULL
13274871    ACL000105   NULL
13275398    AED420319   NULL
-------

How do I code this cursor statement in a for loop to iterate over the entire dataframe and post values to SQL server.

MaxU - stand with Ukraine · Accepted Answer · 2016-06-02 16:00:07Z

2

i would recommend to do it differently:

save your df as SQL Server table (let's say: tmp_result)

df.to_sql('tmp_result', conn, if_exists='replace')

and then update your table as follows:

update result 
  set result.Pay_Agent = tmp_result.Pay_Agent
FROM result
INNER JOIN tmp_result
  on result.SEND_AGENT = tmp_result.SEND_AGENT;

so that you will achieve your goals without looping

answered Jun 2, 2016 at 16:00

MaxU - stand with Ukraine

212k37 gold badges402 silver badges436 bronze badges

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

1 Comment

JCM Over a year ago

This overwrites the table structure though. When I tried this the data types were overridden from int to varchar(max).

mysterious_guy · Accepted Answer · 2016-06-02 15:51:15Z

1

Below is the way to loop through the dataframe:

     for index,rows in df.iterrows():
      cursor.execute("""update result SET Pay_Agent = ? WHERE SEND_AGENT = ?""",df['PAY_AGENT'][rownum],df['SEND_AGENT'][rownum])
     rownum+=1

I am sure this will help someone else trying to achieve the same.

answered Jun 2, 2016 at 15:51

mysterious_guy

4351 gold badge11 silver badges24 bronze badges

Collectives™ on Stack Overflow

Pyodbc cursor to loop through a dataframe and insert in sql

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related