Python SQLite 数据库插入 Tips

speculatecat

一个冲不好咖啡的作家不是好码农

SQLite 是一款常用、轻量的数据库。虽然使用比较简单易懂，但也存在一些小技巧。本文将介绍 SQLite 中 Python 应用的Tips。

首先，在 Python 使用 SQLite 要先安装 sqlite3 库的依赖：

# conda 安装
conda install sqlite3
# pip 安装
pip install sqlite3

然后需要在程序的开头应用库：

import sqlite3

在开始介绍 Tips 之前，先来创建一个数据表：

# 连接数据库，如果文件不存在，会自动创建文件
db = sqlite3.connect('database/py-sqlite-inster-tips.db')

接下来，为演示创建一个 Person 的数据表：

# 数据库创建的 SQL 语句
create_sql = '''
    CREATE TABLE "Person" (
        "id" INTEGER NOT NULL UNIQUE,
        "name" TEXT NOT NULL,
        "age" INTEGER NOT NULL DEFAULT 18,
        PRIMARY KEY("id" AUTOINCREMENT)
# 执行 SQL 创建数据库
db.execute(create_sql)
# 查询当前数据库中的表 - SQL 语句
select_table_name_sql = 'SELECT name FROM sqlite_master WHERE type = "table";'
# 创建游标
cursor = db.cursor()
# 执行查询SQL
cursor.execute(select_table_name_sql)
# 打印结果
print(cursor.fetchall())
[('Person',), ('sqlite_sequence',)]

这样，我们就已经成功创建了演示用的 Person 数据表了。

插入数据 INSERT 操作

sqlite3 使用游标 cursor 的 execute 可以执行 SQL 语句。在 SQLite 中，插入数据语法如下：

INSERT INTO <TABLE> VALUES (value1, value2...);

我们尝试插入一条记录：

insert_sql = 'INSERT INTO Person VALUES (1, 'persion 1', 20)'
cursor.execute(insert_sql)
# 查询插入结果
query_sql = 'SELECT * FROM Person;'
cursor.execute(quert_sql)
# 打印结果
print(cursor.fetchall())
[(1, 'person-1', 20)]

这里就可以看到我们已经向表中添加了一条记录。这里要注意一点，上面的代码插入一行记录，并且我们使用查询语句也可以查询到我们添加的记录，但是我们的插入到现在为止并没有提交。可以尝试去查看一下数据库目录，可以看到下面会多了一个文件 py-sqlite-inster-tips.db-journal ，这时，如果我们使用另外一个程序(GUI工具 - DB Browser 或者使用另外一个python程序)打开 py-sqlite-inster-tips.db ，会发现里面并没有我们刚刚添加的记录。所以我们需要执行一下提交。

# 提交数据库修改
db.commit()

这时，我们可以发现，刚刚的 db-journal 文件不见了，我们使用其他工具再打开数据库，也是可以看到新的记录。

唯一约束字段插入操作

接下来，我们再尝试插入另外一条新的记录。

insert_person_2_sql = 'INSERT INTO Person VALUES (1, "person 2", 21);'
cursor.execute('insert_person_2_sql')
......
IntegrityError: UNIQUE constraint failed: Person.id

可以看到，程序出现了报错。这是因为我们在创建表的时候，给 id 字段添加了唯一的约束。现在的 Person 表中，已经存在 id 为 1 的字段，因此如果再向数据表中添加 id 的 1 的数据记录，就会报错。解决办法也很简单，我们使用一个没有在数据表中占用的 id 即可。

insert_person_2_sql = 'INSERT INTO Person VALUES (2, "person 2", 21);'
cursor.execute('insert_person_2_sql')
# 打印结果
cursor.execute(quert_sql)
print(cursor.fetchall())
[(1, 'person-1', 20), (2, 'persopn 2', 21)]

自增字段插入操作

我们的 id 字段，除了唯一约束，还有自增属性。我们为 Person 表再添加一个记录。

insert_person_10_sql = 'INSERT INTO Person VALUES (10, "person 10", 30);'
cursor.execute('insert_person_10_sql')
# 打印结果
cursor.execute(quert_sql)
print(cursor.fetchall())
[(1, 'person-1', 20), (2, 'persopn 2', 21), (10, 'person 10', 30)]

然后，我们在上面 Person 表中有三个记录的基础上，在添加一个记录，这一次，我们不在指定 id 。

insert_person_3_sql = 'INSERT INTO Person VALUES (NULL, "person 3", 18);'
cursor.execute('insert_person_10_sql')
# 打印结果
cursor.execute(quert_sql)
print(cursor.fetchall())
[(1, 'person-1', 20), (2, 'persopn 2', 21), (10, 'person 10', 30), (11, 'person 3', 18)]

可以看到，最后添加的字段，它的 id 是在上一条记录之上加 1 ，变成 11，而数据表中，3～9 的 id 就会没有。我们在设计表的时候，设置 id 字段为唯一及自增，通常是想让 id 字段自己按顺序增长，但因为我们上面指定 id 的操作，会让我们的数据记录没有像我们预期般存储。解决的办法也很简单，我们可以不对 id 进行指定，使用 NULL 让数据库自己处理，又或者我们指定我们需要插入的数据字段：

insert_person_sql = 'INSERT INTO Person (name, age) VALUES (?, ?);'
cursor.execute(insert_person_sql, ('person_12', 33))
# 打印结果
cursor.execute(quert_sql)
print(cursor.fetchall())
[(1, 'person-1', 20), (2, 'persopn 2', 21), (10, 'person 10', 30), (11, 'person 3', 18), (12, 'person_12', 33)]

单一字段插入

在上面的例子中，我们在 SQL 语句中使用了 ? 来替代真实需要插入的数据。但是这里会有一个问题，可以看一下下面的代码：

insert_person_single_sql = 'INSERT INTO PERSON (name) VALUES (?);'
cursor.execute(insert_person_single_sql, 'person 13')
......
ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 10 supplied.

可以看到，我们如果将 name 字段的内容直接作为参数，会出现报错。报错提示我们的参数个数不对。接下来，我们对代码进行修改：

cursor.execute(insert_person_single_sql, ('person 13',))
# 打印结果
cursor.execute(quert_sql)
print(cursor.fetchall())
[(1, 'person-1', 20), (2, 'persopn 2', 21), (10, 'person 10', 30), (11, 'person 3', 18), (12, 'person_12', 33), (13, 'person', 18)]

这样，就可以正常插入了。为什么我们需要构造一个元组才能成功执行代码呢？来看一下下面代码：

len('person 10')
type('person 10')
len(('person 10', ))
type(('person 10', ))
tuple

我们知道 SQL 语句需要接收一个数据，我们可以从上面的代码知道，如果只传入我们需要存储的数据(作为str)，它的长度是10，而当我们将数据作为一个元组成为参数，这里的长度是1。至于其中的原理，与 python 的数据结构特性有关，感兴趣的可以自行了解，这里就不多费篇幅。至于为什么要写成 (<value>, ) 的形式而不是 (<value>) 的形式，可以参考下面代码：

type(('string'))
type(('string', ))
tuple

一次插入多行数据

除了一次插入一行记录，也可以使用 executemany 一次插入多行数据，代码如下：

persons = [
    ('person 14', 12),
    ('person 15', 20),
    ('person 16', 22),
    ('person 17', 17)
cursor.executemany(insert_person_sql, persons)