CODEWITHSUNDEEP
sql-server-2005pandas
Bee
Bee

Reputation: 341

Passing query parameters in Pandas

Trying to write a simple pandas script which executes a query from SQL Server with WHERE clause. However, the query doesnt return any values. Possibly because the parameter is not passed? I thought we could pass the key-value pairs as below. Can you please point out what i am doing wrong here?

Posting just the query and relevant pieces. All the libraries have been imported as needed.

  curr_sales_month = '2015-08-01'
  sql_query = """SELECT sale_month,region,oem,nameplate,Model,Segment,Sales  FROM [MONTHLY_SALES] WHERE Sale_Month = %(salesmonth)s"""
    print ("Executed SQL Extract", sql_query)

    df = pd.read_sql_query(sql_query,conn,params={"salesmonth":curr_sales_month})

The program returned with: Closed Connection - Fetched 0 rows for Report

    Process finished with exit code 0

Upvotes: 2

Views: 5506

Answers (1)

Dickster
Dickster

Reputation: 3009

Further to my comment. Here is an example that uses pyodbc to communicate to sql server and demonstrates passing a variable.

import pandas as pd
import pyodbc
pd.set_option('display.max_columns',50)
pd.set_option('display.width',5000)

conn_str = r"DRIVER={0};SERVER={1};DATABASE={2};UID={3};PWD={4}".format("SQL Server",'.','master','user','pwd')
cnxn = pyodbc.connect(conn_str)
sql_statement = "SELECT * FROM sys.databases WHERE database_id = ?"
df = pd.read_sql_query(sql = sql_statement, con = cnxn, params = [2])
cnxn.close()
print df.iloc[:,0:2].head()

which produces:

     name  database_id 
0  tempdb            2

And if you wish to pass multiple parameters:

sql_statement = "SELECT * FROM sys.databases WHERE database_id > ? and database_id < ?"
df = pd.read_sql_query(sql = sql_statement, con = cnxn, params = [2,5])
cnxn.close()
print df.iloc[:,0:2].head()

which produces:

    name  database_id
0  model            3
1   msdb            4

my preferred way with dynamic inline sql statements

create_date = '2015-01-01'
name = 'mod'

sql_statement_template = r"""SELECT * FROM sys.databases WHERE database_id > {0} AND database_id < {1} AND create_date > '{2}' AND name LIKE '{3}%'"""
sql_statement = sql_statement_template.format('2','5',create_date,name)
print sql_statement

yields

SELECT * FROM sys.databases WHERE database_id > 2 AND database_id < 5 AND create_date > '2015-01-01' AND name LIKE 'mod%'

A further benefit if you do print this out, is you can copy and paste the sql commnand to management studio (or equivalent) and test your sql syntax easily.

and result should be:

    name  database_id
0  model            3

So this example demonstrates handling: date,string and int datatypes. Including a LIKE with wildcard %

Upvotes: 4

Related Questions