CODEWITHSUNDEEP
pandaspyodbc
mysterious_guy
mysterious_guy

Reputation: 435

PYODBC cursor to update the table in SQL database

Below is a small sample of my dataframe

    In [121]: df
    Out[121]:
  TXN_KEY SEND_AGENT  PAY_AGENT
0  13273870  ANO080012  API352676
1  13274676  AUK359401  API352676
2  13274871  ACL000105  API352676
3  13275398  AED420319  API352676
4  13278566  ARA030210  API352676
5  13278955  AYM003098  API352676
6  13280334  AJ5020114  API352676
7  13280512  A11171047  API352676
8  13281278  AOG010045  API352676
9  13282118  AMX334165  API352676

I am trying to use a cursor in pyodbc to update the table "result" in sql server database with dataframe values

cursor.execute("Update result set Pay_Agent = df['PAY_AGENT'][0] where Send_Agent = df['SEND_AGENT'][0]")

I get below error:

 ProgrammingError: ('42000', "[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near ''PAY_AGENT''. (102) (SQLExecDirectW)")

But I am making sure that datatypes are properly matched in my update statement.

In [126]: type(df['PAY_AGENT'][0])
Out[126]: str

In [127]: type(df['SEND_AGENT'][0])
Out[127]: str

Any insights are appreciated.

Upvotes: 0

Views: 3035

Answers (1)

FlipperPA
FlipperPA

Reputation: 14361

You'll have to format your query properly using parameters:

cursor.execute("Update result set Pay_Agent = ? where Send_Agent = ?", (df['PAY_AGENT'][0], df['SEND_AGENT'][0]))

Using bound parameters in this fashion is important in any language you use to avoid possible SQL injection. Good luck!

Upvotes: 2

Related Questions