python - SQLite3 - Executemany 未完成 Python3 中大型列表的更新

Question

我正在尝试更新 SQLite 数据库中的大约 500k 行。我可以很快地创建它们，但是当我更新时，它似乎无限期地挂起，但我没有收到错误消息。 (相同大小的插入需要 35 秒，本次更新已经持续了 12 个小时以上)。

我的代码中进行更新的部分是:

for line in result:
if --- blah blah blah ---:
   stuff
else:
    counter = 1
    print("Starting to append result_list...")
    result_list = []
    for line in result:
        result_list.append((str(line),counter))
        counter += 1                 
    sql = 'UPDATE BRFSS2015 SET ' + col[1] + \
         ' = ? where row_id = ?'
    print("Executing SQL...")
    c.executemany(sql, result_list)
print("Committing.")
conn.commit()

它打印“Executing SQL...”并可能尝试执行executemany，这就是它卡住的地方。变量“结果”是一个记录列表，据我所知，它正在工作，因为插入语句正在工作，并且它基本上是相同的。

我是否滥用了executemany？我在executemany() 上看到许多线程，但据我所知，所有线程都收到错误消息，而不仅仅是无限期挂起。

作为引用，我拥有的完整代码如下。基本上我正在尝试将 ASCII 文件转换为 sqlite 数据库。我知道从技术上讲我可以同时插入所有列，但是我可以访问的机器都仅限于 32 位 Python，并且它们会耗尽内存(这个文件相当大，接近 1GB 的文本)。

import pandas as pd
import sqlite3

ascii_file = r'c:\Path\to\file.ASC_'
sqlite_file = r'c:\path\to\sqlite.db'

conn = sqlite3.connect(sqlite_file)
c = conn.cursor()

# Taken from https://www.cdc.gov/brfss/annual_data/2015/llcp_varlayout_15_onecolumn.html
raw_list = [[1,"_STATE",2],
[17,"FMONTH",2],
... many other values here
[2154,"_AIDTST3",1],]

col_list = []
for col in raw_list:
    begin = (col[0] - 1)
    col_name = col[1]
    end = (begin + col[2])
    col_list.append([(begin, end,), col_name,])

for col in col_list:
    print(col)
    col_specification = [col[0]]
    print("Parsing...")
    data = pd.read_fwf(ascii_file, colspecs=col_specification)
    print("Done")
    result = data.iloc[:,[0]]
    result = result.values.flatten()
    sql = '''CREATE table if not exists BRFSS2015
             (row_id integer NOT NULL,
              ''' + col[1] +  ' text)'
    print(sql)
    c.execute(sql)
    conn.commit()
    sql = '''ALTER TABLE 
             BRFSS2015 ADD COLUMN ''' + col[1] + ' text'
    try:
        c.execute(sql)
        print(sql)
        conn.commit()
    except Exception as e:
        print("Error Happened instead")
        print(e)

    counter = 1  
    result_list = []
    for line in result:
        result_list.append((counter, str(line)))
        counter += 1

    if '_STATE' in col:
        counter = 1  
        result_list = []
        for line in result:
            result_list.append((counter, str(line)))
            counter += 1
        sql = 'INSERT into BRFSS2015 (row_id,' + col[1] + ')'\
               + 'values (?,?)'
        c.executemany(sql, result_list)
    else:
        counter = 1
        print("Starting to append result_list...")
        result_list = []
        for line in result:
            result_list.append((str(line),counter))
            counter += 1                 
        sql = 'UPDATE BRFSS2015 SET ' + col[1] + \
             ' = ? where row_id = ?'
        print("Executing SQL...")
        c.executemany(sql, result_list)
    print("Committing.")
    conn.commit()
    print("Comitted... moving on to next column...")

Answer 1

对于要更新的​​每一行，数据库必须搜索该行。 (插入时不需要这样做。)如果 row_id 列上没有索引，则数据库必须在每次更新时遍历整个表。

最好一次插入整行。如果不可能，create an index在 row_id 上，或者更好，将其声明为 INTEGER PRIMARY KEY .