How insert variables into SQL query in Python?

Question

I want to get data from the SQL database, however, sometimes the filter condition in SQL query should be changed (dates).

Python code is working when filters of the dates are inside query:

import vertica_python
conn_info = {'host': 'unreachable.server.com',
 'port': 888,
 'user': 'some_user',
 'password': 'some_password',
 'database': 'a_database',
 'backup_server_node': ['123.456.789.123', 'invalid.com', ('10.20.82.77', 6000)]}
connection = vertica_python.connect(**conn_info)
cur = connection.cursor()
start_date = '2020-06-25'
end_date = '2020-07-25'
cur.execute("""
SELECT price as price, volume as volume
FROM My_DB
WHERE START_TS >= '2020-06-25'
and START_TS <= '2020-07-25'
ORDER BY price
 """) 
df = pd.DataFrame(cur.fetchall())

However, I want to replace dates with variables start_date and end_date I tried following approaches with format and f-type strings, however there was an error (Query error).

cur.execute(f"""
SELECT price as price, volume as volume
FROM My_DB
WHERE START_TS >= {start_date }
and START_TS <= {end_date }
ORDER BY price
 """) 
df = pd.DataFrame(cur.fetchall())

and

cur.execute("""
SELECT price as price, volume as volume
FROM My_DB
WHERE START_TS >= {}
and START_TS <= {}
ORDER BY price
 """.format(start_date ,end_date )) 
df = pd.DataFrame(cur.fetchall())

• user5386938: pandas.read_sql with params=(start_date ,end_date) is better than building your SQL statement which leaves you open to SQL injection. Am on phone so cannot post an answer.

Answer 1

Use quotes to enclose your variables: '{start_date }' and '{end_date }'

cur.execute(f"""
SELECT price as price, volume as volume
FROM My_DB
WHERE START_TS >= '{start_date }'
and START_TS <= '{end_date }'
ORDER BY price
 """) 
df = pd.DataFrame(cur.fetchall())

Answer 2

Avoid SQL injection

connection = vertica_python.connect(**conn_info)
start_date = '2020-06-25'
end_date = '2020-07-25'
# not sure about the %s placeholder; you may need to use ? instead
# the engine takes care of the quoting (if required) for you
sql = """
 SELECT price as price, volume as volume
 FROM My_DB
 WHERE START_TS >= %s
 AND START_TS <= %s
 ORDER BY price
 """
df = pd.readsql(sql, connection, params=(start_date, end_date))