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())

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. — user5386938
– user5386938, Commented Jul 16, 2021 at 8:18

Corralien · Accepted Answer · 2021-07-16 07:57:46Z

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())

answered Jul 16, 2021 at 7:57

Corralien

121k8 gold badges43 silver badges68 bronze badges

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

Comments

user5386938 · Accepted Answer · 2021-07-16 12:34:39Z

1

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))

answered Jul 16, 2021 at 12:34

user5386938

Collectives™ on Stack Overflow

How insert variables into SQL query in Python?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related