pythonpostgresqlsqlalchemypsycopg2

How to set `lock_timeout` on a PostgreSQL connection with SQLAlchemy and psycopg2?

With PostgreSQL, you can run this query to set a specific lock_timeout for a session:

SET lock_timeout TO '3s'

I'm wondering if there is a nice way to set this option when setting up a connection with SQLAlchemy. The way I'm instantiating SQLAlchemy sessions is the following:

engine = create_engine('postgresql+psycopg2://{user}:{pswd}@{host}:{port}/{name}')
session = scoped_session(sessionmaker(bind=engine))

I've tried passing it in connect_args but that is not supported:

engine = create_engine(
    'postgresql+psycopg2://{user}:{pswd}@{host}:{port}/{name}',
    connect_args={'lock_timeout': 3}
)

Is there a way to set this option per-session/connection with SQLAlchemy and psycopg2?

Solution

  • As it turned out, this is the right way to set lock_timeout for a session (note that the value is in milliseconds):

    engine = create_engine(
    'postgresql+psycopg2://{user}:{pswd}@{host}:{port}/{name}',
    connect_args={'options': '-c lock_timeout=3000'}
)