通过 Python 快速将数据从 CSV 移动到 SQLite

Question

>我有一个问题。有数百个 CSV 文件，每个文件大约有 1,000,000 行。我需要以特定的方式移动该数据，但脚本运行非常慢（每小时传递几十个tousand）。

我的代码：

    import sqlite3 as lite
    import csv
    import os

my_file = open('file.csv', 'r')
reader = csv.reader(my_file, delimiter=',')

date = '2014-09-29'
con = lite.connect('test.db', isolation_level = 'exclusive')
for row in reader:
    position = row[0]
    item_name = row[1]

    cur = con.cursor()
    cur.execute("CREATE TABLE IF NOT EXISTS [%s] (Date TEXT, Position INT)" % item_name)
    cur.execute("INSERT INTO [%s] VALUES(?, ?)" % item_name, (date, position))
con.commit()

我找到了一个关于isolation_level和单一访问数据库的信息，但它效果不佳。

行 CSV 文件具有结构：1，项目1 | 2，项目2

有人可以帮助我吗？谢谢！

Answer 1

不要做 sql 插入。首先准备CSV文件，然后执行以下操作：

.separator <separator>
.import <loadFile> <tableName>

看这里： http://cs.stanford.edu/people/widom/cs145/sqlite/SQLiteLoad.html

Answer 2

您当然不想为要插入的每一行创建一个新的游标对象 - 检查每一行的表创建肯定会减慢您的速度 -

我建议分 2 次进行：首先您创建所需的表，在第二次传递时记录数据。如果它仍然很慢，你可以做一个更复杂的内存中数据收集插入并执行"执行许多" - 但这将在内存中按名称对数据进行分组需要一些复杂性在提交之前;。

import sqlite3 as lite
import csv
import os
my_file = open('file.csv', 'r')
reader = csv.reader(my_file, delimiter=',')
date = '2014-09-29'
con = lite.connect('test.db', isolation_level = 'exclusive')
cur  = con.cursor()
table_names = set(row[1] for row in reader)
my_file.seek(0)
for name in table_names:
     cur.execute("CREATE TABLE IF NOT EXISTS [%s] (Date TEXT, Position INT)" % item_name)
for row in reader:
    position = row[0]
    item_name = row[1]
    cur.execute("INSERT INTO [%s] VALUES(?, ?)" % item_name, (date, position))
con.commit()

Answer 3

该代码效率低下，因为它对 CSV 中的每一行执行两个 SQL 语句。尝试优化。

1. 有没有办法先处理CSV并将其转换为SQL语句？
2. CSV 中的行是否按表（item name）分组？如果是，则可以累积要插入到同一表中的行（为同一表生成一组 INSERT 语句），并且只在生成的语句集前面加上CREATE TABLE IF NOT EXISTS一次，而不是每个语句。
3. 如果可能，请使用批量插入。如果我做对了，SQLite v.3.27.1 引入了批量插入。更多关于这个：是否可以在SQLite数据库中一次插入多行？
4. 如果需要，批量插入块。更多关于这个：使用Python将大量数据批量插入SQLite中

Answer 4

我有同样的问题。现在解决了！我想与面临相同问题的每个人分享这些方法！

我们以 sqlite3 数据库为例，其他数据库也可以工作，但不确定。我们在python中采用pandas和sqlites模块。

这可以快速将 csv 文件 [file1，file2,...] 列表转换为 talbes [table1，table2,...]。

import pandas as pd
import sqlite3 as sql

DataBasePath="C:\Users\...\database.sqlite"
conn=sql.connect(DataBasePath)
filePath="C:\Users\...\filefolder\"    
datafiles=["file1","file2","file3",...]
for f in datafiles:
    df=pd.read_csv(filePath+f+".csv")        
    df.to_sql(name=f,con=conn,if_exists='append', index=False)    
conn.close()

更重要的是，如果此代码不存在，则可以创建数据库。pd.to_sql（） 'if_exists' 的参数很重要。默认情况下，其值为"fail"，如果存在，它将导入数据，否则将不执行任何操作;"替换"将首先删除表（如果存在），然后创建新表并导入数据;"append"将导入数据（如果存在），否则创建一个新的可以导入数据。