Bug #69657 Python Connect queries don't work with only one %s parameter

• Submitted: 3 Jul 2013 9:17
• Modified: 4 Aug 2013 8:53
• Reporter: Andy Nield
• Status: No Feedback
• Category: Connector / Python
• Severity: S2 (Serious)
• OS: Windows (OSX and windows 8)
• CPU Architecture: Any

[3 Jul 2013 9:17] Andy Nield

Description:
query won't work with one %s parameter

cursor = self.db.cursor()       
query  = ("SELECT results_date, nap_id, scores_date, publish_date FROM state WHERE start_date=%s")
cursor.execute(query, (date_value))

causes exception:

mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%s'

cursor = self.db.cursor()       
query  = ("SELECT results_date, nap_id, scores_date, publish_date FROM state WHERE start_date=%s or start_date=%s")
cursor.execute(query, (date_value, date_value))

works ok...

How to repeat:
create a query with one %s parameter:

cursor = self.db.cursor()       
query  = ("SELECT results_date, nap_id, scores_date, publish_date FROM state WHERE start_date=%s")
cursor.execute(query, (date_value))

Suggested fix:
Check how values are parsed?

seems to only be a problem when there is only 1 %s value as far as I can tell

[4 Jul 2013 8:53] Geert Vanderkelen

Hi Andy,

You have an error when calling the execute() method:

  cursor.execute(query, (date_value))
should be:
  cursor.execute(query, (date_value,))

This is PEP-249 complient, but we should raise a ValueError when the parameters argument is not a sequence or mapping.

Can you confirm?

Additional question: what is date_value? What Python type?

[5 Aug 2013 1:00] Bugs System

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".