cardinal_pythonlib.sqlalchemy.engine_func

Original code copyright (C) 2009-2022 Rudolf Cardinal (rudolf@pobox.com).

This file is part of cardinal_pythonlib.

Licensed under the Apache License, Version 2.0 (the “License”); you may not use this file except in compliance with the License. You may obtain a copy of the License at

https://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an “AS IS” BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.

Functions to help with SQLAlchemy Engines.

cardinal_pythonlib.sqlalchemy.engine_func.get_sqlserver_product_version(engine: Engine) → Tuple[int, ...][source]

Gets SQL Server version information.

Attempted to use dialect.server_version_info:

from sqlalchemy import create_engine

url = "mssql+pyodbc://USER:PASSWORD@ODBC_NAME"
engine = create_engine(url, future=True)
dialect = engine.dialect
vi = dialect.server_version_info

Unfortunately, vi == () for an SQL Server 2014 instance via mssql+pyodbc. It’s also None for a mysql+pymysql connection. So this seems server_version_info is a badly supported feature.

So the only other way is to ask the database directly. The problem is that this requires an Engine or similar. (The initial hope was to be able to use this from within SQL compilation hooks, to vary the SQL based on the engine version. Still, this isn’t so bad.)

We could use either

SELECT @@version;  -- returns a human-readable string
SELECT SERVERPROPERTY('ProductVersion');  -- better

The pyodbc interface will fall over with ODBC SQL type -150 is not yet supported with that last call, though, meaning that a VARIANT is coming back, so we CAST as per the source below.

cardinal_pythonlib.sqlalchemy.engine_func.is_databricks(engine: Engine) → bool[source]: Is the SQLAlchemy Engine a Databricks database?

cardinal_pythonlib.sqlalchemy.engine_func.is_mysql(engine: Engine) → bool[source]: Is the SQLAlchemy Engine a MySQL/MariaDB database?

cardinal_pythonlib.sqlalchemy.engine_func.is_sqlserver(engine: Engine) → bool[source]: Is the SQLAlchemy Engine a Microsoft SQL Server database?

cardinal_pythonlib.sqlalchemy.engine_func.is_sqlserver_2008_or_later(engine: Engine) → bool[source]: Is the SQLAlchemy Engine an instance of Microsoft SQL Server, version 2008 or later?