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 ?

what db library do you use for connection? are you using sqlalchemy? — Ali Nikneshan
– Ali Nikneshan, Commented Dec 24, 2015 at 20:18

Ali Nikneshan · Accepted Answer · 2015-12-24 20:29:52Z

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.

You can check if connection still active or not:

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

answered Dec 24, 2015 at 20:29

Ali Nikneshan

3,51231 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Cahit Yıldırım Over a year ago

I work on desktop application.But is it too expensive to close and reopen connection again and again ?

Farshid Ashouri Over a year ago

@CahitYıldırım No, You have to close and open connections on each cycle again and again. Use database pools to handle multiple connections at once.

Cahit Yıldırım Over a year ago

@Farsheed PyMssql has no connectiton pool mechanism . So as Ali Nikneshan said , I check the connection still active or not and writed the code above . Are there any wrong in my code ?

Farshid Ashouri Over a year ago

@CahitYıldırım. Your code seems OK, and what @Ali said is also completely correct. But I think you must consider using a more sophisticated abstraction layer like pydal.

Farshid Ashouri · Accepted Answer · 2015-12-25 21:08:05Z

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, ('[email protected]', '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, ('[email protected]',))
            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()

answered Dec 25, 2015 at 21:08

Farshid Ashouri

18.1k7 gold badges58 silver badges68 bronze badges

3 Comments

Cahit Yıldırım Over a year ago

Thank u for your advices . pydal has no support for pymssql .So i can't use it .

Farshid Ashouri Over a year ago

Pydal supports pymsql. install it alongside with pydal. github.com/web2py/pydal/blob/master/pydal/drivers.py#L26

Cahit Yıldırım Over a year ago

you seem pymysql like pymssql . I looked the link but i didn't see pymssql

Collectives™ on Stack Overflow

Connection Management Working with Database in Python

2 Answers 2

4 Comments

PYDAL:

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

PYDAL:

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related