简单 Python 快乐之旅之：Python 基础语法之 sqlite3 专题

文章目录

• 1. 创建连接对象
• • 1.1. 使用 sqlite3 创建一个 Connection 对象的例子
• 2. 在 sqlite3 数据库创建表
• • 2.1. 使用 sqlite3 新建表
  • 2.2. 只有在表不存在的情况下才会创建新表
  • 2.3. 小结
• 3. 查看表是否已存在
• • 3.1. 检查表是否已在存在于 sqlite3
  • 3.2. 检查表是否已存在于 sqlite3 (不存在的场景)
  • 3.3. 检查表是否存在于内存 (RAM)
  • 3.4. 小结
• 4. 向 sqlite3 表中插入行
• • 4.1. 插入一行到 sqlite3 表
  • 4.2. 插入新行到 sqlite3 表并检查该插入是否成功
  • 4.3. 小结
• 5. 从 sqlite3 表中查询行
• • 5.1. 从 sqlite3 表中读取数据
  • 5.2. 使用 WHERE 从句从 sqlite3 中读取行
  • 5.3. 小结
• 6. 插入多行到 sqlite3 表
• • 6.1. 插入多行记录到 sqlite 表
  • 6.2. 小结
• 7. 删除 sqlite3 表中的所有记录
• • 7.1. 删除 sqlite3 表中的行
  • 7.2. 小结
• 参考资料

SQLite 是一种基于磁盘的轻量级的数据库，它不像 MySQL 那样需要一个独立的服务器，它的数据是直接持久存储，它的数据库其实就是一个文件。
正如其他任意关系型数据库那样，我们将在本文中掌握 Python 对 sqlite3 的数据库连接对象的创建、在数据库中创建表、向表中插入记录、基于从句查询表中数据、基于从句更新数据、删除部分或整表数据等相关技能。

1. 创建连接对象

Python 内置了 sqlite3，并提供 sqlite3 库。因此我们不需要安装任意东西、直接使用即可。
要创建一个 sqlite 的连接对象，可以使用 sqlite3.connect() 函数。该函数语法如下：

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

其中，connect() 函数读取了作为数据库名的一个字符串并返回一个 sqlite3.Connection 类对象。
如果该数据库已存在，它只会返回一个 Connection 对象，否则的话将会创建该数据库并返回一个该新建数据库的 Connection 对象。

1.1. 使用 sqlite3 创建一个 Connection 对象的例子

本例中，我们将创建一个连接到 sqlite 名为 mysqlite.db 数据库的 Connection 对象。

import sqlite3
conn = sqlite3.connect('mysqlite.db')

在使用 sqlite3 的任意函数之前你需要先导入 sqlite3 库。
当然，你也可以在内存 (RAM) 中创建一个数据库，只需要在创建 Connection 对象的时候将 :memory: 作为参数传给 sqlite3.connect() 即可。

import sqlite3
conn = sqlite3.connect(':memory:')

要在你所创建的 sqlite 数据库上进行操作的话，你还得给 Connection 对象创建一个游标。

import sqlite3
conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()

2. 在 sqlite3 数据库创建表

你可以在 sqlite3 数据库中创建一个或多个表。
在本节中，我们将会了解到如何使用 Python 在 sqlite3 数据库中新建一张表。
使用 Python sqlie3 创建表需要遵循以下步骤：

1. 创建一个连接到 sqlite3 数据库的 Connection 对象。
2. 一个 Connection 对象的游标。
3. 将 CREATE 查询传给 sqlite3.execute() 来新建表。

2.1. 使用 sqlite3 新建表

在本示例中，我们将创建一个名为 mysqlite.db 的数据库，并在其中新建一张名为 students 的表。

import sqlite3
conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()
# create table
c.execute('''CREATE TABLE students (rollno real, name text, class real)''')
# commit the changes to db
conn.commit()
# close the connection
conn.close()

执行和输出：

执行完该程序以后，一个名为 students 的新表将会在数据库 mysqlite.db 中创建。但是如果你再执行该程序，你将会得到以下错误结果：

为了不再受到表是否已存在的打扰，我们可以参考下小节例子，只有在表不存在的情况下才会创建新表。

2.2. 只有在表不存在的情况下才会创建新表

在查询语句中，我们可以定义为只有当该表不存在的情况下才会创建新表。你可以在查询语句中的表名之前使用 IF NOT EXISTS 来创建新表。

import sqlite3
conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()
# Create the table
c.execute('''CREATE TABLE IF NOT EXISTS students (rollno real, name text, class real)''')
# commit the changes to db
conn.commit()
# close the connection
conn.close()

执行和输出：

可以看到，虽然我们在执行该程序前已经创建过表 students，但是并没有受到前面表已存在错误的打扰。

2.3. 小结

在本节中，我们了解到了如何在 sqlite3 数据库中新建一张表。此外，我们还了解了如何在只有表不存在的情况下才新建表的相关知识。

3. 查看表是否已存在

在对表执行查询语句之前，你可以先检查一下该表是否已存在于 sqlite3 数据库。
要检查某张表是否已存在于 sqlite3 数据库，你可以从表 sqlite_master 中查询是否已有和你的表名匹配的表名。
相关语法如下：

SELECT name FROM sqlite_master WHERE type='table' AND name='table_name';

其中，table_name 需要替换为你要查询的表名。
在该查询的结果中你可以检查是否已有一些行存在。如果有一行结果的话，那么该表已存在。

3.1. 检查表是否已在存在于 sqlite3

在上一节的示例中我们已经新建了一张名为 students 的表。现在，我们将会用程序检查该表是否已存在。

import sqlite3
conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()
# get the count of the tables with the name
c.execute('''SELECT count(name) FROM sqlite_master WHERE type='table' AND name='students' ''')
# if the count is 1, then table exists
if c.fetchone()[0]==1:
    print('Table students exists')
else:
    print('Table students not exists')
# commit the changes to db
conn.commit()
# close the connection
conn.close()

执行和输出：

3.2. 检查表是否已存在于 sqlite3 (不存在的场景)

本实例中我们将检查一个反面场景，也就是名为 students1 的表不存在于 sqlite3 数据库时的场景。

import sqlite3
conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()
# get the count of tables with the name
c.execute('''SELECT count(name) FROM sqlite_master WHERE type='table' and name='students1' ''')
# if the count is 1, then exists
if c.fetchone()[0]==1:
    print('Table students1 exists')
else:
    print('Table students1 does not exists')
# commit the changes to db
conn.commit()
# close the connection
conn.close()

执行和输出：

3.3. 检查表是否存在于内存 (RAM)

如果想检查存放于内存 (RAM) 中的表，可以使用 sqlite_temp_master 将上述示例中的 sqlite_master 替换。

3.4. 小结

在本示例中我们了解了检查指定表是否已存在于 sqlite3 数据库的相关知识。

4. 向 sqlite3 表中插入行

你可以向 sqlite3 表中插入一行或多行。
要插入一行数据到 sqlite3 表，执行以下步骤。

1. 创建一个连接到 sqlite3 数据库的连接。
2. 拿到该连接的一个游标。
3. 检查该表是否已存在，如果不存在的话新建之。
4. 该表已存在的话，使用游标的 execute() 方法，通过将 SQL 插入查询传给该方法插入新行。

你可以通过 cursor.lastrowid() 来检查新行是否插入成功。

4.1. 插入一行到 sqlite3 表

在接下来的例子中，如果表不存在的话我们将新建表，然后使用 INSERT INTO 查询来插入一条记录到该表中去。

import sqlite3
conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()
# create table
c.execute('''CREATE TABLE IF NOT EXISTS students (rollno real, name text, class real)''')
c.execute('''INSERT INTO students VALUES(1, 'Alex', 8)''')
# commit the changes to db
conn.commit()
# close the connection
conn.close()

执行和输出：

4.2. 插入新行到 sqlite3 表并检查该插入是否成功

在大多数场景下，我们需要去核实该 INSERT INTO 查询是否成功插入。
要做到这一点，我们可以检查由 sqlite3 的游标所插入的最后一行记录的 id，如果该 id 不是 0，那么我们就可以确认该插入已经成功了。

import sqlite3
conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()
# create table
c.execute('''CREATE TABLE IF NOT EXISTS students (rollno real, name text, class real)''')
c.execute('''INSERT INTO students VALUES(1, 'Glen', 8)''')
print(c.lastrowid)
# commit the changes to db
conn.commit()
# close the connection
conn.close()

执行和输出：

如果输出结果非零，那么证明你的插入确实奏效了。如果你得到了一个零，那么你需要调试你的程序了。

4.3. 小结

本节中，我们了解到了怎样向 sqlite3 表中插入新行。

5. 从 sqlite3 表中查询行

要从 sqlite3 数据库表中查询数据需要遵循以下步骤：

1. 创建一个连接到 sqlite 数据库的 Connection 对象。
2. 新建一个该连接的游标。
3. 将 SELECT FROM 查询传给 sqlite3.execute() 方法并执行。

5.1. 从 sqlite3 表中读取数据

以下示例将读取 sqlite3 数据库表 students 里的所有行。

import sqlite3
conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()
c.execute('''SELECT * FROM students;''')
rows = c.fetchall()
for row in rows:
    print(row)
# commit the changes to db
conn.commit()
# close the connection
conn.close()

执行和输出：

可以看到之前插入的测试数据都已打印出来了。当然，还可以修改 SELECT FROM 查询来对数据进行过滤、排序或转换。

5.2. 使用 WHERE 从句从 sqlite3 中读取行

接下来是在 SELECT FROM 查询中使用 WHERE 从句来过滤查询行的示例。

import sqlite3
conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()
c.execute('''SELECT * FROM students WHERE name="Alex";''')
rows = c.fetchall()
for row in rows:
    print(row)
# commit the changes to db
conn.commit()
# close the connection
conn.close()

执行和输出：

5.3. 小结

本节中我们了解到了如何从一张表中查询记录的相关知识。

6. 插入多行到 sqlite3 表

在第四节中，我们了解到了如何向表中插入单行。本节中我们来了解一下如何使用单个 INSERT INTO 查询向表中插入多行数据。
插入多行记录到一张表的步骤如下：

1. 准备连接到数据库的 Connection 对象并获取一个游标。
2. 将待插入的记录存放进一张 list 里。
3. 将待插入记录的 list 作为参数传给 executemany() 方法并执行。

6.1. 插入多行记录到 sqlite 表

在接下来的示例中，我们将向前面已创建的 students 表中再插入三条数据。

import sqlite3
conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()
# records or rows in a list
records = [(1, 'Alen', 8),
           (2, 'Elliot', 9),
           (3, 'Bob', 7)]
# insert multiple records in a single query
c.executemany('INSERT INTO students VALUES(?,?,?);', records)
print('We have inserted', c.rowcount, 'records to the table.')
# commit the changes to db
conn.commit()
# close the connection
conn.close()

执行和输出：

我们打印出 cursor.rowcount 以确认该查询语句成功插入了三条记录。

6.2. 小结

在本节中，我们了解到了如何在单个查询语句中插入多行记录到 sqlite3 表的相关知识。

7. 删除 sqlite3 表中的所有记录

要删除 sqlite3 表中的所有记录，执行 DELETE FROM 查询。详细步骤如下：

1. 新建一个 sqlite3 的连接。
2. 得到该连接的游标。
3. 执行 DELETE FROM table 查询。

7.1. 删除 sqlite3 表中的行

以下示例中，我们将学习到如何使用 DELETE FROM table 查询语句来将 sqlite3 的表中的记录全部删除。

import sqlite3
conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()
# delete all rows from table
c.execute('DELETE FROM students;')
print('We have deleted', c.rowcount, 'records from the table.')
# commit the changes to db
conn.commit()
# close the connection
conn.close()

执行和输出：

没错，我们前边向该表总共插入了 5 条数据，现在全部删除了。

7.2. 小结

在本节中，我们了解了删除 sqlite3 表中的所有记录的相关知识。

参考资料

• Python sqlite3 Tutorial
• Python sqlite3 – Create Connection Object
• Python sqlite3 – Create Table
• Python sqlite3 – Check if Table Exists
• Python sqlite3 – INSERT INTO table
• Python sqlite3 – SELECT FROM TABLE
• Python sqlite3 – INSERT Multiple Rows to TABLE
• Python sqlite3 – DELETE all rows FROM TABLE