Python将MySQL表复制到SQLite3 [英] Python copy MySQL table to SQLite3

问题描述

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

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

...非常慢(挂在.execute()，我不知道要持续多久).

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

我只需要这样做一次，所以我不介意是否要花费几个小时，但是有其他方法可以做到吗?也可以使用其他工具而不是Python.

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.

推荐答案

您没有确切显示插入行的方式，但提到了execute().

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

您可以改用executemany() *.
例如:

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()，如 Python DB-API 中所述:

.executemany(operation,seq_of_parameters)
准备数据库操作(查询或 命令)，然后针对 所有参数序列或映射 在序列中找到 seq_of_parameters.

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

这篇关于Python将MySQL表复制到SQLite3的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！