CODEWITHSUNDEEP
pythonpandasprepared-statement
Kumar AK
Kumar AK

Reputation: 1037

How to pass variable values dynamically in pandas sql query

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?

Upvotes: 7

Views: 20689

Answers (3)

wallflower
wallflower

Reputation: 1

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())

Upvotes: 0

Sunita Rawat
Sunita Rawat

Reputation: 56

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])

Upvotes: 3

unutbu
unutbu

Reputation: 880389

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 ?.

Upvotes: 16

Related Questions