Why fetchall always results in `None`?

Question

Here are data in MySQL database：

python3 table：users_info

But when i use fetchall to get all data in tables it always return none!!

I really really dont konw how to fix it,can someone has met this problem?

the followings are the code files.

encapsulation.py

import MySQLdb

class mysql_encapsulation(object):
    def __init__(self,host,port,user,passwd,db,charset):
        self.host = host
        self.port = port
        self.user = user
        self.passwd = passwd
        self.db = db
        self.charset = charset

    def open(self):
        self.conn = MySQLdb.connect(host=self.host,port=self.port,user=self.user,passwd=self.passwd,db=self.db,charset=self.charset)
        self.cursor = self.conn.cursor()

    def close(self):
        self.cursor.close()
        self.conn.close()

    def operate(self,sql,params):
        try:
            self.open()
            self.cursor.execute(sql,params)
            self.conn.commit()
            print(' operate ok')
            self.close()
        except Exception,e:
            print(e.message)
            self.conn.rollback()

    def get_result_set(self,sql,params=[]):
        result=None
        try:

            self.open()
            self.cursor.execute(sql,params)
            result = self.cursor.fetchall()
            self.close()


        except Exception,e:
            print('error!')
            print(e.message)

        return result

use.py(problem in this file)

#coding=utf-8
from encapsulation import *

mysql = mysql_encapsulation(port=3306,host='localhost',user='root',passwd='mysql',
                           db='python3',charset='utf8')

sql='select id,name from users_info where id=3'
result=mysql.get_result_set(sql)
print (result)

Answer 1

As ruohola already explained in his answer, your exception handler is hiding all the important informations about what really went wrong - FWIW, there's the "Error !" string printed in your screen capture just above the None. The point is: your try/except block is not only useless, it's actually harmful - it prevents some other code up the call stack to even be aware that there was a problem (and eventually solve it), it also prevents you from knowing what went wrong. As a general rule, only catch exception the exact exception that you expect AND can effectively handle at this point in the code - if you can't fix the error at this point, let the exception propagate and let the calling code deal with it. Also, you want to have as few code as possible in your try clause (the only exception being the application's top-level exception handler of course).

This being said, there is indeed a very valid reason for wanting to be warned of exception, which is to make sure you free resources (and eventually rollback a transaction), but then you want 1/ to use a finally clause for resources cleanup (a finally block is always executed, whatever happens) and 2/ for the rollback part, use an except clause but re-raise the exception, ie:

# leave this one out of the try block - 
# there's nothing you can do here about a connection error
# so let the calling code deal with it
self.connect()

try:
    self.cursor.execute(sql, params)
except Exception as e:
    self.connection.rollback()
finally:
    # this will ALWAYS be executed (unless `self.connect()` raised
    # but then you don't even want to close the connection <g>
    self.close()

wrt/ the real cause of your problem, Kurose's answer is certainly the right one.

Also, there are a quite a few other things that are debatable with your "mysql_encapsulation" class (the naming to start with but let ignore this ATM), the main one being to open and close the connection for each query. Opening a database connection has a rather high cost, so you want to keep it opened as long as possible (and eventually reconnect if you get a "2006 mysql has gone away" error).

Answer 2

The problem happens in this method;

def get_result_set(self,sql,params=[]):
    result=None
    try:
        self.open()
        self.cursor.execute(sql,params)
        result = self.cursor.fetchall()
        self.close()
    except Exception,e:
        print('error!')
        print(e.message)
    return result

One of the first 3 lines in the try block (self.open / self.cursor.exec / result = self.cursor.fetch) generate an error, which you are then catching in the except block and (you can see that it prints "error!"). That's why the result always stays in it's default None value. Remove the except block and it will tell you what kind of error occured.

You should almost never catch the bare Exception but instead catch specific kinds of exception and handle each of them correctly, this problem is a prefect example why.

 

The error in question probably happens because in your SQL query you are selecting id and name, when the columns in your table are actually id and user_name. So your SQL query should be like this;

sql = 'select id, user_name from users_info where id = 3'

Answer 3

Did you check your sql statement? Based on your table your columns are "id", "user_name" and "passwd", but in your sql you are searching for "id" and "name", and "name" isn't a column so that will throw an error. Change your sql to "sql='select id,user_name from users_info where id=3'"