Skip to content

Instantly share code, notes, and snippets.

@methane methane/mylite.py
Created Jun 22, 2012

Embed
What would you like to do?
Copy table from mysql to sqlite
#! /usr/bin/python
# coding: utf-8
"""Copy table from mysql to sqlite.
Require:
* SQLAlchemy
* MySQLdb or PyMySQL
Usage:
mylite.py "mysql+pymysql://user:password@host/db?charset=utf8" "sqlite:///out.db" table_name [table_name2...]
"""
import sqlalchemy as sa
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.dialects import mysql
@compiles(mysql.TINYINT, 'sqlite')
@compiles(mysql.SMALLINT, 'sqlite')
def compile_sqlite_tinyint(type_, compiler, **kw):
return 'INTEGER'
@compiles(mysql.LONGBLOB, 'sqlite')
def compile_sqlite_tinyint(type_, compiler, **kw):
return 'BLOB'
def copy_table(my_engine, lite_engine, table_name):
meta = sa.MetaData(my_engine)
table = sa.Table(table_name, meta, autoload=True)
lite_engine.execute("DROP TABLE IF EXISTS " + table_name)
table.create(lite_engine)
rows = my_engine.execute(table.select()).fetchall()
with lite_engine.begin() as con:
for row in rows:
con.execute(table.insert().values(**row))
def main():
import sys
mysql = sa.create_engine(sys.argv[1])
lite = sa.create_engine(sys.argv[2])
for table in sys.argv[3:]:
copy_table(mysql, lite, table)
if __name__ == '__main__':
main()
@tomers

This comment has been minimized.

Copy link

tomers commented Sep 3, 2017

The two compile_* functions have the same name

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.