python - How to pass variable values dynamically in pandas sql query

Question

How to pass variable parameters dynamically

order = 10100

status = 'Shipped'

df1 = pd.read_sql_query("SELECT  * from orders where orderNumber =""" +  
str(10100) + """ and status = """ + 'status' +"""  order by orderNumber """,cnx)

TypeError: must be str, not int

getting above error although i converted to strings any idea?

is there any alternative wy to pass the parameters?

Answer 1

Use parametrized sql by supplying the arguments via the params keyword argument. The proper quotation of arguments will be done for you by the database adapter and the code will be less vulnerable to SQL injection attacks. (See Little Bobby Tables for an example of the kind of trouble improperly quoted, non-parametrized sql can get you into.)

order = 10100

status = 'Shipped'

sql = """SELECT  * from orders where orderNumber = ?
         and status = ? order by orderNumber"""
df1 = pd.read_sql_query(sql, cnx, params=[order, status])

The ?s in sql are parameter markers. They get replaced with properly quoted values from params. Note that the proper parameter marker depends on the database adapter you are using. For example, MySQLdb and psycopg2 uses %s, while sqlite3, and oursql uses ?.