CODEWITHSUNDEEP
pythonmysql
Abhilash
Abhilash

Reputation: 2953

python use variable for sql date

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?

Upvotes: 0

Views: 432

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522292

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

Upvotes: 2

Tim Roberts
Tim Roberts

Reputation: 54733

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.

Upvotes: 2

Related Questions