How to use DATE_FORMAT in the where clause using mysql.connector ? It produces Not enough parameters for the SQL statement error

Question

_SQL = """SELECT u.first_name, u.last_name, 
                 DATE_FORMAT(a.arrival_time, '%H:%i:%s') AS arrival_time, 
                 DATE_FORMAT(a.leave_time, '%H:%i:%s') AS leave_time 
          FROM attendance a JOIN users u ON (a.user_id = u.user_id) 
          WHERE DATE_FORMAT(a.arrival_time, '%Y-%m-%d') = %s"""

cursor.execute(_SQL, (str(datetime.date.today()), ))

Hello, executing this query produces an error: mysql.connector.errors.ProgrammingError: Not enough parameters for the SQL statement

the query seems to be correct in my mind. Could anyone what is wrong in the query? Help would be appreciated

you can [check this](https://stackoverflow.com/questions/26393895/python-how-to-escape-s) out. — Barbaros Özhan, Feb 26 '21 at 13:56
In your format strings, use `%%s` to escape the `%` or `%S` as suggested in @Georg answer. — Nick, Feb 27 '21 at 00:22

score 0 · Answer 1 · answered Feb 26 '21 at 22:35

It is a bug in the parser of MySQL Connector Python, which tries to substitute the %s in date_format function with a parameter. Due to this bug it expects 3 parameters in total while only one parameter was specified in the parameter tuple.

As a work around you could specify the format parameter for seconds in date_format function in uppercase which should solve the problem: DATE_FORMAT(time, '%H:%i:%S')

How to use DATE_FORMAT in the where clause using mysql.connector ? It produces Not enough parameters for the SQL statement error

1 Answers1