Mar 7, 2014
by Sebastian Raschka

---

连接到一个 SQLite 数据库

我们将在本教程中使用的sqlite3是Python标准库的一部分，它是SQLite数据库的一个良好且简单的接口：没有涉及服务器进程，无需配置，也没有我们需要担心的其他障碍。

一般来说，在我们可以通过Python的sqlite3模块对SQLite数据库执行任何操作之前，惟一需要做的就是打开一个SQLite数据库文件的连接：

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

 

数据库文件 (sqlite_file) 可以存储在磁盘上的任何地方，例如：

sqlite_file = '/Users/Sebastian/Desktop/my_db.sqlite'

如果是第一次尝试连接到一个数据库文件的话，将会自动创建一个新的数据库文件(sqlite文件)。然而，我们必须意识到，它还没有任何一个表。在下一节中，我们将学习如何创建一个新的SQLite数据库文件的示例代码，其中包含用于存储某些数据的表。

关于连接到SQLite数据库文件的这一节，还有两种操作需要一提。如果我们完成了对数据库文件的操作，就必须通过.close()方法关闭连接：

conn.close()

如果我们对数据库执行了任何操作，而不是简单的发送查询，那么我们需要通过.commit()方法在关闭连接之前提交这些更改:

conn.commit()
conn.close()

创建一个新的SQLite数据库

让我们看一些示例代码，以创建一个新的SQLite数据库文件，其中有两个表:一个表有主键列，另一个则没有主键列(不要担心，在本节中还有更多关于主键的信息)。

import sqlite3

sqlite_file = 'my_first_db.sqlite' #数据库文件名称
table_name1 = 'my_table_1' #要创建的表格的名称
table_name2 = 'my_table_2' #要创建的另一个表格的名称
new_field = 'my_1st_column' #列的名称
field_type = 'INTEGER' #列的数据类型

# 连接到数据库文件
conn = sqlite3.connect(sqlite_file)
c = conn.cursor()

# 创建一个仅有一列的表
c.execute('CREATE TABLE {tn} ({nf} {ft})'\
.format(tn=table_name1, nf=new_field, ft=field_type))

# 创建第二个仅有一列的表，并将其作为主键列
# 注意主键列不可以含有两个相同的值
c.execute('CREATE TABLE {tn} ({nf} {ft} PRIMARY KEY)'\
.format(tn=table_name2, nf=new_field, ft=field_type))

# 提交更改并关掉与数据库文件的连接
conn.commit()
conn.close()

在 create_new_db.py 下载以上代码。

---

提示：
一个方便的可视化和访问SQLite数据库的工具是免费的FireFox SQLite管理器插件。在本文中，我将使用这个工具创建我们在相应代码段下面创建的数据库结构的截屏。

Using the code above, we created a new .sqlite database file with 2 tables. Each table consists of currently one column only, which is of type INTEGER.
借助以上代码， 我们创建了一个新的含有两张表格的.sqlite数据库文件A。每个表格现在都只有一列，数值类型是整型。

---

以下是SQLite 3支持的数据类型一览表：

• INTEGER: 最长可达 8 bytes 的有符号数；
• REAL: 8 bytes 的浮点数；
• TEXT: 字符串，一般是UTF-8编码（取决于数据库的编码）；
• BLOB: 一团二进制数据（二进制数据块），用于存储二进制数据；
• NULL: 空值，表示数据缺失或为空。

通过上面的表格，您可能已经注意到SQLite 3没有指定的布尔数据类型。但是，这应该不是一个问题，因为我们可以简单地重新使用整数类型来表示布尔值(0 = false, 1 = true)。

关于主键的一点提示：

在上面的示例代码中，我们将第二个表中的主键列设为第一列。如果我们使用主键列作为查询来访问表中的行，那么可以带来显著的性能提升。每个表格至多只能有一个主键（单个或多个列），并且该列中的值必须是唯一的。在后面的章节中，我们会介绍更多关于列索引的内容。

增加列

如果想要在一个表里增加新的一列的话， 我们可以选择将每行的值留空（NULL），或者也可以给每行设定一个默认值，这对有些情况来说十分方便。请看以下代码：

import sqlite3

sqlite_file = 'my_first_db.sqlite' #数据库文件名称
table_name = 'my_table_2' #要创建的另一个表格的名称
id_column = 'my_1st_column' #主键列的名称
new_column1 = 'my_2nd_column' #新列的名称
new_column2 = 'my_3rd_column' #新列的名称
column_type = 'TEXT' #或者 INTEGER, TEXT, NULL, REAL, BLOB
default_val = 'Hello World' #所有行的新增列的默认值

# 连接到数据库文件
conn = sqlite3.connect(sqlite_file)
c = conn.cursor()

# A) 增加没有默认值的列
c.execute("ALTER TABLE {tn} ADD COLUMN '{cn}' {ct}"\
.format(tn=table_name, cn=new_column1, ct=column_type))

# B) 增加有默认值的列
c.execute("ALTER TABLE {tn} ADD COLUMN '{cn}' {ct} DEFAULT '{df}'"\
.format(tn=table_name, cn=new_column2, ct=column_type, df=default_val))

# 提交更改并关掉与数据库文件的连接
conn.commit()
conn.close()

下载代码：add_new_column.py

我们刚才在SQLite数据库里的my_table_2这张表的主键列my_1st_column的旁边增加了两个新列。

这两列的区别在于，第三列my_3rd_column的默认值将被初始化为'Hello World'，在添加行的时候，如果我们不插入或者更新这个值，它将默认插入到本列下的每个现有单元格中。

插入和更新行

将行插入和更新到现有的SQLite数据库表中，可能是仅次于发送查询最常见的数据库操作。结构化查询语言 (SQL) 有一个方便的UPSERT功能，基本上是在UPDATE和INSERT命令的合并：它将某一行加入到一个数据库表中，并查询主键列的值。如果它不存在，就新建该行。如果存在，则更新现有的主键列的行的值。不幸的是，我们在这里使用的更紧凑的SQLite数据库不支持这种方便的语法。然而，也有一些变通方法。但是让我们先看以下的示例代码:

import sqlite3

sqlite_file = 'my_first_db.sqlite'
table_name = 'my_table_2'
id_column = 'my_1st_column'
column_name = 'my_2nd_column'

#连接数据库文件
conn = sqlite3.connect(sqlite_file)
c = conn.cursor()

# A) 插入在第二列中具有唯一ID的行
try:
c.execute("INSERT INTO {tn} ({idf}, {cn}) VALUES (123456, 'test')".\
format(tn=table_name, idf=id_column, cn=column_name))
except sqlite3.IntegrityError:
print('ERROR: ID already exists in PRIMARY KEY column {}'.format(id_column))

# B) 尝试插入一个可能具有唯一ID（如果还不存在）的行
c.execute("INSERT OR IGNORE INTO {tn} ({idf}, {cn}) VALUES (123456, 'test')".\
format(tn=table_name, idf=id_column, cn=column_name))

# C) 更新刚插入的或者已经存在的行的值
c.execute("UPDATE {tn} SET {cn}=('Hi World') WHERE {idf}=(123456)".\
format(tn=table_name, cn=column_name, idf=id_column))

conn.commit()
conn.close()

下载代码：update_or_insert_records.py

方法AINSERT和方法BINSERT OR IGNORE在给定的主键值不存在的情况下，都可以实现向数据库中插入新行的功能。然而，如果我们尝试添加一个已经存在的主键值，那么简单的INSERT命令会触发sqlite3.IntegrityError的一个错误异常。这种情况，可以使用try-except语句（方法A）来捕获该异常，或者使用SQLite称之为INSERT OR IGNORE的命令来处理（方法B）。如果我们想要在SQLite中构建一个等同于UPSERT的命令，这会非常有用。例如，假如我们想将一个新的数据表添加到现有的数据库表中，而这个数据表既包含有新的数据行，又有已有数据行的更新。