python - Why would MySQL execute return None?

Question

I am trying to query on a local MySQL database using Python's (3.4) MySQL module with the following code:

class databases():

  def externaldatabase(self):

  try:
    c = mysql.connector.connect(host="127.0.0.1", user="user",
                                password="password", database="database")
     if c.is_connected():
           c.autocommit = True
      return(c)
    except:
         return(None)
    d = databases().externaldatabase()
    c = d.cursor() 
    r = c.execute('''select * from tbl_wiki''')
    print(r) 
> Returns: None

As far as I can tell, the connection is successful, the database is composed of several rows but the query always returns the none type.

What instances does the MySQL execute function return None?

c.execute() doesn't return anything unless you use the multi=True option. Then it returns an iterator that lets you step through the results of each query in the multiple queries. — Barmar
Normally you call methods on the cursor to get the results out of the query, e.g. c.fetchone() to get one row, c.fetchall() to get all the results. — Barmar
See the documentation at dev.mysql.com/doc/connector-python/en/… — Barmar
Which MySQL interface are you using? In general, with Python DB-API database libraries, execute returns None. (The DB-API spec just says "Return values are undefined".) The point of execute is to modify the cursor so you can extract rows from it, and in Python, methods that modify an object normally return None. Some libraries do have return values for execute in certain cases for shortcuts or additional functionality, but if you're not using any of those cases, you shouldn't expect anything meaningful from execute. — abarnert
@BarryBrian Are you sure you're doing c.fetchone(), not r.fetchone()? — Barmar

rask004 rask004 · Accepted Answer · 2015-05-24T12:05:52

Query executions have no return values.

The pattern you need to follow is:

cursor creation;
cursor, execute query;
cursor, *fetch rows*;

Or in python:

c = d.cursor()

c.execute(query)    # selected rows stored in cursor memory

rows = c.fetchall()    # get all selected rows, as Barmar mentioned
for r in rows:
    print(r)

Also some db modules allow you to iterate over the cursor using the for...in pattern, but triple-check that regarding mysql.

python - Why would MySQL execute return None?

1 Answers