sqlalchemy 的操作

使用PyMySQL的前提：

　　1. 先建好表

　　2. 自己动手需要手动去写多条SQL语句

改进：

　　类 ------>  表

　　实例化 -> 数据

这种思想叫：ORM（Object Relationship Mapping）对象关系映射

SQLAlchemy是Python编程语言下的一款ORM框架

SQLAlchemy的操作:

　　基本原理：将代码转换成SQL语句执行

1. 创建表

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer, String,ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import  sessionmaker, relationship
from sqlalchemy import create_engine

# 链接MySQL
engine = create_engine("mysql+pymysql://root@127.0.0.1:3306/day45?charset=utf8", max_overflow = 5) # max_overflow = 5代表最大连接池为5

# 声明Base类，后续的类都要继承Base类
Base = declarative_base()

# 创建单表
"""
create table user(
    id int auto_increment primary key,
    name varchar(32) not null default '',
    extra varchar(32) not null default '' 
)engine=Innodb charset=utf8
"""

# 创建usertype， 并设置id，title
class UserType(Base):
    __tablename__ = "usertype"   # 表名
    id = Column(Integer, autoincrement=True, primary_key=True)
    title = Column(String(32), nullable=False,server_default="")


# 创建user表，并设置表的id, name, extra
class Users(Base):
    __tablename__ = 'user'   # 表名
    id = Column(Integer, autoincrement=True, primary_key=True)   # Column:字段，列
    name = Column(String(32), nullable=False, server_default="")
    # name = Column(String(32), nullable=False, server_default=""， unique=True) # 给name这一列添加唯一索引
    extra = Column(String(32), nullable=False, server_default="")   # extra:特点

    # 外键(让User中的id与UserType中的id发生了外键的关系)
    type_id = Column(Integer, ForeignKey(UserType.id))
    # type_id = Column(Integer, ForeignKey("usertype".id))

    # # 添加索引
    # __table_args__ = (
    #     UniqueConstraint("id", "name", name="uix_id_name"),  # 给id,name添加联合唯一索引
    #     Index("ix_id_name","name", "extra")   # 给name，extra添加普通索引
    # )


# 删除表
def drop_db():
    Base.metadata.drop_all(engine)

# 会将当前文件中所有继承自Base类的类，生成表
def create_db():
    Base.metadata.create_all(engine)

2. 操作表

# 操作表中的数据
Session = sessionmaker(bind=engine)
session = Session()   # session：窗口， 实例化Session，相当于从连接池中拿一个连接过来进行操作

 2.1 添加数据

# 往UserType中添加数据， 因为UserType与User存在外键联系，所以给UserType添加数据，则再往User中添加数据时，type_id就会有数据产生

# 添加一条数据
obj = UserType(title = "普通用户")
session.add(obj)   # 把对象数据添加到数据库中

# 添加多条数据
session.add_all([
    UserType(title = "VIP用户"),
    UserType(title = "VIP中P用户"),
    UserType(title = "SVIP用户"),
    UserType(title = "黑金用户")
])

session.commit()
session.close()

2.2 查询数据

2.2.1 普通查询（.all(), .first()）

res = session.query(UserType)   # 这一步就是将代码转换成SQL语句
print(res)   # SELECT usertype.id AS usertype_id, usertype.title AS usertype_title  FROM usertype

# 查询全部,返回的是列表，列表中是对象
res = session.query(UserType).all()   # .all()就是讲SQL语句发送给服务端执行SQL指令，得到一个列表对象
print(res) # [<__main__.UserType object at 0x00000164C846BEF0>, <__main__.UserType object at 0x00000164C846BF60>, <__main__.UserType object at 0x00000164C846BDA0>, <__main__.UserType object at 0x00000164C846BDD8>, <__main__.UserType object at 0x00000164C846BC88>]
for k in res:
    print(k.id, k.title)    # 1 普通用户
                            # 2 VIP用户
                            # 3 VIP中P用户
                            # 4 SVIP用户
                            # 5 黑金用户

# 查询一条，获得一条对象
res = session.query(UserType).first()
print(res)      # <__main__.UserType object at 0x000001640E9EBDD8>
print(res.id, res.title)   # 1 普通用户

2.2.2 类似sql中的where查询（filter(), filter_by()）

# filter
res = session.query(UserType).filter(UserType.title=="VIP用户")  # filter 将查找（过滤）的条件转换成SQL语句
print(res)    # SELECT usertype.id AS usertype_id, usertype.title AS usertype_title FROM usertype  WHERE usertype.title = %(title_1)s

res = session.query(UserType).filter(UserType.title=="VIP用户", UserType.id==2).all()  # .first()与上一样
print(res)   # [<__main__.UserType object at 0x000002C1ADFD73C8>]
for row in res:
    print(row.id, row.title)    # 2 VIP用户
print(res[0].id, res[0].title)  # 2 VIP用户


# filter_by 传入的是一个类似key=value的数据， filter中传入的是一个表达式
res = session.query(UserType).filter_by(title="VIP用户").all()
print(res)  # [<__main__.UserType object at 0x000001FCDB07FDA0>]

2.3 删除数据（delete）

# 删除数据之前先查找数据
session.query(UserType).filter(UserType.id>3).delete()
session.query(UserType).delete()   # 相当于删除整个表

2.4 修改数据（update）

session.query(UserType).filter(UserType.id == 3).update({"title":"SVIP用户"})  # 将id=3数据的title的值改为SVIP用户

2.5 高级查询

"""
高级查询: 通配符、分组、排序、between and、in、not in、or
 """
"""
此时数据恢复成如下所示
+----+------------+
| id | title      |
+----+------------+
|  1 | 普通用户   |
|  2 | VIP用户    |
|  3 | VIP中P用户 |
|  4 | SVIP用户   |
|  5 | 黑金用户   |
+----+------------+
"""

# 逗号默认为 and
res = session.query(UserType).filter(UserType.id==2, UserType.title=="VIP用户").all()
for row in res:
    print(row.id, row.title)  # row

# between(1,3) 在1到3的范围内，包括1和3
res = session.query(UserType).filter(UserType.id.between(1,3 )).all()
for row in res:
    print(row.id, row.title)    # 1 普通用户
                                # 2 VIP用户
                                # 3 VIP中P用户

# in not in
res = session.query(UserType).filter(UserType.id.in_([1,3,4])).all()
ret = session.query(UserType).filter(~UserType.id.in_([1,3,4])).all()
print(res)   # [<__main__.UserType object at 0x000002839AD46048>, <__main__.UserType object at 0x000002839AD46198>, <__main__.UserType object at 0x000002839AD46278>]
print(ret)   # [<__main__.UserType object at 0x000002839AD467B8>, <__main__.UserType object at 0x000002839AD46828>]

rer = session.query(UserType).filter(UserType.id.in_(session.query(UserType.id).filter_by(title='VIP用户'))).all()
print(rer)     # [<__main__.UserType object at 0x0000023CBD0D57B8>]

from sqlalchemy import and_,or_
ret = session.query(UserType).filter(and_(UserType.id > 3, UserType.title == 'VIP用户')).all()
res = session.query(UserType).filter(or_(UserType.id < 2, UserType.title == 'VIP用户')).all()
print(ret)
print(res)

# 通配符
ret = session.query(UserType).filter(UserType.title.like('S%')).all()
res = session.query(UserType).filter(~UserType.title.like('S%')).all()
print(ret)
print(res)

# 排序
ret = session.query(UserType).order_by(UserType.title.desc()).all()
res = session.query(UserType).order_by(UserType.title.desc(), UserType.id.asc()).all()

# 分组 group_by
"""
+----+----------+-------+---------+
| id | name     | extra | type_id |
+----+----------+-------+---------+
|  1 | wangyong | nb    |       5 |
|  2 | liguo    | cb    |       3 |
|  3 | jiyuzhi  | sb    |       1 |
|  4 | kelinwei | zb    |       3 |
|  5 | gouyang  | bb    |       2 |
+----+----------+-------+---------+
"""
from sqlalchemy.sql import func
res = session.query(
    Users.type_id,
    func.max(Users.id),
    func.min(Users.id)).group_by(Users.type_id).all()
print(res)    # [(1, 3, 3), (2, 5, 5), (3, 4, 2), (5, 1, 1)]

res = session.query(
    Users.type_id,
    func.max(Users.id),
    func.min(Users.id)).group_by(Users.type_id).having(func.min(Users.id)>2).all()
print(res)      # [(1, 3, 3), (2, 5, 5)]

"""
连表
"""
res = session.query(Users).join(UserType)
print(res)
# SELECT user.id AS user_id, user.name AS user_name, user.extra AS user_extra, user.type_id AS user_type_id
# FROM user INNER JOIN usertype ON usertype.id = user.type_id

res = session.query(Users).join(UserType,isouter=True) # 会自动检测是否含有外键，如果存在，会自动进行关联
print(res)
# SELECT user.id AS user_id, user.name AS user_name, user.extra AS user_extra, user.type_id AS user_type_id
# FROM user LEFT OUTER JOIN usertype ON usertype.id = user.type_id

res = session.query(Users).join(UserType,isouter=True).all() # 存在问题：只能查询到Users表的值，UserType表中的值无法查询
print(res)  # [<__main__.Users object at 0x0000026AD9D74898>, <__main__.Users object at 0x0000026AD9D74908>, <__main__.Users object at 0x0000026AD9D74978>, <__main__.Users object at 0x0000026AD9D749E8>, <__main__.Users object at 0x0000026AD9D74A58>]
for row in res:
    print(row.id, row.name)

# 1. 想要既能查询到Users表中数据，又能查询到UserType中的数据
# 方法一：
res = session.query(Users, UserType).join(UserType,isouter=True).all() # 存在问题：只能查询到Users表的值，UserType表中的值无法查询
for row in res:
    print(row[0].id, row[0].name, row[1].title)

# 方法二：使用relationship 在Users类中加入
usertype = relationship('UserType') # 关联到UserType，在创建User表时，会将UserType的数据添加到Users中，但是不会显示出来，就相当于一个隐藏属性

res = session.query(Users).all()
for row in res:
    print(row.id, row.name, row.extra, row.usertype.title)

# 2.想要知道某一个类型下面的用户
# 第一种
res = session.query(UserType).all()
for row in res:
    print(row.id, row.title, session.query(Users.id).filter(Users.type_id==row.id).all())

# 第二种 在定义Users类时，继续添加   usertype = relationship('UserType', backref = "xxoo")   backref:反向查询

res = session.query(UserType).all()
for row in res:
    print(row.id, row.title, row.xxoo)   # row.xxoo 多条记录查询


# relationship 哪张表中有外键，就把relationship 放在哪张表中