我有两个剧本。1将数据添加到SQLite数据库，1使用3个不同的查询进行查询。数据库将有1亿行和2列(平均每条记录50个字符)。
问题1：我添加的每个CSV文件大约有1000万个，需要几个小时才能导入。
问题2：当我运行查询脚本时，加载时间超过30分钟。实际的查询只需要几秒钟。有没有更好的办法来管理这件事？
输入脚本：

import sqlite3

conn = sqlite3.connect('Main_Database.db')

c = conn.cursor()

# c.execute('SELECT * FROM user_data')

# result = c.fetchall()

with open('my_list_2.txt') as f:
    for line in f:
        try:
            l = line.split('\n')[0].split(':')
            # print(l[0])
            # print(l[1])
            c.execute("INSERT INTO user_data VALUES (?,?)", (l[0], l[1]))
        except IndexError:
            pass
        except sqlite3.IntegrityError:
            pass
conn.commit()
conn.close()

查询脚本：

import sqlite3
from datetime import datetime
from datetime import date
import re
import csv
import numpy as np

conn = sqlite3.connect('Main_Database.db')

c = conn.cursor()

c.execute("SELECT * FROM user_data")

items = c.fetchall()

exact_match_mail = 'abc123'
#method = 'method 1'

word_match_mail = 'hot'
#method = 'method 2'

method = 'method 3'
word_match_mail_one = 'hot'
word_match_mail_two = 'chocolate'

L = []
if method == 'method 1':
    for x, y in items:
        if x == exact_match_mail:
            print('there is an exact match to the email: ', x)
            L.append(x)

elif method == 'method 2':
    for x, y in items:
        if word_match_mail in re.split("@|_", x):
            print('Partial match based on one word found in the email: ', x)
            L.append(x)

elif method == 'method 3':
    for x, y in items:
        if word_match_mail_one in re.split("@|_", x) and word_match_mail_two in re.split("@|_", x):
            print('Partial match based on two words found in the email: ', x)
            L.append(x)

a = input('press any key to export result to a csv file')

if a != np.nan:
    f = open('saved_emails.csv', 'a', encoding='UTF8')
    writer = csv.writer(f)
    now = datetime.now()
    current_time = now.strftime("%H:%M:%S")
    today = date.today()
    d1 = today.strftime("%d/%m/%Y")
    row = "********* report "+d1+" "+current_time+" **************"
    writer.writerow([row])
    row = "method used for this query is: " + method
    writer.writerow([row])
    row = "emails obtained are: "
    writer.writerow([row])
    for x in L:
        writer.writerow([x])
    f.close()

conn.commit()

conn.close()