Cryssie Cryssie - 5 months ago 10
MySQL Question

Python how to initiate and close a MySQL connection inside a class

I am trying to create a class that holds all my database operations. I would like to initiate a MySQL connection whenever this class is called, perform whatever db operations it needs to do and close it after it's done.

This is what I have so far:

import MySQLdb

class Database_Test(object):
def __init__(self, db_local):
self.db_conn = MySQLdb.connect(**db_local)
self.db_cursor = self.db_conn.cursor()

def get_row(self, sql, data = None):
self.db_cursor.execute(sql)
self.resultset = self.db_cursor.fetchall()
self.db_cursor.close()
return self.resultset

# Close db connection something like this?
# db_conn.close()

db_config = {
'host':"127.0.0.1", # database host
'port': 3306, # port
'user':"root", # username
'passwd':"admin", # password
'db':"test", # database
'charset':'utf8' # charset encoding
}

sql = "SELECT * FROM mytest LIMIT 10"

test = Database_Test(db_config)
test.get_row(sql)
print(test)


This is what I get:

<__main__.Database_Test object at 0x00774BF0>


Somehow this is not what I was expecting to get as I was expecting to get some records from the database.

Answer

@Alecxe answer was about your statement execution, in case of you question about opening and closing a connection, you can use Context Managers magic methods:

import MySQLdb

class Database_Test(object):
    def __init__(self, db_local):
        self.db_local = db_local
        self.db_conn = None
        self.db_cursor = None

    def __enter__(self):
        # This ensure, whenever an object is created using "with"
        # this magic method is called, where you can create the connection.
        self.db_conn = MySQLdb.connect(**self.db_local)
        self.db_cursor = self.db_conn.cursor()

    def __exit__(self, exception_type, exception_val, trace):
        # once the with block is over, the __exit__ method would be called
        # with that, you close the connnection
        try:
           self.db_cursor.close()
           self.db_conn.close()
        except AttributeError: # isn't closable
           print 'Not closable.'
           return True # exception handled successfully

    def get_row(self, sql, data = None):
        self.db_cursor.execute(sql)
        self.resultset = self.db_cursor.fetchall()
        return self.resultset

db_config =  {
            'host':"127.0.0.1",                 # database host
            'port': 3306,                       # port
            'user':"root",                      # username
            'passwd':"admin",                   # password
            'db':"test",                        # database
            'charset':'utf8'                    # charset encoding
            }


sql = "SELECT * FROM mytest LIMIT 10" 

with Database_Test(db_config) as test:
    resultSet = test.get_row(sql)
    print(resultSet)
Comments