MySQLdb execute

0
votes

I have searched high and low on this site and many others and have found similar questions, but none of the answers have worked for me (usually just accounting for the tuple). I'm writing a python script to parse html pages and populate a database. I have almost everything working except the populating part...

Here is the code segment that deals with the mySQL database (note: using MySQLdb module in python)

conn = MySQLdb.connect(user="root", passwd="xxxxx",db="nutrients")
cur = conn.cursor()
test = "Canned Corn"
cur.execute("INSERT INTO food (name) VALUES (%s)", (test,))
conn.commit()

I was first testing it with parsed string but that wasn't working. This gives me 2 errors:

  1. Traceback (most recent call last): File "C:\Python32\lib\site-packages\MySQLdb\cursors.py", line 171, in execute r = self._query(query) File "C:\Python32\lib\site-packages\MySQLdb\cursors.py", line 330, in _query rowcount = self._do_query(q) File "C:\Python32\lib\site-packages\MySQLdb\cursors.py", line 294, in _do_query db.query(q) _mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax ; check the manual that corresponds to your MySQL server version for the right s yntax to use near '%s)' at line 1")

  2. Traceback (most recent call last): File "C:\Python32\lib\site-packages\MySQLdb\cursors.py", line 171, in execute r = self._query(query) File "C:\Python32\lib\site-packages\MySQLdb\cursors.py", line 330, in _query rowcount = self._do_query(q) File "C:\Python32\lib\site-packages\MySQLdb\cursors.py", line 294, in _do_query db.query(q) _mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax ; check the manual that corresponds to your MySQL server version for the right s yntax to use near '%s)' at line 1")

Why does this query not work!?

Update: After nearly pulling my hair out I decided to revert back to 2.7.3 and guess what... everything works now :D Should have know it was an error of that type... Thanks for the help none the less everyone!

3
pythonmysqlsyntax-errormysql-python
@user145896 is name the only column that you have in your table? - jcho360
Maybe try to cat the string inside as follows: str = "INSERT INTO food (name) VALUES" + test and then cur.execute(str) - Florin Stingaciu
@jcho360 no it's not, but I though you could just specify 1 column and the data in SQL - Odub
The problems seems to be not in the SQL syntax itself, but in that %s isn't really substituted with the value of test. - Lev Levitsky
Are you sure you execute the exact same code that you show? This error could happen if you forgot to pass (test,) as the second argument to execute or if you have another %s in the query. - Lev Levitsky

3 Answers

1
votes

I think you don't need any tricky or advanced SQL; you just need to store and retrieve some stuff. So, I think an ORM might make life a lot easier for you.

I suggest you try using the Autumn ORM:

http://pypi.python.org/pypi/autumn/0.5.1

That is a small and simple ORM, easy to understand and work with.

Another good and popular ORM for Python is SQLAlchemy:

http://www.sqlalchemy.org/

2
votes

I just ran across this too. After a lot of digging around, this was found to work (note the change to values):

conn = MySQLdb.connect(user="root", passwd="xxxxx",db="nutrients")
cur = conn.cursor()
test = "Canned Corn"
cur.execute("INSERT INTO food (name) VALUES ({0})", (test,))
conn.commit()

Background info: In the port of MySQLdb to Python 3 posted on an unofficial Python packages site, the execute function in cursors.py was modified to use format() rather than the % operator. Hence why %s remains in the SQL statement rather than being substituted. It looks like these changes were never upstreamed to the official source.

-3
votes

You should write your code like this :

 conn = MySQLdb.connect(user="root", passwd="xxxxx",db="nutrients")
 cur = conn.cursor()
 test = "Canned Corn"
 cur.execute("INSERT INTO food (name) VALUES (%s)" % (test,) )
 conn.commit()

If you are new to python and have some programming experience , then follow Dive Into Python book. Its free.