python学习-SQLite数据库

python学习-SQLite数据库

本文介绍 SQLite 数据库在 Python 中的使用，包括 SQLite 数据库的简介、集成 SQLite、基本数据库操作（增删改查 CRUD）、事务处理。

供自己以后查漏补缺，也欢迎同道朋友交流学习。

引言

之前我们学习的都是直接读取文件数据、但在平常开发中，我们其中主要读取数据库里的数据，面向数据库进行数据的 CRUD 比较多，下面我们就学习下 SQLite 数据库在 Python 中的使用。

本章主要介绍 SQLite 数据库在 Python 中的使用，包括 SQLite 数据库的简介、集成 SQLite、基本数据库操作（增删改查 CRUD）、事务处理。

SQLite 简介

SQLite 是一个开源的嵌入式关系型数据库管理系统，它不需要一个单独的服务器进程或系统。SQLite 由 D. Richard Hipp 开发，并于 2000 年首次发布。

SQLite 的设计目标是提供一个轻量级的数据库解决方案，它可以被集成到应用程序中，而不需要额外的数据库服务器。

SQLite的主要特点

• 轻量级：SQLite 的数据库存储在一个单一的磁盘文件中，不需要运行一个独立的服务或进程，这使得它非常适合于小型应用程序和嵌入式系统。
• 无需服务器：与需要运行数据库服务器的数据库系统（如 MySQL、PostgreSQL）不同，SQLite 完全嵌入在应用程序中，不需要网络连接或服务器进程。
• 跨平台支持：可以在多种操作系统上运行，包括 Windows、macOS、Linux、Android 和 iOS，这使得它在不同的开发环境中都能使用。
• 自包含：SQLite 的数据库是一个单一的文件，这使得数据库的备份和迁移变得非常简单。
• 零配置：不需要复杂的配置，可以即开即用，这简化了部署和维护工作。
• 事务支持：支持 ACID 事务，确保数据的完整性和一致性。
• 多种编程语言支持：提供了多种编程语言的接口，包括 C、C++、Python、Java、PHP 等，这使得开发者可以轻松地在不同的编程环境中使用 SQLite。

Python集成SQLite

SQLite 与 Python 的集成是通过标准库中的 sqlite3 模块实现的。这个模块提供了一个轻量级的磁盘基数据库，它不需要一个独立的服务器进程，并且不依赖于 Python 的安装。

检查SQLite支持

import sqlite3
print(sqlite3.version)

基本数据库操作

数据库链接

在 Python 中连接到 SQLite 数据库非常简单，主要通过 sqlite3 模块中的 connect() 函数来实现。

这个函数允许你指定数据库文件的路径，并返回一个连接对象，通过这个连接对象，你可以执行 SQL 命令。

import sqlite3

# 连接到SQLite数据库（如果不存在，则创建一个新的数据库）
conn = sqlite3.connect('example-1.db')

# 关闭连接
conn.close()

创建表结构并插入数据

定义表结构：

在创建表之前，你需要定义表的结构，包括表的名称、字段（列）名称、数据类型以及每个字段的约束条件（如主键、外键、唯一性约束等）。设计表结构时，需要考虑数据的组织方式、查询效率以及数据完整性。

在 SQLite 中，使用 CREATE TABLE 语句来创建新表。该语句的基本格式如下：

CREATE TABLE table_name (
  column1 datatype constraints,
  column2 datatype constraints,
  ...
);

• table_name：要创建的表的名称。
• column1, column2, ...：表的列（字段）名称。
• datatype：列的数据类型，如 INTEGER、TEXT、REAL、BLOB 等。
• constraints：列的约束条件，如 PRIMARY KEY、UNIQUE、NOT NULL、FOREIGN KEY 等。

插入数据：

在创建表结构后，你可以使用 INSERT INTO 语句向表中插入数据。这个语句允许你指定要插入的表名、要插入的列名以及要插入的值。

import sqlite3

# 连接到SQLite数据库（如果不存在，则创建一个新的数据库）
conn = sqlite3.connect('example-2.db')

# 创建一个游标对象
cursor = conn.cursor()

# 创建一个表结构
create_table_sql = '''
  CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER
  )
'''
cursor.execute(create_table_sql)

# 插入单条数据
cursor.execute("INSERT INTO users (name, age) VALUES ('张三', 25)")

# 要插入的数据
data = (
  ('李四', 20),
  ('王五', 33),
  ('孙六', 44),
  ('常七', 34),
)

# 插入多条数据
cursor.executemany("INSERT INTO users (name, age) VALUES (?,?)", data)

# 提交事务
conn.commit()

# 关闭游标
cursor.close()

# 关闭连接
conn.close()

数据查询

在 SQLite 中，SELECT 语句用于查询数据库中的数据。基本的 SELECT 语句的语法如下：

SELECT column1, column2, ...
FROM table_name
WHERE condition;

• column1, column2, ...：你想从表中检索的列名。
• table_name：你想从中检索数据的表名。
• condition：查询条件（可选）。

如果没有指定 WHERE 子句，SELECT 语句将返回表中的所有行。

import sqlite3

# 连接到SQLite数据库（如果不存在，则创建一个新的数据库）
conn = sqlite3.connect('example-2.db')

# 创建一个游标对象
cursor = conn.cursor()

# 查询全部结果
cursor.execute('SELECT * FROM users')

# 打印查询结果
data = cursor.fetchall()
print('查询全部结果：', data)
# 查询全部结果： [(1, '张三', 25), (2, '李四', 20), (3, '王五', 33), (4, '孙六', 44), (5, '常七', 34)]

# 使用 WHERE 子句
cursor.execute('SELECT * FROM users WHERE age > 30')
# 打印查询结果
data = cursor.fetchall()
print('使用 WHERE 子句：', data)
# 查询全部结果： [(3, '王五', 33), (4, '孙六', 44), (5, '常七', 34)]

# 排序查询结果
cursor.execute('SELECT * FROM users ORDER BY age DESC')
# 打印查询结果
data = cursor.fetchall()
print('排序查询结果：', data)
# 查询全部结果： [(4, '孙六', 44), (5, '常七', 34), (3, '王五', 33), (1, '张三', 25), (2, '李四', 20)]

# 分组查询
cursor.execute('SELECT age, COUNT(*) FROM users GROUP BY age')
# 打印查询结果
data = cursor.fetchall()
print('分组查询：', data)
# 分组查询： [(20, 1), (25, 1), (33, 1), (34, 1), (44, 1)]

# 限制结果
cursor.execute('SELECT name, age FROM users Limit 2')
# 打印查询结果
data = cursor.fetchall()
print('限制结果：', data)
# 限制结果： [('张三', 25), ('李四', 20)]

# 关闭游标
cursor.close()

# 关闭连接
conn.close()

数据更新

在 SQLite 中，UPDATE 语句用于修改表中的现有记录。基本语法如下：

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

• table_name：要更新的表名。
• column1, column2, ...：要更新的列名。
• value1, value2, ...：新值。
• condition：查询条件（可选）。

更新用户表的年龄：

import sqlite3

# 连接到SQLite数据库（如果不存在，则创建一个新的数据库）
conn = sqlite3.connect('example-2.db')

# 创建一个游标对象
cursor = conn.cursor()

# 更新记录的 SQL 语句
update_sql = '''
  UPDATE users
  SET age = ?
  WHERE name = ?
'''

# 执行 SQL 语句 更新年龄
cursor.execute(update_sql, (30, '张三'))

# 更新多条记录
data = (
  (31, '李四'),
  (32, '王五'),
  (33, '孙六'),
  (34, '常七'),
)
# 更新多条记录的年龄
cursor.executemany(update_sql, data)

# 查询全部结果
cursor.execute('SELECT * FROM users')

# 打印查询结果
data = cursor.fetchall()
print('更新后的查询结果：', data)
# 更新后的查询结果： [(1, '张三', 30), (2, '李四', 31), (3, '王五', 32), (4, '孙六', 33), (5, '常七', 34)]

# 关闭游标
cursor.close()

# 关闭连接
conn.close()

数据删除

在 SQLite 中，DELETE 语句用于从表中删除记录。基本语法如下：

DELETE FROM table_name WHERE condition;

• table_name：要删除记录的表名。
• condition：查询条件（可选）。

警告：如果不使用 WHERE 子句，DELETE 语句将删除表中的所有记录。

import sqlite3

# 连接到SQLite数据库（如果不存在，则创建一个新的数据库）
conn = sqlite3.connect('example-2.db')

# 创建一个游标对象
cursor = conn.cursor()

# 插入单条数据
cursor.execute("INSERT INTO users (name, age) VALUES ('朱八', 24)")

# 删除单条记录
name_to_delete = '朱八'
cursor.execute('DELETE FROM users WHERE name = ?', (name_to_delete,))

# 关闭游标
cursor.close()

# 提交事务
conn.commit()

# 关闭连接
conn.close()

事务处理

事务的概念和重要性

事务是数据库管理系统中的一个核心概念，它指的是一组不可分割的数据库操作，这些操作要么全部成功，要么全部失败。事务的目的是确保数据库状态的完整性和一致性。

事务具有以下四个基本特性，通常被称为 ACID 属性：

• 原子性（Atomicity）：事务中的所有操作要么全部完成，要么全部不完成。
• 一致性（Consistency）：事务必须使数据库从一个一致性状态转换到另一个一致性状态。
• 隔离性（Isolation）：并发执行的事务之间不会相互影响。
• 持久性（Durability）：一旦事务提交，其结果就是永久性的，即使系统发生故障也不会丢失。

事务的重要性在于它们提供了一种可靠的机制来处理复杂的数据库操作，确保即使在发生错误或系统故障的情况下，数据的完整性和一致性也能得到保障。

事务管理

• 使用 commit()：commit() 函数用于提交当前事务，使得所有的更改永久保存到数据库中。

import sqlite3

# 连接到SQLite数据库（如果不存在，则创建一个新的数据库）
conn = sqlite3.connect('example-2.db')

# 创建一个游标对象
cursor = conn.cursor()

# 开始事务
try:
  # 执行一些数据库操作
  cursor.execute('UPDATE users SET age = ? WHERE name = ?', (27, '朱八'))
  
  # 关闭游标
  cursor.close()
  
  # 提交事务
  conn.commit()  
except sqlite3.Error as e:
  print(f"发生错误: {e}")
finally:
  # 关闭连接
  conn.close()

• 使用 rollback()：rollback() 函数用于在事务中发生错误时撤销所有更改，返回到事务开始前的状态。

import sqlite3

# 连接到SQLite数据库（如果不存在，则创建一个新的数据库）
conn = sqlite3.connect('example-2.db')

# 创建一个游标对象
cursor = conn.cursor()

# 开始事务
try:
  # 执行一些数据库操作
  cursor.execute('UPDATE users SET age = ? WHERE name = ?', (27, '朱八'))
  
  # 关闭游标
  cursor.close()
  
  raise sqlite3.Error('假设这里发生了一个错误') 
except sqlite3.Error as e:
  # 回滚事务
  conn.rollback()
  print(f"发生错误: {e}")
finally:
  # 关闭连接
  conn.close()

python学习专栏系列

• python学习-基础学习1
• python学习-基础学习2
• python学习-基础学习3
• python学习-面向对象编程1
• python学习-面向对象编程2
• python学习-文件读写
• python学习-程序异常处理
• python学习-正则
• python学习-处理word文档
• python学习-处理pdf文档
• python学习-处理excel文档
• python学习-处理csv文档
• python学习-使用matplotlib绘制图表
• python学习-处理JSON数据
• python学习-SQLite数据库
• python学习-多线程处理
• python学习-网络爬虫

练习代码库地址

python-study