Python copy MySQL table to SQLite3
I've got a MySQL 开发者_JAVA技巧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.
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.
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.
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.
精彩评论