select data from mysql with mysql-connector-python

Question

I have a mysql db that I manage via MAMP (using port 3306, server is on port 80). I have downloaded and installed the mysql-connector-python library from Oracle and am trying to access and manipulate the db. Curiously, following the tutorials at http://dev.mysql.com/doc/connector-python/en/connector-python-tutorial-cursorbuffered.html, I'm able to run a query to insert new records into a specific table (as long as I issue the .commit() method on my connector).

However, I can't seem to retrieve any data with a simple select command. So the query, "Select * from Assignments" returns None.

query = ('''SELECT title,description FROM  `Assignments` LIMIT 0 , 30''')

cursor = cnx.cursor()
result = cursor.execute(query)
print "result:",result

#All assignment info
for (title, description) in results:
    print title, description

I keep getting the error, "TypeError: 'NoneType' object is not iterable". I believe this has to do with the fact that the result of the executed query is None. B/c I'm able to commit update and insert changes to the db, I know that I'm connecting just fine. Why can't I run a simple SELECT command and get something?

Alexander · Accepted Answer · 2015-02-09 13:54:16Z

6

You should use a MySQLCursor.fetchall method to get the results .

cursor.execute(query)
rows = cursor.fetchall()

or

cursor.execute(query)
head_rows = cursor.fetchmany(size=2)
remaining_rows = cursor.fetchall()

edited Feb 9, 2015 at 13:54

answered Feb 9, 2015 at 13:49

Alexander

12.8k5 gold badges62 silver badges79 bronze badges

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

1 Comment

staypuffinpc Over a year ago

Thank you @Alexander! This worked beautifully. I wonder why it's not mentioned on the documentation tutorial page (dev.mysql.com/doc/connector-python/en/…). What's there is what results in the error (for me).

Padraic Cunningham · Accepted Answer · 2016-12-21 12:19:39Z

4

You don't need to call fetchall, if you look at the docs you will see there is no assignment to the return value of the execute call as the method returns None, you just execute and then iterate over the MySQLCursor/cursor object:

query = ('''SELECT title,description FROM  `Assignments` LIMIT 0 , 30''')

cursor = cnx.cursor()

# forget assigning, just execute
cursor.execute(query)

# iterate over the cursor 
for (title, description) in cursor:
    print title, description

answered Dec 21, 2016 at 12:19

Padraic Cunningham

181k30 gold badges264 silver badges327 bronze badges

Comments

DragonFire · Accepted Answer · 2023-01-25 06:46:15Z

0

If you want to use mycursor.fetchone() then do the following:

   myresult = mycursor.fetchone()
   row_count = mycursor.rowcount
    
   if row_count == 1:
      row_id = myresult[0]
      print(row_id)

Basically you can access the items as an array like myresult[0], myresult[1] and so on....

answered Jan 25, 2023 at 6:46

DragonFire

4,1222 gold badges41 silver badges59 bronze badges

Collectives™ on Stack Overflow

select data from mysql with mysql-connector-python

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related