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?

That line is not a valid SQL query by itself.

Barmar
– Barmar

2018-01-19 21:44:53 +00:00
Commented Jan 19, 2018 at 21:44 — Barmar
– Barmar, Commented Jan 19, 2018 at 21:44
If that's just part of the query, show the whole thing.

Barmar
– Barmar

2018-01-19 21:45:37 +00:00
Commented Jan 19, 2018 at 21:45 — Barmar
– Barmar, Commented Jan 19, 2018 at 21:45

Eric Shreve · Accepted Answer · 2018-01-22 21:08:28Z

0

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.

answered Jan 22, 2018 at 21:08

Eric Shreve

1861 gold badge3 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Error running SQL on MySQL from Python3

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related