PythonAnywhere Forums

Insert into MySQL error

Hi all,

I hope just one of you can find the error in this insert query.

Code start

tagword = 'Apple'

username = tweet['user']['screen_name']

tweet = tweet['text'].encode('ascii','ignore')

cur.execute("INSERT INTO tweets(tagword,username,tweet) VALUES("tagword","username","tweet")")

Code end

I get this error

cur.execute("INSERT INTO tweets(tagword,username,tweet) VALUES("tagword","username","tweet")")
^ SyntaxError: invalid syntax

The solution is to insert like this cur.execute("INSERT INTO tweets(tagword,username,tweet) VALUES(?,?,?)",[tagword, username, tweet])

But now I get this error

ID = 1
Tagword = Apple
Username = hugevan
Tweet = I liked a @YouTube video from @wylsacom http://t.co/NN3WHJ0J : iPhone 5 Galaxy S3

ERROR: (<type 'exceptions.TypeError'>, TypeError('not all arguments converted during string formatting',), <traceback object at 0xd99cb0>)

Edited the code to

Code

cur.execute("INSERT INTO tweets(tagword,username,tweet) VALUES(%s,%s,%s)" ,[tagword, username, tweet])

Code

Now I don't get any errors, but the data is not inserted to the MySQL.

Not sure, but I believe you need parentheses instead of the brackets around the three variables: cur.execute("INSERT INTO tweets(tagword,username,tweet) VALUES(%s,%s,%s)" ,(tagword, username, tweet))

I'd tend to agree with catweazle.

It's entirely possible you want to use (...) instead of [...], it depends how the MySQLdb library processes the arguments. If it uses string formatting (i.e. the % operator) then it definitely makes a difference. I would have expected you to see an exception if this was the case, however, similar to the following:

>>> "%s %s %s" % ["one", "two", "three"]
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
TypeError: not enough arguments for format string

Compare with:

>>> "%s %s %s" % ("one", "two", "three")
'one two three'

The reason is simply that string formatting is interpreting the list (created with [...]) as a single item to substitute, whereas the tuple (created with (...)) is interpreted as a list of parameters. From the official documentation:

If format requires a single argument, values may be a single non-tuple object. [5] Otherwise, values must be a tuple with exactly the number of items specified by the format string, or a single mapping object (for example, a dictionary).

So, if you pass a list then Python is interpreting that as the single argument case, and it will then probably raise an exception because the format string has three subsititutions but the value list only one.

Of course, I repeat that this is an educated guess about how MySQLdb is working under the hood - it's possible it's a red herring. Definitely worth a try, though.

Great stuff guys. While you we're figuring out the solution to the problem I was try'n'error and found the same solution as catweazel.

It works :) Thanks both of you.

Thanks. That helps.