SQLAlchemy: How to transfer data from one table in an old DB to another table in a new/different DB?

Question

I am transfering some data from one DB to another DB using sqlalchemy in python. I want to make a direct and rapid transfer.

I don't know how to use the function of bulk_insert_mappings() from SQLAlchemy. (Field-wise both tables are identical)

This is what I have tried so far.

from sqlalchemy import create_engine, Column, Integer, String, Date  
from sqlalchemy.orm import sessionmaker  
from sqlalchemy.ext.declarative import declarative_base  

engine_old = create_engine('mysql+pymysql://<id>:<pw>@database_old.amazonaws.com:3306/schema_name_old?charset=utf8')  
engine_new = create_engine('mysql+pymysql://<id>:<pw>@database_new.amazonaws.com:3306/schema_name_new?charset=utf8')   

data_old = engine_before.execute('SELECT * FROM table_old')  

session = sessionmaker()  
session.configure(bind=engine_after)  
s = session()  

how to handle with "s.bulk_insert_mappings(????, data_old)"?**

Could anyone help me?

Thank you.

Answer 1

There are many ways to achieve moving data from one database to another. The specificity of the method depends your individual needs and what you already have implemented. Assuming that both databases old and new already have a schema in their respective DBs, you would need two separate bases and engines. The mapping of an existing database's schema is achieved using automap_base(). Below I am sharing a short example of how this would look like:

from sqlalchemy.orm import Session
from sqlalchemy import create_engine
from sqlalchemy.ext.automap import automap_base

old_base = automap_base()
old_engine = create_engine("<OLD_DB_URI>", echo=True)
old_base.prepare(old_engine, reflect=True)
TableOld = old_base.classes.table_old
old_session = Session(old_engine)

new_base = automap_base()
new_engine = create_engine("<NEW_DB_URI>", echo=True)
new_base.prepare(new_engine, reflect=True)
TableNew = old_base.classes.table_new
new_session = Session(new_engine)

# here you can write your queries
old_table_results = old_session.query(TableOld).all()
new_data = []
for result in old_table_results:
    new = TableNew()
    new.id = result.id
    new.name = result.name
    new_data.append(new)
new_session.bulk_save_objects(new_data)
new_session.commit()

Now, about you second question here's a link of examples directly from SQLAlchemy's site: http://docs.sqlalchemy.org/en/latest/_modules/examples/performance/bulk_inserts.html and to answer you question bulk_insert_mappings takes a two parameter a db model (TableNew or TableOld) in the example above and a list of dictionaries representing instances (aka rows) in a db model.