Python MySQLdb error - what is causing this

Question

I am wondering why I am receiving this error:

cmd = "INSERT INTO resulttest (category, value, timestamp) VALUES (" + key + ", " + str(value) + ", " + str(timestamp) + ")"
c.execute(cmd)
db.commit()

    INSERT INTO resulttest (category, value, timestamp) VALUES (composed, 2, 1343186948.8)

Traceback (most recent call last):
  File "C:/Behavioral Technology/Google Drive/twitterthingv5test.py", line 94, in <module>
    moodParser()
  File "C:/Behavioral Technology/Google Drive/twitterthingv5test.py", line 92, in moodParser
    query()
  File "C:/Behavioral Technology/Google Drive/twitterthingv5test.py", line 37, in query
    main(columns)
  File "C:/Behavioral Technology/Google Drive/twitterthingv5test.py", line 81, in main
    c.execute(cmd)
  File "C:\Python27\lib\site-packages\MySQLdb\cursors.py", line 174, in execute
    self.errorhandler(self, exc, value)
  File "C:\Python27\lib\site-packages\MySQLdb\connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue

I believe it has something to do with how I am passing my values to the SQL command.

You aren't showing the actual exception. – Ned Batchelder Jul 25 '12 at 03:35 — Ned Batchelder, Jul 25 '12 at 03:35

score 4 · Answer 1 · answered Jul 25 '12 at 03:35

Your code to create the query isn't attempting to quote the string values:

cmd = "INSERT INTO resulttest (category, value, timestamp) VALUES (" + key + ", " + str(value) + ", " + str(timestamp) + ")"

Look at the SQL statement you printed:

INSERT INTO resulttest (category, value, timestamp) VALUES (composed, 2, 1343186948.8)

Shouldn't "category" be quoted?

You shouldn't be composing a SQL statement with string operations in the first place. This is how SQL injection vulnerabilities happen. Instead you should use placeholders and let the MySQL library deal with them:

c.execute(
    "INSERT INTO resulttest (category, value, timestamp) VALUES (?, ?, ?)", 
    (key, value, timestamp)
)

When I quoted the category value everything worked. I did not know that strings needed to be quoted when inserted into SQL. I will remember this for the future. I'm still trying to get amazing at Database design/function (my coworker recommend I read up on normal forms) — Steven Matthews, Jul 25 '12 at 03:38

Antimony · Answer 2 · 2012-07-27T11:27:45.297

In case Ned Batchelder's suggestion doesn't work, here's an alternative:

sql = "INSERT into resulttest (category, value, timestamp) VALUES (%s, %s, %s)"
c.execute(sql % (key, value, timestamp))

I faced a problem where my SQL command wasn't being executed, and following this kind of a syntax made it work.

Although you must be careful, because using % instead of , opens up the possibility for SQL injections, but this was the only syntax that worked for me. Might be a Python-MySQL version and compatibility problem.

A better idea would be to install compatible versions and follow the correct syntax.

Python MySQLdb error - what is causing this

2 Answers2

Linked