python带参数mysql查询_Python MySQL参数查询动态表名称

I want to query MySQL tables using my python program. However, the tables that I want to query are the ones whose names are passed in as variables (parameters).

I tried something like this:

readTable_query = """SELECT * FROM %s"""

readTable_cursor.execute(readTable_query, (table_name))

The problem with this is that the dynamically generated SQL query is putting the table name in quotes, i.e. instead of generating a query like "SELECT * FROM products", its generating a query like "SELECT * FROM 'products'". This is causing my program to fail and throws errors. Is there a workaround?

I know I could concatenate the query but that would lead to a security risk of SQL injection.

Full code for reference:

import MySQLdb

table_name = 'products'

db_connection = MySQLdb.connect(host,user,passwd,db)

readTable_cursor = db_connection.cursor()

readTable_query = """SELECT * FROM %s"""

readTable_cursor.execute(readTable_query, (table_name))

解决方案

According to http://dev.mysql.com/doc/refman/5.0/en/identifiers.html , valid table names consist of the following characters. [0-9a-zA-Z_$] So, it shouldn't be hard to write your own validator:

import re

table_name_validator = re.compile(r'^[0-9a-zA-Z_\$]+$')

if not table_name_validator.match(table_name):

raise ValueError('Hey! No SQL injecting allowed!')