5、SQLAlchemy基础查询 sqlalchemy教程

query可用参数

聚合函数：

filter扩展运算

常用查询选项

关系选项

查询案例

filter条件过滤

外键约束

一对多

一对多结构

一对多增加数据

一对多数据删除

一对多修改数据

一对多查询数据

query可用参数

1、模型对象，指定查找这个模型中所有的对象；

2、模型中的属性，可以指定查找某个模型中的几个属性；

3、聚合函数；

聚合函数：

func上没有任何聚合函数，但是因为它底层做了一些转换，只要mysql中有的聚合函数，都可以通过func调用；

func.count：统计行数量；

func.avg：求平均值；

func.max：求最大值；

func.min：求最小值；

func.sum：求和；

filter扩展运算

equals：=,判断是否相等；

not equals：!=,判断是否不等；

like： like('cc%'),模糊匹配；

ilike：ilike('cc%'),模糊匹配，不区分大小写；

in：in_([1,2,3]),判断一个集合；

not in：notin_([1,2,3]),判断一个非集合；

is null：is_(None),判断是否为NULL；

is not null：isnot(None),判断是否不为NULL；

and:与运算；

or：或运算；

常用查询选项

filter：传入参数的写法，要用：类名.列名 两个等号 去判断

举例：

query(User.name).filter(User.fullname==’Ed Jones’)

且更复杂的查询的语法，比如_and()，or_()等多个条件的查询，只支持filter

举例：

query.filter(or_(User.name == ‘ed’, User.name == ‘wendy’))

query.filter(and_(User.name == ‘ed’, User.fullname == ‘Ed Jones’))

filter_by：传入参数的写法，只需要用：（不带类名的）列名 单个等号 就可以判断。filter中，语法更加贴近于，类似Python的语法。

举例：

query(User.name).filter_by(fullname=’Ed Jones’)

关系选项

backref：在关系的另一模型中添加反向引用；

primaryjoin：明确指定两个模型之间使用的联结条件；

uselist：如果为False，不使用列表，而使用标量值；

order_by：指定关系中记录的排序方式；

secondary：指定多对多中记录的排序方式；

secondaryjoin：在SQLAlchemy中无法自行决定时，指定多对多关系中的二级联结条件；

查询案例

from sqlalchemy import create_engine

from sqlalchemy.ext.declarative import declarative_base # 导入一个函数

from sqlalchemy import Column, Integer, String, Enum, Float, func, Boolean, Date, DateTime # 每一个字段都需要用到Column来定义，并且指定类型

from sqlalchemy.orm import sessionmaker # 导入一个session模块

engine = create_engine('mysql+pymysql://root:root@192.168.1.254:3306/cce?charset=utf8', echo=False)

Base = declarative_base() # 生成一个基类

class User(Base):

__tablename__ = 'users'

id = Column(Integer, primary_key=True, autoincrement=True)

title = Column(String(50), nullable=False)

price = Column(Float, nullable=False)

# Base.metadata.drop_all(engine)

# Base.metadata.create_all(engine)

# Session = sessionmaker(engine)

# session = Session()

# cce = list()

# for i in range(1, 10):

# price = 100 * i

# title = 'cce' + str(i)

# cce.append(User(price=price, title=title))

# session.add_all(cce)

# session.commit()

Session = sessionmaker(engine)

session = Session()

# 查询所有对象

session.query(User).all()

# 查询所有对象的title字段的值

session.query(User.title).all()

# 查找数据的条数

session.query(User).count()

session.query(func.count(User.id)).first()

# 查找平均值

session.query(func.avg(User.price)).first()

# 求最大最小以及求和

session.query(func.max(User.price)).first()

session.query(func.min(User.price)).first()

session.query(func.sum(User.price)).first()

filter条件过滤

过滤是数据提取的一个很重要的功能，以下对一些常用的过滤条件进行解释；

from sqlalchemy import create_engine

from sqlalchemy.ext.declarative import declarative_base # 导入一个函数

from sqlalchemy import Column, Integer, String, Enum, Float, func, Boolean, Date, DateTime # 每一个字段都需要用到Column来定义，并且指定类型

from sqlalchemy.orm import sessionmaker # 导入一个session模块

engine = create_engine('mysql+pymysql://root:root@192.168.1.254:3306/cce?charset=utf8', echo=False)

Base = declarative_base()

class User(Base):

__tablename__ = 'users'

id = Column(Integer, primary_key=True, autoincrement=True)

title = Column(String(50), nullable=False)

price = Column(Float, nullable=False)

# Base.metadata.drop_all(engine)

# Base.metadata.create_all(engine)

# Session = sessionmaker(engine)

# session = Session()

# cce = list()

# for i in range(1, 10):

# price = 100 * i

# title = 'cce' + str(i)

# cce.append(User(price=price, title=title))

# session.add_all(cce)

# session.commit()

Session = sessionmaker(engine)

session = Session()

# 在User模型里面查找User.title字段，条件为User.id为1

session.query(User.title).filter(User.id == 1).all() # 默认得到是一个对象集合可以时候也first来拿到一个数据

# like、ilike查找所有字段，条件为title的值like cc

session.query(User).filter(User.title.like("cc%")).all() # 默认得到是一个对象集合可以时候也first来拿到一个数据

session.query(User).filter(User.title.ilike("cc%")).all() # 不区分大小写

# in_，判断某个或多个值在某个字段

session.query(User).filter(User.title.in_(['cce1','cce2'])).all()

# notin_，和in相反

session.query(User).filter(User.title.notin_(['cce1','cce2'])).all()

# is null,判断字段是否为空

session.query(User).filter(User.title.is_(None)).all()

# and，和

session.query(User).filter(User.id==1 and User.title == 'cce').all()

# or，或者

session.query(User).filter(User.id==1 or User.title == 'cce').all()

外键约束

在Mysql中，外键可以让表之间的关系更加紧密，而sqlalchemy同样支持外键，通过ForeignKey类来实现，并且可以指定表的外键约束，相关示例如下：

RESTRICT：父表数据被删除，会阻止删除；

NO ACTION：在MySQL中，同RESTRICT；

CASCADE：级联删除；

SET NULL：父表删除，子表数据会跟着删除；

from sqlalchemy import create_engine

from sqlalchemy.ext.declarative import declarative_base # 导入一个函数

from sqlalchemy import Column, Integer, String, Enum, Float, func, Boolean, Date, DateTime, \

ForeignKey # 每一个字段都需要用到Column来定义，并且指定类型

from sqlalchemy.orm import sessionmaker # 导入一个session模块

engine = create_engine('mysql+pymysql://root:root@192.168.1.254:3306/cce?charset=utf8', echo=False)

Base = declarative_base()

class User(Base):

__tablename__ = 'user'

id = Column(Integer, autoincrement=True, primary_key=True)

uname = Column(String(32), nullable=False)

email = Column(String(32), nullable=True)

role_id = Column(Integer, ForeignKey('role.id',ondelete='RESTRICT'))

class Role(Base):

__tablename__ = 'role'

id = Column(Integer, autoincrement=True, primary_key=True)

rname = Column(String(32), nullable=False)

User.metadata.drop_all(engine)

Role.metadata.drop_all(engine)

User.metadata.create_all(engine)

Role.metadata.create_all(engine)

一对多

mysql级别的外键，还不够ORM，必须拿到一个表的外键，然后通过外键再去另一张表中查找，这样太麻烦了，SQLAlchemy提供了一个relationship的功能，这个类可以定义属性，以后再访问相关联的表之后，就可以直接通过属性访问的方式就可以访问到了，还可以通过，backref来指定反向访问的属性名字；

一对多结构

from sqlalchemy import create_engine

from sqlalchemy.ext.declarative import declarative_base # 导入一个函数

from sqlalchemy import Column, Integer, String, ForeignKey # 每一个字段都需要用到Column来定义，并且指定类型

from sqlalchemy.orm import sessionmaker, relationship # 导入一个session模块

engine = create_engine('mysql+pymysql://root:root@192.168.1.254:3306/cce?charset=utf8', echo=False)

Base = declarative_base()

class User(Base):

__tablename__ = 'user'

id = Column(Integer, autoincrement=True, primary_key=True)

uname = Column(String(32), nullable=False)

email = Column(String(32), nullable=True)

role_id = Column(Integer, ForeignKey('role.id', ondelete='RESTRICT'))

role = relationship('Role', backref='user') # 将user字段映射为User表,backref为反向查询的名字(其内部意义就是在User对象里面新增一个role属性，指向对应的role表的数据)

class Role(Base):

__tablename__ = 'role'

id = Column(Integer, autoincrement=True, primary_key=True)

rname = Column(String(32), nullable=False)

User.metadata.drop_all(engine)

Role.metadata.drop_all(engine)

User.metadata.create_all(engine)

Role.metadata.create_all(engine)

一对多增加数据

此时我将模型和sql文件分开了，模型文件存储在db.py，内容同上，sql处理逻辑在sql.py，所以事先需要导入db.py；

// 从头创建

from db import User, Role # 导入模块

from sqlalchemy.orm import sessionmaker

from sqlalchemy import create_engine

engine = create_engine('mysql+pymysql://root:root@192.168.1.254:3306/cce?charset=utf8', echo=False)

# 创建一个数据库窗口

Session = sessionmaker(engine)

# 拿到一个窗口对象

session = Session()

# 创建新的数据，其实这就是基于role创建user

# 首先创建一个父表的数据

role = Role(rname='管理员')

# 创建一个用户对象，此时无需加入role

user = User(uname='cce', email='mail0426@163.com')

# 将用户对象加入到role对象里面

role.user.append(user)

# 提交

session.add(role)

session.commit()

# 基于已经存在的父元素创建子元素

# 获取现有的role,得到一个对象

result = session.query(Role).filter(Role.id == 1).first()

# 定义role为上面返回的这个对象

user = User(uname='cc1e', email='mail0426@163.com',role=result)

# 提交

session.add(user)

session.commit()

一对多数据删除

from db import User, Role

from sqlalchemy.orm import sessionmaker

from sqlalchemy import create_engine

engine = create_engine('mysql+pymysql://root:root@192.168.1.254:3306/cce?charset=utf8', echo=False)

# 创建一个数据库窗口

Session = sessionmaker(engine)

# 拿到一个窗口对象

session = Session()

# 获取现有的role,利用get(1),直接得到id为1的一个对象

result = session.query(User).get(1)

# 删除该对象的数据

session.delete(result)

# 提交

session.commit()

一对多修改数据

修改数据，直接将要修改的数据拿出来，然后使用对象的方式幅值，继而提交即可；

from db import User, Role

from sqlalchemy.orm import sessionmaker

from sqlalchemy import create_engine

engine = create_engine('mysql+pymysql://root:root@192.168.1.254:3306/cce?charset=utf8', echo=False)

# 创建一个数据库窗口

Session = sessionmaker(engine)

# 拿到一个窗口对象

session = Session()

# 获取现有的role,拿到一个对象

result = session.query(User).limit(1).first()

# 修改对象的值

result.uname='caidaye'

# 数据提交

session.add(result)

session.commit()

一对多查询数据

正向查询，反向查询；

from db import User, Role

from sqlalchemy.orm import sessionmaker

from sqlalchemy import create_engine

engine = create_engine('mysql+pymysql://root:root@192.168.1.254:3306/cce?charset=utf8', echo=False)

# 创建一个数据库窗口

Session = sessionmaker(engine)

# 拿到一个窗口对象

session = Session()

# 获取现有的role,拿到一个对象

# 通过role查询所有的user

# 拿到一个role对象

role = session.query(Role).filter(Role.id == 1).first()

# 拿到属于这个role的所有user

print(role.user)

# 通过user拿到所属的role

user=session.query(User).first()

print(user.role.rname)