How to create a postgres database using SQLAlchemy/Python and avoid session in use error

Question

I'm trying to create a postgresql database in Python. I can create the database but it does not close the database session(or SQLAlchemy session?). I can't delete it after, unless I restart my application. The error I get is when I try to delete it later is:

Traceback (most recent call last):
  File "/Users/rabraham/Documents/dev/fifteenrock/mara/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context
    cursor, statement, parameters, context
  File "/Users/rabraham/Documents/dev/fifteenrock/mara/venv/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.ObjectInUse: database "db_ff264a9c_b693_457b_9c51_928786e93577" is being accessed by other users
DETAIL:  There is 1 other session using the database.


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/Users/rabraham/Documents/dev/fifteenrock/mara/trial.py", line 147, in <module>
    db_helper.remove_master_database(db, engine, custom_id)
  File "/Users/rabraham/Documents/dev/fifteenrock/mara/helpers/db_helper.py", line 194, in remove_master_database
    execute_in_session(db.session, remove_db_func)
  File "/Users/rabraham/Documents/dev/fifteenrock/mara/helpers/db_helper.py", line 266, in execute_in_session
    raise e
  File "/Users/rabraham/Documents/dev/fifteenrock/mara/helpers/db_helper.py", line 257, in execute_in_session
    result = a_function(session)
  File "/Users/rabraham/Documents/dev/fifteenrock/mara/helpers/db_helper.py", line 217, in _remove_master_database
    db_api.drop_database(engine, a_db.db_name)
  File "/Users/rabraham/Documents/dev/fifteenrock/mara/mara_app/db_api.py", line 383, in drop_database
    execute_with_autocommit(engine, query)
  File "/Users/rabraham/Documents/dev/fifteenrock/mara/mara_app/db_api.py", line 394, in execute_with_autocommit
    conn.execute(query)
  File "/Users/rabraham/Documents/dev/fifteenrock/mara/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 982, in execute
    return self._execute_text(object_, multiparams, params)
  File "/Users/rabraham/Documents/dev/fifteenrock/mara/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1155, in _execute_text
    parameters,
  File "/Users/rabraham/Documents/dev/fifteenrock/mara/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context
    e, statement, parameters, cursor, context
  File "/Users/rabraham/Documents/dev/fifteenrock/mara/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1466, in _handle_dbapi_exception
    util.raise_from_cause(sqlalchemy_exception, exc_info)
  File "/Users/rabraham/Documents/dev/fifteenrock/mara/venv/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 383, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/Users/rabraham/Documents/dev/fifteenrock/mara/venv/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 128, in reraise
    raise value.with_traceback(tb)
  File "/Users/rabraham/Documents/dev/fifteenrock/mara/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context
    cursor, statement, parameters, context
  File "/Users/rabraham/Documents/dev/fifteenrock/mara/venv/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (psycopg2.errors.ObjectInUse) database "db_ff264a9c_b693_457b_9c51_928786e93577" is being accessed by other users
DETAIL:  There is 1 other session using the database.

[SQL: drop database db_ff264a9c_b693_457b_9c51_928786e93577;]
(Background on this error at: http://sqlalche.me/e/e3q8)

Process finished with exit code 1

So far, I have tried different codes but they all lead to the same issue. I have also tried NullPool. i.e.

My current one is: My engine is created somewhere else with code like:

from sqlalchemy import create_engine
from sqlalchemy.pool import NullPool
from sqlalchemy.orm import scoped_session, sessionmaker
engine = create_engine(db_string) # or engine = create_engine(db_string, poolclass=NullPool)
# Not sure about db_session is required but putting it on SO
db_session = scoped_session(sessionmaker(autocommit=False,
                                     autoflush=False,
                                     bind=engine))

My create database code:

def create_database(engine, database_name: str):
    query = f"create database {database_name};"
    conn = engine.connect()
    conn.execution_options(isolation_level="AUTOCOMMIT").execute(query)
    conn.close()
    engine.dispose()

can you show me how you have the connection imports declared? Are you using psycopg2? How have you implemented SQLAlchemy? — lopezdp, Jun 30 '19 at 03:12
@lopezdp I get the connection from my engine. I have updated the question with my engine imports. Is that what you meant? Yes, I use psycopg2. Thanks! — RAbraham, Jun 30 '19 at 03:24
@lopezdp I have tried the solutions suggested in the possible duplicate link and it did not work. — RAbraham, Jun 30 '19 at 12:42

How to create a postgres database using SQLAlchemy/Python and avoid session in use error

0 Answers0