def bulk_insert_extras(dialect_name: str, fileobj: TextIO,
start: bool) -> None:
"""
Writes bulk insert preamble (start=True) or end (start=False).
"""
lines = []
if dialect_name == 'mysql':
if start:
lines = [
"SET autocommit=0;",
"SET unique_checks=0;",
"SET foreign_key_checks=0;",
]
else:
lines = [
"SET foreign_key_checks=1;",
"SET unique_checks=1;",
"COMMIT;",
]
writelines_nl(fileobj, lines)
def bulk_insert_extras(dialect_name: str,
fileobj: TextIO,
start: bool) -> None:
"""
Writes bulk insert preamble (start=True) or end (start=False).
"""
lines = []
if dialect_name == 'mysql':
if start:
lines = [
"SET autocommit=0;",
"SET unique_checks=0;",
"SET foreign_key_checks=0;",
]
else:
lines = [
"SET foreign_key_checks=1;",
"SET unique_checks=1;",
"COMMIT;",
]
writelines_nl(fileobj, lines)
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_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")
