Python 课程9-資料庫操作

前言

在现代软件开发中，数据库是核心组件之一，它负责数据的存储、管理和检索。无论是简单的应用程序还是复杂的企业级系统，数据库操作都是必不可少的。本教程将深入讲解如何使用 Python 进行数据库操作，涵盖使用 sqlite3 进行本地数据库操作、MySQL 数据库操作，以及数据库的连接与查询。我们将提供详尽的代码示例，并逐步解析每个细节，确保您能够完全掌握这些技能。最后，我们还将提供完整的应用实例，并展示实际运行的结果，帮助您将所学知识应用于实际项目中。

数据库操作简介
- 什么是数据库？
- 关系型数据库与非关系型数据库
使用 sqlite3 进行本地数据库操作
- SQLite 简介
- 连接到 SQLite 数据库
- 创建表
- 插入数据
- 查询数据
- 更新和删除数据
- 应用实例：学生管理系统
MySQL 数据库操作
- MySQL 简介
- 安装 MySQL Connector/Python
- 连接到 MySQL 数据库
- 创建表
- 插入数据
- 查询数据
- 更新和删除数据
- 应用实例：员工管理系统
数据库连接与查询
- 理解数据库连接
- 执行 SQL 查询
- 使用参数化查询防止 SQL 注入
- 异常处理
总结

1. 数据库操作简介

1.1 什么是数据库？

数据库是一个以特定方式组织、存储和管理数据的仓库。它允许用户以结构化的方式存储数据，以便高效地检索、插入、更新和删除数据。

1.2 关系型数据库与非关系型数据库

关系型数据库（RDBMS）：使用表格来存储数据，表与表之间通过外键建立关系。常见的有 SQLite、MySQL、PostgreSQL 等。
非关系型数据库（NoSQL）：以键值对、文档、列族或图等方式存储数据。常见的有 MongoDB、Redis、Cassandra 等。

2. 使用 sqlite3 进行本地数据库操作

2.1 SQLite 简介

SQLite 是一个自包含的、无需服务器的、零配置的、事务性的 SQL 数据库引擎。它非常适合用于本地、小型项目的数据存储。

2.2 连接到 SQLite 数据库

import sqlite3  # 导入 sqlite3 模块# 连接到 SQLite 数据库文件，如果文件不存在，会自动创建
conn = sqlite3.connect('students.db')# 创建游标对象，用于执行 SQL 语句
cursor = conn.cursor()

解释：

sqlite3.connect('students.db')：连接到名为 students.db 的数据库文件。
cursor = conn.cursor()：创建一个游标对象，用于执行数据库操作。

2.3 创建表

# 创建名为 students 的表
cursor.execute('''CREATE TABLE IF NOT EXISTS students (id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT NOT NULL,age INTEGER NOT NULL,gender TEXT NOT NULL)
''')# 提交事务
conn.commit()

解释：

CREATE TABLE IF NOT EXISTS students：创建一个名为 students 的表，如果表已存在则不创建。
字段说明：
- id：自增主键。
- name：学生姓名。
- age：学生年龄。
- gender：学生性别。

2.4 插入数据

# 插入单条数据
cursor.execute('INSERT INTO students (name, age, gender) VALUES (?, ?, ?)', ('张三', 20, '男'))# 插入多条数据
students_data = [('李四', 22, '女'),('王五', 19, '男'),('赵六', 21, '女')
]
cursor.executemany('INSERT INTO students (name, age, gender) VALUES (?, ?, ?)', students_data)# 提交事务
conn.commit()

解释：

使用 ? 作为参数占位符，防止 SQL 注入攻击。
cursor.executemany()：一次性插入多条数据。

2.5 查询数据

# 查询所有学生信息
cursor.execute('SELECT * FROM students')# 获取所有查询结果
students = cursor.fetchall()# 输出结果
print("学生信息：")
for student in students:print(f"ID: {student[0]}, 姓名: {student[1]}, 年龄: {student[2]}, 性别: {student[3]}")

输出示例：

学生信息：
ID: 1, 姓名: 张三, 年龄: 20, 性别: 男
ID: 2, 姓名: 李四, 年龄: 22, 性别: 女
ID: 3, 姓名: 王五, 年龄: 19, 性别: 男
ID: 4, 姓名: 赵六, 年龄: 21, 性别: 女

解释：

cursor.fetchall()：获取查询的所有结果。
遍历结果并输出每个学生的信息。

2.6 更新和删除数据

# 更新学生信息，将张三的年龄改为 21 岁
cursor.execute('UPDATE students SET age = ? WHERE name = ?', (21, '张三'))# 删除姓名为王五的学生
cursor.execute('DELETE FROM students WHERE name = ?', ('王五',))# 提交事务
conn.commit()

解释：

UPDATE 语句用于更新数据。
DELETE 语句用于删除数据。

2.7 应用实例：学生管理系统

下面我们将整合以上操作，构建一个简单的学生管理系统。

import sqlite3def create_connection(db_file):"""创建数据库连接"""conn = sqlite3.connect(db_file)return conndef create_table(conn):"""创建 students 表"""cursor = conn.cursor()cursor.execute('''CREATE TABLE IF NOT EXISTS students (id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT NOT NULL,age INTEGER NOT NULL,gender TEXT NOT NULL)''')conn.commit()def add_student(conn, name, age, gender):"""添加学生"""cursor = conn.cursor()cursor.execute('INSERT INTO students (name, age, gender) VALUES (?, ?, ?)', (name, age, gender))conn.commit()def view_students(conn):"""查看所有学生"""cursor = conn.cursor()cursor.execute('SELECT * FROM students')students = cursor.fetchall()for student in students:print(f"ID: {student[0]}, 姓名: {student[1]}, 年龄: {student[2]}, 性别: {student[3]}")def update_student(conn, student_id, name, age, gender):"""更新学生信息"""cursor = conn.cursor()cursor.execute('UPDATE students SET name = ?, age = ?, gender = ? WHERE id = ?', (name, age, gender, student_id))conn.commit()def delete_student(conn, student_id):"""删除学生"""cursor = conn.cursor()cursor.execute('DELETE FROM students WHERE id = ?', (student_id,))conn.commit()def main():conn = create_connection('students.db')create_table(conn)while True:print("\n=== 学生管理系统 ===")print("1. 添加学生")print("2. 查看所有学生")print("3. 更新学生信息")print("4. 删除学生")print("5. 退出")choice = input("请输入选项（1-5）：")if choice == '1':name = input("姓名：")age = int(input("年龄："))gender = input("性别：")add_student(conn, name, age, gender)print("学生添加成功！")elif choice == '2':view_students(conn)elif choice == '3':student_id = int(input("请输入要更新的学生ID："))name = input("新的姓名：")age = int(input("新的年龄："))gender = input("新的性别：")update_student(conn, student_id, name, age, gender)print("学生信息更新成功！")elif choice == '4':student_id = int(input("请输入要删除的学生ID："))delete_student(conn, student_id)print("学生删除成功！")elif choice == '5':conn.close()print("已退出系统。")breakelse:print("无效的选项，请重新输入。")if __name__ == '__main__':main()

解释：

这是一个简单的控制台程序，提供添加、查看、更新和删除学生的功能。
使用 input() 获取用户输入。
程序运行时，会根据用户的选择执行相应的操作。

3. MySQL 数据库操作

3.1 MySQL 简介

MySQL 是一个开源的关系型数据库管理系统，具有高性能、高可靠性和易用性，广泛应用于各种应用程序中。

3.2 安装 MySQL Connector/Python

要使用 Python 连接 MySQL 数据库，需要安装 mysql-connector-python 模块。

pip install mysql-connector-python

3.3 连接到 MySQL 数据库

import mysql.connector  # 导入 mysql.connector 模块# 建立数据库连接
conn = mysql.connector.connect(host='localhost',        # 主机地址user='root',             # 用户名password='your_password',# 密码database='company_db'    # 数据库名称
)# 创建游标对象
cursor = conn.cursor()

解释：

请将 'your_password' 替换为实际的 MySQL 密码。
'company_db' 是要连接的数据库，如果不存在，需要先在 MySQL 中创建。

3.4 创建表

# 创建名为 employees 的表
cursor.execute('''CREATE TABLE IF NOT EXISTS employees (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(255) NOT NULL,position VARCHAR(255) NOT NULL,salary DECIMAL(10, 2) NOT NULL)
''')# 提交事务
conn.commit()

解释：

DECIMAL(10, 2)：定义一个精度为 10 位、保留 2 位小数的数值，适用于存储货币等精确数值。

3.5 插入数据

# 插入单条数据
cursor.execute('INSERT INTO employees (name, position, salary) VALUES (%s, %s, %s)', ('Alice', '经理', 12000.00))# 插入多条数据
employees_data = [('Bob', '工程师', 8000.00),('Charlie', '销售', 7000.00),('Diana', '人事', 6000.00)
]
cursor.executemany('INSERT INTO employees (name, position, salary) VALUES (%s, %s, %s)', employees_data)# 提交事务
conn.commit()

解释：

使用 %s 作为参数占位符，MySQL Connector 会自动处理数据类型和转义。

3.6 查询数据

# 查询所有员工信息
cursor.execute('SELECT * FROM employees')# 获取所有查询结果
employees = cursor.fetchall()# 输出结果
print("员工信息：")
for emp in employees:print(f"ID: {emp[0]}, 姓名: {emp[1]}, 职位: {emp[2]}, 薪资: {emp[3]}")

输出示例：

员工信息：
ID: 1, 姓名: Alice, 职位: 经理, 薪资: 12000.00
ID: 2, 姓名: Bob, 职位: 工程师, 薪资: 8000.00
ID: 3, 姓名: Charlie, 职位: 销售, 薪资: 7000.00
ID: 4, 姓名: Diana, 职位: 人事, 薪资: 6000.00

解释：

cursor.fetchall()：获取查询的所有结果。
遍历结果并输出每个员工的信息。

3.7 更新和删除数据

# 更新员工薪资
cursor.execute('UPDATE employees SET salary = %s WHERE name = %s', (13000.00, 'Alice'))# 删除员工
cursor.execute('DELETE FROM employees WHERE name = %s', ('Diana',))# 提交事务
conn.commit()

解释：

UPDATE 语句用于更新数据。
DELETE 语句用于删除数据。

3.8 应用实例：员工管理系统

下面我们将构建一个简单的员工管理系统。

import mysql.connectordef create_connection():"""创建数据库连接"""conn = mysql.connector.connect(host='localhost',user='root',password='your_password',database='company_db')return conndef create_table(conn):"""创建 employees 表"""cursor = conn.cursor()cursor.execute('''CREATE TABLE IF NOT EXISTS employees (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(255) NOT NULL,position VARCHAR(255) NOT NULL,salary DECIMAL(10, 2) NOT NULL)''')conn.commit()def add_employee(conn, name, position, salary):"""添加员工"""cursor = conn.cursor()cursor.execute('INSERT INTO employees (name, position, salary) VALUES (%s, %s, %s)', (name, position, salary))conn.commit()def view_employees(conn):"""查看所有员工"""cursor = conn.cursor()cursor.execute('SELECT * FROM employees')employees = cursor.fetchall()for emp in employees:print(f"ID: {emp[0]}, 姓名: {emp[1]}, 职位: {emp[2]}, 薪资: {emp[3]}")def update_employee(conn, emp_id, name, position, salary):"""更新员工信息"""cursor = conn.cursor()cursor.execute('UPDATE employees SET name = %s, position = %s, salary = %s WHERE id = %s', (name, position, salary, emp_id))conn.commit()def delete_employee(conn, emp_id):"""删除员工"""cursor = conn.cursor()cursor.execute('DELETE FROM employees WHERE id = %s', (emp_id,))conn.commit()def main():conn = create_connection()create_table(conn)while True:print("\n=== 员工管理系统 ===")print("1. 添加员工")print("2. 查看所有员工")print("3. 更新员工信息")print("4. 删除员工")print("5. 退出")choice = input("请输入选项（1-5）：")if choice == '1':name = input("姓名：")position = input("职位：")salary = float(input("薪资："))add_employee(conn, name, position, salary)print("员工添加成功！")elif choice == '2':view_employees(conn)elif choice == '3':emp_id = int(input("请输入要更新的员工ID："))name = input("新的姓名：")position = input("新的职位：")salary = float(input("新的薪资："))update_employee(conn, emp_id, name, position, salary)print("员工信息更新成功！")elif choice == '4':emp_id = int(input("请输入要删除的员工ID："))delete_employee(conn, emp_id)print("员工删除成功！")elif choice == '5':conn.close()print("已退出系统。")breakelse:print("无效的选项，请重新输入。")if __name__ == '__main__':main()

运行结果示例：

=== 员工管理系统 ===
1. 添加员工
2. 查看所有员工
3. 更新员工信息
4. 删除员工
5. 退出
请输入选项（1-5）：1
姓名：Emily
职位：设计师
薪资：9000
员工添加成功！=== 员工管理系统 ===
1. 添加员工
2. 查看所有员工
3. 更新员工信息
4. 删除员工
5. 退出
请输入选项（1-5）：2
ID: 1, 姓名: Alice, 职位: 经理, 薪资: 13000.00
ID: 2, 姓名: Bob, 职位: 工程师, 薪资: 8000.00
ID: 3, 姓名: Charlie, 职位: 销售, 薪资: 7000.00
ID: 5, 姓名: Emily, 职位: 设计师, 薪资: 9000.00=== 员工管理系统 ===
1. 添加员工
2. 查看所有员工
3. 更新员工信息
4. 删除员工
5. 退出
请输入选项（1-5）：5
已退出系统。

解释：

该程序提供添加、查看、更新和删除员工的功能。
使用 mysql.connector 连接 MySQL 数据库。
运行前确保数据库连接配置正确，并已创建对应的数据库。

4. 数据库连接与查询

4.1 理解数据库连接

数据库连接是应用程序与数据库之间的通信通道。建立连接后，应用程序可以执行 SQL 语句，查询或修改数据。

关闭连接：

操作完成后，必须关闭游标和连接，释放资源。

# 关闭游标
cursor.close()# 关闭连接
conn.close()

4.2 执行 SQL 查询

执行 SQL 查询的一般步骤：

创建连接和游标
编写 SQL 语句
执行 SQL 语句
提交事务（针对数据修改操作）
获取结果（针对查询操作）
关闭游标和连接

4.3 使用参数化查询防止 SQL 注入

使用参数化查询可以防止 SQL 注入，确保数据安全。

# 用户输入
user_input = input("请输入用户名：")# 安全的参数化查询
cursor.execute('SELECT * FROM users WHERE username = %s', (user_input,))

解释：

避免将用户输入直接拼接到 SQL 语句中。
使用参数占位符，数据库驱动程序会自动处理转义。

4.4 异常处理

在数据库操作中，可能会发生各种异常，需要捕获并处理。

try:# 执行数据库操作cursor.execute('SELECT * FROM non_existing_table')
except mysql.connector.Error as err:print(f"发生错误：{err}")
finally:# 确保关闭连接cursor.close()conn.close()