CODEWITHSUNDEEP
pythonmysql
staypuffinpc
staypuffinpc

Reputation: 337

select data from mysql with mysql-connector-python

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?

Upvotes: 3

Views: 11911

Answers (3)

DragonFire
DragonFire

Reputation: 4082

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....

Upvotes: 0

Padraic Cunningham
Padraic Cunningham

Reputation: 180391

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

Upvotes: 4

Alexander
Alexander

Reputation: 12775

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()

Upvotes: 6

Related Questions