Python ORM对象关系映射教程 - 优缺点对比与框架推荐

# 原生SQL方式（使用PyMySQL）
cursor.execute('SELECT id, name, email, age FROM users')
users = cursor.fetchall()
for user in users:
    print(f"{user['name']}: {user['email']}")

# ORM方式（使用SQLAlchemy）
users = session.query(User).all()
for user in users:
    print(f"{user.name}: {user.email}")

# 原生SQL方式
cursor.execute(
    'SELECT * FROM users WHERE age > %s AND status = %s ORDER BY name',
    (18, 'active')
)
users = cursor.fetchall()

# ORM方式
users = session.query(User)\
    .filter(User.age > 18, User.status == 'active')\
    .order_by(User.name)\
    .all()

# 原生SQL方式
cursor.execute(
    'INSERT INTO users (name, email, age) VALUES (%s, %s, %s)',
    ('张三', 'zhangsan@example.com', 25)
)
conn.commit()
new_id = cursor.lastrowid

# ORM方式
new_user = User(name='张三', email='zhangsan@example.com', age=25)
session.add(new_user)
session.commit()
new_id = new_user.id

# 原生SQL方式
cursor.execute('''
    SELECT u.name, o.product_name, o.amount
    FROM users u
    JOIN orders o ON u.id = o.user_id
    WHERE u.id = %s
''', (user_id,))
results = cursor.fetchall()

# ORM方式（假设已定义User和Order模型的关系）
user = session.query(User).get(user_id)
for order in user.orders:
    print(f"{user.name} 购买了 {order.product_name}，金额：{order.amount}")

# SQLAlchemy模型定义示例
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import declarative_base

Base = declarative_base()

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)
    
    def __repr__(self):
        return f"<User(id={self.id}, name='{self.name}')>"

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

# 创建引擎
engine = create_engine('sqlite:///example.db')

# 创建会话工厂
Session = sessionmaker(bind=engine)

# 创建会话
session = Session()

# 使用会话操作数据库
new_user = User(name='张三', email='zhangsan@example.com', age=25)
session.add(new_user)
session.commit()
session.close()

from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship

class User(Base):
    __tablename__ = 'users'
    
    id = Column(Integer, primary_key=True)
    name = Column(String(100), nullable=False)
    
    # 一对多关系：一个用户可以有多个订单
    orders = relationship('Order', back_populates='user')

class Order(Base):
    __tablename__ = 'orders'
    
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id'))
    product_name = Column(String(200))
    
    # 反向关系
    user = relationship('User', back_populates='orders')

# 基本查询
users = session.query(User).all()

# 条件查询
adults = session.query(User).filter(User.age >= 18).all()

# 排序和限制
top_users = session.query(User).order_by(User.name).limit(10).all()

# 聚合查询
from sqlalchemy import func
count = session.query(func.count(User.id)).scalar()
avg_age = session.query(func.avg(User.age)).scalar()

# 关联查询（预加载避免N+1问题）
from sqlalchemy.orm import joinedload
users_with_orders = session.query(User)\
    .options(joinedload(User.orders))\
    .all()