0

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.

mysterious_guy
  • 425
  • 11
  • 23
  • Use parameters to pass the `df` values to SQL Server, instead of trying to embed them in the update statement. See my answer [here](http://stackoverflow.com/a/9521382/366335) for details - it references a select query but the steps are the same for an update. – Bryan Jun 01 '16 at 18:59
  • @Bryan having a hard time following where exactly in your solution are parameters being used to pass to the df. Can you point out how to do that in my example? – mysterious_guy Jun 01 '16 at 19:31

1 Answers1

2

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!

FlipperPA
  • 13,607
  • 4
  • 39
  • 71
  • any idea on how to parameterise create : cursor.execute("""Create table result ? bigint Primary Key, ? varchar(9), ? varchar(9)""",x[0],x[1],x[2] ) x = df.columns.tolist() ProgrammingError: ('42000', "[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near '@P1'. (102) (SQLExecDirectW)") – mysterious_guy Jun 02 '16 at 19:07