Python将CSV转换为SQLite

Question

我正在"转换"一个大约1.6GB的CSV文件，并将CSV的特定字段插入到SQLite数据库中。我的代码本质上看起来像：

import csv, sqlite3

conn = sqlite3.connect( "path/to/file.db" )
conn.text_factory = str  #bugger 8-bit bytestrings
cur = conn.cur()
cur.execute('CREATE TABLE IF NOT EXISTS mytable (field2 VARCHAR, field4 VARCHAR)')

reader = csv.reader(open(filecsv.txt, "rb"))
for field1, field2, field3, field4, field5 in reader:
  cur.execute('INSERT OR IGNORE INTO mytable (field2, field4) VALUES (?,?)', (field2, field4))

除了一个问题，一切都按我预期的运行...这个问题是处理所需的时间太长了。 我的编码方式有问题吗？ 有更好的方法来提高性能并完成我所需要的任务（将CSV的几个字段转换为SQLite表）吗？

**编辑 - 我尝试直接将csv导入sqlite，但结果发现我的文件中的某些字段有逗号（例如"My title, comma"）。 这会导致导入错误。 看起来有太多这样的情况无法手动编辑文件...

还有其他想法吗？**

- user735304

---

这是一个大文件。需要多长时间？ - Blender

有多少个重复记录？如果有很多，可能更快的方法是保留一个本地的“set”已经插入的记录，并跳过对于重复项完全调用SQL。 - kindall

以下是一些 MySQL 批量加载性能优化技巧：这里。 - kindall

你使用的操作系统和Python版本是什么？ - Cristian Ciupitu

看起来有太多这些出现次数需要手动编辑文件。让我们想想，手动改变太多了？如果你能使用一种编程语言编写程序将CSV文件重新格式化为TAB分隔的文件，那该多好啊。你有什么想法用什么语言来编写这样的程序呢？ - S.Lott

2020年的解决方案是使用Pandas。Pandas拥有出色的SQL编写器，可以让您分块编写。Pandas使这个问题变得非常容易，并且可以避免您担心低级细节。Pandas读取器使得在将数据写入SQL之前，可以轻松地处理CSV文件中的异常情况（避免了另一个常见的头疼问题）。请查看我的答案以获取更多详细信息。 - Powers

Answer 1

Chris 是正确的 - 使用事务; 将数据分成块，然后存储它。

"... 除非已经在事务中，否则每个SQL语句都会为其启动一个新事务。这非常昂贵，因为它需要为每个语句重新打开、写入和关闭日志文件。这可以通过使用BEGIN TRANSACTION;和END TRANSACTION;语句包装SQL语句序列来避免。这也适用于不改变数据库的语句。" - 来源：http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html

"... 还有另一个技巧可以加速SQLite：事务。每当您必须进行多个数据库写入时，请将它们放在事务中。写入查询被发出时，不是每次都写入（和锁定）文件，而是仅在事务完成时发生写入。" - 来源：How Scalable is SQLite?

import csv, sqlite3, time

def chunks(data, rows=10000):
    """ Divides the data into 10000 rows each """

    for i in xrange(0, len(data), rows):
        yield data[i:i+rows]


if __name__ == "__main__":

    t = time.time()

    conn = sqlite3.connect( "path/to/file.db" )
    conn.text_factory = str  #bugger 8-bit bytestrings
    cur = conn.cur()
    cur.execute('CREATE TABLE IF NOT EXISTS mytable (field2 VARCHAR, field4 VARCHAR)')

    csvData = csv.reader(open(filecsv.txt, "rb"))

    divData = chunks(csvData) # divide into 10000 rows each

    for chunk in divData:
        cur.execute('BEGIN TRANSACTION')

        for field1, field2, field3, field4, field5 in chunk:
            cur.execute('INSERT OR IGNORE INTO mytable (field2, field4) VALUES (?,?)', (field2, field4))

        cur.execute('COMMIT')

    print "\n Time Taken: %.3f sec" % (time.time()-t) 

- Sam

---

另一个用户在遵循此代码时，尝试在其CSV读取器中使用len()时遇到了问题：https://dev59.com/4XXYa4cB1Zd3GeqP408o#18063276 - rutter

Answer 2

可以直接导入CSV文件：

sqlite> .separator ","
sqlite> .import filecsv.txt mytable

http://www.sqlite.org/cvstrac/wiki?p=ImportingFiles

- fengb

---

似乎没有默认的转义方式。此外，引号将成为字符串中的文字。使用CSV解析并使用不同的分隔符输出可能是有意义的，但这可能会破坏首先使用导入工具的目的。 - fengb

尝试使用 .mode csv 而不是 .separator，参见： https://dev59.com/9WUp5IYBdhLWcg3w661B#24582022 - NumesSanguis

Answer 3

正如Chris和Sam所说的，事务确实可以大幅提高插入性能。

请允许我推荐另一种选择，使用一组Python工具来处理CSV文件，csvkit。

安装方法：

pip install csvkit

为解决您的问题

csvsql --db sqlite:///path/to/file.db --insert --table mytable filecsv.txt

- migonzalvar

---

csvkit在我的基准测试中可惜地非常慢：https://dev59.com/gG025IYBdhLWcg3wjGpO#76659998 另外，有没有办法用它来控制列类型？相关问题：https://github.com/wireservice/csvkit/issues/151 不过如果性能不是问题的话，它还是很方便的。 - Ciro Santilli OurBigBook.com

Answer 4

尝试使用事务。

begin    
insert 50,000 rows    
commit

那将定期提交数据，而不是每行提交一次。

- Chris N

Answer 5

Pandas可以轻松地将大文件分块加载到数据库中。将CSV文件读入Pandas DataFrame，然后使用Pandas SQL writer（让Pandas完成所有艰苦的工作）。以下是如何以100,000行为单位加载数据的方法。

import pandas as pd

orders = pd.read_csv('path/to/your/file.csv')
orders.to_sql('orders', conn, if_exists='append', index = False, chunksize=100000)

现代的Pandas版本非常高效。不要重复发明轮子。点击这里获取更多信息。

- Powers

Answer 6

cursor.executemany比cursor.execute快。

根据在https://dev59.com/WWMl5IYBdhLWcg3wqIhh#76659706上的基准测试，cursor.executemany应该比for循环要快得多（观察到的速度提高了3倍），如：https://dev59.com/gG025IYBdhLWcg3wjGpO#7137270。 executemany在单个事务中运行，但默认情况下循环也是在没有任何显式的BEGIN/COMMIT的情况下进行的：加速是由于其他因素引起的。

main.py

from pathlib import Path
import csv
import sqlite3

f = 'tmp.sqlite'
Path(f).unlink(missing_ok=True)
connection = sqlite3.connect(f)
cursor = connection.cursor()
cursor.execute("CREATE TABLE t (x integer)")
cursor.executemany('INSERT INTO t VALUES (?)', csv.reader(open('10m.csv', 'r')))
connection.commit()
connection.close()

测试通过：

python -c 'for i in range(10000000): print(i)' > 10m.csv
time ./main.py

这个操作在9秒内完成，与没有使用csv读取的虚拟range()循环相比，只有轻微的减速。参考链接：https://dev59.com/WWMl5IYBdhLWcg3wqIhh#76659706

要从CSV中选择或预处理列，我们现在可以使用一个生成器表达式，例如：

python -c 'for i in range(10): print(f"{i},{i*2},{i*4}")' > 10x3.csv
cursor.execute("CREATE TABLE t (x integer, z integer)")
cursor.executemany('INSERT INTO t VALUES (?, ?)',
    ((d[0], d[2]) for d in csv.reader(open('10x3.csv', 'r'))))

csvkit的速度很慢

我希望csvkit（之前在https://dev59.com/gG025IYBdhLWcg3wjGpO#9913925提到过）能更好地支持这种用例，但目前它比上面的最简单手动executemany脚本要慢得多。

echo x > 10m-head.csv
python -c 'for i in range(10000000): print(i)' >> 10m-head.csv
time csvsql --db sqlite:///tmp.sqlite --insert --table t 10m-head.csv

给出1分25秒。

使用sqlite .import的理论速度限制

只是为了看看由于Python而偏离多少，假设没有任何东西能超过更原生的.import命令：

sqlite3 tmp.sqlite 'create table t(x integer)'
time sqlite3 tmp.sqlite ".import --csv 10m.csv t"

我的SSD上的结果是5.8秒。比Python快得多，但很高兴看到我们也没有差太远。

在Ubuntu 23.04、Python 3.11.2、Lenovo ThinkPad P51上进行了测试，SSD为Samsung MZVLB512HAJQ-000L7 512GB SSD，名义速度为3 GB/s，使用csvkit==1.0.7和sqlite 3.40.1。

- Ciro Santilli OurBigBook.com