python复制mysql表到另外的主机,Python将MySQL表复制到SQLite3

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.

解决方案

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.