使用Python将CSV文件导入sqlite3数据库表

Question

我有一个CSV文件，我想用Python把这个文件批量导入到我的sqlite3数据库里。命令是“.import .....”，但是好像这样不行。有没有人能给我一个在sqlite3中怎么做的例子？我是在Windows系统上操作的，特意说明一下。谢谢！

Answer 1

你说得对，.import确实是个好方法，但这是SQLite3命令行程序中的一个命令。很多关于这个问题的热门回答都是用原生的Python循环来处理，但如果你的文件很大（比如我的文件有100万到1000万条记录），你就不想把所有内容都读入pandas或者使用原生的Python列表推导式/循环（虽然我没有对它们进行时间比较）。

对于大文件，我认为最好的选择是使用subprocess.run()来执行SQLite的导入命令。在下面的例子中，我假设表已经存在，但CSV文件的第一行有表头。想了解更多信息，可以查看.import文档。

subprocess.run()

from pathlib import Path
db_name = Path('my.db').resolve()
csv_file = Path('file.csv').resolve()
result = subprocess.run(['sqlite3',
                         str(db_name),
                         '-cmd',
                         '.mode csv',
                         '.import --skip 1 ' + str(csv_file).replace('\\','\\\\')
                                 +' <table_name>'],
                        capture_output=True)

编辑说明：sqlite3的.import命令已经改进，可以将第一行视为表头名称，甚至可以跳过前x行（需要版本>=3.32，具体见这个回答）。如果你使用的是旧版本的sqlite3，可能需要先创建表，然后在导入之前去掉CSV的第一行。--skip 1参数在3.32之前会报错。

解释
从命令行来看，你需要的命令是sqlite3 my.db -cmd ".mode csv" ".import file.csv table"。subprocess.run()用于运行一个命令行进程。传给subprocess.run()的参数是一个字符串序列，这些字符串会被解释为一个命令及其所有参数。

• sqlite3 my.db打开数据库
• -cmd标志在数据库后面，让你可以传递多个后续命令给sqlite程序。在命令行中，每个命令都需要用引号括起来，但在这里，它们只需要作为序列的独立元素。
• '.mode csv'的作用是显而易见的。
• '.import --skip 1'+str(csv_file).replace('\\','\\\\')+' <table_name>'是导入命令。
  不幸的是，由于subprocess将所有后续命令作为带引号的字符串传递给-cmd，如果你有Windows目录路径，就需要将反斜杠加倍。

去掉表头

这其实不是问题的重点，但这是我使用的方法。同样，我不想在任何时候将整个文件读入内存：

with open(csv, "r") as source:
    source.readline()
    with open(str(csv)+"_nohead", "w") as target:
        shutil.copyfileobj(source, target)

Answer 2

在磁盘上创建一个sqlite文件的连接，这个部分留给读者自己去练习……不过现在有了pandas库，可以用两行代码轻松搞定。

df = pandas.read_csv(csvfile)
df.to_sql(table_name, conn, if_exists='append', index=False)

Answer 3

在编程中，有时候我们需要让程序在特定的条件下执行某些操作。比如说，你可能希望在用户点击一个按钮时，程序才开始运行某段代码。这种情况下，我们就会用到“事件”这个概念。

事件就像是一个信号，告诉程序“嘿，有人点击了按钮！”当这个信号发出时，程序就会执行你预先设定好的代码。这样，程序就不会一直在运行，而是等着用户的操作。

另外，处理事件的方式也有很多种。你可以选择在事件发生时立即执行代码，或者先把代码放在一边，等到合适的时机再执行。这种灵活性让程序能够更高效地运行。

总之，事件是让程序与用户互动的重要工具，理解它们可以帮助你写出更智能的程序。

import csv, sqlite3

con = sqlite3.connect(":memory:") # change to 'sqlite:///your_filename.db'
cur = con.cursor()
cur.execute("CREATE TABLE t (col1, col2);") # use your column names here

with open('data.csv','r') as fin: # `with` statement available in 2.5+
    # csv.DictReader uses first line in file for column headings by default
    dr = csv.DictReader(fin) # comma is default delimiter
    to_db = [(i['col1'], i['col2']) for i in dr]

cur.executemany("INSERT INTO t (col1, col2) VALUES (?, ?);", to_db)
con.commit()
con.close()