sqlalchemyflask-sqlalchemy

Generically check server database version with SQLAlchemy

I've searched all over the internet and a handful of AI bots for what seems to me to be an extremely basic function, to no avail. What is the SQLAlchemy command to check the server database version?

I know I can execute a raw SQL query, but the exact syntax varies between SQL variants. Two examples to whit:

  1. Postgres: SELECT version()
  2. SQLite: SELECT get_version()

Desired response:

PostgreSQL 15.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12), 64-bit
...or...
PostgreSQL 15.7

I have searched all over the SQLAlchemy 1.4 and 2.0 docs but can't find it. Surely there's a way?

Solution

  • The closest to what you want is the dialect's server_version_info attribute, which provides version information as a tuple. The engine must connect in order to populate this attribute.

    >>> import sqlalchemy as sa
>>> engine = sa.create_engine('postgresql+psycopg2:///so')
>>> with engine.connect() as conn:
...     pass
...     
>>> engine.dialect.server_version_info
(17, 2)
>>> engine = sa.create_engine('sqlite://')
>>> with engine.connect() as conn:
...     pass
...     
>>> engine.dialect.server_version_info
(3, 40, 1)