MySQLdb execute

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!

Answers


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/


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.


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.


Need Your Help

@media not working on phone but does on desktop

html css media-queries

I created my website and want to make it mobile friendly.

jquery show/hide on user selection and form validation

javascript php jquery html validation

I have form with an 'Employment Status' section. If the user selects 'Employed' from a drop-down list (#employment_status), then the following jQuery code shows an additional div section (which sta...