python use variable for sql date

Question

I have a SQL query that fetches records between 2 dates

my_table_name='adbtable'
sql_query = '''SELECT * FROM {} WHERE date_time BETWEEN '2021/02/05'  and '2021/04/28' ORDER BY date_time DESC'''.format(my_table_name)

The table name variable is successfully getting interpolated in the SQL statement and fetches the data properly.

But when I tried to add interpolation to the 2 dates am getting an error

my_table_name='adbtable'
start_date='2021/02/05'
end_date='2021/04/28'
sql_query = '''SELECT * FROM {} WHERE date_time BETWEEN {}  and {} ORDER BY date_time DESC'''.format(my_table_name, start_date, end_date)

This is the error am getting.

HINT: No operator matches the given name and argument types. You might need to add explicit type casts.

But how can we cast the dates here if that's the issue?

Tim Roberts · Accepted Answer · 2021-05-02 06:26:27Z

2

The .format command doesn't do quoting. You either need to write BETWEEN '{}' AND '{}' or let the connector do substitution:

sql_query = '''SELECT * FROM {} WHERE date_time BETWEEN %s  and %s ORDER BY date_time DESC'''.format(my_table_name)
q = cursor.execute( sql_query, (start_date, end_date))

In general, it's best to allow the SQL connector to do substitutions for field values. You DON'T do that for table names and field names.

answered May 2, 2021 at 6:26

Tim Roberts

55.3k4 gold badges28 silver badges41 bronze badges

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

Comments

Tim Biegeleisen · Accepted Answer · 2021-05-02 06:26:43Z

2

You should be using a prepared statement here, to which you bind the date literal values from your Python script. Assuming you are using Connector/Python, you may try:

import mysql.connector
from mysql.connector.cursor import MySQLCursorPrepared

cursor = cnx.cursor(prepared=True)
my_table_name = 'adbtable'
sql_query = 'SELECT * FROM {} WHERE date_time BETWEEN %s AND %s ORDER BY date_time DESC'.format(my_table_name)
cursor.execute(stmt, ('2021-02-05', '2021-04-28',))
# access result set here

answered May 2, 2021 at 6:26

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Collectives™ on Stack Overflow

python use variable for sql date

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