day45

SQLalcherm

1.概念：SQLAlchemy是Python编程语言下的一款ORM框架，该框架建立在数据库API之上，使用关系对象映射进行数据库操作，简言之便是：将对象转换成SQL，然后使用数据API执行SQL并获取执行结果。

2.目的：SQLAlchemy是Python中最有名的ORM框架（Object-Relational Mapping，把关系数据库的表结构映射到对象上。）

3.注意：sqlalcherm需要自己进行安装   pip3 install SQLAlchemy

如何使用

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
# 也可进行下列导入方式

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.schema import *

2.建立连接

engine=create_engine('mysql+pymysql://root:123@127.0.0.1:3306/tt?charset=utf8',max_overflow=5)

3.创建基类

Base=declarative_base()

4.定义表格

class UserType(Base):
    __tablename__='usertype' # 表名
    id=Column(Integer,autoincrement=True,primary_key=True)
    title=Column(String(32),nullable=False,server_default='')

class Users(Base):
    __tablename__="users"
    id=Column(Integer,autoincrement=True,primary_key=True)
    name=Column(String(32),nullable=False,server_default='',index=True) # unique=True 单独添加普通或联合唯一索引
    extra=Column(String(32),nullable=False,server_default='')
    type_id=Column(Integer,ForeignKey(UserType.id)) # 创建外键
    usertype=relation('UserType',backref='xxoo') # 创建正向，反向查询
    # 多个列创建索引
    __table_args__=(
        UniqueConstraint('id','name',name='uix_id_name'), # 联合唯一索引
        Index('ix_name_name_extra','type_id','extra'), # 组合索引
    )

5.生成或删除表格

#  会将当前执行文件中所有继承自Base类的类,生成表
def create_table():
    Base.metadata.create_all(engine)
# 删除所有的表格
def drop_table():
    Base.metadata.drop_all(engine)

6.创建操作表格类和对象

Session = sessionmaker(bind=engine)
session = Session()

表格操作

增：

# 增加一个数据
obj=UserType(title='普通用户')
session.add(obj)
session.commit()
# 增加多条数据
session.add_all([
    UserType(title='VIP用户'),
    UserType(title='VIP中P用户'),
    UserType(title='SVIP用户'),
    UserType(title='黑金用户')
])
session.commit()

查

# 查询全部数据
res=session.query(UserType).all() # 不加.all()是一个查询语句；加的话就是一个列表套对象
print(res)
for row in res:
    print(row.id,row.title)
# 查询一条数据(返回的是一个对象）
res=session.query(UserType).first()
print(res.id)

# 查询where条件
res=session.query(UserType).filter(UserType.title=='VIP用户',UserType.id==2).all()
for row in res:
    print(row.id,row.title)

删

session.query(UserType).filter(UserType.id>5).delete()
session.commit()

改

session.query(UserType).filter(UserType.id==3).update({'title':"SVIP用户"})
session.commit()

查询高级操作

# between ..and..
res=session.query(UserType).filter(UserType.id.between(1,3)).all()
print(res)
for row in res:
    print(row.id,row.title)
# in
res=session.query(UserType).filter(UserType.id.in_([1,3,4])).all()
print(res)
# not in
res=session.query(UserType).filter(~UserType.id.in_([1,3,4])).all()
for roe in res:
    print(roe.id,roe.title)
# from sqlalchemy import and_,or_ 默认是and
res=session.query(UserType).filter(or_(UserType.title=='SVIP用户',UserType.id==3)).all()
for row in res:
    print(row.id,row.title)
# 通配符
res=session.query(UserType).filter(UserType.title.like('S%')).all()
print(res)
for row in res:
    print(row.id,row.title)
# 限制 limit(切片)
res=session.query(UserType)[0:3]
for row in res:
    print('n',row.id,row.title)

# 排序 order by
res=session.query(UserType).order_by(UserType.id.desc())
for row in res:
    print(row.id,row.title)

# 分组(返回的是列表套元组)
res=session.query(func.max(UserType.id)).group_by(UserType.title).all()
print(res) # [(4,), (2,), (1,), (5,)]
ret = session.query(
    func.max(Users.id),
    func.min(Users.id)).group_by(Users.type_id).having(func.min(Users.id) >2).all()

# 连表(列表套元组；元组中第一个对象是Users第一行内容，第二个是UserType第一行内容；以此类推)

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

# 正反向查询

res=session.query(Users).all()
print('正向查询')
for row in res:
    print(row.name,row.extra,row.type_id,row.usertype.id,row.usertype.title)
# 正向查询
# owen nb 1 1 普通用户
# ss ok 2 2 VIP用户
# zekai xx 5 5 黑金用户
# egon hh 3 3 SVIP用户
# vae gg 4 4 SVIP用户

res=session.query(UserType).all()
print('反向查询')
for row in res:
    print(row.id,row.title,row.xxoo[0].name) # row.xxoo得到的是一个列表套对象 
#     反向查询
# 1 普通用户 owen
# 2 VIP用户 ss
# 3 SVIP用户 egon
# 4 SVIP用户 vae
# 5 黑金用户 zekai

总结：1.正反向的语句要写在外键表的类中 例：usertype=relation('UserType',backref='xxoo') # 创建正向，反向查询
     2.正反向查询都是在自己的表内容对象中获取别的表的内容
     3.正反向查询相当于2个表通过外键进行连接只是被连接的部分被隐藏了，但可以通过语句获取他的内容