45 SQLALchemy

SQLAlchemy

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

安装

pip3 install sqlalchemy 

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:123@127.0.0.1:3306/db3?charset=utf8', max_overflow=5)

Base = declarative_base()
"""
create table db3(
    id int auto_increment primary key,
    name varchar(32) not null default '',
    extra varchar(32) not null default ''
    )
"""

"""
UserType
id     title      xx00
1      普通用户

row.xxoo  : 多条记录对象
"""


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


"""
users
id   name  extra   type_id
1    aa     nz      1
usertype = releationship('Usertype')
row.usertype

"""


class Users(Base):
    __tablename__ = 'users'
    id = Column(Integer, autoincrement=True, primary_key=True)
    name = Column(String(32), nullable=False, default='')
    extra = Column(String(32), nullable=False, default='')
    type_id = Column(Integer, ForeignKey(UserType.id))
    # usertype = relationship('UserType',backref='xx00')

    __table_args__ = (
        UniqueConstraint('id', 'name', name='mix_id_name'),
        # 联合唯一索引
        Index('ix_name_extra', 'name', 'extra'),
        # 组合索引
    )


def drop_db():
    Base.metadata.drop_all(engine)


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


create_db()

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

# 增加一条数据
obj = UserType(title='普通用户')
session.add(obj)

# 添加多条数据
session.add_all([
    UserType(title='1'),
    UserType(title='2'),
    UserType(title='3'),
    UserType(title='4'),
    UserType(title='5')
])

# 查询
# 查询全部,返回的是一个列表,列表中套对象
res = session.query().all()
for row in res:
    print(row.id, row.name)

# 查询一条数据
res = session.query(UserType).first()
print(res)

# where 条件
res = session.query(UserType).filter(UserType.name == '1', UserType.id == 2).all()
for row in res:
    print(row.id, row.name)
print(res[0].name, res[0].id)
res = session.query(UserType).filter_by(name='1').all()
print(res)

# 删除
session.query(UserType).filter(UserType.id > 3).delete()

# 修改
session.query(UserType).filter(UserType.id == 3).updata({'name': '2'})

# MySQL 高级查询操作

# 通配符  分组 分页 排序 between and in not in


# between...and...

res = session.query(UserType).filter(UserType.id.between(1, 4)).all()
for row in res:
    print(row.id, row.title)

# in 操作
res = session.query(UserType).filter(UserType.id.in_([1, 2, 5])).all()
print(res)

# not in
# select * from Usertype
res = session.query(UserType).filter(~UserType.id.in_([1, 2, 5])).all()

for row in res:
    print(row.id, row.title)

# session.query(UserType).filter(Users.id.in_(session.query(Users.id).filter_by(name='eric'))).all()
# res = session.query(UserType.title).filter(~UserType.id.in_([1,3,4])).all()
# for row in res:
#     print( row.title)


from sqlalchemy import and_, or_

# 通配符
# res = session.query(Users).all()

from sqlalchemy.sql import func

# rea = session.query(
#     Users.type_id,
#     func.max(Users.id),
#     func.min(Users.id)).group_by(Users.type_id).all()
#
# print(rea)

ret = session.query(
    func.max(Users.id),
    func.min(Users.id)).group_by(Users.type_id).having(func.min(Users.id) > 2).all()

# left jion
# 1. 查询某一个用户的用户类型
# 第一种方法:
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()
for row in res:
    print(row.id, row.name, row.extra, row.usertype.tiytle)

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

### 第二种方法
res = session.query(UserType).all()
for row in res:
    print(row.id, row.title, row.xx00)