Read database into dataframe using pyodbc with parameterized query

Question

I'm using pyodbc to get data from a SQL Server, using the script shown here:

conn = pyodbc.connect('DSN=DATASOURCE')
tbl = "SELECT TableA.Field_1 \
    FROM TableA \
    WHERE TableA.Date>=2019/04/01"
SQL_Query = pd.read_sql_query(tbl, conn)
conn.close

Now I want to make this query into a Python function, where I can change the date (2019/04/01) in the example above as a function variable.

I found pyodbc offers parameterization, but all in the context of cursor.execute function.

Ideally, I'd like to create a function like this:

def DB_Query(date):
    conn = pyodbc.connect('DSN=DATASOURCE')
    tbl = "SELECT TableA.Field_1 \
    FROM TableA \
    WHERE TableA.Date>=?", date
    SQL_Query = pd.read_sql_query(tbl, conn)
    conn.close
    return SQL_Query

Apparently this doesn't work because tbl has to be a normal string, but is it possible to use pyodbc's parameterization feature together with pandas' pd.read_sql_query or pd.read_sql?

Andreas Ågren · Accepted Answer · 2020-02-27 07:02:46Z

2

You can parameterize read_sql_query in the same way as cursor.execute by setting the params parameter: https://pandas.pydata.org/docs/reference/api/pandas.read_sql_query.html

Example for SQL Server:

import pandas as pd

sql = '''
 select *
 from Table
 where Column = ?
'''
df = pd.read_sql(sql, params=[query_param])

Example for Oracle:

import pandas as pd

sql = '''
 select *
 from table
 where Column = :query_param
'''
df = pd.read_sql(sql, params={'query_param': 'query_value'})

edited Feb 27, 2020 at 7:02

answered Feb 24, 2020 at 21:45

Andreas Ågren

3,94926 silver badges33 bronze badges

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

4 Comments

chen Over a year ago

This worked on SQL server as DB driver, now my DB driver changed to Microsoft ODBC for Oracle. Do you happen to know how to parameterize in this context? Apparently use "?" in the query and add params argument in pd.read_sql(params) doesn't work in this case. ? is a DATE in this case. Thanks!

Andreas Ågren Over a year ago

Parameterization in a query is usually db specific as you've found out and I'm not very familiar with Oracle, but it seems you need to prefix the parameter name in the query with a colon, e.g. ":param1", and then you probably need to set the param parameter to a dictionary using the same name (without colon) and a value. Here's an example: stackoverflow.com/a/51790579/114174

Andreas Ågren Over a year ago

Updated answer with examples.

chen Over a year ago

Thanks a lot! I found the answer to this is to pass python datetime as params. stackoverflow.com/questions/60421591/…

Collectives™ on Stack Overflow

Read database into dataframe using pyodbc with parameterized query

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related