Formatting for pyodbc INSERT INTO query SQL server

Question

I've been having trouble with 1 particular query for the past week. I can't find anything online for this specific situation (or atleast I'm not searching the right thing) but surely this is something somebody has done before. I'm generating a new table on each cycle (with an incrementing ID/key column, and 3 columns for my data) so the table name changes, then putting in some data (all integers). The line causing the error is:

cursor.execute(''' INSERT INTO ? (column1, column2, column3) VALUES (?, ?, ?) ''' , tablename, rowData[0], rowData[1], rowData[2])

The error I'm getting is:

('42000', '[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the table variable "@P1". (1087) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (8180)')

I previously tried using the usual python string formatting, with a dictionary '''INSERT INTO %(table)s VALUES %(list0)i, %(list1)i, %(list2)i ''' % {"table":tablename, "list0":list[0], "list1":list[1], "list2":list[2]}) and some variations of the 2 formats but those have resulted in errors too.

Any help would be appreciated.

is more easy if you use procedures

GiovaniSalazar
– GiovaniSalazar

2019-11-06 21:59:55 +00:00
Commented Nov 6, 2019 at 21:59 — GiovaniSalazar
– GiovaniSalazar, Commented Nov 6, 2019 at 21:59

Gord Thompson · Accepted Answer · 2019-11-06 22:22:07Z

2

You're right in thinking that you should be using a parameterized query, but query parameters can only be used to insert column values, not column/table names. So you need to use a mix of dynamic SQL and query parameterization:

sql = "INSERT INTO [%s] (column1, column2, column3) VALUES (?, ?, ?)" % tablename
cursor.execute(sql, rowData[0], rowData[1], rowData[2])

answered Nov 6, 2019 at 22:22

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

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

1 Comment

JWatt Over a year ago

Ah, I see. Thank you so much. This is annoyingly close to one of the variations I tried too

Collectives™ on Stack Overflow

Formatting for pyodbc INSERT INTO query SQL server

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