0

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?

chen
  • 97
  • 1
  • 10

1 Answers1

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'})
Andreas Ågren
  • 3,879
  • 24
  • 33
  • 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! – chen Feb 26 '20 at 19:41
  • 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: https://stackoverflow.com/a/51790579/114174 – Andreas Ågren Feb 27 '20 at 06:55
  • Updated answer with examples. – Andreas Ågren Feb 27 '20 at 07:03
  • 1
    Thanks a lot! I found the answer to this is to pass python datetime as params. https://stackoverflow.com/questions/60421591/query-database-with-parameter-using-pandas-read-sql – chen Feb 27 '20 at 14:29