bi_etl.database.database_metadata module

Created on Dec 23, 2015

@author: Derek Wood

class bi_etl.database.database_metadata.DatabaseMetadata(bind=None, reflect=False, schema=None, quote_schema=None, naming_convention=immutabledict({'ix': 'ix_%(column_0_label)s'}), info=None, database_name=None, uses_bytes_length_limits=None)[source]

Bases: MetaData

A light wrapper over sqlalchemy.schema.MetaData

__init__(bind=None, reflect=False, schema=None, quote_schema=None, naming_convention=immutabledict({'ix': 'ix_%(column_0_label)s'}), info=None, database_name=None, uses_bytes_length_limits=None)[source]

Create a new MetaData object.

Parameters:

bind¶ –
An Engine or Connection to bind to. May also be a string or URL instance, these are passed to _sa.create_engine() and this _schema.MetaData will be bound to the resulting engine.

Deprecated since version 1.4: The _schema.MetaData.bind argument is deprecated and will be removed in SQLAlchemy 2.0.
schema¶ –
The default schema to use for the _schema.Table, Sequence, and potentially other objects associated with this _schema.MetaData. Defaults to None.

See also

Specifying a Default Schema Name with MetaData - details on how the _schema.MetaData.schema parameter is used.

_schema.Table.schema

Sequence.schema
quote_schema¶ – Sets the quote_schema flag for those _schema.Table, Sequence, and other objects which make usage of the local schema name.
info¶ –
Optional data dictionary which will be populated into the SchemaItem.info attribute of this object.

New in version 1.0.0.
naming_convention¶ –
a dictionary referring to values which will establish default naming conventions for Constraint and Index objects, for those objects which are not given a name explicitly.

The keys of this dictionary may be:
- a constraint or Index class, e.g. the UniqueConstraint, _schema.ForeignKeyConstraint class, the Index class
- a string mnemonic for one of the known constraint classes; "fk", "pk", "ix", "ck", "uq" for foreign key, primary key, index, check, and unique constraint, respectively.
- the string name of a user-defined “token” that can be used to define new naming tokens.
The values associated with each “constraint class” or “constraint mnemonic” key are string naming templates, such as "uq_%(table_name)s_%(column_0_name)s", which describe how the name should be composed. The values associated with user-defined “token” keys should be callables of the form fn(constraint, table), which accepts the constraint/index object and _schema.Table as arguments, returning a string result.

The built-in names are as follows, some of which may only be available for certain types of constraint:
- %(table_name)s - the name of the _schema.Table object associated with the constraint.
- %(referred_table_name)s - the name of the _schema.Table object associated with the referencing target of a _schema.ForeignKeyConstraint.
- %(column_0_name)s - the name of the _schema.Column at index position “0” within the constraint.
- %(column_0N_name)s - the name of all _schema.Column objects in order within the constraint, joined without a separator.
- %(column_0_N_name)s - the name of all _schema.Column objects in order within the constraint, joined with an underscore as a separator.
- %(column_0_label)s, %(column_0N_label)s, %(column_0_N_label)s - the label of either the zeroth _schema.Column or all Columns, separated with or without an underscore
- %(column_0_key)s, %(column_0N_key)s, %(column_0_N_key)s - the key of either the zeroth _schema.Column or all Columns, separated with or without an underscore
- %(referred_column_0_name)s, %(referred_column_0N_name)s %(referred_column_0_N_name)s, %(referred_column_0_key)s, %(referred_column_0N_key)s, … column tokens which render the names/keys/labels of columns that are referenced by a _schema.ForeignKeyConstraint.
- %(constraint_name)s - a special key that refers to the existing name given to the constraint. When this key is present, the Constraint object’s existing name will be replaced with one that is composed from template string that uses this token. When this token is present, it is required that the Constraint is given an explicit name ahead of time.
- user-defined: any additional token may be implemented by passing it along with a fn(constraint, table) callable to the naming_convention dictionary.
New in version 1.3.0: - added new %(column_0N_name)s, %(column_0_N_name)s, and related tokens that produce concatenations of names, keys, or labels for all columns referred to by a given constraint.

See also

Configuring Constraint Naming Conventions - for detailed usage examples.

begin(connection_name: str = None) → Transaction[source]

property bind

An _engine.Engine or _engine.Connection to which this _schema.MetaData is bound.

Typically, a _engine.Engine is assigned to this attribute so that “implicit execution” may be used, or alternatively as a means of providing engine binding information to an ORM Session object:

engine = create_engine("someurl://")
metadata.bind = engine

Deprecated since version 1.4: The metadata.bind attribute, as part of the deprecated system of “implicit execution”, is itself deprecated and will be removed in SQLAlchemy 2.0.

See also

dbengine_implicit - background on “bound metadata”

clear(): Clear all Table objects from this MetaData.

close_connection(connection_name: str = None)[source]

close_connections(exceptions: set = None)[source]

commit(connection_name: str = None)[source]

Commit based on a connection name rather than via a ‘sqlalchemy.engine.base.Transaction’ object (which you could call .commit() on

Parameters:: connection_name¶ –

connect(connection_name: str = None) → Connection[source]

connection(connection_name: str = None, open_if_not_exist: bool = True, open_if_closed: bool = True) → Connection[source]

create_all(bind=None, tables=None, checkfirst=True)

Create all tables stored in this metadata.

Conditional by default, will not attempt to recreate tables already present in the target database.

Parameters:

bind¶ –
A Connectable used to access the database; if None, uses the existing bind on this MetaData, if any.

Note

the “bind” argument will be required in SQLAlchemy 2.0.
tables¶ – Optional list of Table objects, which is a subset of the total tables in the MetaData (others are ignored).
checkfirst¶ – Defaults to True, don’t issue CREATEs for tables already present in the target database.

create_drop_stringify_dialect = 'default'

property dialect

property dialect_name

dispatch = <sqlalchemy.event.base.DDLEventsDispatch object>

dispose()[source]: This method leaves the possibility of checked-out connections remaining open, as it only affects connections that are idle in the pool.

drop_all(bind=None, tables=None, checkfirst=True)

Drop all tables stored in this metadata.

Conditional by default, will not attempt to drop tables not present in the target database.

Parameters:

bind¶ –
A Connectable used to access the database; if None, uses the existing bind on this MetaData, if any.

Note

the “bind” argument will be required in SQLAlchemy 2.0.
tables¶ – Optional list of Table objects, which is a subset of the total tables in the MetaData (others are ignored).
checkfirst¶ – Defaults to True, only issue DROPs for tables confirmed to be present in the target database.

drop_table_if_exists(table_name, schema=None, connection_name: str = None, transaction: bool = False, auto_close: bool = False)[source]

execute(sql, *list_params, transaction: bool = True, auto_close: bool = True, connection_name: str = None, **params)[source]

execute_direct(sql, return_results=False)[source]

execute_procedure(procedure_name, *args, return_results=False, dpapi_connection=None)[source]

Execute a stored procedure

Parameters:

procedure_name¶ (str) – The procedure to run.
args¶ – The arguments to pass
return_results¶ – Needs to be a keyword param. Should we try and get result rows from the procedure.
dpapi_connection¶ – A raw dpapi connection to use. Optional.

Raises:

sqlalchemy.exc.DBAPIError: – API error
sqlalchemy.exc.DatabaseError: – Proxy for database error

get_children(omit_attrs=(), **kw)

Return immediate child visitors.Traversible elements of this visitors.Traversible.

This is used for visit traversal.

**kw may contain flags that change the collection that is returned, for example to return a subset of items in order to cut down on larger traversals, or to return child items from a different context (such as schema-level collections instead of clause-level).

has_active_transaction(connection_name: str = None)[source]

info

Info dictionary associated with the object, allowing user-defined data to be associated with this SchemaItem.

The dictionary is automatically generated when first accessed. It can also be specified in the constructor of some objects, such as _schema.Table and _schema.Column.

is_bound(): True if this MetaData is bound to an Engine or Connection.

is_connected(connection_name: str = None) → bool[source]

static qualified_name(schema, table)[source]

reflect(bind=None, schema=None, views=False, only=None, extend_existing=False, autoload_replace=True, resolve_fks=True, **dialect_kwargs)

Load all available table definitions from the database.

Automatically creates Table entries in this MetaData for any table available in the database but not yet present in the MetaData. May be called multiple times to pick up tables recently added to the database, however no special action is taken if a table in this MetaData no longer exists in the database.

Parameters:

bind¶ –
A Connectable used to access the database; if None, uses the existing bind on this MetaData, if any.

Note

the “bind” argument will be required in SQLAlchemy 2.0.
schema¶ – Optional, query and reflect tables from an alternate schema. If None, the schema associated with this _schema.MetaData is used, if any.
views¶ – If True, also reflect views.
only¶ –
Optional. Load only a sub-set of available named tables. May be specified as a sequence of names or a callable.

If a sequence of names is provided, only those tables will be reflected. An error is raised if a table is requested but not available. Named tables already present in this MetaData are ignored.

If a callable is provided, it will be used as a boolean predicate to filter the list of potential table names. The callable is called with a table name and this MetaData instance as positional arguments and should return a true value for any table to reflect.
extend_existing¶ –
Passed along to each _schema.Table as _schema.Table.extend_existing.

New in version 0.9.1.
autoload_replace¶ –
Passed along to each _schema.Table as _schema.Table.autoload_replace.

New in version 0.9.1.
resolve_fks¶ –
if True, reflect _schema.Table objects linked to _schema.ForeignKey objects located in each _schema.Table. For _schema.MetaData.reflect(), this has the effect of reflecting related tables that might otherwise not be in the list of tables being reflected, for example if the referenced table is in a different schema or is omitted via the MetaData.reflect.only parameter. When False, _schema.ForeignKey objects are not followed to the _schema.Table in which they link, however if the related table is also part of the list of tables that would be reflected in any case, the _schema.ForeignKey object will still resolve to its related _schema.Table after the _schema.MetaData.reflect() operation is complete. Defaults to True.

New in version 1.3.0.

See also

_schema.Table.resolve_fks
**dialect_kwargs¶ –
Additional keyword arguments not mentioned above are dialect specific, and passed in the form <dialectname>_<argname>. See the documentation regarding an individual dialect at Dialects for detail on documented arguments.

New in version 0.9.2: - Added MetaData.reflect.**dialect_kwargs to support dialect-level reflection options for all _schema.Table objects reflected.

remove(table): Remove the given Table object from this MetaData.

rename_table(schema, table_name, new_table_name)[source]

resolve_connection_name(connection_name: str = None) → str[source]

rollback(connection_name: str = None)[source]

session()[source]

property sorted_tables

Returns a list of _schema.Table objects sorted in order of foreign key dependency.

The sorting will place _schema.Table objects that have dependencies first, before the dependencies themselves, representing the order in which they can be created. To get the order in which the tables would be dropped, use the reversed() Python built-in.

Warning

The MetaData.sorted_tables attribute cannot by itself accommodate automatic resolution of dependency cycles between tables, which are usually caused by mutually dependent foreign key constraints. When these cycles are detected, the foreign keys of these tables are omitted from consideration in the sort. A warning is emitted when this condition occurs, which will be an exception raise in a future release. Tables which are not part of the cycle will still be returned in dependency order.

To resolve these cycles, the _schema.ForeignKeyConstraint.use_alter parameter may be applied to those constraints which create a cycle. Alternatively, the _schema.sort_tables_and_constraints() function will automatically return foreign key constraints in a separate collection when cycles are detected so that they may be applied to a schema separately.

Changed in version 1.3.17: - a warning is emitted when MetaData.sorted_tables cannot perform a proper sort due to cyclical dependencies. This will be an exception in a future release. Additionally, the sort will continue to return other tables not involved in the cycle in dependency order which was not the case previously.