Arquivo de Migração de Base Firebird para Postgresql

migrate.py

# -*- coding:utf-8 -*-
from pdb import set_trace
from sqlalchemy import create_engine, MetaData
from sqlalchemy import Column, Integer, String, Table, Text
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

metadata = MetaData()

FIREBIRD = 'firebird+fdb://sysdba:[email protected]:3050//var/lib/firebird/2.5/data/millenium.fdb?charset=ISO8859_1'
POSTGRES = 'postgresql+psycopg2://postgres:postgres@localhost:5432/millenium'


src = create_engine(FIREBIRD)
dst = create_engine(POSTGRES)

src_session = sessionmaker(src)
dst_session = sessionmaker(dst)

metadata.reflect(bind=src)

set_trace()

Base = declarative_base()

'''
Convertendo o tipo Text para String, para funcionar no postgres
Contando a quantidade de relacionamentos que a tabela tem para criar 
e incluir primeiro as sem relacionamentos
'''
mapa = {}
tables = metadata.tables
for tbl in tables:
    table = tables[tbl]
    if not tbl in mapa:
        mapa[tbl] = 0
    for col in table.columns:
        print(col)
        print(col.type)
        if isinstance(col.type, Text):
            tamanho = 500
            if col.type and col.type.length and col.name != 'obs':
                tamanho = col.type.length
            col.type = String(tamanho)
        if col.foreign_keys:
            lista_fks = list(col.foreign_keys)
            for fk in lista_fks:
                fk_table = fk._table_key()
                if not fk_table in mapa:
                    mapa[fk_table] = 0
                mapa[fk_table] += 1


set_trace()
lista = sorted(mapa, key=mapa.__getitem__, reverse=True)

metadata.create_all(dst)
for tbl in lista:
#for tbl in lista:
    print ('##################################')
    print (tbl)
    #tables[tbl].create(dst) # create each table
    print ( tables[tbl].select())
    data = src.execute(tables[tbl].select()).fetchall()
    for a in data: print(a)
    if data:
        print (tables[tbl].insert())
        dst.execute( tables[tbl].insert(), data)