Python MySQLdb execute table variable

Question

I'm trying to use a variable for a table name. I get the error "... near ''myTable'' at line 1 I must not be escaping this right. The double '' in the error seems to be a clue, but I don't get it.

db = MySQLdb.connect("localhost","user","pw","database" )
table = "myTable"
def geno_order(db, table):
    cursor = db.cursor() # prepare a cursor object using cursor() method
    sql = "SELECT * FROM %s"
    cursor.execute(sql, table)
    results = cursor.fetchall()

I am sure there are several duplicates. I picked the one I knew, but if someone else finds one that is even more like the OP's problem by all means use it instead. — mechanical_meat, Mar 06 '13 at 19:03

score 17 · Answer 1 · edited Dec 29 '17 at 20:42

17

You can't use a parameter for the table name in the execute call. You'll need to use normal Python string interpolation for that:

sql = "SELECT * FROM %s" % table
cursor.execute(sql)

Naturally, you'll need to be extra careful if the table name is coming from user input. To mitigate SQL injection, validate the table name against a list of valid names.

edited Dec 29 '17 at 20:42

davidism

121,510
29
395
339

answered Mar 06 '13 at 19:04

Daniel Roseman

588,541
66
880
895

cursor.execute("INSERT INTO {0}(var_name)VALUES({1});".format('table_name','"value"')) #if you need to insert more variables – X.C. Mar 03 '20 at 04:15

Python MySQLdb execute table variable

1 Answers1

Linked

Related