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")