Dialects Module

The dialects module provides SQLAlchemy dialect implementations for various databases.

Base Dialect

class sqlalchemy_jdbcapi.dialects.base.BaseJDBCDialect

Bases: Dialect, ABC

Abstract base class for JDBC-based SQLAlchemy dialects.

This class implements the Template Method pattern, providing common JDBC functionality while allowing database-specific customization through abstract methods.

Subclasses must implement: - get_driver_config(): Return driver configuration - _get_server_version_info(): Parse database version

SQLAlchemy 2.0+ compatible with full type hints.

driver: str = 'jdbcapi'

identifying name for the dialect’s DBAPI

supports_native_decimal: bool = True

indicates if Decimal objects are handled and returned for precision numeric types, or if floats are returned

supports_sane_rowcount: bool = False

Indicate whether the dialect properly implements rowcount for UPDATE and DELETE statements.

supports_sane_multi_rowcount: bool = False

Indicate whether the dialect properly implements rowcount for UPDATE and DELETE statements when executed via executemany.

supports_unicode_binds = True

supports_statement_cache: bool = True

indicates if this dialect supports caching.

All dialects that are compatible with statement caching should set this flag to True directly on each dialect class and subclass that supports it. SQLAlchemy tests that this flag is locally present on each dialect subclass before it will use statement caching. This is to provide safety for legacy or new dialects that are not yet fully tested to be compliant with SQL statement caching.

Added in version 1.4.5.

See also

Caching for Third Party Dialects

supports_server_side_cursors: generic_fn_descriptor[bool] | bool = False

indicates if the dialect supports server side cursors

supports_native_boolean: bool = True

Indicates if the dialect supports a native boolean construct. This will prevent _types.Boolean from generating a CHECK constraint when that type is used.

poolclass

alias of QueuePool

classmethod import_dbapi()

Return the DB-API module.

Returns our custom JDBC bridge module that implements the Python DB-API 2.0 specification.

classmethod dbapi()

Deprecated: Use import_dbapi() instead.

abstractmethod classmethod get_driver_config()

Get JDBC driver configuration for this dialect.

This method must be implemented by each database dialect to provide driver-specific configuration.

Returns:

JDBCDriverConfig instance

Return type:

JDBCDriverConfig

create_connect_args(url)

Create connection arguments from SQLAlchemy URL.

Converts a SQLAlchemy URL into arguments for our JDBC connect() function, following the Adapter pattern.

Parameters:

url (URL) – SQLAlchemy connection URL

Returns:

Tuple of (args, kwargs) for jdbc.connect()

Return type:

tuple[list[Any], dict[str, Any]]

initialize(connection)

Initialize a new connection.

Called when a new connection is established to set up connection-specific settings.

Parameters:

connection (Connection) – SQLAlchemy connection object

is_disconnect(e, connection, cursor)

Check if an exception indicates a database disconnect.

Parameters:

• e (Exception) – Exception that occurred

• connection (Any) – Database connection

• cursor (Any) – Database cursor

Returns:

True if this is a disconnect error

Return type:

bool

do_rollback(dbapi_connection)

Perform a rollback on the connection.

Some JDBC drivers have issues with rollback, this can be overridden by subclasses.

Parameters:

dbapi_connection (Any) – DB-API connection object

do_commit(dbapi_connection)

Perform a commit on the connection.

Parameters:

dbapi_connection (Any) – DB-API connection object

do_close(dbapi_connection)

Close the connection.

Parameters:

dbapi_connection (Any) – DB-API connection object

do_ping(dbapi_connection)

Check if connection is alive.

Parameters:

dbapi_connection (Any) – DB-API connection object

Returns:

True if connection is alive, False otherwise

Return type:

bool

get_isolation_level(dbapi_connection)

Get the current transaction isolation level.

Parameters:

dbapi_connection (Any) – DB-API connection object

Returns:

Isolation level name or None

Return type:

str | None

set_isolation_level(dbapi_connection, level)

Set the transaction isolation level.

Parameters:

• dbapi_connection (Any) – DB-API connection object

• level (str) – Isolation level to set

get_schema_names(connection, **kw)

Get list of schema names using JDBC DatabaseMetaData.

Parameters:

• connection (Connection) – SQLAlchemy connection

• **kw (Any) – Additional keyword arguments

Returns:

List of schema names

Return type:

list[str]

get_table_names(connection, schema=None, **kw)

Get list of table names using JDBC DatabaseMetaData.

Parameters:

• connection (Connection) – SQLAlchemy connection

• schema (str | None) – Schema name (None for default schema)

• **kw (Any) – Additional keyword arguments

Returns:

List of table names

Return type:

list[str]

get_view_names(connection, schema=None, **kw)

Get list of view names using JDBC DatabaseMetaData.

Parameters:

• connection (Connection) – SQLAlchemy connection

• schema (str | None) – Schema name (None for default schema)

• **kw (Any) – Additional keyword arguments

Returns:

List of view names

Return type:

list[str]

has_table(connection, table_name, schema=None, **kw)

Check if a table exists using JDBC DatabaseMetaData.

Parameters:

• connection (Connection) – SQLAlchemy connection

• table_name (str) – Table name to check

• schema (str | None) – Schema name (None for default schema)

• **kw (Any) – Additional keyword arguments

Returns:

True if table exists, False otherwise

Return type:

bool

get_columns(connection, table_name, schema=None, **kw)

Get column definitions for a table using JDBC DatabaseMetaData.

Parameters:

• connection (Connection) – SQLAlchemy connection

• table_name (str) – Table name

• schema (str | None) – Schema name (None for default schema)

• **kw (Any) – Additional keyword arguments

Returns:

• name: Column name

• type: SQLAlchemy type instance

• nullable: Boolean

• default: Default value (string or None)

• autoincrement: Boolean

Return type:

List of column dictionaries with keys

get_pk_constraint(connection, table_name, schema=None, **kw)

Get primary key constraint for a table using JDBC DatabaseMetaData.

Parameters:

• connection (Connection) – SQLAlchemy connection

• table_name (str) – Table name

• schema (str | None) – Schema name (None for default schema)

• **kw (Any) – Additional keyword arguments

Returns:

• name: Constraint name

• constrained_columns: List of column names

Return type:

Dictionary with keys

get_foreign_keys(connection, table_name, schema=None, **kw)

Get foreign key constraints for a table using JDBC DatabaseMetaData.

Parameters:

• connection (Connection) – SQLAlchemy connection

• table_name (str) – Table name

• schema (str | None) – Schema name (None for default schema)

• **kw (Any) – Additional keyword arguments

Returns:

• name: Constraint name

• constrained_columns: List of local column names

• referred_schema: Referenced schema name

• referred_table: Referenced table name

• referred_columns: List of referenced column names

Return type:

List of dictionaries with keys

get_indexes(connection, table_name, schema=None, **kw)

Get indexes for a table using JDBC DatabaseMetaData.

Parameters:

• connection (Connection) – SQLAlchemy connection

• table_name (str) – Table name

• schema (str | None) – Schema name (None for default schema)

• **kw (Any) – Additional keyword arguments

Returns:

• name: Index name

• column_names: List of column names

• unique: Boolean

Return type:

List of dictionaries with keys

get_unique_constraints(connection, table_name, schema=None, **kw)

Get unique constraints for a table.

This is extracted from get_indexes() by filtering for unique indexes.

Parameters:

• connection (Connection) – SQLAlchemy connection

• table_name (str) – Table name

• schema (str | None) – Schema name (None for default schema)

• **kw (Any) – Additional keyword arguments

Returns:

• name: Constraint name

• column_names: List of column names

Return type:

List of dictionaries with keys

get_check_constraints(connection, table_name, schema=None, **kw)

Get check constraints for a table.

Note: JDBC DatabaseMetaData doesn’t have a standard method for check constraints. This returns an empty list. Database-specific dialects should override this method to query system tables if check constraint information is needed.

Parameters:

• connection (Connection) – SQLAlchemy connection

• table_name (str) – Table name

• schema (str | None) – Schema name (None for default schema)

• **kw (Any) – Additional keyword arguments

Returns:

• name: Constraint name

• sqltext: Check constraint SQL expression

Return type:

List of dictionaries with keys

__repr__()

String representation of the dialect.

PostgreSQL Dialect

Oracle Dialect

MySQL Dialect

SQL Server Dialect

DB2 Dialect

OceanBase Dialect

SQLite Dialect

ODBC Dialects

Base ODBC dialect for SQLAlchemy.

Provides the foundation for ODBC-based database dialects using pyodbc.

class sqlalchemy_jdbcapi.dialects.odbc_base.ODBCDialect(paramstyle=None, isolation_level=None, dbapi=None, implicit_returning=True, supports_native_boolean=None, max_identifier_length=None, label_length=None, insertmanyvalues_page_size=_NoArg.NO_ARG, use_insertmanyvalues=None, compiler_linting=0, server_side_cursors=False, skip_autocommit_rollback=False, **kwargs)

Bases: DefaultDialect

Base ODBC dialect for SQLAlchemy.

This provides common functionality for all ODBC-based dialects.

driver: str = 'pyodbc'

identifying name for the dialect’s DBAPI

supports_statement_cache: bool = True

indicates if this dialect supports caching.

All dialects that are compatible with statement caching should set this flag to True directly on each dialect class and subclass that supports it. SQLAlchemy tests that this flag is locally present on each dialect subclass before it will use statement caching. This is to provide safety for legacy or new dialects that are not yet fully tested to be compliant with SQL statement caching.

Added in version 1.4.5.

See also

Caching for Third Party Dialects

supports_native_decimal: bool = True

indicates if Decimal objects are handled and returned for precision numeric types, or if floats are returned

supports_unicode_binds = True

supports_unicode_statements = True

supports_multivalues_insert: bool = True

Target database supports INSERT…VALUES with multiple value sets, i.e. INSERT INTO table (cols) VALUES (…), (…), (…), …

supports_native_boolean: bool = False

Indicates if the dialect supports a native boolean construct. This will prevent _types.Boolean from generating a CHECK constraint when that type is used.

default_paramstyle = 'qmark'

poolclass

alias of QueuePool

pyodbc_driver_name: str | None = None

default_schema_name: str | None = None

the name of the default schema. This value is only available for supporting dialects, and is typically populated during the initial connection to the database.

classmethod import_dbapi()

Import the pyodbc module.

create_connect_args(url)

Build connection arguments from SQLAlchemy URL.

Parameters:

url (URL) – SQLAlchemy connection URL.

Returns:

Tuple of (args, kwargs) for connect function.

Return type:

tuple[list[Any], dict[str, Any]]

get_schema_names(connection, **kwargs)

Get list of schema names.

Parameters:

• connection (SAConnection) – SQLAlchemy connection.

• **kwargs (Any) – Additional arguments.

Returns:

List of schema names.

Return type:

list[str]

get_table_names(connection, schema=None, **kwargs)

Get list of table names in a schema.

Parameters:

• connection (SAConnection) – SQLAlchemy connection.

• schema (str | None) – Schema name (optional).

• **kwargs (Any) – Additional arguments.

Returns:

List of table names.

Return type:

list[str]

get_view_names(connection, schema=None, **kwargs)

Get list of view names in a schema.

Parameters:

• connection (SAConnection) – SQLAlchemy connection.

• schema (str | None) – Schema name (optional).

• **kwargs (Any) – Additional arguments.

Returns:

List of view names.

Return type:

list[str]

has_table(connection, table_name, schema=None, **kwargs)

Check if a table exists.

Parameters:

• connection (SAConnection) – SQLAlchemy connection.

• table_name (str) – Table name.

• schema (str | None) – Schema name (optional).

• **kwargs (Any) – Additional arguments.

Returns:

True if table exists, False otherwise.

Return type:

bool

get_columns(connection, table_name, schema=None, **kwargs)

Get column information for a table.

Parameters:

• connection (SAConnection) – SQLAlchemy connection.

• table_name (str) – Table name.

• schema (str | None) – Schema name (optional).

• **kwargs (Any) – Additional arguments.

Returns:

List of column dictionaries.

Return type:

list[ReflectedColumn]

get_pk_constraint(connection, table_name, schema=None, **kwargs)

Get primary key constraint information.

Parameters:

• connection (SAConnection) – SQLAlchemy connection.

• table_name (str) – Table name.

• schema (str | None) – Schema name (optional).

• **kwargs (Any) – Additional arguments.

Returns:

Dictionary with ‘constrained_columns’ and ‘name’.

Return type:

dict[str, Any]

get_foreign_keys(connection, table_name, schema=None, **kwargs)

Get foreign key constraints.

Parameters:

• connection (SAConnection) – SQLAlchemy connection.

• table_name (str) – Table name.

• schema (str | None) – Schema name (optional).

• **kwargs (Any) – Additional arguments.

Returns:

List of foreign key dictionaries.

Return type:

list[dict[str, Any]]

get_indexes(connection, table_name, schema=None, **kwargs)

Get index information.

Parameters:

• connection (SAConnection) – SQLAlchemy connection.

• table_name (str) – Table name.

• schema (str | None) – Schema name (optional).

• **kwargs (Any) – Additional arguments.

Returns:

List of index dictionaries.

Return type:

list[dict[str, Any]]

class sqlalchemy_jdbcapi.dialects.odbc_postgresql.PostgreSQLODBCDialect(native_inet_types=None, json_serializer=None, json_deserializer=None, **kwargs)

Bases: ODBCDialect, PGDialect

PostgreSQL ODBC dialect.

Supports PostgreSQL via ODBC using the PostgreSQL Unicode ODBC driver.

Recommended ODBC Driver:

• PostgreSQL Unicode (psqlODBC): Latest version

• Download: https://www.postgresql.org/ftp/odbc/versions/

Connection URL:

odbcapi+postgresql://user:password@host:5432/database

name: str = 'postgresql'

identifying name for the dialect from a DBAPI-neutral point of view (i.e. ‘sqlite’)

driver: str = 'odbcapi+postgresql'

identifying name for the dialect’s DBAPI

pyodbc_driver_name: str | None = 'PostgreSQL Unicode'

default_schema_name: str | None = 'public'

the name of the default schema. This value is only available for supporting dialects, and is typically populated during the initial connection to the database.

supports_sequences: bool = True

Indicates if the dialect supports CREATE SEQUENCE or similar.

supports_native_boolean: bool = True

Indicates if the dialect supports a native boolean construct. This will prevent _types.Boolean from generating a CHECK constraint when that type is used.

classmethod import_dbapi()

Import pyodbc module.

class sqlalchemy_jdbcapi.dialects.odbc_mysql.MySQLODBCDialect(json_serializer=None, json_deserializer=None, is_mariadb=None, **kwargs)

Bases: ODBCDialect, MySQLDialect

MySQL ODBC dialect.

Supports MySQL via ODBC using the MySQL Connector/ODBC driver.

Recommended ODBC Driver:

• MySQL Connector/ODBC 8.0+

• Download: https://dev.mysql.com/downloads/connector/odbc/

Connection URL:

odbcapi+mysql://user:password@host:3306/database

name: str = 'mysql'

identifying name for the dialect from a DBAPI-neutral point of view (i.e. ‘sqlite’)

driver: str = 'odbcapi+mysql'

identifying name for the dialect’s DBAPI

pyodbc_driver_name: str | None = 'MySQL ODBC 8.0 Driver'

supports_native_decimal: bool = True

indicates if Decimal objects are handled and returned for precision numeric types, or if floats are returned

classmethod import_dbapi()

Import pyodbc module.

class sqlalchemy_jdbcapi.dialects.odbc_mssql.MSSQLODBCDialect(query_timeout=None, use_scope_identity=True, schema_name='dbo', deprecate_large_types=None, supports_comments=None, json_serializer=None, json_deserializer=None, legacy_schema_aliasing=None, ignore_no_transaction_on_rollback=False, **opts)

Bases: ODBCDialect, MSDialect

Microsoft SQL Server ODBC dialect.

Supports SQL Server via ODBC using the Microsoft ODBC Driver for SQL Server.

Recommended ODBC Driver:

• ODBC Driver 18 for SQL Server (latest)

• ODBC Driver 17 for SQL Server

• Download: https://learn.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server

Connection URL:

odbcapi+mssql://user:password@host:1433/database odbcapi+sqlserver://user:password@host:1433/database (alias)

Features:

• Full T-SQL support

• Window functions

• CTEs (Common Table Expressions)

• JSON support (SQL Server 2016+)

• Sequence support (SQL Server 2012+)

name: str = 'mssql'

identifying name for the dialect from a DBAPI-neutral point of view (i.e. ‘sqlite’)

driver: str = 'odbcapi+mssql'

identifying name for the dialect’s DBAPI

pyodbc_driver_name: str | None = 'ODBC Driver 18 for SQL Server'

default_schema_name: str | None = 'dbo'

the name of the default schema. This value is only available for supporting dialects, and is typically populated during the initial connection to the database.

supports_sequences: bool = True

Indicates if the dialect supports CREATE SEQUENCE or similar.

supports_native_boolean: bool = False

Indicates if the dialect supports a native boolean construct. This will prevent _types.Boolean from generating a CHECK constraint when that type is used.

classmethod import_dbapi()

Import pyodbc module.

class sqlalchemy_jdbcapi.dialects.odbc_oracle.OracleODBCDialect(use_ansi=True, optimize_limits=False, use_binds_for_limits=None, use_nchar_for_unicode=False, exclude_tablespaces=('SYSTEM', 'SYSAUX'), enable_offset_fetch=True, **kwargs)

Bases: ODBCDialect, OracleDialect

Oracle ODBC dialect.

Supports Oracle Database via ODBC using the Oracle ODBC driver.

Recommended ODBC Driver:

• Oracle Instant Client ODBC (latest)

• Download: https://www.oracle.com/database/technologies/instant-client/downloads.html

Connection URL:

odbcapi+oracle://user:password@host:1521/service_name

Features:

• Full Oracle SQL support

• Sequences

• Synonyms

• Database links

• PL/SQL support

name: str = 'oracle'

identifying name for the dialect from a DBAPI-neutral point of view (i.e. ‘sqlite’)

driver: str = 'odbcapi+oracle'

identifying name for the dialect’s DBAPI

pyodbc_driver_name: str | None = 'Oracle in instantclient_21_13'

supports_sequences: bool = True

Indicates if the dialect supports CREATE SEQUENCE or similar.

supports_native_boolean: bool = False

Indicates if the dialect supports a native boolean construct. This will prevent _types.Boolean from generating a CHECK constraint when that type is used.

classmethod import_dbapi()

Import pyodbc module.