Python 与数据库交互的 15 个 SQL 查询技巧
当你刚开始学习 Python 与数据库交互时，可能会觉得有点不知所所措。但别担心，通过本文，你将学会如何使用 Python 进行基本的 SQL 查询，并掌握一些高级技巧。让我们一步步来，从简单的查询到更复杂的操作。
首先，你需要连接到数据库。这里以 SQLite 为例，因为它是轻量级且易于上手的。
复制
import sqlite3
# 连接到 SQLite 数据库（如果不存在则会创建）
conn = sqlite3.connect(example.db)
# 创建一个游标对象
cursor = conn.cursor()1.2.3.4.5.6.7.
在开始查询之前，我们需要一个表。假设我们要创建一个存储用户信息的表。
复制
# 创建表
cursor.execute(
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
age INTEGER NOT NULL,
email TEXT UNIQUE NOT NULL
)
)1.2.3.4.5.6.7.8.9.
接下来，我们可以向表中插入一些数据。
复制
# 插入数据
cursor.execute(
INSERT INTO users (name, age, email) VALUES (?, ?, ?)
, (Alice, 30, alice@example.com))
cursor.execute(
INSERT INTO users (name, age, email) VALUES (?, ?, ?)
, (Bob, 25, bob@example.com))
# 提交事务
conn.commit()1.2.3.4.5.6.7.8.9.10.11.
现在，让我们查询表中的所有记录。
复制
# 查询所有记录
cursor.execute(SELECT * FROM users)
rows = cursor.fetchall()
for row in rows:
print(row)1.2.3.4.5.6.
你可以通过条件来查询特定的记录。
复制
# 查询特定记录
cursor.execute(SELECT * FROM users WHERE age > 25)
rows = cursor.fetchall()
for row in rows:
print(row)1.2.3.4.5.6.
为了避免 SQL 注入攻击，建议使用参数化查询。
复制
# 参数化查询
age_threshold = 25
cursor.execute(SELECT * FROM users WHERE age > ?, (age_threshold,))
rows = cursor.fetchall()
for row in rows:
print(row)1.2.3.4.5.6.7.
你可以更新表中的记录。
复制
# 更新记录
cursor.execute(UPDATE users SET age = ? WHERE name = ?, (31, Alice))
conn.commit()1.2.3.
你也可以删除表中的记录。
复制
# 删除记录
cursor.execute(DELETE FROM users WHERE name = ?, (Bob,))
conn.commit()1.2.3.
如果你有多个表，可以使用 JOIN 来查询相关数据。
复制
# 假设有一个 orders 表
cursor.execute(
CREATE TABLE IF NOT EXISTS orders (
id INTEGER PRIMARY KEY,
user_id INTEGER,
product TEXT,
FOREIGN KEY (user_id) REFERENCES users (id)
)
)
# 插入订单数据
cursor.execute(INSERT INTO orders (user_id, product) VALUES (?, ?), (1, Laptop))
conn.commit()
# 使用 JOIN 查询
cursor.execute(
SELECT users.name, orders.product
FROM users
JOIN orders ON users.id = orders.user_id
)
rows = cursor.fetchall()
for row in rows:
print(row)1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.
聚合函数可以帮助你处理和汇总数据。
复制
# 使用聚合函数
cursor.execute(SELECT COUNT(*) FROM users)
count = cursor.fetchone()[0]
print(fTotal number of users: {count})1.2.3.4.
你可以使用 GROUP BY 和 ORDER BY 来分组和排序数据。
复制
# 分组和排序
cursor.execute(
SELECT age, COUNT(*)
FROM users
GROUP BY age
ORDER BY age DESC
)
rows = cursor.fetchall()
for row in rows:
print(row)1.2.3.4.5.6.7.8.9.10.11.
子查询可以在查询中嵌套另一个查询。
复制
# 使用子查询
cursor.execute(
SELECT *
FROM users
WHERE id IN (SELECT user_id FROM orders)
)
rows = cursor.fetchall()
for row in rows:
print(row)1.2.3.4.5.6.7.8.9.10.
事务管理可以帮助你确保数据的一致性和完整性。
复制
# 事务管理
try:
cursor.execute(INSERT INTO users (name, age, email) VALUES (?, ?, ?), (Charlie, 28, charlie@example.com))
cursor.execute(INSERT INTO orders (user_id, product) VALUES (?, ?), (3, Phone))
conn.commit()
except Exception as e:
conn.rollback()
print(fError: {e})1.2.3.4.5.6.7.8.
上下文管理器可以自动管理资源，如关闭数据库连接。
复制
# 使用上下文管理器
with sqlite3.connect(example.db) as conn:
cursor = conn.cursor()
cursor.execute(SELECT * FROM users)
rows = cursor.fetchall()
for row in rows:
print(row)1.2.3.4.5.6.7.
对于更复杂的项目，可以考虑使用 ORM 框架，如 SQLAlchemy。
复制
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
Base = declarative_base()
class User(Base):
__tablename__ = users
id = Column(Integer, primary_key=True)
name = Column(String, nullable=False)
age = Column(Integer, nullable=False)
email = Column(String, unique=True, nullable=False)
class Order(Base):
__tablename__ = orders
id = Column(Integer, primary_key=True)
user_id = Column(Integer, ForeignKey(users.id))
product = Column(String, nullable=False)
user = relationship("User")
# 创建数据库引擎
engine = create_engine(sqlite:///example.db)
# 创建表
Base.metadata.create_all(engine)
# 创建会话
Session = sessionmaker(bind=engine)
session = Session()
# 插入数据
new_user = User(name=David, age=27, email=david@example.com)
session.add(new_user)
session.commit()
# 查询数据
users = session.query(User).all()
for user in users:
print(user.name, user.age, user.email)1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.
假设你要开发一个简单的用户管理系统，需要实现以下功能：
添加用户：允许管理员添加新用户。查询用户：允许管理员按条件查询用户。更新用户信息：允许管理员更新用户的年龄和邮箱。删除用户：允许管理员删除用户。复制
def add_user(name, age, email):
with sqlite3.connect(example.db) as conn:
cursor = conn.cursor()
cursor.execute(INSERT INTO users (name, age, email) VALUES (?, ?, ?), (name, age, email))
conn.commit()
def query_users(age_threshold):
with sqlite3.connect(example.db) as conn:
cursor = conn.cursor()
cursor.execute(SELECT * FROM users WHERE age > ?, (age_threshold,))
rows = cursor.fetchall()
return rows
def update_user(user_id, new_age, new_email):
with sqlite3.connect(example.db) as conn:
cursor = conn.cursor()
cursor.execute(UPDATE users SET age = ?, email = ? WHERE id = ?, (new_age, new_email, user_id))
conn.commit()
def delete_user(user_id):
with sqlite3.connect(example.db) as conn:
cursor = conn.cursor()
cursor.execute(DELETE FROM users WHERE id = ?, (user_id,))
conn.commit()
# 示例操作
add_user(Eve, 32, eve@example.com)
print(query_users(30))
update_user(1, 33, alice_new@example.com)
delete_user(2)1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.
通过本文，你学会了如何使用 Python 进行基本的 SQL 查询，包括连接数据库、创建表、插入数据、查询记录、更新和删除记录等。此外，你还了解了如何使用参数化查询、JOIN 查询、聚合函数、分组和排序、子查询、事务管理和 ORM 框架。最后，我们通过一个实战案例展示了如何将这些知识应用于实际项目中。希望这些内容对你有所帮助！
THE END
