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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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, tofileobj
.- Parameters:
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: E501
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:
- 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
- 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