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