前言

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

目录

  1. 数据库操作简介
  2. 使用 sqlite3 进行本地数据库操作
  3. MySQL 数据库操作
  4. 数据库连接与查询
  5. 总结

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 sqlite3

def create_connection(db_file):
    """创建数据库连接"""
    conn = sqlite3.connect(db_file)
    return conn

def 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("已退出系统。")
            break
        else:
            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.connector

def create_connection():
    """创建数据库连接"""
    conn = mysql.connector.connect(
        host='localhost',
        user='root',
        password='your_password',
        database='company_db'
    )
    return conn

def 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("已退出系统。")
            break
        else:
            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 查询的一般步骤：

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

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

5. 总结

        数据库是应用程序开发中不可或缺的一部分。通过深入学习数据库操作，您可以构建功能更丰富、性能更优越的应用程序。本教程提供了详尽的代码示例和解释，希望能够帮助您夯实基础，提升技能。在未来的学习和工作中，您可以进一步探索更高级的数据库技术，如事务处理、索引优化、视图和存储过程等，不断提升自己的专业水平。

通过本教程，您学会了：

  • 使用 sqlite3 模块进行本地 SQLite 数据库的操作，包括连接数据库、创建表、插入、查询、更新和删除数据。
  • 使用 mysql.connector 模块连接和操作 MySQL 数据库，掌握了如何配置连接、执行 SQL 语句以及处理查询结果。
  • 构建了完整的应用实例，包括学生管理系统和员工管理系统，了解了如何将数据库操作应用于实际项目。
  • 理解了数据库连接的原理，学会了如何安全地执行 SQL 查询，以及如何处理异常。
# 数据库
Logo
松山湖开发者村综合服务平台

助力广东及东莞地区开发者，代码托管、在线学习与竞赛、技术交流与分享、资源共享、职业发展，成为松山湖开发者首选的工作与学习平台

更多推荐

cover

【初阶数据结构与算法】线性表之链表的分类以及双链表的定义与实现

avatar 松山湖开发者村综合服务平台
cover

【优选算法篇】微位至简，数之恢宏——解构 C++ 位运算中的理与美

avatar 松山湖开发者村综合服务平台
cover

动态规划 —— dp 问题-买卖股票的最佳时机含手续费

avatar 松山湖开发者村综合服务平台