Struggling to use one variable for multiple parameterization markers in pandas-CodePudding

Using pandas I am doing a parametrized sql query shown in the snippet of code below. I have one variable a date that I am feeding in as a marker for both the inception and expiration in the where clause, but I don't want to have to add this in twice as a parameter as it is the same variable. I'm sure there is a way around this but I can't find it.

import pandas as pd
import datetime

dt = datetime.date(2021, 10, 1)

select_sql = """Select Inception, Expiry From TableTest Where Inception <= ? and Expiry >= ?"""

sql_query = pd.read_sql_query(select_sql, conn, params=[dt,dt])

CodePudding user response：

You could use Python's f-string syntax to create the query string that you want to pass to the SQL function. The date values need to be surrounded by quote marks in the SQL query.

select_sql = f"SELECT Inception, Expiry FROM TableTest WHERE Inception <= '{dt}' AND Expiry >= '{dt}'"

print(select_sql)

SELECT Inception, Expiry FROM TableTest WHERE Inception <= '2021-10-01' AND Expiry >= '2021-10-01'

Then you can pass this query string to the pandas function with pd.read_sql_query(select_sql, conn), as you don't need the params argument anymore with this approach.