引言
在现代应用程序开发中，数据持久化是一个核心需求。无论是Web应用、桌面软件还是移动应用，几乎所有的应用程序都需要将数据存储在数据库中，以便长期保存和高效检索。Python作为一种广泛应用的编程语言，提供了丰富的工具和库来与各种数据库进行交互。
数据库交互是应用程序与数据存储系统之间的桥梁，它允许程序执行创建、读取、更新和删除（CRUD）操作。掌握数据库交互技能对于任何希望开发数据驱动应用程序的开发者来说都是必不可少的。
在本章中，我们将深入探讨Python与数据库交互的各种方法。我们将从Python内置的SQLite数据库开始，逐步学习如何连接和操作流行的数据库系统如MySQL和PostgreSQL。此外，我们还将介绍ORM（对象关系映射）的概念和使用，以及如何使用SQLAlchemy这样的强大工具来简化数据库操作。
学习目标
完成本章学习后，您将能够：
- 理解数据库交互的基本概念和重要性
- 熟练使用Python内置的sqlite3模块进行数据库操作
- 掌握连接和操作MySQL、PostgreSQL等关系型数据库的方法
- 理解ORM（对象关系映射）的概念和优势
- 使用SQLAlchemy进行高级数据库操作
- 处理数据库连接异常和事务管理
- 构建一个完整的数据库应用示例
- 了解数据库安全和最佳实践
核心知识点讲解
1. 数据库交互基础概念
数据库交互是指应用程序通过编程语言与数据库管理系统进行通信，执行数据操作的过程。在Python中，这种交互通常通过数据库驱动程序和相应的API来实现。
数据库类型
- 关系型数据库（RDBMS）：如SQLite、MySQL、PostgreSQL、Oracle等
- 非关系型数据库（NoSQL）：如MongoDB、Redis、Cassandra等
数据库操作基础
- 连接数据库：建立应用程序与数据库之间的连接
- 执行SQL语句：通过SQL语言操作数据库
- 处理结果集：获取和处理查询结果
- 事务管理：确保数据一致性和完整性
- 连接池管理：优化数据库连接性能
2. SQLite数据库操作
SQLite是一个轻量级的嵌入式数据库，Python内置了对其的支持，无需额外安装驱动程序。
连接SQLite数据库
import sqlite3
import os
# 连接到数据库（如果不存在会自动创建）
conn = sqlite3.connect('example.db')
# 创建游标对象
cursor = conn.cursor()
# 执行SQL语句
cursor.execute('''CREATE TABLE IF NOT EXISTS users
(id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT UNIQUE,
age INTEGER)''')
# 提交事务
conn.commit()
# 关闭连接
conn.close()
基本CRUD操作
import sqlite3
class UserDatabase:
def __init__(self, db_name='users.db'):
self.db_name = db_name
self.init_database()
def init_database(self):
"""初始化数据库"""
with sqlite3.connect(self.db_name) as conn:
cursor = conn.cursor()
cursor.execute('''CREATE TABLE IF NOT EXISTS users
(id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT UNIQUE,
age INTEGER,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)''')
conn.commit()
def create_user(self, name, email, age):
"""创建用户"""
try:
with sqlite3.connect(self.db_name) as conn:
cursor = conn.cursor()
cursor.execute("INSERT INTO users (name, email, age) VALUES (?, ?, ?)",
(name, email, age))
conn.commit()
return cursor.lastrowid
except sqlite3.IntegrityError as e:
print(f"用户创建失败: {e}")
return None
def get_user(self, user_id):
"""获取用户信息"""
with sqlite3.connect(self.db_name) as conn:
cursor = conn.cursor()
cursor.execute("SELECT * FROM users WHERE id = ?", (user_id,))
return cursor.fetchone()
def get_all_users(self):
"""获取所有用户"""
with sqlite3.connect(self.db_name) as conn:
cursor = conn.cursor()
cursor.execute("SELECT * FROM users ORDER BY created_at DESC")
return cursor.fetchall()
def update_user(self, user_id, name=None, email=None, age=None):
"""更新用户信息"""
try:
with sqlite3.connect(self.db_name) as conn:
cursor = conn.cursor()
# 构建动态更新语句
updates = []
params = []
if name is not None:
updates.append("name = ?")
params.append(name)
if email is not None:
updates.append("email = ?")
params.append(email)
if age is not None:
updates.append("age = ?")
params.append(age)
if updates:
params.append(user_id)
sql = f"UPDATE users SET {', '.join(updates)} WHERE id = ?"
cursor.execute(sql, params)
conn.commit()
return cursor.rowcount > 0
return False
except sqlite3.IntegrityError as e:
print(f"用户更新失败: {e}")
return False
def delete_user(self, user_id):
"""删除用户"""
with sqlite3.connect(self.db_name) as conn:
cursor = conn.cursor()
cursor.execute("DELETE FROM users WHERE id = ?", (user_id,))
conn.commit()
return cursor.rowcount > 0
def search_users(self, keyword):
"""搜索用户"""
with sqlite3.connect(self.db_name) as conn:
cursor = conn.cursor()
cursor.execute("""SELECT * FROM users
WHERE name LIKE ? OR email LIKE ?""",
(f"%{keyword}%", f"%{keyword}%"))
return cursor.fetchall()
# 使用示例
def sqlite_demo():
# 创建数据库实例
db = UserDatabase()
# 创建用户
user_id = db.create_user("张三", "zhangsan@example.com", 25)
print(f"创建用户，ID: {user_id}")
# 获取用户
user = db.get_user(user_id)
print(f"获取用户: {user}")
# 更新用户
db.update_user(user_id, age=26)
updated_user = db.get_user(user_id)
print(f"更新后用户: {updated_user}")
# 搜索用户
results = db.search_users("张")
print(f"搜索结果: {results}")
# 获取所有用户
all_users = db.get_all_users()
print(f"所有用户: {all_users}")
# 运行示例
# sqlite_demo()
3. MySQL数据库操作
MySQL是最流行的开源关系型数据库之一。要使用Python操作MySQL，需要安装相应的驱动程序。
安装MySQL驱动
pip install mysql-connector-python
# 或者
pip install PyMySQL
连接MySQL数据库
import mysql.connector
from mysql.connector import Error
def create_mysql_connection(host, database, user, password, port=3306):
"""创建MySQL数据库连接"""
try:
connection = mysql.connector.connect(
host=host,
port=port,
database=database,
user=user,
password=password,
charset='utf8mb4',
autocommit=False
)
if connection.is_connected():
print("成功连接到MySQL数据库")
return connection
except Error as e:
print(f"连接MySQL数据库时出错: {e}")
return None
def create_user_table_mysql(connection):
"""在MySQL中创建用户表"""
try:
cursor = connection.cursor()
create_table_query = """
CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
age INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
"""
cursor.execute(create_table_query)
connection.commit()
print("用户表创建成功")
except Error as e:
print(f"创建表时出错: {e}")
finally:
cursor.close()
def insert_user_mysql(connection, name, email, age):
"""向MySQL插入用户"""
try:
cursor = connection.cursor()
insert_query = "INSERT INTO users (name, email, age) VALUES (%s, %s, %s)"
cursor.execute(insert_query, (name, email, age))
connection.commit()
print(f"用户 {name} 插入成功，ID: {cursor.lastrowid}")
return cursor.lastrowid
except Error as e:
print(f"插入用户时出错: {e}")
connection.rollback()
return None
finally:
cursor.close()
def select_users_mysql(connection):
"""从MySQL查询所有用户"""
try:
cursor = connection.cursor()
select_query = "SELECT * FROM users ORDER BY created_at DESC"
cursor.execute(select_query)
records = cursor.fetchall()
print("用户列表:")
for row in records:
print(f"ID: {row[0]}, 姓名: {row[1]}, 邮箱: {row[2]}, 年龄: {row[3]}, 创建时间: {row[4]}")
return records
except Error as e:
print(f"查询用户时出错: {e}")
return []
finally:
cursor.close()
# 使用示例
def mysql_demo():
# 连接数据库（需要先配置好MySQL服务器）
connection = create_mysql_connection(
host='localhost',
database='testdb',
user='your_username',
password='your_password'
)
if connection:
try:
# 创建表
create_user_table_mysql(connection)
# 插入用户
insert_user_mysql(connection, "李四", "lisi@example.com", 30)
insert_user_mysql(connection, "王五", "wangwu@example.com", 28)
# 查询用户
select_users_mysql(connection)
finally:
connection.close()
print("MySQL连接已关闭")
# 注意：需要先安装MySQL服务器并配置好连接参数才能运行
# mysql_demo()
4. PostgreSQL数据库操作
PostgreSQL是一个功能强大的开源对象关系型数据库系统。要使用Python操作PostgreSQL，需要安装psycopg2驱动。
安装PostgreSQL驱动
pip install psycopg2-binary
连接PostgreSQL数据库
import psycopg2
from psycopg2 import Error
def create_postgresql_connection(host, database, user, password, port=5432):
"""创建PostgreSQL数据库连接"""
try:
connection = psycopg2.connect(
host=host,
port=port,
database=database,
user=user,
password=password
)
print("成功连接到PostgreSQL数据库")
return connection
except Error as e:
print(f"连接PostgreSQL数据库时出错: {e}")
return None
def create_user_table_postgresql(connection):
"""在PostgreSQL中创建用户表"""
try:
cursor = connection.cursor()
create_table_query = """
CREATE TABLE IF NOT EXISTS users (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
age INTEGER,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
"""
cursor.execute(create_table_query)
connection.commit()
print("用户表创建成功")
except Error as e:
print(f"创建表时出错: {e}")
connection.rollback()
finally:
cursor.close()
def insert_user_postgresql(connection, name, email, age):
"""向PostgreSQL插入用户"""
try:
cursor = connection.cursor()
insert_query = "INSERT INTO users (name, email, age) VALUES (%s, %s, %s) RETURNING id"
cursor.execute(insert_query, (name, email, age))
user_id = cursor.fetchone()[0]
connection.commit()
print(f"用户 {name} 插入成功，ID: {user_id}")
return user_id
except Error as e:
print(f"插入用户时出错: {e}")
connection.rollback()
return None
finally:
cursor.close()
def select_users_postgresql(connection):
"""从PostgreSQL查询所有用户"""
try:
cursor = connection.cursor()
select_query = "SELECT * FROM users ORDER BY created_at DESC"
cursor.execute(select_query)
records = cursor.fetchall()
print("用户列表:")
for row in records:
print(f"ID: {row[0]}, 姓名: {row[1]}, 邮箱: {row[2]}, 年龄: {row[3]}, 创建时间: {row[4]}")
return records
except Error as e:
print(f"查询用户时出错: {e}")
return []
finally:
cursor.close()
# 使用示例
def postgresql_demo():
# 连接数据库（需要先配置好PostgreSQL服务器）
connection = create_postgresql_connection(
host='localhost',
database='testdb',
user='your_username',
password='your_password'
)
if connection:
try:
# 创建表
create_user_table_postgresql(connection)
# 插入用户
insert_user_postgresql(connection, "赵六", "zhaoliu@example.com", 32)
insert_user_postgresql(connection, "孙七", "sunqi@example.com", 29)
# 查询用户
select_users_postgresql(connection)
finally:
connection.close()
print("PostgreSQL连接已关闭")
# 注意：需要先安装PostgreSQL服务器并配置好连接参数才能运行
# postgresql_demo()
5. ORM概念与SQLAlchemy
ORM（Object-Relational Mapping，对象关系映射）是一种编程技术，用于在面向对象编程语言中实现关系型数据库的访问。它允许开发者使用面向对象的方式来操作数据库，而不需要直接编写SQL语句。
SQLAlchemy简介
SQLAlchemy是Python中最流行的ORM库之一，它提供了完整的工具集来处理数据库交互。
安装SQLAlchemy
pip install sqlalchemy
基本使用
from sqlalchemy import create_engine, Column, Integer, String, DateTime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from datetime import datetime
# 创建基类
Base = declarative_base()
# 定义User模型
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(100), nullable=False)
email = Column(String(100), unique=True, nullable=False)
age = Column(Integer)
created_at = Column(DateTime, default=datetime.utcnow)
def __repr__(self):
return f"<User(name='{self.name}', email='{self.email}', age={self.age})>"
# 创建数据库引擎（使用SQLite作为示例）
engine = create_engine('sqlite:///sqlalchemy_example.db', echo=True)
# 创建表
Base.metadata.create_all(engine)
# 创建会话
Session = sessionmaker(bind=engine)
session = Session()
# CRUD操作示例
def sqlalchemy_crud_demo():
# 创建用户
new_user = User(name="ORM用户", email="orm@example.com", age=27)
session.add(new_user)
session.commit()
print(f"创建用户: {new_user}")
# 查询用户
users = session.query(User).all()
print("所有用户:")
for user in users:
print(user)
# 根据ID查询
user = session.query(User).filter(User.id == 1).first()
if user:
print(f"查询到用户: {user}")
# 更新用户
if user:
user.age = 28
session.commit()
print(f"更新用户: {user}")
# 删除用户
# session.delete(user)
# session.commit()
# print("用户已删除")
# 运行示例
# sqlalchemy_crud_demo()
高级查询
from sqlalchemy import and_, or_, func
def advanced_sqlalchemy_queries():
# 条件查询
users = session.query(User).filter(User.age > 25).all()
print("年龄大于25的用户:", users)
# 多条件查询
users = session.query(User).filter(
and_(User.age > 20, User.name.like('%用户%'))
).all()
print("复合条件查询结果:", users)
# 排序
users = session.query(User).order_by(User.created_at.desc()).all()
print("按创建时间倒序排列:", users)
# 限制结果数量
users = session.query(User).limit(5).all()
print("限制5条结果:", users)
# 统计查询
count = session.query(func.count(User.id)).scalar()
print(f"用户总数: {count}")
# 分组统计
avg_age = session.query(func.avg(User.age)).scalar()
print(f"平均年龄: {avg_age}")
# advanced_sqlalchemy_queries()
6. 数据库连接池
在生产环境中，频繁地创建和销毁数据库连接会影响性能。使用连接池可以有效管理数据库连接。
from sqlalchemy import create_engine
from sqlalchemy.pool import QueuePool
import threading
import time
# 创建带连接池的引擎
engine = create_engine(
'sqlite:///pooled_example.db',
poolclass=QueuePool,
pool_size=10, # 连接池大小
max_overflow=20, # 超出pool_size后最多可创建的连接数
pool_recycle=3600, # 连接回收时间（秒）
pool_pre_ping=True # 连接前检查有效性
)
def worker(worker_id):
"""工作线程函数"""
for i in range(5):
try:
# 获取连接
with engine.connect() as conn:
result = conn.execute("SELECT 1")
print(f"Worker {worker_id}, iteration {i}: {result.fetchone()}")
time.sleep(0.1)
except Exception as e:
print(f"Worker {worker_id} 出错: {e}")
def connection_pool_demo():
"""连接池演示"""
# 创建多个工作线程
threads = []
for i in range(5):
thread = threading.Thread(target=worker, args=(i,))
threads.append(thread)
thread.start()
# 等待所有线程完成
for thread in threads:
thread.join()
print("连接池演示完成")
# connection_pool_demo()
7. 事务管理
事务是数据库操作的基本单位，确保数据的一致性和完整性。
import sqlite3
def transaction_demo():
"""事务管理演示"""
conn = sqlite3.connect('transaction_example.db')
cursor = conn.cursor()
# 创建表
cursor.execute('''CREATE TABLE IF NOT EXISTS accounts
(id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
balance REAL NOT NULL)''')
# 初始化账户数据
cursor.execute("DELETE FROM accounts")
cursor.execute("INSERT INTO accounts (name, balance) VALUES (?, ?)", ("Alice", 1000.0))
cursor.execute("INSERT INTO accounts (name, balance) VALUES (?, ?)", ("Bob", 500.0))
conn.commit()
def transfer_money(from_account, to_account, amount):
"""转账函数"""
try:
# 开始事务
cursor.execute("BEGIN")
# 检查转出账户余额
cursor.execute("SELECT balance FROM accounts WHERE name = ?", (from_account,))
from_balance = cursor.fetchone()[0]
if from_balance < amount:
raise Exception("余额不足")
# 执行转账
cursor.execute("UPDATE accounts SET balance = balance - ? WHERE name = ?",
(amount, from_account))
cursor.execute("UPDATE accounts SET balance = balance + ? WHERE name = ?",
(amount, to_account))
# 提交事务
conn.commit()
print(f"转账成功: {from_account} -> {to_account}, 金额: {amount}")
except Exception as e:
# 回滚事务
conn.rollback()
print(f"转账失败: {e}")
# 查询初始余额
cursor.execute("SELECT name, balance FROM accounts")
print("初始余额:", cursor.fetchall())
# 执行转账
transfer_money("Alice", "Bob", 200.0)
# 查询转账后余额
cursor.execute("SELECT name, balance FROM accounts")
print("转账后余额:", cursor.fetchall())
conn.close()
# transaction_demo()
代码示例与实战
让我们通过一个完整的数据库应用项目来实践所学知识。
实战：图书管理系统
import sqlite3
from datetime import datetime
from typing import List, Optional
class Book:
"""图书类"""
def __init__(self, book_id: int = None, title: str = "", author: str = "",
isbn: str = "", publication_year: int = None,
genre: str = "", available: bool = True):
self.book_id = book_id
self.title = title
self.author = author
self.isbn = isbn
self.publication_year = publication_year
self.genre = genre
self.available = available
class Member:
"""会员类"""
def __init__(self, member_id: int = None, name: str = "",
email: str = "", phone: str = ""):
self.member_id = member_id
self.name = name
self.email = email
self.phone = phone
class BorrowRecord:
"""借阅记录类"""
def __init__(self, record_id: int = None, book_id: int = None,
member_id: int = None, borrow_date: datetime = None,
return_date: datetime = None, returned: bool = False):
self.record_id = record_id
self.book_id = book_id
self.member_id = member_id
self.borrow_date = borrow_date or datetime.now()
self.return_date = return_date
self.returned = returned
class LibraryManagementSystem:
"""图书馆管理系统"""
def __init__(self, db_name: str = "library.db"):
self.db_name = db_name
self.init_database()
def init_database(self):
"""初始化数据库"""
with sqlite3.connect(self.db_name) as conn:
cursor = conn.cursor()
# 创建图书表
cursor.execute('''
CREATE TABLE IF NOT EXISTS books (
book_id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL,
author TEXT NOT NULL,
isbn TEXT UNIQUE,
publication_year INTEGER,
genre TEXT,
available BOOLEAN DEFAULT TRUE
)
''')
# 创建会员表
cursor.execute('''
CREATE TABLE IF NOT EXISTS members (
member_id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT UNIQUE,
phone TEXT
)
''')
# 创建借阅记录表
cursor.execute('''
CREATE TABLE IF NOT EXISTS borrow_records (
record_id INTEGER PRIMARY KEY AUTOINCREMENT,
book_id INTEGER,
member_id INTEGER,
borrow_date DATETIME DEFAULT CURRENT_TIMESTAMP,
return_date DATETIME,
returned BOOLEAN DEFAULT FALSE,
FOREIGN KEY (book_id) REFERENCES books (book_id),
FOREIGN KEY (member_id) REFERENCES members (member_id)
)
''')
conn.commit()
# 图书管理方法
def add_book(self, book: Book) -> Optional[int]:
"""添加图书"""
try:
with sqlite3.connect(self.db_name) as conn:
cursor = conn.cursor()
cursor.execute('''
INSERT INTO books (title, author, isbn, publication_year, genre)
VALUES (?, ?, ?, ?, ?)
''', (book.title, book.author, book.isbn, book.publication_year, book.genre))
conn.commit()
return cursor.lastrowid
except sqlite3.IntegrityError as e:
print(f"添加图书失败: {e}")
return None
def get_book(self, book_id: int) -> Optional[Book]:
"""获取图书信息"""
with sqlite3.connect(self.db_name) as conn:
cursor = conn.cursor()
cursor.execute('SELECT * FROM books WHERE book_id = ?', (book_id,))
row = cursor.fetchone()
if row:
return Book(*row)
return None
def search_books(self, keyword: str) -> List[Book]:
"""搜索图书"""
with sqlite3.connect(self.db_name) as conn:
cursor = conn.cursor()
cursor.execute('''
SELECT * FROM books
WHERE title LIKE ? OR author LIKE ? OR isbn LIKE ?
ORDER BY title
''', (f"%{keyword}%", f"%{keyword}%", f"%{keyword}%"))
rows = cursor.fetchall()
return [Book(*row) for row in rows]
def update_book(self, book: Book) -> bool:
"""更新图书信息"""
try:
with sqlite3.connect(self.db_name) as conn:
cursor = conn.cursor()
cursor.execute('''
UPDATE books
SET title = ?, author = ?, isbn = ?, publication_year = ?, genre = ?
WHERE book_id = ?
''', (book.title, book.author, book.isbn, book.publication_year, book.genre, book.book_id))
conn.commit()
return cursor.rowcount > 0
except sqlite3.IntegrityError as e:
print(f"更新图书失败: {e}")
return False
def delete_book(self, book_id: int) -> bool:
"""删除图书"""
with sqlite3.connect(self.db_name) as conn:
cursor = conn.cursor()
cursor.execute('DELETE FROM books WHERE book_id = ?', (book_id,))
conn.commit()
return cursor.rowcount > 0
# 会员管理方法
def add_member(self, member: Member) -> Optional[int]:
"""添加会员"""
try:
with sqlite3.connect(self.db_name) as conn:
cursor = conn.cursor()
cursor.execute('''
INSERT INTO members (name, email, phone)
VALUES (?, ?, ?)
''', (member.name, member.email, member.phone))
conn.commit()
return cursor.lastrowid
except sqlite3.IntegrityError as e:
print(f"添加会员失败: {e}")
return None
def get_member(self, member_id: int) -> Optional[Member]:
"""获取会员信息"""
with sqlite3.connect(self.db_name) as conn:
cursor = conn.cursor()
cursor.execute('SELECT * FROM members WHERE member_id = ?', (member_id,))
row = cursor.fetchone()
if row:
return Member(*row)
return None
def search_members(self, keyword: str) -> List[Member]:
"""搜索会员"""
with sqlite3.connect(self.db_name) as conn:
cursor = conn.cursor()
cursor.execute('''
SELECT * FROM members
WHERE name LIKE ? OR email LIKE ? OR phone LIKE ?
ORDER BY name
''', (f"%{keyword}%", f"%{keyword}%", f"%{keyword}%"))
rows = cursor.fetchall()
return [Member(*row) for row in rows]
# 借阅管理方法
def borrow_book(self, book_id: int, member_id: int) -> Optional[int]:
"""借阅图书"""
# 检查图书是否可借
book = self.get_book(book_id)
if not book or not book.available:
print("图书不可借阅")
return None
# 检查会员是否存在
member = self.get_member(member_id)
if not member:
print("会员不存在")
return None
try:
with sqlite3.connect(self.db_name) as conn:
cursor = conn.cursor()
# 创建借阅记录
cursor.execute('''
INSERT INTO borrow_records (book_id, member_id)
VALUES (?, ?)
''', (book_id, member_id))
# 更新图书状态为不可借
cursor.execute('''
UPDATE books SET available = FALSE WHERE book_id = ?
''', (book_id,))
conn.commit()
return cursor.lastrowid
except Exception as e:
print(f"借阅图书失败: {e}")
return None
def return_book(self, record_id: int) -> bool:
"""归还图书"""
try:
with sqlite3.connect(self.db_name) as conn:
cursor = conn.cursor()
# 获取借阅记录
cursor.execute('''
SELECT book_id FROM borrow_records
WHERE record_id = ? AND returned = FALSE
''', (record_id,))
row = cursor.fetchone()
if not row:
print("无效的借阅记录或图书已归还")
return False
book_id = row[0]
# 更新借阅记录
cursor.execute('''
UPDATE borrow_records
SET return_date = CURRENT_TIMESTAMP, returned = TRUE
WHERE record_id = ?
''', (record_id,))
# 更新图书状态为可借
cursor.execute('''
UPDATE books SET available = TRUE WHERE book_id = ?
''', (book_id,))
conn.commit()
return True
except Exception as e:
print(f"归还图书失败: {e}")
return False
def get_borrowed_books(self, member_id: int = None) -> List[tuple]:
"""获取借阅中的图书"""
with sqlite3.connect(self.db_name) as conn:
cursor = conn.cursor()
if member_id:
cursor.execute('''
SELECT br.*, b.title, b.author, m.name
FROM borrow_records br
JOIN books b ON br.book_id = b.book_id
JOIN members m ON br.member_id = m.member_id
WHERE br.member_id = ? AND br.returned = FALSE
ORDER BY br.borrow_date DESC
''', (member_id,))
else:
cursor.execute('''
SELECT br.*, b.title, b.author, m.name
FROM borrow_records br
JOIN books b ON br.book_id = b.book_id
JOIN members m ON br.member_id = m.member_id
WHERE br.returned = FALSE
ORDER BY br.borrow_date DESC
''')
return cursor.fetchall()
# 统计方法
def get_statistics(self) -> dict:
"""获取图书馆统计信息"""
with sqlite3.connect(self.db_name) as conn:
cursor = conn.cursor()
# 总图书数
cursor.execute('SELECT COUNT(*) FROM books')
total_books = cursor.fetchone()[0]
# 可借图书数
cursor.execute('SELECT COUNT(*) FROM books WHERE available = TRUE')
available_books = cursor.fetchone()[0]
# 总会员数
cursor.execute('SELECT COUNT(*) FROM members')
total_members = cursor.fetchone()[0]
# 当前借阅数
cursor.execute('SELECT COUNT(*) FROM borrow_records WHERE returned = FALSE')
current_borrows = cursor.fetchone()[0]
return {
"total_books": total_books,
"available_books": available_books,
"total_members": total_members,
"current_borrows": current_borrows
}
# 使用示例
def library_system_demo():
"""图书馆系统演示"""
# 创建系统实例
library = LibraryManagementSystem()
print("=== 图书馆管理系统演示 ===\n")
# 添加图书
print("1. 添加图书")
book1 = Book(title="Python编程入门", author="张三", isbn="978-1234567890",
publication_year=2023, genre="编程")
book2 = Book(title="数据结构与算法", author="李四", isbn="978-0987654321",
publication_year=2022, genre="计算机科学")
book1_id = library.add_book(book1)
book2_id = library.add_book(book2)
print(f"添加图书成功，ID: {book1_id}, {book2_id}\n")
# 添加会员
print("2. 添加会员")
member1 = Member(name="王五", email="wangwu@example.com", phone="13800138000")
member2 = Member(name="赵六", email="zhaoliu@example.com", phone="13900139000")
member1_id = library.add_member(member1)
member2_id = library.add_member(member2)
print(f"添加会员成功，ID: {member1_id}, {member2_id}\n")
# 搜索图书
print("3. 搜索图书")
books = library.search_books("Python")
for book in books:
print(f" {book.title} - {book.author}")
print()
# 借阅图书
print("4. 借阅图书")
record_id = library.borrow_book(book1_id, member1_id)
if record_id:
print(f"借阅成功，记录ID: {record_id}")
print()
# 查看借阅记录
print("5. 查看借阅中的图书")
borrowed_books = library.get_borrowed_books()
for record in borrowed_books:
print(f" {record[7]} 借阅了《{record[5]}》 by {record[6]}")
print()
# 归还图书
print("6. 归还图书")
if library.return_book(record_id):
print("归还成功")
print()
# 查看统计信息
print("7. 图书馆统计信息")
stats = library.get_statistics()
for key, value in stats.items():
print(f" {key}: {value}")
# 运行演示
# library_system_demo()
小结与回顾
在本章中，我们深入学习了Python与数据库交互的各种方法和技巧。主要内容包括：
-
数据库交互基础：了解了数据库交互的基本概念、重要性和常见数据库类型。
-
SQLite操作：掌握了使用Python内置的sqlite3模块进行数据库操作的方法，包括连接、CRUD操作和事务管理。
-
MySQL和PostgreSQL操作：学会了连接和操作流行的数据库系统，理解了不同数据库系统的特性和使用方法。
-
ORM概念：理解了ORM（对象关系映射）的概念和优势，掌握了SQLAlchemy的基本使用方法。
-
高级特性：学习了数据库连接池、事务管理等高级特性，提高了数据库操作的性能和可靠性。
-
实战项目：通过完整的图书管理系统项目，实践了数据库设计、CRUD操作和业务逻辑实现。
数据库交互是现代应用程序开发的核心技能之一。掌握这些技能不仅能够帮助您构建数据驱动的应用程序，还能让您更好地理解和优化数据存储与检索过程。随着实践经验的积累，您可以进一步学习更高级的数据库技术，如数据库优化、分库分表、NoSQL数据库等。
练习与挑战
-
基础练习
- 使用sqlite3模块创建一个学生管理系统，包含学生信息的增删改查功能
- 连接本地MySQL数据库，创建一个博客系统的基本表结构并实现文章的发布和查询
- 使用SQLAlchemy定义一个电子商务系统的基本模型，包括商品、订单、用户等实体
-
进阶挑战
- 实现一个数据库连接池管理器，支持多种数据库类型
- 创建一个支持事务的银行转账系统，确保资金转移的原子性和一致性
- 开发一个数据库备份和恢复工具，支持定时备份和增量备份
-
综合项目
- 构建一个完整的在线商城系统，包含商品管理、用户管理、订单处理等功能
- 开发一个内容管理系统（CMS），支持文章发布、分类管理、用户权限控制等
- 实现一个社交网络应用的后端，包含用户关系、动态发布、消息通知等功能
扩展阅读
-
官方文档：
-
进阶库：
- Peewee: 轻量级ORM库
- Django ORM: Django框架内置的ORM
- Tortoise ORM: 异步ORM库
- Databases: 异步数据库连接库
-
专业书籍：
- 《Python数据库编程》- Andrew womack等著
- 《SQL必知必会》- Ben Forta著
- 《高性能MySQL》- Baron Schwartz等著
-
在线资源：
- Real Python: 数据库编程教程
- SQLAlchemy官方教程
- Database Design Tutorial
-
相关技术：
- 学习数据库设计原则和范式
- 了解数据库索引和查询优化
- 掌握数据库安全和权限管理
- 学习NoSQL数据库（MongoDB、Redis等）的使用
