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.
If checkfirst is True, it uses 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, "{sql};".format(sql=compsql))
writeline_nl(fileobj,
sql_comment("Schema (for dialect {}):".format(dialect_name)))
engine = create_engine('{dialect}://'.format(dialect=dialect_name),
strategy='mock', executor=dump)
metadata.create_all(engine, checkfirst=checkfirst)
def dump_orm_object_as_insert_sql(engine: Engine,
obj: object,
fileobj: TextIO) -> None:
# literal_query = make_literal_query_fn(engine.dialect)
insp = inspect(obj)
# insp: an InstanceState
# http://docs.sqlalchemy.org/en/latest/orm/internals.html#sqlalchemy.orm.state.InstanceState # noqa
# insp.mapper: a Mapper
# http://docs.sqlalchemy.org/en/latest/orm/mapping_api.html#sqlalchemy.orm.mapper.Mapper # noqa
# Don't do this:
# table = insp.mapper.mapped_table
# Do this instead. The method above gives you fancy data types like list
# and Arrow on the Python side. We want the bog-standard datatypes drawn
# from the database itself.
meta = MetaData(bind=engine)
table_name = insp.mapper.mapped_table.name
# log.debug("table_name: {}".format(table_name))
table = Table(table_name, meta, autoload=True)
# log.debug("table: {}".format(table))
# NewRecord = quick_mapper(table)
# columns = table.columns.keys()
query = sql.select(table.columns)
# log.debug("query: {}".format(query))
for orm_pkcol in insp.mapper.primary_key:
core_pkcol = table.columns.get(orm_pkcol.name)
pkval = getattr(obj, orm_pkcol.name)
query = query.where(core_pkcol == pkval)
# log.debug("query: {}".format(query))
cursor = engine.execute(query)
row = cursor.fetchone() # should only be one...
row_dict = dict(row)
# log.debug("obj: {}".format(obj))
# log.debug("row_dict: {}".format(row_dict))
statement = table.insert(values=row_dict)
# insert_str = literal_query(statement)
insert_str = get_literal_query(statement, bind=engine)
writeline_nl(fileobj, insert_str)
def dump_orm_object_as_insert_sql(engine: Engine, obj: object,
fileobj: TextIO) -> None:
# literal_query = make_literal_query_fn(engine.dialect)
insp = inspect(obj)
# insp: an InstanceState
# http://docs.sqlalchemy.org/en/latest/orm/internals.html#sqlalchemy.orm.state.InstanceState # noqa
# insp.mapper: a Mapper
# http://docs.sqlalchemy.org/en/latest/orm/mapping_api.html#sqlalchemy.orm.mapper.Mapper # noqa
# Don't do this:
# table = insp.mapper.mapped_table
# Do this instead. The method above gives you fancy data types like list
# and Arrow on the Python side. We want the bog-standard datatypes drawn
# from the database itself.
meta = MetaData(bind=engine)
table_name = insp.mapper.mapped_table.name
# log.debug("table_name: {}".format(table_name))
table = Table(table_name, meta, autoload=True)
# log.debug("table: {}".format(table))
# NewRecord = quick_mapper(table)
# columns = table.columns.keys()
query = sql.select(table.columns)
# log.debug("query: {}".format(query))
for orm_pkcol in insp.mapper.primary_key:
core_pkcol = table.columns.get(orm_pkcol.name)
pkval = getattr(obj, orm_pkcol.name)
query = query.where(core_pkcol == pkval)
# log.debug("query: {}".format(query))
cursor = engine.execute(query)
row = cursor.fetchone() # should only be one...
row_dict = dict(row)
# log.debug("obj: {}".format(obj))
# log.debug("row_dict: {}".format(row_dict))
statement = table.insert(values=row_dict)
# insert_str = literal_query(statement)
insert_str = get_literal_query(statement, bind=engine)
writeline_nl(fileobj, insert_str)
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.
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 # noqa
- Not so obvious how we can iterate through the list of ORM objects and
guarantee correct insertion order with respect to all FKs.
"""
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(baseobj):
dump_orm_object_as_insert_sql(engine, part, fileobj)
bulk_insert_extras(engine.dialect.name, fileobj, start=False)
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.
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 # noqa
- Not so obvious how we can iterate through the list of ORM objects and
guarantee correct insertion order with respect to all FKs.
"""
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(baseobj):
dump_orm_object_as_insert_sql(engine, part, fileobj)
bulk_insert_extras(engine.dialect.name, fileobj, start=False)
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.
If checkfirst is True, it uses 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, "{sql};".format(sql=compsql))
writeline_nl(fileobj,
sql_comment("Schema (for dialect {}):".format(dialect_name)))
engine = create_engine('{dialect}://'.format(dialect=dialect_name),
strategy='mock',
executor=dump)
metadata.create_all(engine, checkfirst=checkfirst)
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:
# 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={}".format(table_name))
writelines_nl(fileobj, [
sql_comment("Data for table: {}".format(table_name)),
sql_comment("Filters: {}".format(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: {}".format(meta)) # obscures password
# log.debug("table: {}".format(table))
# log.debug("table.columns: {}".format(repr(table.columns)))
# log.debug("multirow: {}".format(multirow))
query = sql.select(table.columns)
if wheredict:
for k, v in wheredict.items():
col = table.columns.get(k)
query = query.where(col == v)
# log.debug("query: {}".format(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: {}".format(row_dict_list))
statement = table.insert().values(row_dict_list)
# log.debug("statement: {}".format(repr(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:
for r in cursor:
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: {}".format(row_dict))
# log.debug("insert_str: {}".format(insert_str))
writeline_nl(fileobj, insert_str)
log.debug("... done")
def dump(querysql, *multiparams, **params):
compsql = querysql.compile(dialect=engine.dialect)
writeline_nl(fileobj, "{sql};".format(sql=compsql))
def dump_connection_info(engine: Engine, fileobj: TextIO = sys.stdout) -> None:
"""
Dumps some connection info. Obscures passwords.
"""
meta = MetaData(bind=engine)
writeline_nl(fileobj, sql_comment('Database info: {}'.format(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:
# 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={}".format(table_name))
writelines_nl(fileobj, [
sql_comment("Data for table: {}".format(table_name)),
sql_comment("Filters: {}".format(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: {}".format(meta)) # obscures password
# log.debug("table: {}".format(table))
# log.debug("table.columns: {}".format(repr(table.columns)))
# log.debug("multirow: {}".format(multirow))
query = sql.select(table.columns)
if wheredict:
for k, v in wheredict.items():
col = table.columns.get(k)
query = query.where(col == v)
# log.debug("query: {}".format(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: {}".format(row_dict_list))
statement = table.insert().values(row_dict_list)
# log.debug("statement: {}".format(repr(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:
for r in cursor:
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: {}".format(row_dict))
# log.debug("insert_str: {}".format(insert_str))
writeline_nl(fileobj, insert_str)
log.debug("... done")
def dump(querysql, *multiparams, **params):
compsql = querysql.compile(dialect=engine.dialect)
writeline_nl(fileobj, "{sql};".format(sql=compsql))
def dump_connection_info(engine: Engine, fileobj: TextIO = sys.stdout) -> None:
"""
Dumps some connection info. Obscures passwords.
"""
meta = MetaData(bind=engine)
writeline_nl(fileobj, sql_comment('Database info: {}'.format(meta)))
