Python SQLite3 教程

在本教程中，我们将使用 Python 以编程方式处理 SQLite3 数据库。

SQLite 通常是一种无服务器数据库，您可以在包括 Python 在内的几乎所有编程语言中使用它。无服务器意味着无需安装单独的服务器来使用 SQLite，因此您可以直接与数据库连接。

SQLite 是一个轻量级数据库，它可以提供零配置的关系数据库管理系统，因为无需配置或设置任何东西即可使用它。

我们将使用 SQLite 版本 3 或 SQLite3，所以让我们开始吧。

目录

• 创建连接
• SQLite3 游标
• 创建数据库
• 创建表
• 插入表格
• 更新表
• 选择语句
• 获取所有数据
• SQLite3 行数
• 列出表格
• 检查表是否存在
• 删除表
• SQLite3 异常
  • 数据库错误
  • 完整性错误
  • 编程错误
  • 操作错误
  • 不支持错误
• SQLite3 Executemany（批量插入）
• 关闭连接
• SQLite3 日期时间

创建连接

要在 Python 中使用 SQLite3，首先，您必须导入_sqlite3_模块，然后创建一个连接对象，它将我们连接到数据库并让我们执行 SQL 语句。

您可以使用_connect()_函数创建连接对象：

import sqlite3

con = sqlite3.connect('mydatabase.db')
1
2
3

这将创建一个名为“mydatabase.db”的新文件。

SQLite3 游标

要在 Python 中执行 SQLite 语句，您需要一个游标对象。您可以使用_cursor()_方法创建它。

SQLite3 游标是连接对象的一种方法。要执行SQLite3语句，首先要建立连接，然后使用连接对象创建游标对象，如下所示：

con = sqlite3.connect('mydatabase.db')

cursorObj = con.cursor()
1
2
3

现在我们可以使用游标对象调用_execute()_方法来执行任何 SQL 查询。

创建数据库

当您创建与 SQLite 的连接时，如果它不存在，它将自动创建一个数据库文件。这个数据库文件是在磁盘上创建的；我们还可以使用 :memory: 和 connect 函数在 RAM 中创建数据库。该数据库称为内存数据库。

考虑下面的代码，其中我们使用_try_、_except_和_finally_块创建了一个数据库来处理任何异常：

import sqlite3

from sqlite3 import Error

def sql_connection():

    try:

        con = sqlite3.connect(':memory:')

        print("Connection is established: Database is created in memory")

    except Error:

        print(Error)

    finally:

        con.close()

sql_connection()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

首先，我们导入_sqlite3_模块，然后我们定义一个函数 sql_connection。在这个函数内部，我们有一个_try_块，_connect()_函数在建立连接后返回一个连接对象。

然后我们有_except_块，它在任何异常的情况下都会打印错误消息。如果没有错误，将建立连接并显示如下消息。

之后，我们在_finally_块中关闭了我们的连接。关闭连接是可选的，但这是一种很好的编程习惯，因此您可以从任何未使用的资源中释放内存。

创建表

要在 SQLite3 中创建表，您可以在_execute()_方法中使用 Create Table 查询。考虑以下步骤：

1. 创建连接对象。
2. 从连接对象创建一个游标对象。
3. 使用游标对象，以创建表查询为参数调用execute方法。

让我们创建具有以下属性的员工：

employees (id, name, salary, department, position, hireDate)

代码将是这样的：

import sqlite3

from sqlite3 import Error

def sql_connection():

    try:

        con = sqlite3.connect('mydatabase.db')

        return con

    except Error:

        print(Error)

def sql_table(con):

    cursorObj = con.cursor()

    cursorObj.execute("CREATE TABLE employees(id integer PRIMARY KEY, name text, salary real, department text, position text, hireDate text)")

    con.commit()

con = sql_connection()

sql_table(con)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

在上面的代码中，我们定义了两个方法，第一个方法建立连接，第二个方法创建一个游标对象来执行create table语句。

在_提交（）_方法保存所有我们所做的更改。最后，这两种方法都被调用。

要检查我们的表是否已创建，您可以使用SQLite的数据库浏览器来查看您的表。使用该程序打开 mydatabase.db 文件，您应该会看到您的表：

插入表格

要在表中插入数据，我们使用 INSERT INTO 语句。考虑以下代码行：

cursorObj.execute("INSERT INTO employees VALUES(1, 'John', 700, 'HR', 'Manager', '2017-01-04')")

con.commit()
1
2
3

要检查数据是否已插入，请单击 DB Browser 中的 Browse Data：

我们还可以将值/参数传递给_execute()_方法中的 INSERT 语句。您可以使用问号 () 作为每个值的占位符。INSERT 的语法如下所示：

cursorObj.execute(‘’‘INSERT INTO employees(id, name, salary, department, position, hireDate) VALUES(, , , , , )’‘’, entities)

其中实体包含占位符的值如下：

entities = (2, ‘Andrew’, 800, ‘IT’, ‘Tech’, ‘2018-02-06’)

整个代码如下：

import sqlite3

con = sqlite3.connect('mydatabase.db')

def sql_insert(con, entities):

    cursorObj = con.cursor()
    
    cursorObj.execute('INSERT INTO employees(id, name, salary, department, position, hireDate) VALUES(?, ?, ?, ?, ?, ?)', entities)
    
    con.commit()

entities = (2, 'Andrew', 800, 'IT', 'Tech', '2018-02-06')

sql_insert(con, entities)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

更新表

要更新表，只需创建一个连接，然后使用该连接创建一个游标对象，最后在_execute()_方法中使用 UPDATE 语句。

假设我们要更新 id 等于 2 的员工的姓名。为了更新，我们将使用 UPDATE 语句和 id 等于 2 的员工。我们将使用 WHERE 子句作为选择该员工的条件。

考虑以下代码：

import sqlite3

con = sqlite3.connect('mydatabase.db')

def sql_update(con):

    cursorObj = con.cursor()

    cursorObj.execute('UPDATE employees SET name = "Rogers" where id = 2')

    con.commit()

sql_update(con)
1
2
3
4
5
6
7
8
9
10
11
12
13

这会将名称从 Andrew 更改为 Rogers，如下所示：

选择语句

您可以使用 select 语句从特定表中选择数据。如果要从表中选择数据的所有列，可以使用星号 (*)。其语法如下：

select * from table_name

在SQLite3中，SELECT语句是在游标对象的execute方法中执行的。例如选择employees表的所有列，运行如下代码：

cursorObj.execute('SELECT * FROM employees ')

如果要从表中选择几列，请指定如下所示的列：

select column1, column2 from tables_name

例如，

cursorObj.execute(‘SELECT id, name FROM employees’)

select语句从数据库表中选择需要的数据，如果要获取选中的数据，则使用游标对象的_fetchall()_方法。我们将在下一节中演示这一点。

获取所有数据

要从数据库中获取数据，我们将执行 SELECT 语句，然后使用游标对象的_fetchall()_方法将值存储到变量中。之后，我们将遍历变量并打印所有值。

代码将是这样的：

import sqlite3

con = sqlite3.connect('mydatabase.db')

def sql_fetch(con):

    cursorObj = con.cursor()

    cursorObj.execute('SELECT * FROM employees')

    rows = cursorObj.fetchall()

    for row in rows:

        print(row)

sql_fetch(con)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

上面的代码会打印出我们数据库中的记录如下：

您还可以在_一行中_使用_fetchall()_，如下所示：

[print(row) for row in cursorObj.fetchall()]

如果要从数据库中获取特定数据，可以使用 WHERE 子句。例如，我们想要获取工资大于 800 的员工的 id 和姓名。为此，让我们用更多行填充我们的表，然后执行我们的查询。

您可以使用插入语句来填充数据，也可以在 DB 浏览器程序中手动输入它们。

现在，要获取工资大于 800 的人的 id 和姓名：

import sqlite3

con = sqlite3.connect('mydatabase.db')

def sql_fetch(con):

    cursorObj = con.cursor()

    cursorObj.execute('SELECT id, name FROM employees WHERE salary > 800.0')

    rows = cursorObj.fetchall()

    for row in rows:

        print(row)

sql_fetch(con)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

在上面的 SELECT 语句中，我们没有使用星号 (*)，而是指定了 id 和 name 属性。结果将如下所示：

SQLite3 行数

SQLite3 rowcount 用于返回最近执行的 SQL 查询影响或选择的行数。

当我们在 SELECT 语句中使用 rowcount 时，将返回 -1，因为在全部提取之前，选择了多少行是未知的。考虑下面的例子：

print(cursorObj.execute(‘SELECT * FROM employees’).rowcount)

因此，要获取行数，需要获取所有数据，然后获取结果的长度：

rows = cursorObj.fetchall()

print len (rows)
1
2
3

当您使用不带任何条件（where 子句）的 DELETE 语句时，将删除表中的所有行，并返回 rowcount 中已删除行的总数。

print(cursorObj.execute(‘DELETE FROM employees’).rowcount)

如果没有行被删除，它将返回零。

列出表格

要列出 SQLite3 数据库中的所有表，您应该查询 sqlite_master 表，然后使用_fetchall()_从 SELECT 语句中获取结果。

sqlite_master 是 SQLite3 中的主表，存放所有的表。

import sqlite3

con = sqlite3.connect('mydatabase.db')

def sql_fetch(con):

    cursorObj = con.cursor()

    cursorObj.execute('SELECT name from sqlite_master where type= "table"')

    print(cursorObj.fetchall())

sql_fetch(con)
1
2
3
4
5
6
7
8
9
10
11
12
13

这将列出所有表，如下所示：

检查表是否存在

创建表时，我们应该确保该表不存在。同样，删除/删除表时，该表应该存在。

为了检查表是否已经存在，我们在 CREATE TABLE 语句中使用“if not exists”，如下所示：

create table if not exists table_name (column1, column2, …, columnN)

例如：

import sqlite3

con = sqlite3.connect('mydatabase.db')

def sql_fetch(con):

    cursorObj = con.cursor()

    cursorObj.execute('create table if not exists projects(id integer, name text)')

    con.commit()

sql_fetch(con)
1
2
3
4
5
6
7
8
9
10
11
12
13

同样，要在删除时检查表是否存在，我们在 DROP TABLE 语句中使用“if exists”，如下所示：

drop table if exists table_name

例如，

cursorObj.execute(‘drop table if exists projects’)

我们还可以通过执行以下查询来检查我们要访问的表是否存在：

cursorObj.execute('SELECT name from sqlite_master WHERE type = "table" AND name = "employees"')

print(cursorObj.fetchall())
1
2
3

如果员工表存在，它将返回其名称如下：

如果我们指定的表名不存在，将返回一个空数组：

删除表

您可以使用 DROP 语句删除/删除表。DROP 语句的语法如下：

drop table table_name

要删除表，该表应存在于数据库中。因此，建议在 drop 语句中使用“if exists”，如下所示：

drop table if exists table_name

例如，

import sqlite3

con = sqlite3.connect('mydatabase.db')

def sql_fetch(con):

    cursorObj = con.cursor()

    cursorObj.execute('DROP table if exists employees')

    con.commit()

sql_fetch(con)
1
2
3
4
5
6
7
8
9
10
11
12
13

SQLite3 异常

异常是运行时错误。在Python 编程中，所有异常都是从 BaseException 派生的类的实例。

在 SQLite3 中，我们有以下主要的 Python 异常：

数据库错误

任何与数据库相关的错误都会引发 DatabaseError。

完整性错误

IntegrityError 是 DatabaseError 的子类，在出现数据完整性问题时会引发。例如，所有表中的外部数据均未更新，从而导致数据不一致。

编程错误

当存在语法错误或未找到表或使用错误数量的参数/参数调用函数时，将引发异常 ProgrammingError。

操作错误

当数据库操作失败时会引发此异常，例如异常断开连接。这不是程序员的错。

不支持错误

当您使用数据库未定义或不支持的某些方法时，将引发 NotSupportedError 异常。

SQLite3 Executemany（批量插入）

您可以使用 executemany 语句一次插入多行。

考虑以下代码：

import sqlite3

con = sqlite3.connect('mydatabase.db')

cursorObj = con.cursor()

cursorObj.execute('create table if not exists projects(id integer, name text)')

data = [(1, "Ridesharing"), (2, "Water Purifying"), (3, "Forensics"), (4, "Botany")]

cursorObj.executemany("INSERT INTO projects VALUES(?, ?)", data)

con.commit()
1
2
3
4
5
6
7
8
9
10
11
12
13

这里我们创建了一个有两列的表，“data”每列有四个值。我们将变量与查询一起传递给_executemany()_方法。

请注意，我们使用了占位符来传递值。

上面的代码将生成以下结果：

关闭连接

使用完数据库后，最好关闭连接。您可以使用_close()_方法关闭连接。

要关闭连接，请使用连接对象并调用_close()_方法，如下所示：

con = sqlite3.connect('mydatabase.db')

#program statements

con.close()
1
2
3
4
5

SQLite3 日期时间

在 Python SQLite3 数据库中，我们可以通过导入_datatime_模块轻松存储日期或时间。以下格式是可用于日期时间的最常见格式：

YYYY-MM-DD

YYYY-MM-DD HH:MM

YYYY-MM-DD HH:MM:SS

YYYY-MM-DD HH:MM:SS.SSS

HH:MM

HH:MM:SS

HH:MM:SS.SSS

now
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

考虑以下代码：

import sqlite3

import datetime

con = sqlite3.connect('mydatabase.db')

cursorObj = con.cursor()

cursorObj.execute('create table if not exists assignments(id integer, name text, date date)')

data = [(1, "Ridesharing", datetime.date(2017, 1, 2)), (2, "Water Purifying", datetime.date(2018, 3, 4))]

cursorObj.executemany("INSERT INTO assignments VALUES(?, ?, ?)", data)

con.commit()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

在这段代码中，我们首先导入了 datetime 模块，并创建了一个名为 assignments 的表，其中包含三列。

第三列的数据类型是日期。为了在列中插入日期，我们使用了_datetime.date_。同样，我们可以使用_datetime.time_来处理时间。

上面的代码将生成以下输出：

SQLite3 数据库的巨大灵活性和移动性使其成为任何开发人员使用它并将其与任何产品一起发布的首选。

您可以在 Windows、Linux、Mac OS、Android 和 iOS 项目中使用 SQLite3 数据库，因为它们具有出色的可移植性。因此，您随项目一起发送一个文件，仅此而已。

先自我介绍一下，小编13年上师交大毕业，曾经在小公司待过，去过华为OPPO等大厂，18年进入阿里，直到现在。深知大多数初中级java工程师，想要升技能，往往是需要自己摸索成长或是报班学习，但对于培训机构动则近万元的学费，着实压力不小。自己不成体系的自学效率很低又漫长，而且容易碰到天花板技术停止不前。因此我收集了一份《java开发全套学习资料》送给大家，初衷也很简单，就是希望帮助到想自学又不知道该从何学起的朋友，同时减轻大家的负担。添加下方名片，即可获取全套学习资料哦