Python 课程9-資料庫操作

前言

        在现代软件开发中,数据库是核心组件之一,它负责数据的存储、管理和检索。无论是简单的应用程序还是复杂的企业级系统,数据库操作都是必不可少的。本教程将深入讲解如何使用 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 查询,以及如何处理异常。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值