Error running SQL on MySQL from Python3

Question

I have a script that connects to MySQL and executes an SQL script to load the data into Excel. Everything works fine until I put this one line in the SQL:

convert_tz(from_unixtime(o.DeviceTimeStamp/1000, '%Y-%m-%d %H:%i:%s'), 'UTC', 'America/New_York')

What I'm doing is pulling back a unix time stamp and converting it to eastern time, as requested by the customer. I know the unix time stamp is not the normal version and I've accounted for that. When I run the same query directly in MySQL it works perfectly. When I run it using Python it get this error:

Traceback (most recent call last): File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/mysql/connector/cursor.py", line 78, in call return bytes(self.params[index]) IndexError: tuple index out of range

During handling of the above exception, another exception occurred:

Traceback (most recent call last): File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/pandas/io/sql.py", line 1409, in execute cur.execute(*args) File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/mysql/connector/cursor.py", line 550, in execute stmt = RE_PY_PARAM.sub(psub, stmt) File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/mysql/connector/cursor.py", line 81, in call "Not enough parameters for the SQL statement") mysql.connector.errors.ProgrammingError: Not enough parameters for the SQL statement

If I take that line out, the query and the script work just fine. Any idea why that line causes an error?

• Barmar: That line is not a valid SQL query by itself.
• Barmar: If that's just part of the query, show the whole thing.

Answer 1

The issue ended up being that "'%Y-%m-%d %H:%i:%s'" was being treated by Python as a string substitution and was causing an error. I was able to change that part to be "'{p}Y-{p}m-{p}d {p}H:{p}i:{p}s'"

In my Python script I put the SQL into a variable called sql (Creative, I know). So, I added "sql.format(p='%)" to put the '%' in at run time, and that resolved the issue.