mysql2sqlite.py

from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session, sessionmaker
from sqlalchemy import create_engine, MetaData, Column, Table
import sqlalchemy.dialects.mysql.base
import sqlalchemy.dialects.sqlite

dest_engine = create_engine('sqlite:////tmp/test.sqlite3', echo=True, encoding='utf8')
DestSession = sessionmaker(dest_engine)


Base = automap_base()

# engine, suppose it has two tables 'user' and 'address' set up
engine = create_engine("mysql://username:password@localhost/database?unix_socket=/var/lib/mysql/mysql.sock&charset=utf8")

# reflect the tables
Base.prepare(engine, reflect=True)

session = Session(engine)


destMetadata = MetaData(bind=dest_engine)

mysql2sqlite = {
    sqlalchemy.dialects.mysql.base.INTEGER: sqlalchemy.dialects.sqlite.INTEGER,
    sqlalchemy.dialects.mysql.base.VARCHAR: sqlalchemy.dialects.sqlite.VARCHAR,
    sqlalchemy.dialects.mysql.base.DATETIME: sqlalchemy.dialects.sqlite.DATETIME,
    sqlalchemy.dialects.mysql.base.TINYTEXT: sqlalchemy.dialects.sqlite.TEXT,
    sqlalchemy.dialects.mysql.base.TEXT: sqlalchemy.dialects.sqlite.TEXT,
    sqlalchemy.dialects.mysql.base.MEDIUMTEXT: sqlalchemy.dialects.sqlite.TEXT,
    sqlalchemy.dialects.mysql.base.LONGTEXT: sqlalchemy.dialects.sqlite.TEXT,
    sqlalchemy.dialects.mysql.base.TINYINT: sqlalchemy.dialects.sqlite.INTEGER,
    sqlalchemy.dialects.mysql.base.SMALLINT: sqlalchemy.dialects.sqlite.SMALLINT,
    sqlalchemy.dialects.mysql.base.MEDIUMINT: sqlalchemy.dialects.sqlite.INTEGER,
    sqlalchemy.dialects.mysql.base.ENUM: sqlalchemy.dialects.sqlite.VARCHAR,
    sqlalchemy.dialects.mysql.base.CHAR: sqlalchemy.dialects.sqlite.CHAR,
    sqlalchemy.dialects.mysql.base.DECIMAL: sqlalchemy.dialects.sqlite.DECIMAL,
    sqlalchemy.dialects.mysql.base.DOUBLE: sqlalchemy.dialects.sqlite.FLOAT,
    sqlalchemy.dialects.mysql.base.DATE: sqlalchemy.dialects.sqlite.DATE,
}

for tablename in Base.metadata.tables.keys():
    query= session.query(Base.metadata.tables[tablename])

    metadata = MetaData(bind=dest_engine)
    columns = []
    for desc in query.column_descriptions:
        columns += [Column(desc['name'], mysql2sqlite[type(desc['type'])])]
    print columns
    table = Table(tablename, metadata, *columns)

    # Create the new table in the destination database
    table.create(dest_engine)

    # Finally execute the query
    destSession = DestSession()
    for row in query:
        print row
        destSession.execute(table.insert(row))
    destSession.commit()