Postgresql - Python sqlalchemy completely terminate session correctly

clayton_wonders
June 28, 2023
251 views
0 votes
2 Answers

I apologize if this has been answered. I’ve searched repeatedly over years and have yet to find an adequate solution to actually terminate a session on a postgresql 13 server made by my python scripts. A simple example:

from sqlalchemy import create_engine, text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import sessionmaker

# Create engine
connection_string = v.connection_string
engine = create_engine(connection_string)

# Create a Session
Session = sessionmaker(bind=engine)
session = Session()

try:
    # Use session connection
    with session.connection() as connection:
        connection.execute(text('DROP TABLE IF EXISTS 
        interim_schema.location;'))
    session.commit()

except SQLAlchemyError as e:
    print(f"Error occurred while executing SQL commands: {e}")
    session.rollback()
finally:
    session.close()

My understanding is this should completely close the session, but it doesn’t.

I’ve tried engine.dispose() but that just resets the connection pool. I’ve tried the recommended session as explained above which also does not terminate the session. I expect the database connection to vanish on the postresql server side.

Answers

It’s just an example. I suggest you to read more about context manager and decorators in python. It’s really helpful.

from contextlib import contextmanager
from sqlalchemy import create_engine
from sqlalchemy.orm import Session, declarative_base, sessionmaker


class SqlAlchemy:
    def __init__(self, database_url: str):
        self.engine = create_engine(database_url, echo=False)
        self.session = sessionmaker(self.engine)
        self.base = declarative_base()


@contextmanager
def in_transaction(session) -> Session:
    session = session()
    session.begin()
    try:
        yield session
        session.commit()
    except BaseException:
        session.rollback()
        raise
    finally:
        session.close()


db = SqlAlchemy('your database url')


def db_session(db):
    with in_transaction(db.session) as session:
        yield session
        
        
# example function where you need session
@db_session
def my_function(session):
    session.execute('SQL for example:)')

Can you please provide some more information?

What information are you using to determine that the session has closed?
What output, if any, does your program have? (e.g. error messages or sql statements)
Does the code work otherwise? I.e., is the table successfully dropped?

Also, enable the sqlalchemy engine echo logging feature:

engine = create_engine(connection_string, echo=True)

For example, when I run very similar code to yours (I try to drop "foo"), I get the following output:

2023-06-28 08:26:03,673 INFO sqlalchemy.engine.Engine select pg_catalog.version()
2023-06-28 08:26:03,673 INFO sqlalchemy.engine.Engine [raw sql] {}
2023-06-28 08:26:03,674 INFO sqlalchemy.engine.Engine select current_schema()
2023-06-28 08:26:03,674 INFO sqlalchemy.engine.Engine [raw sql] {}
2023-06-28 08:26:03,679 INFO sqlalchemy.engine.Engine show standard_conforming_strings
2023-06-28 08:26:03,679 INFO sqlalchemy.engine.Engine [raw sql] {}
2023-06-28 08:26:03,686 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-06-28 08:26:03,686 INFO sqlalchemy.engine.Engine DROP TABLE IF EXISTS foo;
2023-06-28 08:26:03,686 INFO sqlalchemy.engine.Engine [generated in 0.00021s] {}
2023-06-28 08:26:03,688 INFO sqlalchemy.engine.Engine ROLLBACK
Error occurred while executing SQL commands: This transaction is inactive
/home/nchappell/programming/py/foo/foo.py:21: SAWarning: transaction already deassociated from connection
  session.rollback()

Everything from BEGIN (implicit) and below is interesting and tells me that the program is not correctly configured. Please share more info and I can try to help you out.

Please signup or login to give your own answer.

Click here to cancel reply.

Postgresql – Python sqlalchemy completely terminate session correctly

Answers