How can I use multiple parameters using pandas pd.read_sql_query?

Question

I am trying to pass three variables in a sql query. These are region, feature, newUser. I am using SQL driver SQL Server Native Client 11.0.

Here is my code that works.

query = "SELECT LicenseNo FROM License_Mgmt_Reporting.dbo.MATLAB_NNU_OPTIONS  WHERE Region = ?"

data_df = pd.read_sql_query((query),engine,params={region})

output.

     LicenseNo
 0           12
 1            5

Instead i want to pass in three variables and this code does not work.

query = "SELECT LicenseNo FROM License_Mgmt_Reporting.dbo.MATLAB_NNU_OPTIONS WHERE Region = ? and FeatureName = ? and NewUser =?"

nnu_data_df = pd.read_sql_query((query),engine,params={region, feature, newUser})

Output returns an empty data frame.

Empty DataFrame
Columns: [LicenseNo]
Index: []

Steven G · Accepted Answer · 2016-10-21 16:51:24Z

8

try a string in a tuple, also you can take out the () in the query:

so you could do something like

query = "SELECT LicenseNo FROM License_Mgmt_Reporting.dbo.MATLAB_NNU_OPTIONS WHERE Region = ? and FeatureName = ? and NewUser =?"
region = 'US'
feature = 'tall'
newUser = 'john'
data_df = pd.read_sql_query(query, engine, params=(region, feature , newUser))

answered Oct 21, 2016 at 16:51

Steven G

17.3k11 gold badges57 silver badges79 bronze badges

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

4 Comments

mattk Over a year ago

Hi Steven - I converted region, feature, and newUser to strings and used your syntax and still returned an empty data frame.

mattk Over a year ago

query = "SELECT LicenseNo FROM License_Mgmt_Reporting.dbo.MATLAB_NNU_OPTIONS WHERE Region = ? and FeatureName = ? and NewUser =?" Region = str(region) FeatureName = str(feature) NewUser = str(newUser) nnu_data_df = pd.read_sql_query(query,engine, params=(Region, FeatureName, NewUser))

mattk Over a year ago

sorry Steven I forgot to tag you

pacman Over a year ago

Can this work even with snowflake sql? even after setting snowflake.connector.paramstyle='qmark' its not working for me.

mattk · Accepted Answer · 2016-10-21 17:25:51Z

0

Operator error by me :( I was using the wrong variable and the database returned no results because it didn't exist!

answered Oct 21, 2016 at 17:25

mattk

1031 gold badge1 silver badge7 bronze badges

Collectives™ on Stack Overflow

How can I use multiple parameters using pandas pd.read_sql_query?

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related