day44

ORM(SQLAlchemy)

'''
一种思想:ORM(Object Relationship Mapping) 对象关系映射
基于这种思想开发的产品,python比较出名的ORM的框架:SQLAlchemy
DB frist:先有数据库,再有后续的操作
Code frist:先有代码,通过代码创建DB
'''
from sqlalchemy import create_engine, UniqueConstraint, Index, Column, Integer, String, ForeignKey, func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql.expression import text
from sqlalchemy.orm import sessionmaker, relationship
​
# 连接数据库 max_overflow 表示最大连接数
engine = create_engine("mysql+pymysql://root:456@127.0.0.1:3306/db2?           charset=utf8", max_overflow=5)
​
#继承了元类,重写了类的定义的方法
Base = declarative_base()
​
​
class UserType(Base):
    __tablename__ = 'usertype'
    id = Column(Integer, autoincrement=True, primary_key=True)
    name = Column(String(32), nullable=False, default='')
​
​
class Users(Base):
    __tablename__ = 'user'
    uid = Column(Integer, autoincrement=True, primary_key=True)
    uname = Column(String(32), nullable=False, server_default='', unique=True)
    extra = Column(String(32), nullable=False, server_default='', index=True)
    # 外键约束
    type_id = Column(Integer, ForeignKey(UserType.id))
    #那张表有外键,relationship就写到哪,用来正向查询和反向查询的
    #usertype表示对应UserType这张表的数据,相当于再自己(Users)多了一个隐藏字段,不会显示
    #backref表示再usertype中多了一个隐藏字段xxoo,xxoo代表该表的多条记录对象
    usertype = relationship('UserType', backref='xxoo')
​
​
 __table_args__ = (
    UniqueConstraint('id','name', name='uix_id_name'),#联合唯一索引
    Index('ix_name_extra','name','extra') #联合索引
 )
​
def create_db():
    # 会将当前执行文件中继承自Base类的类创建成表
    Base.metadata.create_all(engine)
​
​
def drop_db():
    # 删除表
    Base.metadata.drop_all(engine)
​
​
###增删改查
Session = sessionmaker(bind=engine)
session = Session()
​
# 增加一条
obj_ut = UserType(name='Centurion')
session.add(obj_ut)
# 增加多条
session.add_all([
    Users(uname='zs', extra='sb', type_id=1),
    Users(uname='eric', extra='cb', type_id=3),
    Users(uname='zxx', extra='zb', type_id=1),
    Users(uname='owen', extra='nb', type_id=2),
    Users(uname='lxx', extra='tb', type_id=2)
])
​
# 查询:
# 查询全部:返回一个列表,列表存包含所有的对象(每个对象对应着表中的一条记录)
res = session.query((UserType)).all()
print(res)
for row in res:
    print(row.id,row.name)
# 查询一条数据:
res = session.query((UserType)).first()
​
# where条件,返回符合条件的对象列表
# 方式一:
res = session.query(UserType).filter(UserType.name == 'vip').all()
# 方式二:fliter_by可以通过属性名称等于值得方式,但是filter不可以
res = session.query(UserType).filter_by(name='vip').all()
print(res[0].id)
​
# 删除 执行成功返回1,失败返回0
res = session.query(UserType).filter(UserType.id == 4).delete()
print(res)
​
# 修改 执行成功返回1,失败返回0
res = session.query(UserType).filter(UserType.id == 4).update({'name':'svip'})
print(res)
​
# 高级查询
# between
res = session.query(UserType).filter(UserType.id.between(1,3)).all()
for row in res:
    print(row.id,row.name)
    
# in 里面用列表包裹
res = session.query(UserType).filter(UserType.id.in_([1,2,3])).all()
for row in res:
    print(row.id,row.name)
    
# not in
res = session.query(UserType).filter(~UserType.id.in_([1,2,3])).all()
for row in res:
    print(row.id,row.name)
    
# 通配符 like % _
res = session.query(Users).filter(Users.uname.like('z_')).all()
res = session.query(Users).filter(~Users.uname.like('z%')).all()
print(res[0].uid,res[0].uname)
​
# 限制取几条可以通过列表切片来完成
res = session.query(UserType)
print(res[0:1:1])
​
# 排序
res = session.query(Users).filter(Users.uid < 4).order_by(Users.uid.desc()).all
()
for row in res:
     print(row.uid,row.uname)
        
# 分组  group by having
# 聚合函数 import sqlalchemy.sql import func func.sum func.count func.max
res = session.query(
    func.max(Users.uid),
    func.min(Users.uid)).group_by(Users.type_id).all()
print(res)
​
# 多表联查
# join 默认式内连接, 当把isouter设置位True时变为left join
# query里只写一个对象时查询是没有UserType表的数据的
# 返回值是列表包含元组,元组里有两个类的实例
res = session.query(Users, UserType).join(UserType, isouter=True).all()
​
# 正向查询
res = session.query(Users).all()
for row in res:
    print(row.uid,row.uname,row.usertype.name)
​
# 反向查询
res = session.query(UserType).all()
for row in res:
    print(row.id,row.name,row.xxoo)
# ps:正向查询和反向查询用来简化代码,我们也可以同过普通查询得方式得到结果.当两张表有关联并且我们需要查询两张表得数据时,我们可以这样用来简化代码.
​
# session需要提交和关闭session
session.commit()
session.close()