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 --

我有一个大约10米行的MySQL表。我在SQLite3中创建了一个并行模式，我想以某种方式复制表。使用Python似乎是一个可接受的解决方案，但这种方式 -

# ...
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).

...非常慢（挂在.execute（），我不知道多长时间）。

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.

我只需要做一次，所以我不介意是否需要几个小时，但有不同的方法吗？使用不同的工具而不是Python也是可以接受的。

3 个解决方案

#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.

最简单的方法可能是使用mysqldump获取整个db的SQL文件，然后使用SQLite命令行工具来执行该文件。

#2

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

您没有准确显示插入行的方式，但提到了execute（）。

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

您可以尝试使用executemany（）*。例如：

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:

* Python DB-API中描述的executemany（）：

.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.

.executemany（operation，seq_of_parameters）准备数据库操作（查询或命令），然后针对序列seq_of_parameters中找到的所有参数序列或映射执行它。

#3

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

您可以使用select into outfile从mysql导出平面文件，并使用sqlite的.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:

如果你真的想用python做这个（也许是为了转换数据），我会使用MySQLdb.cursors.SSCursor迭代数据 - 否则mysql结果集会被缓存在内存中，这就是你的查询挂起执行的原因。所以这看起来像：

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.

这将比出口/进口方法慢得多。

#1