Connection Management Working with Database in Python

Question

I have a class that working with db operations like below :

class DepartmentOperations(DatabaseOperations):

def __init__(self):
    try:
        self._connection = Database.create_connection()
        self._cursor = self._connection.cursor()
        self.isactive = True
    except ConnectionException as ex:
        print(ex.args)

def get_id(self, department_name):
    if(self.isactive):
        try:
            self._cursor.execute("select BolumId from BOLUMLER where BolumAdi = %s" , department_name)
            row = self._cursor.fetchone()
            if row is not None:
                return row[0]
            else:
                return 0
        except:
            raise DbException("Kayıt Getirirken Hata OLuştu...")
        finally:
            self._connection.close()
            self._cursor.close()
            self.isactive = False
    else:
        try:
            self._connection = Database.create_connection()
            self._cursor = self._connection.cursor()
            self.isactive = True
        except ConnectionException as ex:
            print(ex.args)
        try:
            self._cursor.execute("select BolumId from BOLUMLER where BolumAdi = %s" , department_name)
            row = self._cursor.fetchone()
            if row is not None:
                return row[0]
            else:
                return 0
        except:
            raise DbException("Kayıt Getirirken Hata OLuştu...")
        finally:
            self._connection.close()
            self._cursor.close()
            self.isactive = False



def add(self, department_name):
    if(self.isactive):
        try:
            self._cursor.execute("insert into BOLUMLER values (%s)",(department_name))
            self._connection.commit()
        except:
            raise DbException("Veri kayıt ederken hata oluştu.")
        finally:
            self._connection.close()
            self._cursor.close()
            self.isactive = False
    else:
        try:
            self._connection = Database.create_connection()
            self._cursor = self._connection.cursor()
            self.isactive = True
        except ConnectionException as ex:
            print(ex.args)
        try:
            self._cursor.execute("insert into BOLUMLER values (%s)",(department_name))
            self._connection.commit()
        except:
            raise DbException("Veri kayıt ederken hata oluştu.")
        finally:
            self._connection.close()
            self._cursor.close()
            self.isactive = False

When i instantiate this class and use it, works for the first but not second time because as u see in the code in finally block i close the connection . I delete finally block the methods work good but when i close the connection . How can i manage connections ?

Answer 1

You can use a custom pool:

def pool(ctor, limit=None):
    local_pool = multiprocessing.Queue()
    n = multiprocesing.Value('i', 0)
    @contextlib.contextmanager
    def pooled(ctor=ctor, lpool=local_pool, n=n):
        # block iff at limit
        try: i = lpool.get(limit and n.value >= limit)
        except multiprocessing.queues.Empty:
            n.value += 1
            i = ctor()
        yield i
        lpool.put(i)
    return pooled

example:

def do_something():
    try:
        with connection.cursor() as cursor:
            # Create a new record
            sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
            cursor.execute(sql, ('webmaster@python.org', 'very-secret'))

        connection.commit()

        with connection.cursor() as cursor:
            # Read a single record
            sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"
            cursor.execute(sql, ('webmaster@python.org',))
            result = cursor.fetchone()
            print(result)
    finally:
        connection.close()

and then

my_pool = pool(lambda: do_something())
with my_pool() as my_obj:
    my_obj.do_something()

PYDAL:

I recommend you to use pydal, Extended documentation here

General usage is something like this: (Almost for every database usage):

from pydal import DAL
db = DAL('mysql://username:password@localhost/test', pool_size=150)

class DALHandler(Object):
    def __init__(self, db):
        self.db = db

    def on_start(self):
        self.db._adapter.reconnect()

    def on_success(self):
        self.db.commit()

    def on_failure(self):
        self.db.rollback()

    def on_end(self):
        self.db._adapter.close()

Answer 2

1. Best way is don't keep the connection open if you are in a web application, instead you can use with statement :

Like this:

with pymssql.connect(server, user, password, "tempdb") as conn:
    with conn.cursor(as_dict=True) as cursor:
        cursor.execute('SELECT * FROM persons WHERE salesrep=%s', 'John Doe')
        for row in cursor:
            print("ID=%d, Name=%s" % (row['id'], row['name']))

This way, the connection will open and close in the context.

2. You can check if connection still active or not:

Use try/except and if the db connection is closed, reopen it.