def dump_ddl(metadata: MetaData,
dialect_name: str,
fileobj: TextIO = sys.stdout,
checkfirst: bool = True) -> None:
"""
Sends schema-creating DDL from the metadata to the dump engine.
This makes ``CREATE TABLE`` statements.
Args:
metadata: SQLAlchemy :class:`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.
"""
# http://docs.sqlalchemy.org/en/rel_0_8/faq.html#how-can-i-get-the-create-table-drop-table-output-as-a-string # noqa
# http://stackoverflow.com/questions/870925/how-to-generate-a-file-with-ddl-in-the-engines-sql-dialect-in-sqlalchemy # noqa
# https://github.com/plq/scripts/blob/master/pg_dump.py
# noinspection PyUnusedLocal
def dump(querysql, *multiparams, **params):
compsql = querysql.compile(dialect=engine.dialect)
writeline_nl(fileobj, f"{compsql};")
writeline_nl(fileobj, sql_comment(f"Schema (for dialect {dialect_name}):"))
engine = create_engine(f'{dialect_name}://',
strategy='mock',
executor=dump)
metadata.create_all(engine, checkfirst=checkfirst)
def dump_orm_tree_as_insert_sql(engine: Engine, baseobj: object,
fileobj: TextIO) -> None:
"""
Sends an object, and all its relations (discovered via "relationship"
links) as ``INSERT`` commands in SQL, to ``fileobj``.
Args:
engine: SQLAlchemy :class:`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):
http://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.
""" # noqa
writeline_nl(
fileobj,
sql_comment("Data for all objects related to the first below:"))
bulk_insert_extras(engine.dialect.name, fileobj, start=True)
for part in walk_orm_tree(baseobj):
dump_orm_object_as_insert_sql(engine, part, fileobj)
bulk_insert_extras(engine.dialect.name, fileobj, start=False)
def save_to_sql(self, fileobj: TextIO, filename: str) -> None:
"""
Writes a SQLAlchemy ORM tree, as SQL, to a disk file.
Args:
fileobj: the file-like object to which to write
filename: the filename (for cosmetic purposes only)
"""
session = self.dbsession
engine = session.bind
writelines_nl(fileobj, [
sql_comment('whisker_serial_order data file'),
sql_comment('Filename: {}'.format(filename)),
sql_comment('Created at: {}'.format(arrow.now())),
sql_comment('=' * 76)
])
dump_connection_info(engine, fileobj)
dump_ddl(Base.metadata, dialect_name=engine.dialect.name,
fileobj=fileobj)
dump_orm_tree_as_insert_sql(engine, self.tasksession, fileobj)
def dump_connection_info(engine: Engine, fileobj: TextIO = sys.stdout) -> None:
"""
Dumps some connection info, as an SQL comment. Obscures passwords.
Args:
engine: the SQLAlchemy :class:`Engine` to dump metadata information
from
fileobj: the file-like object (default ``sys.stdout``) to write
information to
"""
meta = MetaData(bind=engine)
writeline_nl(fileobj, sql_comment(f'Database info: {meta}'))
def dump_table_as_insert_sql(engine: Engine,
table_name: str,
fileobj: TextIO,
wheredict: Dict[str, Any] = None,
include_ddl: bool = False,
multirow: bool = False) -> None:
"""
Reads a table from the database, and writes SQL to replicate the table's
data to the output ``fileobj``.
Args:
engine: SQLAlchemy :class:`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
"""
# http://stackoverflow.com/questions/5631078/sqlalchemy-print-the-actual-query # noqa
# http://docs.sqlalchemy.org/en/latest/faq/sqlexpressions.html
# http://www.tylerlesmann.com/2009/apr/27/copying-databases-across-platforms-sqlalchemy/ # noqa
# https://github.com/plq/scripts/blob/master/pg_dump.py
log.info("dump_data_as_insert_sql: table_name={}", table_name)
writelines_nl(fileobj, [
SEP1,
sql_comment(f"Data for table: {table_name}"),
SEP2,
sql_comment(f"Filters: {wheredict}"),
])
dialect = engine.dialect
if not dialect.supports_multivalues_insert:
multirow = False
if multirow:
log.warning("dump_data_as_insert_sql: multirow parameter substitution "
"not working yet")
multirow = False
# literal_query = make_literal_query_fn(dialect)
meta = MetaData(bind=engine)
log.debug("... retrieving schema")
table = Table(table_name, meta, autoload=True)
if include_ddl:
log.debug("... producing DDL")
dump_ddl(table.metadata,
dialect_name=engine.dialect.name,
fileobj=fileobj)
# NewRecord = quick_mapper(table)
# columns = table.columns.keys()
log.debug("... fetching records")
# log.debug("meta: {}", meta) # obscures password
# log.debug("table: {}", table)
# log.debug("table.columns: {!r}", table.columns)
# log.debug("multirow: {}", multirow)
query = select(table.columns)
if wheredict:
for k, v in wheredict.items():
col = table.columns.get(k)
query = query.where(col == v)
# log.debug("query: {}", query)
cursor = engine.execute(query)
if multirow:
row_dict_list = []
for r in cursor:
row_dict_list.append(dict(r))
# log.debug("row_dict_list: {}", row_dict_list)
if row_dict_list:
statement = table.insert().values(row_dict_list)
# log.debug("statement: {!r}", statement)
# insert_str = literal_query(statement)
insert_str = get_literal_query(statement, bind=engine)
# NOT WORKING FOR MULTIROW INSERTS. ONLY SUBSTITUTES FIRST ROW.
writeline_nl(fileobj, insert_str)
else:
writeline_nl(fileobj, sql_comment("No data!"))
else:
found_one = False
for r in cursor:
found_one = True
row_dict = dict(r)
statement = table.insert(values=row_dict)
# insert_str = literal_query(statement)
insert_str = get_literal_query(statement, bind=engine)
# log.debug("row_dict: {}", row_dict)
# log.debug("insert_str: {}", insert_str)
writeline_nl(fileobj, insert_str)
if not found_one:
writeline_nl(fileobj, sql_comment("No data!"))
writeline_nl(fileobj, SEP2)
log.debug("... done")
from sqlalchemy.sql.base import Executable
from sqlalchemy.sql.elements import BindParameter
from sqlalchemy.sql.expression import select
from sqlalchemy.sql.schema import MetaData, Table
from sqlalchemy.sql.sqltypes import DateTime, NullType, String
from cardinal_pythonlib.file_io import writeline_nl, writelines_nl
from cardinal_pythonlib.logs import get_brace_style_log_with_null_handler
from cardinal_pythonlib.sql.literals import sql_comment
from cardinal_pythonlib.sqlalchemy.dialect import SqlaDialectName
from cardinal_pythonlib.sqlalchemy.orm_inspect import walk_orm_tree
from cardinal_pythonlib.sqlalchemy.schema import get_table_names
log = get_brace_style_log_with_null_handler(__name__)
SEP1 = sql_comment("=" * 76)
SEP2 = sql_comment("-" * 76)
# =============================================================================
# Dump functions: get DDL and/or data as SQL commands
# =============================================================================
def dump_connection_info(engine: Engine, fileobj: TextIO = sys.stdout) -> None:
"""
Dumps some connection info, as an SQL comment. Obscures passwords.
Args:
engine: the SQLAlchemy :class:`Engine` to dump metadata information
from
fileobj: the file-like object (default ``sys.stdout``) to write