魔客室

27-数据库操作

7分钟阅读

💾 27. 数据库操作

本章概述

学习 Python 数据库操作：SQLite、MySQL、ORM 等。 预计学习时间：60 分钟

27.1 SQLite

SQLite 是 Python 内置的轻量级数据库，不需要安装，单文件存储。

基本操作

import sqlite3
 
# 连接数据库（文件不存在会自动创建）
conn = sqlite3.connect("test.db")
 
# 创建游标
cursor = conn.cursor()
 
# 创建表
cursor.execute("""
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        age INTEGER,
        email TEXT
    )
""")
 
# 插入数据
cursor.execute("INSERT INTO users (name, age, email) VALUES (?, ?, ?)",
               ("张三", 18, "zhangsan@example.com"))
 
# 提交事务
conn.commit()
 
# 查询数据
cursor.execute("SELECT * FROM users")
results = cursor.fetchall()
for row in results:
    print(row)
 
# 关闭连接
cursor.close()
conn.close()

增删改查

import sqlite3
 
conn = sqlite3.connect("test.db")
cursor = conn.cursor()
 
# 增加（Create）
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("李四", 20))
conn.commit()
print("新增ID:", cursor.lastrowid)
 
# 查询（Read）
# 查询所有
cursor.execute("SELECT * FROM users")
print(cursor.fetchall())
 
# 查询一条
cursor.execute("SELECT * FROM users WHERE id = ?", (1,))
print(cursor.fetchone())
 
# 条件查询
cursor.execute("SELECT * FROM users WHERE age > ? ORDER BY age DESC", (18,))
print(cursor.fetchall())
 
# 更新（Update）
cursor.execute("UPDATE users SET age = ? WHERE name = ?", (25, "张三"))
conn.commit()
print("影响行数:", cursor.rowcount)
 
# 删除（Delete）
cursor.execute("DELETE FROM users WHERE id = ?", (2,))
conn.commit()
 
cursor.close()
conn.close()

参数化查询

用 ? 占位符，不要用字符串拼接，防止 SQL 注入。

# ❌ 危险：SQL注入
cursor.execute(f"SELECT * FROM users WHERE name = '{name}'")
 
# ✅ 安全：参数化
cursor.execute("SELECT * FROM users WHERE name = ?", (name,))

批量操作

import sqlite3
 
conn = sqlite3.connect("test.db")
cursor = conn.cursor()
 
# 批量插入
users = [
    ("王五", 22, "wangwu@example.com"),
    ("赵六", 24, "zhaoliu@example.com"),
    ("钱七", 26, "qianqi@example.com")
]
cursor.executemany("INSERT INTO users (name, age, email) VALUES (?, ?, ?)", users)
conn.commit()
 
cursor.close()
conn.close()

行工厂（按列名访问）

import sqlite3
 
conn = sqlite3.connect("test.db")
conn.row_factory = sqlite3.Row  # 设置行工厂
 
cursor = conn.cursor()
cursor.execute("SELECT * FROM users")
 
for row in cursor:
    print(row["name"], row["age"])  # 可以按列名访问
 
cursor.close()
conn.close()

上下文管理器

import sqlite3
 
# with 语句自动提交和关闭
with sqlite3.connect("test.db") as conn:
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users")
    print(cursor.fetchall())
# 离开with块自动提交和关闭

27.2 MySQL

安装驱动

pip install pymysql
# 或者
pip install mysql-connector-python

基本操作（pymysql）

import pymysql
 
# 连接数据库
conn = pymysql.connect(
    host="localhost",
    port=3306,
    user="root",
    password="password",
    database="test",
    charset="utf8mb4"
)
 
# 创建游标
cursor = conn.cursor()
 
# 创建表
cursor.execute("""
    CREATE TABLE IF NOT EXISTS users (
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(50) NOT NULL,
        age INT,
        email VARCHAR(100)
    )
""")
 
# 插入数据
cursor.execute("INSERT INTO users (name, age) VALUES (%s, %s)", ("张三", 18))
conn.commit()
 
# 查询
cursor.execute("SELECT * FROM users")
results = cursor.fetchall()
for row in results:
    print(row)
 
# 关闭
cursor.close()
conn.close()

注意

MySQL 的占位符是 %s，不是 ?。

27.3 SQLAlchemy ORM

ORM（对象关系映射），用面向对象的方式操作数据库。

安装

pip install sqlalchemy

基本用法

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
 
# 创建引擎
engine = create_engine("sqlite:///test.db", echo=True)  # echo=True 打印SQL
 
# 基类
Base = declarative_base()
 
# 定义模型
class User(Base):
    __tablename__ = "users"
 
    id = Column(Integer, primary_key=True)
    name = Column(String(50), nullable=False)
    age = Column(Integer)
    email = Column(String(100))
 
    def __repr__(self):
        return f"<User(id={self.id}, name='{self.name}', age={self.age})>"
 
# 创建表
Base.metadata.create_all(engine)
 
# 创建会话
Session = sessionmaker(bind=engine)
session = Session()
 
# 增加
user = User(name="张三", age=18, email="zhangsan@example.com")
session.add(user)
session.commit()
print(user.id)  # 提交后可以获取id
 
# 查询
# 查询所有
users = session.query(User).all()
print(users)
 
# 按主键查询
user = session.query(User).get(1)
print(user)
 
# 条件查询
users = session.query(User).filter(User.age > 18).all()
print(users)
 
# 排序
users = session.query(User).order_by(User.age.desc()).all()
print(users)
 
# 更新
user = session.query(User).get(1)
user.age = 25
session.commit()
 
# 删除
user = session.query(User).get(1)
session.delete(user)
session.commit()
 
session.close()

常用查询

# 过滤
session.query(User).filter(User.name == "张三").all()
session.query(User).filter(User.age > 18).all()
session.query(User).filter(User.name.like("%张%")).all()
session.query(User).filter(User.id.in_([1, 2, 3])).all()
 
# 多个条件
from sqlalchemy import and_, or_
session.query(User).filter(and_(User.age > 18, User.name.like("%张%"))).all()
session.query(User).filter(or_(User.age > 25, User.name == "张三")).all()
 
# 排序
session.query(User).order_by(User.age).all()           # 升序
session.query(User).order_by(User.age.desc()).all()    # 降序
 
# 分页
session.query(User).offset(10).limit(10).all()  # 第2页，每页10条
 
# 统计
session.query(User).count()
session.query(User).filter(User.age > 18).count()
 
# 聚合
from sqlalchemy import func
session.query(func.avg(User.age)).scalar()  # 平均年龄
session.query(func.max(User.age)).scalar()  # 最大年龄
session.query(func.sum(User.age)).scalar()  # 年龄总和

27.4 数据库设计基础

表设计原则

  1. 每个表只存一类信息（比如用户表只存用户信息）
  2. 每个字段只存一个值（不要在一个字段里存多个值）
  3. 主键唯一标识每条记录
  4. 避免冗余数据（不要重复存储相同的信息）

常见数据类型

类型说明示例
INTEGER / INT整数年龄、数量
VARCHAR(n)变长字符串姓名、邮箱
TEXT长文本文章内容
REAL / FLOAT浮点数价格、分数
DATE / DATETIME日期时间创建时间
BOOLEAN布尔值是否启用

主键和外键

  • 主键（Primary Key）：唯一标识一条记录，不能重复，不能为空
  • 外键（Foreign Key）：引用其他表的主键，建立表之间的关系

🔗 相关章节

📝 我的笔记

在这里记录你的理解和练习代码

# 你的练习代码

✅ 本章检查清单

  • 掌握 SQLite 的基本操作
  • 会创建表、增删改查
  • 了解参数化查询，防止 SQL 注入
  • 了解 MySQL 的基本用法
  • 了解 ORM 的概念
  • 会使用 SQLAlchemy 基本操作
  • 了解数据库设计的基本原则

关系图谱

反向链接