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?
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 ?.
IN MYSQL ::
order = 10100
status = 'Shipped'
sql = """SELECT * from orders where orderNumber = %s
and status = %s order by orderNumber"""
df1 = pd.read_sql_query(sql, cnx, params=[order, status])
If you think of your query as a string, you can replace your variable with the actual value of that variable using string formatting first, and then use it in your SQL query.
order = 10100
status = 'Shipped'
query1 = "SELECT * FROM orders WHERE orderNumber = " + str(order) + " AND status = " + status + " ORDER BY orderNumber"
df1 = pandasql.sqldf(query1, locals())
orders.dtypes?