How to dynamically bind multiple parameters on SQL query in python?

Question

So, I've a table (say table_name ) and it columns are:

|  A   |  B   |  C   |
------------------------
|  a1  |  b1  |  c1  |
|  a2  |  b2  |  c2  |
   ...

Now i have to read a column data using following query:

import pandas as pd
import pymysql as pycon

con = pycon.connect(host='localhost',user='root',password='', db='database')

#Error in here
df= pd.read_sql_query("SELECT A from table_name where B = %s and C != %s" %variableB %variableC, con)

But I've encountered an error in read_sql_query(...) maybe the query format is wrong because dynamically binded single parameter works fine i.e

df= pd.read_sql_query("SELECT A from table_name where B = %s" %variableB, con)

works w/o error. Can anyone help me with the query?

Try df = pd.read_sql_query("SELECT A from table_name where B = %s and C != %s" % (variableB, variableC), con) — Zero
– Zero, Commented Aug 6, 2017 at 8:26

realpac · Accepted Answer · 2017-10-25 14:00:28Z

3

In case someone ran into same problem, the correct code for query was with a '%s' instead of %s

df= pd.read_sql_query("SELECT A from table_name where B = '%s' and C != '%s'" % (variableB, variableC), con)

the code below gave me pymysql.err.InternalError.

df= pd.read_sql_query("SELECT A from table_name where B = %s and C != %s" % (variableB, variableC), con)

Thank you stackOverflow :)

edited Oct 25, 2017 at 14:00

answered Aug 6, 2017 at 8:37

realpac

5726 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Wonjin · Accepted Answer · 2017-08-06 08:26:57Z

1

when you bind your multiple variable into String, syntax should be like this

df= pd.read_sql_query("SELECT A from table_name where B = %s and C != %s" % (variableB, variableC), con)

answered Aug 6, 2017 at 8:26

Wonjin

4323 silver badges11 bronze badges

1 Comment

realpac Over a year ago

It gave me pymysql.err.InternalError working now. All i had to do was replace %s with '%s' in your code. I have posted a solution as answer below. Thanks for your help

Collectives™ on Stack Overflow

How to dynamically bind multiple parameters on SQL query in python?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related