Python copy MySQL table to SQLite3

Question

Hello, I've got a MySQL table with about ~10m rows. I created a parallel schema in SQLite3, and I'd like to copy the table somehow. Using Python seems like an acceptable solution, but this way --

# ...
mysqlcursor.execute('SELECT * FROM tbl')
rows = mysqlcursor.fetchall() # or mysqlcursor.fetchone()
  for row in rows:
    # ... insert row via sqlite3 cursor

...is incredibly slow (hangs at .execute(), I wouldn't know for how long).

I'd only have to do this once, so I don't mind if it takes a couple of hours, but is there a different way to do this? Using a different tool rather than Python is also acceptable.

Answer 1

The simplest way might be to use mysqldump to get a SQL file of the whole db, then use the SQLite command-line tool to execute the file.

Answer 2

You don't show exactly how you insert rows, but you mention execute().

You might try executemany()* instead.
For example:

import sqlite3
conn = sqlite3.connect('mydb')
c = conn.cursor()
# one '?' placeholder for each column you're inserting
# "rows" needs to be a sequence of values, e.g. ((1,'a'), (2,'b'), (3,'c'))
c.executemany("INSERT INTO tbl VALUES (?,?);", rows)
conn.commit()

*executemany() as described in the Python DB-API:

.executemany(operation,seq_of_parameters)
Prepare a database operation (query or command) and then execute it against all parameter sequences or mappings found in the sequence seq_of_parameters.

Answer 3

You can export a flat file from mysql using select into outfile and import those with sqlite's .import:

mysql> SELECT * INTO OUTFILE '/tmp/export.txt' FROM sometable;

sqlite> .separator "\t"
sqlite> .import /tmp/export.txt sometable

This handles the data export/import but not copying the schema, of course.

If you really want to do this with python (maybe to transform the data), I would use a MySQLdb.cursors.SSCursor to iterate over the data - otherwise the mysql resultset gets cached in memory which is why your query is hanging on execute. So that would look something like:

import MySQLdb
import MySQLdb.cursors
connection = MySQLdb.connect(...)
cursor = connection.cursor(MySQLdb.cursors.SSCursor)
cursor.execute('SELECT * FROM tbl')
for row in cursor:
    # do something with row and add to sqlite database

That will be much slower than the export/import approach.