Passing table name as a parameter in pyodbc

Question

I am trying to pass a table name in pyodbc as a parameter to access data from ms sql 2005. I've tried to substitute it with ? but it never works. I would be glad to receive any advice on how to accomplish this.

score 5 · Answer 1 · answered Aug 10 '11 at 16:58

5

Since you are using pyodbc, I assume you are not calling a SPROC. I recommend building your SQL string in python and then passing it to SQL to execute.

import pyodbc
dbconn = pyodbc.connect(ConnectionString)
c = dbconn.cursor()

j = 'table1' #where table1 is entered, retreived, etc
query = "Select * from %s" % j
c.execute(query)
result = c.fetchall()
print 'Done'

answered Aug 10 '11 at 16:58

JMoney

321
1
2
6

3

Just be careful that the table name is not coming from user input or browser feedback - that's the setup for SQL injection. – Mark Ransom Aug 10 '11 at 17:03
@Mark, you make an excellent point. But table names at least are somewhat easier to sanitize than certain other types of input into SQL. – TimothyAWiseman Aug 10 '11 at 17:07
2

This is a quite old answer. Does anyone know if pyodbc has added some safer way to do this by now? Something similiar to [`psycopg2.sql.SQL`](http://initd.org/psycopg/docs/sql.html#psycopg2.sql.SQL) template objects, maybe? (psycopg2 is a PostgreSQL database adapter for Python) – Håken Lid Aug 23 '19 at 17:49

score 2 · Answer 2 · answered Jul 12 '11 at 18:20

2

You can't have variable as a table name SQL Server. You need to use dynamic SQL for that to work.

Look here for how to deal with dynamic table names. http://www.sommarskog.se/dynamic_sql.html#objectnames

answered Jul 12 '11 at 18:20

Mikael Eriksson

136,425
22
210
281

I found the sweet way to do it! I will share soon! – Siv Niznam Jul 12 '11 at 19:26

Passing table name as a parameter in pyodbc

2 Answers2

Linked