Write variable after reading .sql query-CodePudding

When I have to pass a parameter before running a sql query, I usually do

date = '20220101'

query = f'''SELECT * FROM TABLE WHERE DATE = '{date}''''

On an attempt to reduce the lenght of code, I created a query.sql file with the query above but I'm failing to pass the date variable inside my query, before running the sql.

For reading I'm using

sql_query = open("query.sql", "r")
sql_as_string = sql_query.read()

df = pd.read_sql(sql_as_string, conn)

Is there a way around, instead of pasting the whole SQL query at my .py code?

I'm using pyodbc, ODBC Driver 17 for SQL Server

CodePudding user response：

Use a parametrized query, not string formatting.

The file should just contain the query, with a ? placeholder for the variable.

SELECT * FROM TABLE WHERE DATE = ?

Then you can do

with open("query.sql", "r") as f:
    sql_query = f.read()

df = pd.read_sql(sql_query, conn, params=(date, ))