sqlcompyre.api module

sqlcompyre.api.compare_schemas(engine: Engine, left: str, right: str, include_views: bool = False, float_precision: float = 2.220446049250313e-16, collation: str | None = None, ignore_casing: bool = False) → SchemaComparison

Compare all tables from two schemas in the database. For multi-part schemas (e.g. for MSSQL), it is possible to only specify the first part of the schema and compare tables from all schemas. In MSSQL, this allows comparing entire databases.

Currently, the comparison only compares tables with the same name (and the same schema for multi-part schema comparisons).

Args:

engine: The engine to use to access the database. left: The name of the “left” schema from which to use tables. For multi-part schemas,

the schema may be specified as <database>.* which then references all schemas (and tables) in <database>. Note that this notation is merely a convention and general regex/glob notation is not supported.

right: The name of the “right” schema whose tables to compare to those of the “left”

one. The naming convention follows the convention for the “left” schema.

include_views: Whether to include views in the comparison. float_precision: The precision of floating point comparisons. Values with an absolute

difference below the precision are considered equal.

collation: An optional collation that is used to compare strings. Useful for making

case-sensitive comparisons even if a table’s column uses a case-insensitive collation.

ignore_casing: Whether casing (e.g. capitalization) should be ignored when matching

table names. This is valuable if only interacting with the database through case-insensitive tools (e.g. SQL).

Returns:

A schema comparison object.

sqlcompyre.api.compare_tables(engine: Engine, left: Select | FromClause | str, right: Select | FromClause | str, join_columns: list[str] | None = None, ignore_columns: list[str] | None = None, column_name_mapping: dict[str, str] | None = None, float_precision: float = 2.220446049250313e-16, collation: str | None = None, ignore_casing: bool = False, infer_primary_keys: bool = False) → TableComparison

Compare two tables in the database.

Args:

engine: The engine to use to access the database. left: The “left” database table for the comparison. The table can optionally be

specified with schema (and database) name. For MSSQL, the table name can be specified as [[<database>.]<schema>.]<table> depending on the “default” database of the provided engine and the database’s default schema. If provided as a SQLAlchemy table, the name is extracted automatically.

right: The “right” database table to compare to the “left” table. The naming convention

follows the convention for the “left” table.

join_columns: The columns to join the tables on in order to compare column values. If

not provided, defaults to the union of primary keys. The corresponding primary key(s) of the right table are determined via column_name_mapping.

ignore_columns: Columns to ignore to evaluate equality. These column names should

reference the left table: corresponding columns in the right table are determined via column_name_mapping. Primary key columns passed here are ignored.

column_name_mapping: A mapping from column names in the left table to column names in

the right table. If not provided, defaults to mapping columns with the same names.

float_precision: The precision of floating point comparisons. Values with an absolute

difference below the precision are considered equal.

collation: An optional collation that is used to compare strings. Useful for making

case-sensitive comparisons even if a table’s column uses a case-insensitive collation.

ignore_casing: Whether casing (e.g. capitalization) should be ignored when matching

column names. This is valuable if only interacting with the database through case-insensitive tools (e.g. SQL).

infer_primary_keys: Allows SQLCompyre to build a primary key from all matching columns

automatically and use it to match tables even if they do not have a primary key.

Returns:

A table comparison object that can be used to explore the differences in the tables.

sqlcompyre.api.inspect(engine: Engine, query: Select | FromClause) → QueryInspection

Inspect the results of a query in the database.

Args:

engine: The engine to use to access the database. query: The query whose results to inspect. This can either be a SQLAlchemy SELECT

statement or a FROM clause (which includes plain sqlalchemy.Table objects).

Returns:

A query inspection object that can be used to easily gain insights into the query result.

See also:

inspect_table() if you want to inspect the results of SELECT * FROM table and specify the table as a string.

sqlcompyre.api.inspect_table(engine: Engine, table: Table | str) → QueryInspection

Inspect a table in the database.

Args:

engine: The engine to use to access the database. table: The database table to inspect. When specified as string, the table can optionally

be specified with schema (and database) name. For MSSQL, the table name can be specified as [[<database>.]<schema>.]<table> depending on the “default” database of the provided engine and the database’s default schema.

Returns:

A query inspection object that can be used to easily gain insights into the table.

See also:

inspect() if you want to inspect only a subset of a table or have a sqlalchemy.Table object. This method should preferably only be used when specifying the table name as a string.