Moving data from sqlite to postgres [Pyramid/SQLAlchemy]

move_db_data.py

"""
Moves db rows one by one, from source db (configured using pyramid ini config)
to destination db defined as engine here.
"""

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from pyramid.paster import bootstrap

bootstrap('development.ini')  # bootstrap pyramid's environment along with data models
engine = create_engine('postgresql://postgres@localhost/mydb')  # destination db
sess = sessionmaker()()
sess.bind = engine


def get_attrs(ob):
    d = ob.__dict__.copy()
    d.pop('_sa_instance_state', None)
    return d


def new_obj(ob):
    return ob.__class__(**get_attrs(ob))


def move_obj(ob):
    ob = new_obj(ob)
    #if isinstance(ob, SomeObjecc):
    #    custom data migration
    sess.add(ob)
    sess.commit()


def move_model_data(model):
    i = 0
    src_count = model.query.count()
    dst_count = sess.query(model).count()
    print("Moving %s with %s items" % (model, src_count))
    if src_count == dst_count:
        print("Data already moved")
        return

    for u in model.query:
        move_obj(u)
        i += 1
        if i % 100 == 0:
            print("Done: %s%%" % int((i/src_count) * 100))
    print("Done %s items" % i)


def print_id_seq_update(model):
    """
    printout of a id sequence update for postgres to make the next ID
    as a the next ID based on a largest ID number.
    """
    seq = '%s_id_seq' % model.__tablename__
    print("SELECT setval('%s', (SELECT MAX(id) FROM %s) +1);" % (seq,
                                                                 model.__tablename__))

# from myapp.model import A, B, C

models = [
   # model classes to copy the data from
   # A, B, C ..
]

for model in models:
    move_model_data(model)