0

I am writing a Python3 script to work against a MySQL database. Using mysql.connector functionality.

I am trying to write a DELETE command. I can get it to work with a static string but I cannot get it to work with a parameterized statement.

This one works:

sql = "DELETE FROM mytable where report_dt < '" + str(report_date) + "'"
try:
    mycursor.execute(sql)
    mydb.commit()

This one fails:

sql = "DELETE FROM mytable where report_dt < '%s'"
try:
    mycursor.execute(sql, report_date)
    mydb.commit()

The report_date var comes from datetime.datetime.now() and is formatted as follows:

str(report_date) returns: ‘2020-06-09 10:59:11’

No error message… it just doesn’t actually delete the rows it is supposed to.

Rob Bell
  • 1
  • 2
    As far as I can tell from [the docs](https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html) this needs a tuple, and also doesn't need quotation marks around the formatted part of the SQL. Does it work if you remove the quotation marks in this part of the sql `< '%s'"` and change `mycursor.execute(sql, report_date)` to `mycursor.execute(sql, (report_date,))`? – Minion3665 Jun 09 '20 at 16:27
  • 1
    What pkg are you using? You might find your answer in this post: https://stackoverflow.com/questions/902408/how-to-use-variables-in-sql-statement-in-python – FaLua Jun 09 '20 at 16:45
  • Removing the single quotes results in a SQL error. Same thing when I was just typing the static command into MySQLWorkbench. – Rob Bell Jun 09 '20 at 17:22

2 Answers2

0

Either of these should do it.

sql = "DELETE FROM mytable WHERE report_dt < %(date)s;"
try:
    mycursor.execute(sql, {'date': str(report_date)})
    mydb.commit()
except:
    print(f"Error deleting report from {report_date}.")

OR

sql = "DELETE FROM mytable WHERE report_dt < %s;"
try:
    mycursor.execute(sql, [str(report_date)])
    mydb.commit()
except:
    print(f"Error deleting report from {report_date}.")

Python PEP 249 covers the API that database libraries should follow for consistency in Python.

Under the execute method:

Parameters may be provided as sequence or mapping and will be bound to variables in the operation.

Generally, I try to always use a mapping (e.g. dict) in order to have some easy to follow clarity for what goes where in the SQL. Especially so when I need to repeat the same one in several places.

I may do a sequence (e.g. list) for a short query with only one or two parameters.

From comments, to clarify there are two problems with the questions attempt at parameterization. First, the "parameters" argument to Cursor.execute() needs to be a sequence or mapping type. Second the parameterization action on the query will handle any required quoting, so the single quotes around the parameter marker (i.e. '%s' to only %s) were causing a less understandable error message.

The lack of quoting is because the parameterization is making the query safe, so nothing else written in the SQL statement itself has to do anything. Unfortunately, a simple message like "SQL syntax error" does not make that super obvious. The best thing I can find in the documentation about this is the Type Objects and Constructors. The pyscopg2 documentation does specifically have a section about quoting.

Kevin
  • 2,234
  • 2
  • 21
  • 26
  • The 2nd example with [report_date] gave me a SQL error. – Rob Bell Jun 09 '20 at 17:21
  • The 1st example also results in a "you have an error in your SQL syntax" error. – Rob Bell Jun 09 '20 at 17:26
  • Did the first one work? If so, I think it may be that you need to use something like `report_dt < '?'` or `report_dt < ':1'` depending on MySQL/MariaDB and the library you are using to attach to it. It may also be related to the single quotes wrapping it. I am not as familiar with MySQL flavor SQL as I am with others. – Kevin Jun 09 '20 at 17:27
  • Is `report_date` a python `datetime` object (e.g. `datetime.datetime.today()`)? – Kevin Jun 09 '20 at 17:35
  • now = datetime.datetime.now() report_date = now.strftime('%Y-%m-%d %H:%M:%S') – Rob Bell Jun 09 '20 at 19:35
0

Looks like the solution is a hybrid of several answers above:

sql = "DELETE FROM mytable where report_dt < %s"
try:
    mycursor.execute(sql, [report_date])
    mydb.commit()

Remove the single quotes around the %s and use brackets around the report_date var.

The single quotes are required when it is a static text entry but apparently during parameterization it works differently.

Thanks all!

Rob Bell
  • 1