cardinal_pythonlib.sqlalchemy.dump

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 large-scale dumping of data from SQLAlchemy systems.

class cardinal_pythonlib.sqlalchemy.dump.StringLiteral(length=None, collation=None, convert_unicode=False, unicode_error=None, _warn_on_bytestring=False, _expect_unicode=False)[source]

Teach SQLAlchemy how to literalize various things. Used by make_literal_query_fn, below. See https://stackoverflow.com/questions/5631078/sqlalchemy-print-the-actual-query

Create a string-holding type.

Parameters:

length¶ – optional, a length for the column for use in DDL and CAST expressions. May be safely omitted if no CREATE TABLE will be issued. Certain databases may require a length for use in DDL, and will raise an exception when the CREATE TABLE DDL is issued if a VARCHAR with no length is included. Whether the value is interpreted as bytes or characters is database specific.

collation¶ –

Optional, a column-level collation for use in DDL and CAST expressions. Renders using the COLLATE keyword supported by SQLite, MySQL, and PostgreSQL. E.g.:

>>> from sqlalchemy import cast, select, String
>>> print(select(cast('some string', String(collation='utf8'))))
SELECT CAST(:param_1 AS VARCHAR COLLATE utf8) AS anon_1

convert_unicode¶ –
When set to True, the String type will assume that input is to be passed as Python Unicode objects under Python 2, and results returned as Python Unicode objects. In the rare circumstance that the DBAPI does not support Python unicode under Python 2, SQLAlchemy will use its own encoder/decoder functionality on strings, referring to the value of the _sa.create_engine.encoding parameter parameter passed to _sa.create_engine() as the encoding.

Deprecated since version 1.3: The String.convert_unicode parameter is deprecated and will be removed in a future release. All modern DBAPIs now support Python Unicode directly and this parameter is unnecessary.

For the extremely rare case that Python Unicode is to be encoded/decoded by SQLAlchemy on a backend that does natively support Python Unicode, the string value "force" can be passed here which will cause SQLAlchemy’s encode/decode services to be used unconditionally.

Note

SQLAlchemy’s unicode-conversion flags and features only apply to Python 2; in Python 3, all string objects are Unicode objects. For this reason, as well as the fact that virtually all modern DBAPIs now support Unicode natively even under Python 2, the String.convert_unicode flag is inherently a legacy feature.

Note

In the vast majority of cases, the Unicode or UnicodeText datatypes should be used for a _schema.Column that expects to store non-ascii data. These datatypes will ensure that the correct types are used on the database side as well as set up the correct Unicode behaviors under Python 2.

See also

_sa.create_engine.convert_unicode - _engine.Engine-wide parameter
unicode_error¶ –
Optional, a method to use to handle Unicode conversion errors. Behaves like the errors keyword argument to the standard library’s string.decode() functions, requires that String.convert_unicode is set to "force"

Deprecated since version 1.3: The String.unicode_errors parameter is deprecated and will be removed in a future release. This parameter is unnecessary for modern Python DBAPIs and degrades performance significantly.

literal_processor(dialect: DefaultDialect) → Callable[[Any], str][source]: Returns a function to translate any value to a string.

cardinal_pythonlib.sqlalchemy.dump.bulk_insert_extras(dialect_name: str, fileobj: TextIO, start: bool) → None[source]

Writes bulk INSERT preamble (start=True) or end (start=False).

For MySQL, this temporarily switches off autocommit behaviour and index/FK checks, for speed, then re-enables them at the end and commits.

Parameters:

dialect_name¶ – SQLAlchemy dialect name (see SqlaDialectName)
fileobj¶ – file-like object to write to
start¶ – if True, write preamble; if False, write end

cardinal_pythonlib.sqlalchemy.dump.dump_connection_info(engine: ~sqlalchemy.engine.base.Engine, fileobj: ~typing.TextIO = <_io.TextIOWrapper name='<stdout>' mode='w' encoding='utf-8'>) → None[source]

Dumps some connection info, as an SQL comment. Obscures passwords.

Parameters:

engine¶ – the SQLAlchemy Engine to dump metadata information from
fileobj¶ – the file-like object (default sys.stdout) to write information to

cardinal_pythonlib.sqlalchemy.dump.dump_database_as_insert_sql(engine: ~sqlalchemy.engine.base.Engine, fileobj: ~typing.TextIO = <_io.TextIOWrapper name='<stdout>' mode='w' encoding='utf-8'>, include_ddl: bool = False, multirow: bool = False) → None[source]

Reads an entire database and writes SQL to replicate it to the output file-like object.

Parameters:

engine¶ – SQLAlchemy Engine
fileobj¶ – file-like object to write to
include_ddl¶ – if True, include the DDL to create the table as well
multirow¶ – write multi-row INSERT statements

cardinal_pythonlib.sqlalchemy.dump.dump_ddl(metadata: ~sqlalchemy.sql.schema.MetaData, dialect_name: str, fileobj: ~typing.TextIO = <_io.TextIOWrapper name='<stdout>' mode='w' encoding='utf-8'>, checkfirst: bool = True) → None[source]

Sends schema-creating DDL from the metadata to the dump engine. This makes CREATE TABLE statements.

Parameters:

metadata¶ – SQLAlchemy MetaData
dialect_name¶ – string name of SQL dialect to generate DDL in
fileobj¶ – file-like object to send DDL to
checkfirst¶ – if True, use CREATE TABLE IF NOT EXISTS or equivalent.

cardinal_pythonlib.sqlalchemy.dump.dump_orm_object_as_insert_sql(engine: Engine, obj: object, fileobj: TextIO) → None[source]

Takes a SQLAlchemy ORM object, and writes INSERT SQL to replicate it to the output file-like object.

Parameters:

engine¶ – SQLAlchemy Engine
obj¶ – SQLAlchemy ORM object to write
fileobj¶ – file-like object to write to

cardinal_pythonlib.sqlalchemy.dump.dump_orm_tree_as_insert_sql(engine: Engine, baseobj: object, fileobj: TextIO) → None[source]

Sends an object, and all its relations (discovered via “relationship” links) as INSERT commands in SQL, to fileobj.

Parameters:

engine¶ – SQLAlchemy Engine
baseobj¶ – starting SQLAlchemy ORM object
fileobj¶ – file-like object to write to

Problem: foreign key constraints.

MySQL/InnoDB doesn’t wait to the end of a transaction to check FK integrity (which it should): https://stackoverflow.com/questions/5014700/in-mysql-can-i-defer-referential-integrity-checks-until-commit # noqa
PostgreSQL can.
Anyway, slightly ugly hacks… https://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-bulk-data-loading.html
Not so obvious how we can iterate through the list of ORM objects and guarantee correct insertion order with respect to all FKs.

cardinal_pythonlib.sqlalchemy.dump.dump_table_as_insert_sql(engine: Engine, table_name: str, fileobj: TextIO, wheredict: Dict[str, Any] | None = None, include_ddl: bool = False, multirow: bool = False) → None[source]

Reads a table from the database, and writes SQL to replicate the table’s data to the output fileobj.

Parameters:

engine¶ – SQLAlchemy Engine
table_name¶ – name of the table
fileobj¶ – file-like object to write to
wheredict¶ – optional dictionary of {column_name: value} to use as WHERE filters
include_ddl¶ – if True, include the DDL to create the table as well
multirow¶ – write multi-row INSERT statements

cardinal_pythonlib.sqlalchemy.dump.get_literal_query(statement: Query | Executable, bind: Connectable | None = None) → str[source]

Takes an SQLAlchemy statement and produces a literal SQL version, with values filled in.

As per https://stackoverflow.com/questions/5631078/sqlalchemy-print-the-actual-query

Notes: - for debugging purposes only - insecure; you should always separate queries from their values - please also note that this function is quite slow

Parameters:

statement¶ – the SQL statement (a SQLAlchemy object) to use
bind¶ – if the statement is unbound, you will need to specify an object here that supports SQL execution

Returns:

a string literal version of the query.

cardinal_pythonlib.sqlalchemy.dump.make_literal_query_fn(dialect: Dialect | DefaultDialect) → Callable[[ClauseElement | Query], str][source]: Returns a function that converts SQLAlchemy statements to literal representations.

cardinal_pythonlib.sqlalchemy.dump.quick_mapper(table: Table) → Type[DeclarativeMeta][source]

Makes a new SQLAlchemy mapper for an existing table. See https://www.tylerlesmann.com/2009/apr/27/copying-databases-across-platforms-sqlalchemy/

Parameters:: table¶ – SQLAlchemy Table object
Returns:: a DeclarativeMeta class