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

Question

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.

Don't forget that establishing a connection can be significantly more expensive than using an existing one. Most database systems encourage using a pool of active connections where you acquire a connection from a pool, use it, and release it back to the pool when you're done. — tadman, Jun 06 '16 at 03:35

Nagaraj Tantri · Answer 1 · 2021-01-27T01:29:43.163

@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()
        return self

    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)

You need to remove `self.db_cursor_close()` from `get_row()` — Nick, Jun 06 '16 at 02:22
I have tried the above, but encountered `'NoneType' object has no attribute ___method_name_here___` Solved by returning `self` in `__enter__(self)` https://stackoverflow.com/questions/5093382/object-becomes-none-when-using-a-context-manager — James Yoo, Dec 24 '20 at 15:32

score 2 · Answer 2 · answered Jun 06 '16 at 02:09

2

What you see printed is the test object string representation, but instead you meant to get and use the result of get_row() method call:

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

answered Jun 06 '16 at 02:09

alecxe

462,703
120
1,088
1,195

That was really noob of me. I didn't realise I was printing the string representation. Anyways, how can I close the connection inside the class after use? – Cryssie Jun 06 '16 at 02:14

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

2 Answers2