Description:
I want to execute the following query, together with a parameter binding:
cur.execute(
'select date_format(%s, "%Y-%m-%d %H:%i:%s")',
("2017-06-15 12:20:23", ),
)
This results in an error:
mysql.connector.errors.ProgrammingError: Not enough parameters for the SQL statement
The parameter marker looks like a C printf-style variant, so I try to escape the `%s` by using `%%s` instead:
cur.execute(
'select date_format(%s, "%Y-%m-%d %H:%i:%%s")',
("2017-06-15 12:20:23", ),
)
But it still leads to the same error message.
How can I include the date format string in a query like the above? What is the correct way to escape the parameter marker? It would seem that the usual way when escaping C printf-style markers does not work.
mysql-connector-python version: 9.1.0
Python version: 3.13.1
MySQL version: 9.1.0
How to repeat:
import mysql.connector
def main():
# Connect to server
cnx = mysql.connector.connect(
host="127.0.0.1",
port=3306,
user="root",
password="root",
)
# Get a cursor
cur = cnx.cursor()
# Execute a query
cur.execute(
'select date_format(%s, "%Y-%m-%d %H:%i:%%s")',
("2017-06-15 12:20:23", ),
)
# Fetch one result
row = cur.fetchone()
print(row[0])
# Close connection
cnx.close()
if __name__ == "__main__":
main()
Suggested fix:
Recognize when a parameter marker is escaped
Description: I want to execute the following query, together with a parameter binding: cur.execute( 'select date_format(%s, "%Y-%m-%d %H:%i:%s")', ("2017-06-15 12:20:23", ), ) This results in an error: mysql.connector.errors.ProgrammingError: Not enough parameters for the SQL statement The parameter marker looks like a C printf-style variant, so I try to escape the `%s` by using `%%s` instead: cur.execute( 'select date_format(%s, "%Y-%m-%d %H:%i:%%s")', ("2017-06-15 12:20:23", ), ) But it still leads to the same error message. How can I include the date format string in a query like the above? What is the correct way to escape the parameter marker? It would seem that the usual way when escaping C printf-style markers does not work. mysql-connector-python version: 9.1.0 Python version: 3.13.1 MySQL version: 9.1.0 How to repeat: import mysql.connector def main(): # Connect to server cnx = mysql.connector.connect( host="127.0.0.1", port=3306, user="root", password="root", ) # Get a cursor cur = cnx.cursor() # Execute a query cur.execute( 'select date_format(%s, "%Y-%m-%d %H:%i:%%s")', ("2017-06-15 12:20:23", ), ) # Fetch one result row = cur.fetchone() print(row[0]) # Close connection cnx.close() if __name__ == "__main__": main() Suggested fix: Recognize when a parameter marker is escaped