Parameterized create query for pyodbc

Question

I am trying to use a parameterized query to avoid SQL injection for pyodbc ; based on the documentation specified in the below link: https://code.google.com/archive/p/pyodbc/wikis/GettingStarted.wiki#Parameters

Below is the dataframe:

    In [57]: df
 Out[57]:
            TXN_KEY SEND_AGENT  PAY_AGENT
0     13273870  ANO080012  API352676
1     13274676  AUK359401  AED002782
2     13274871  ACL000105  ACB020203
3     13275398  AED420319  ASE094882
4     13278566  ARA030210  AII071196
5     13278955  AYM003098  AHX012817
6     13280334  AJ5020114  AED000438
7     13280512  A11171047  AEL051943
8     13281278  AOG010045  ADJ031448
9     13282118  AMX334165  APM033226
10    13283955  APL170095  AE4082002

 x=df.columns.tolist()
 x
 Out[59]: [u'TXN_KEY', u'SEND_AGENT', u'PAY_AGENT']

Below is my cursor command to create the table in the sql server database:

    cursor.execute("""Create table result (?  bigint  PRIMARY KEY  , ?  varchar(9), ?  varchar(9))""",x[0],x[1],x[2])

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

But

    x[0]
    Out[63]: u'TXN_KEY'...

And so on. Similarly , I need to write a cursor with parameterized query for alter table add column too. I know how to do that for insert , update and select. I thought this would be on similar lines but it is clearly not.

FlipperPA FlipperPA · Accepted Answer · 2016-06-02T20:30:55

It is ugly, but since you're not binding parameters to columns, you'd need to do something like this (I have tested it):

cursor.execute("""Create table result ({} bigint PRIMARY KEY, {} varchar(9), {} varchar(9))""".format(x[0], x[1], x[2]))

I would highly recommend learning more about pyodbc before digging in too much further. The updated documentation is here, anything on Google Code is very out of date: http://mkleehammer.github.io/pyodbc/ It documents what you'll need to do for SELECTs, INSERTs, DELETEs, and UPDATEs. The last three require a commit() statement afterwards. Good luck!

Parameterized create query for pyodbc

2 Answers