ORM框架——SQLAlchemy

参考资料：https://www.bilibili.com/video/BV1DE411n7fU?p=68

类和对象的简介

1. 提取共性
2. 分类
3. 模板“约束”
4. 当一类函数公用相同的参数时，可以考虑转化成类进行（分类）
5. 面向对象：数据和逻辑（属性和行为）组合在一起；函数编程：数据和逻辑分离。
6. 可以把一张数据表看成一个类，数据表中的每一行数据就是该类的一个object.

ORM框架：SQLAlchemy

• 作用
  • 提供简单的规则
  • 自动转换成SQL语句
  • DB first: 手动创建数据库以及表 -> ORM框架 -> 自动生成类
  • code first: 手动创建类和数据库 -> ORM框架 -> 数据表
• 功能
  • 创建数据库表
    • 连接数据库（这里用pymysql, mysqldb,…）
    • 类转换成SQL语句

实战

自己开发web框架

• socket
• http协议
• HTML知识
• 数据库（pymysql, SQLAlchemy）

例子

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index, CHAR, VARCHAR
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine
from sqlalchemy.sql.expression import select

Base = declarative_base()

#创建单表
class UserType(Base):
    __tablename__ = "usertype" #表名
    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(32), nullable=True, index=True)

class Users(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True, autoincrement=True)
    name  = Column(String(32), nullable=True, default='st', index=True)
    email = Column(String(16), unique=True)
    user_type_id = Column(Integer, ForeignKey("usertype.id"))

    user_type = relationship("UserType", backref="xxoo")

    __table_args__ = (
        UniqueConstraint('id', 'name', name='uix_id_name'),
        Index('ix_n_ex', 'name', 'extra')
    )

engine = create_engine("mysql+pymysql://root:@1.1.1.1:2222/db1?charset=utf8", max_overflow = 5)
#Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()

#类 -> 表
#对象 -> 行

# 增加
# obj1 = UserType(title="普通用户")
# session.add(obj1)

objs = [UserType(title = 'xx1'), UserType(title = "xx2"), UserType(title = "xx 3")]

session.add_all(objs)

session.commit()
session.close()

# 查
print(session.query(UserType))
user_type_list = session.query(UserType).all()
for row in user_type_list:
    print(row.id, row.title)

#select xxx from UserType where ...
user_type_list = session.query(UserType.id, UserType.title).filter(UserType.id > 2)
for row in user_type_list:
    print(row.id, row.title)


# 删除
session.query(UserType.id, UserType.title).filter(UserType.id > 2).delete()


# 修改，更新
session.query(UserType.id, UserType.title).filter(UserType.id > 0).update({"title": "ff"})
session.query(UserType.id, UserType.title).filter(UserType.id > 0).update({UserType.title: UserType.title + 'x'}, synchronize_session=False)
session.query(UserType.id, UserType.title).filter(UserType.id > 0).update({"num": Users.num + 1}, synchronize_session="evaluate")

session.commit()
session.close()

# 连表
ret = session.query(Users, Favor).filter(Users.id == Favor.f_id).all()
ret = session.query(Users).join(Favor).all()
ret = session.query(Users).join(Favor, isouter = True).all()

# 组合
q1 = session.query(Users.dd).filter(Users.id > 0)
q2 = session.query(Favor.ss).filter(Favor.f_id > 0)
ret = q1.union(q2).all()


q1 = session.query(Users.dd).filter(Users.id > 0)
q2 = session.query(Favor.ss).filter(Favor.f_id > 0)
ret = q1.union_all(q2).all()

# 子查询
# select * from (select * from tb) as B

q1 = session.query(UserType).filter(UserType.id > 0).subquery()
ret = session.query(q1).all()
print(ret)


# select id, (select * from users where users.user_type_id = 1) from usertype;
result = session.query(UserType.id, session.query(Users).filter(Users.user_type_id == 1).as_scalar()).all()
for row in result:
    print(row)


# 问题一：获取用户信息及其用户类型名称（FK, relationship）->正向操作
user_list = session.query(Users, UserType).join(UserType, isouter=True)
print(user_list)
for row in user_list:
    print(row[0].id, row[0].name, row[0].email, row[1].title)

user_list = session.query(Users.name, UserType.title).join(UserType, isouter=True).all()
print(user_list)
for row in user_list:
    print(row[0], row[1], row.name, row.title)


user_list = session.query(Users)
for row in user_list:
    print(row.id, row.name, row.user_type.title)

# 问题二：获取用户类型

type_list = session.query(UserType)
for row in type_list:
    print(row.id, row.title, session.query(Users).filter(Users.user_type_id==UserType.id).all())

type_list = session.query(UserType)
for row in type_list:
    print(row.id, row.title, row.xxoo)  # backref: xxoo