Search code examples
pythonmysqlmysql-connector-python

Using Wildcards in SQL Query with mysql-connector-python Python 3.6


I have a SQL query that works fine when copy pasted into my Python code. There is a line with a parameter that I want to make a variable in my Python script,

AND TimeStamp like '%2017-04-17%'

So I set a variable in the Python script:

mydate = datetime.date(2017, 4, 17) #Printing mydate gives 2017-04-17

and change the line in the query to:

AND TimeStamp like %s

Firstly, when I run the script with the date copy pasted in the query:cursor.execute(query) gives no errors and I can print the results with cursor.fetchall()

When I set the date to the variable mydate and use %s and try to run the script, any of these will give me an error:

cursor.execute(query,mydate) #"You have an error in your SQL Syntax..."   
cursor.execute(query, ('%' + 'mydate' + '%',)) #"Not enough parameters for the SQL statement"
cursor.execute(query, ('%' + 'mydate' + '%')) #"You have an error in your SQL Syntax..."
cursor.execute(query, ('%' + mydate + '%')) #"must be str, not datetime.date

"

I simply want '%2017-04-17%' where the %s is.


Solution

  • If the value in your MySQL table is of type TIMESTAMP then you will need to do a

    SELECT whatever FROM table where TimeStamp beween '2017-04-17' and '2017-04-18'

    Having created your initial datetime.date value then you need to use a datetime.timedelta(days=1) on it.