Close SQLAlchemy connection

Question

I have the following function in python:

def add_odm_object(obj, table_name, primary_key, unique_column):
    db = create_engine('mysql+pymysql://root:@127.0.0.1/mydb')
    metadata = MetaData(db)
    t = Table(table_name, metadata, autoload=True)
    s = t.select(t.c[unique_column] == obj[unique_column])    
    rs = s.execute()
    r = rs.fetchone()
    if not r:
        i = t.insert()
        i_res = i.execute(obj)
        v_id = i_res.inserted_primary_key[0]
        return v_id
    else:
        return r[primary_key]

This function looks if the object obj is in the database, and if it is not found, it saves it to the DB. Now, I have a problem. I call the above function in a loop many times. And after few hundred times, I get an error: user root has exceeded the max_user_connections resource (current value: 30) I tried to search for answers and for example the question: How to close sqlalchemy connection in MySQL recommends creating a conn = db.connect() object where dbis the engine and calling conn.close() after my query is completed.

But, where should I open and close the connection in my code? I am not working with the connection directly, but I'm using the Table() and MetaData functions in my code.

score 1 · Accepted Answer · answered Aug 30 '14 at 21:15

The engine is an expensive-to-create factory for database connections. Your application should call create_engine() exactly once per database server.

Similarly, the MetaData and Table objects describe a fixed schema object within a known database. These are also configurational constructs that in most cases are created once, just like classes, in a module.

In this case, your function seems to want to load up tables dynamically, which is fine; the MetaData object acts as a registry, which has the convenience feature that it will give you back an existing table if it already exists.

Within a Python function and especially within a loop, for best performance you typically want to refer to a single database connection only.

Taking these things into account, your module might look like:

# module level variable.  can be initialized later, 
# but generally just want to create this once.
db = create_engine('mysql+pymysql://root:@127.0.0.1/mydb')

# module level MetaData collection.
metadata = MetaData()

def add_odm_object(obj, table_name, primary_key, unique_column):
    with db.begin() as connection:

        # will load table_name exactly once, then store it persistently
        # within the above MetaData
        t = Table(table_name, metadata, autoload=True, autoload_with=conn)

        s = t.select(t.c[unique_column] == obj[unique_column])    
        rs = connection.execute(s)
        r = rs.fetchone()
        if not r:
            i_res = connection.execute(t.insert(), some_col=obj)
            v_id = i_res.inserted_primary_key[0]
            return v_id
        else:
            return r[primary_key]

Close SQLAlchemy connection

1 Answers1