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


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()

执行和输出:
建表的例子.png
执行完该程序以后,一个名为 students 的新表将会在数据库 mysqlite.db 中创建。但是如果你再执行该程序,你将会得到以下错误结果:
再次执行建表语句.jpg
为了不再受到表是否已存在的打扰,我们可以参考下小节例子,只有在表不存在的情况下才会创建新表。

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()

执行和输出:
只有在表不存在的情况下才会创建新表.jpg
可以看到,虽然我们在执行该程序前已经创建过表 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()

执行和输出:
检查表是否已在存在于 sqlite3.png

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()

执行和输出:
检查表是否已存在于 sqlite3 (不存在的场景).png

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()

执行和输出:
插入一行到 sqlite3 表.jpg

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()

执行和输出:
插入新行到 sqlite3 表并检查该插入是否成功
如果输出结果非零,那么证明你的插入确实奏效了。如果你得到了一个零,那么你需要调试你的程序了。

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()

执行和输出:
从 sqlite3 表中读取数据.png
可以看到之前插入的测试数据都已打印出来了。当然,还可以修改 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()

执行和输出:
使用 WHERE 从句从 sqlite3 中读取行.png

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()

执行和输出:
插入多行记录到 sqlite 表.jpg
我们打印出 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()

执行和输出:
删除 sqlite3 表中的行.png
没错,我们前边向该表总共插入了 5 条数据,现在全部删除了。

7.2. 小结

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

参考资料

©️2020 CSDN 皮肤主题: 创作都市 设计师:CSDN官方博客 返回首页