public
Last active

Copy table from mysql to sqlite

  • Download Gist
mylite.py
Python
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48
#! /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()

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.