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

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(*args, **kwargs)[source]

Teach sqlalchemy how to literalize various things. Used by make_literal_query_fn, below. See https://stackoverflow.com/questions/5631078.

This __init__() function exists purely because the docstring in the SQLAlchemy superclass (String) has “pycon+sql” as its source type, which Sphinx warns about.

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) to our text file.

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 the engine’s 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.

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: ConnectionEventsTarget | 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