Checking if connection to database exists before opening a new one in python

Question

import dataset

class db(object):
    _db_connection = None
    _db_cur = None

    def __init__(self):
        self._db_connection = dataset.connect(path_to_database)

    def __del__(self):
        self._db_connection.executable.close()

In the code above, I create a class to connect to an existing database on AWS. Is there a way I can check if connection is already open and if so, return existing connection rather than open a new connection?

Artyer · Accepted Answer · 2017-06-30T22:26:16.230

3

You can use a descriptor for that:

class DBConn(object):
    conn = None

    def __get__(self, instance, owner):
        if instance is None:
            return self
        if self.conn is None or self.conn.closed():
            # Since this is a class attribute, it is shared by all instances
            self.conn = dataset.connect(path_to_database)
        return self.conn

    def __delete__(self, instance):
        self.conn.executable.close()
        self.conn = None

And then just use it as a property:

class DB(object):
    connection = DBConn()

db = DB()
db.connection.do_something()  # Opens a new connections
other_db = DB()
other_db.connection.do_something_else()  # Reuses same connection
del db.connection  # Closes the connection
other_db.connection.another_thing()  # Reopens the connection

edited Jun 30 '17 at 22:26

answered Jun 30 '17 at 22:16

Artyer

31,034
3
47
75

thanks @Artyer, instead of `del db.connection`, can I instead use a `with` context manager? – user308827 Jun 30 '17 at 22:21
@user308827 Sure, if the connection is a context manager object. – Artyer Jun 30 '17 at 22:22
thanks, also what is `owner` in the `__get__` method? – user308827 Jun 30 '17 at 22:23
1

@user308827 See https://stackoverflow.com/a/18038707/5754656 for a longer write up on descriptors, but basically, `instance` is the object accessing the property, and `owner` is the class. `instance` is not set if you call `DB.connection` instead of `DB().connection`, which I made return the descriptor itself, which is standard behaviour. – Artyer Jun 30 '17 at 22:26
thanks, what does `if instance is None: return self` do? – user308827 Jun 30 '17 at 22:33
1

@user308827 `instance` is `None` when you try and access it on the class (So, `DB.connection` instead of `db = DB(); db.connection`). In that case, return the descriptor (The object with the `__get__` and `__set__` methods, rather than a database connection), which makes it less confusing for debugging mostly, and prevents accidentally making connections. – Artyer Jun 30 '17 at 22:38

score 2 · Answer 2 · answered Jun 30 '17 at 22:16

It's called Singleton pattern take a look at: http://python-3-patterns-idioms-test.readthedocs.io/en/latest/Singleton.html

class OnlyOne(object):
    class __OnlyOne:
        def __init__(self):
            self.val = None
        def __str__(self):
            return `self` + self.val
    instance = None
    def __new__(cls): # __new__ always a classmethod
        if not OnlyOne.instance:
            OnlyOne.instance = OnlyOne.__OnlyOne()
        return OnlyOne.instance
    def __getattr__(self, name):
        return getattr(self.instance, name)
    def __setattr__(self, name):
        return setattr(self.instance, name)

MrE · Answer 3 · 2017-06-30T22:12:46.040

1

on connect, you may get an exception if the driver can't connect

so you need to wrap your connect call into a

class db(object):
    _db_connection = None
    _db_cur = None

    def __init__(self):
       try:
           self._db_connection = dataset.connect(path_to_database)
       except WhateverExceptionYourDBDriverThrowsOnError:
           pass

    def __del__(self):
        if self._db_connection:
            self._db_connection.executable.close()

You didn't say what driver, what DB you use, so this is generic code. You need to replace the Exception type with the proper one thrown by your driver.

edited Jun 30 '17 at 22:12

answered Jun 30 '17 at 22:07

MrE

19,584
12
87
105

thanks @MrE, I am slightly confused, can you integrate your answer into the code I have provided? In particular, does the check for `if self._db_connection` go into the `_init_` function? – user308827 Jun 30 '17 at 22:10

score 1 · Answer 4 · answered Jun 30 '17 at 22:11

1

db.local.conn.closed # return True or False

I use it in this post

answered Jun 30 '17 at 22:11

PRMoureu

12,817
6
38
48

sorry i didn't check who was asking :) thanks to you, i appreciated you open the bounty again – PRMoureu Jun 30 '17 at 22:16
not a problem, thanks again for the answer here as well as the bounty answer – user308827 Jun 30 '17 at 22:19

Checking if connection to database exists before opening a new one in python

4 Answers4