How do you properly handle database connections across several modules and functions in Python?

Question

I've been trying to read and understand how to handle a database connection in Python but remain confused. I'm not a professionally trained developer so a lot of the jargon and language is lost on me.

I've created a connection class like this...

db.py

class Database(object):

    def __init__(self):

        db_url = {
            'drivername': 'mysql+pymysql',
            'host': DB_HOST,
            'username': DB_USER,
            'password': DB_PASS,
            'port': DB_PORT
        }

        self.engine = create_engine(URL(**db_url), echo=False, strategy='threadlocal')
        self.connection = self.engine.connect()
        self.metadata = MetaData(bind=self.engine)


    def __enter__(self):
        return self


    def __exit__(self):
        self.connection.close()

In one example, we do a lot of calculations on our players. So I created a player class.

player.py

class PlayerData():

    def __init__(self):

        self.db = Database()


    def load_player_data(self, player_id=None):
        *** database query ***
        result = self.db.connection.execute(s)

    ...

In a separate module, if I have a series of functions using the data in the PlayerData class and potentially several others, ultimately yielding a new set of data we want to save to our database, do I have to then create another instance of the Database class to do so? Like this...

stats.py

 def save_calculations(data):
    db = Database()
    db.connection.execute('insert query')

To my untrained mind, this feels like a lot of database connecting and each time one is established it seems to slow down the whole program. When I first started learning Python I would have a database module that created the connection immediately (not wrapped in a class) and would just import it at the top of each module where I needed to run queries. It seemed as though the same connection was being used everywhere. I was told, however, that's not the correct to handle it.

Please tell me if I'm thinking about this all wrong as well but what is the correct way to handle this? Thank you!

Answer 1

You can use a Singleton, the implementation as described in this answer here. You can read more about the Singleton pattern here. There are pro/cons to it, of course.

You only want one instance of Database in your program to connect to your database a one time.

So:

def singleton(class_):
    instances = {}
    def getinstance(*args, **kwargs):
        if class_ not in instances:
            instances[class_] = class_(*args, **kwargs)
        return instances[class_]
    return getinstance

@singleton
class Database(object):
    # ... class definition

Now each player will use the same Database object.

class PlayerData():

    def __init__(self):

        # Each points to the same Database.
        self.db = Database() 

Answer 2

It seems you have your code split appropriately and if you will be bringing all of them together in one file then you may only need to have your functions require an engine argument. Ultimately you will have to import Database anywhere you need it and since you can't really use the same engine asynchronously, you might as well just create the engine once and use it throughout.

Here is a possibility. You can have all your code written assuming an engine will be passed as an argument to your functions/methods. Then you can have a sort of main.py file where you bring them all together. And since you already have your context manager well defined, you just take advantage of it.

# db.py
class Database(object):

    def __init__(self):
        db_url = {
            'drivername': 'mysql+pymysql',
            'host': DB_HOST,
            'username': DB_USER,
            'password': DB_PASS,
            'port': DB_PORT
        }

        self.engine = create_engine(URL(**db_url), echo=False, strategy='threadlocal')
        self.connection = self.engine.connect()
        self.metadata = MetaData(bind=self.engine)

    def __enter__(self):
        return self

    def __exit__(self):
        self.connection.close()
        self.engine.dispose()


# player.py
class PlayerData():
    def __init__(self):
        pass

    def load_player_data(self, engine, player_id=None):
        # *** database query ***
        result = engine.execute(s)


# stats.py
def save_calculations(data, engine):
    engine.execute('insert query')


# some main.py file somewhere
if __name__ == '__main__':
    from database import Database
    from stats import save_calculations
    from player import PlayerData

    with Database() as db:
        pdata = PlayerData()
        pdata.load_player_data(engine=db.engine, player_id=None)
        save_calculations(data=None, engine=db.engine)

Answer 3

In your code, you are confusing the engine and the connection. As a result, a new connection is created each time a query is performed (which is indeed bad news). Also, the context manager provided by your Database class is already available, built-in, in the SQLAlchemy engine.

Here is what I would do (the simpler, the better):

# db.py
DB_OPTIONS = {
    'drivername': 'mysql+pymysql',
    'host': DB_HOST,
    'username': DB_USER,
    'password': DB_PASS,
    'port': DB_PORT
}

# player.py
class PlayerData():

    def load_player_data(self, cnx, player_id=None):
        # *** database query ***
        result = cnx.execute(s)


# stats.py
def save_calculations(cnx, data): # connection first for homogeneity reasons
    cnx.execute('insert query')


# some main.py file somewhere
from SQLAlchemy import create_engine
from database import DB_OPTIONS
from stats import save_calculations
from player import PlayerData

def main():
    engine = create_engine(URL(**DB_OPTIONS), echo=False, strategy='threadlocal')
    with engine.begin() as connection:
        pdata = PlayerData()
        pdata.load_player_data(connection) # I removed the second parameter because it is the default value
        save_calculations(connection, None)

if __name__ == '__main__':
    main()