Original code copyright (C) 2009-2022 Rudolf Cardinal (

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

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

Create a string-holding type.

  • 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.


    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.


    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.

  • 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.

  • 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.

  • 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.

  • 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.

  • 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.

  • engine – SQLAlchemy Engine

  • baseobj – starting SQLAlchemy ORM object

  • fileobj – file-like object to write to

Problem: foreign key constraints.

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.

  • 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

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

  • 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


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


table – SQLAlchemy Table object


a DeclarativeMeta class