欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

SQLAlchemy 查询语句

程序员文章站 2024-03-03 16:32:52
...

以 Python 原生 sqlalchemy 举例:

# 在 ipython 中写入以下代码,创建两个映射类
In [1]: from sqlalchemy import create_engine, Column, Integer, \
   ...:      String, ForeignKey
   ...: from sqlalchemy.ext.declarative import declarative_base
   ...: from sqlalchemy.orm import relationship, sessionmaker, backref
   ...: engine = create_engine('mysql://[email protected]/test?charset=utf8')
   ...: Base = declarative_base(engine)
   ...: session = sessionmaker(engine)()
   ...: 
   ...: class User(Base):
   ...:     __tablename__ = 'user'
   ...:     id = Column(Integer, primary_key=True)
   ...:     name = Column(String(64), unique=True, index=True)
   ...:     def __repr__(self):
   ...:         return '<User: {}>'.format(self.name)
   ...:             
   ...: class Course(Base):
   ...:     __tablename__ = 'course'
   ...:     id = Column(Integer, primary_key=True)
   ...:     name = Column(String(64), unique=True, nullable=False)
   ...:     user_id = Column(Integer, ForeignKey('user.id'))
   ...:     user = relationship('User', backref='course')
   ...:     def __repr__(self):
   ...:         return '<Course: {}>'.format(self.name)
   ...: 

# 创建数据库表,添加测试数据
In [2]: Base.metadata.create_all()
   ...: u1 = User(name='Kobe')
   ...: u2 = User(name='Nash')
   ...: u3 = User(name='James')
   ...: c1 = Course(name='Mysql 基础', user=u1)
   ...: c2 = Course(name='Flask-SQLAlchemy 快速入门', user=u1)
   ...: for i in (u1, u2, u3, c1, c2):
   ...:     session.add(i)
   ...: session.commit()
   ...: 

query 的基本查询语句:

# 查询全部,相当于 SQL 查询语句:select * from user;
In [7]: session.query(User).all()
Out[7]: [<User Kobe>, <User Nash>, <User James>]

# 查询第一个
In [8]: session.query(User).first()
Out[8]: <User Kobe>

# 条件查询
In [9]: session.query(User).filter(User.name=='James').first()
Out[9]: <User James>

# 同上
In [10]: session.query(User).filter_by(name='James').first()
Out[10]: <User James>

# != 反查询,只有 filter 能用,filter_by 不能用
# 同样的,> < >= <= 均可使用,只有 filter 能用
In [20]: session.query(User).filter(User.name!='Kobe').all()
Out[20]: [<User: James>, <User: Nash>]

# 多条件查询,只有 filter_by 能用,filter 的查询见下面
In [11]: session.query(User).filter_by(name='Kobe', id=1).all()
Out[11]: [<User: Kobe>]

# 查询 User 表中全部数据的 name 值
In [12]: session.query(User.name).all()  # 这种查询方法的返回值类似 namedtuple
Out[12]: [('James'), ('Kobe'), ('Nash')]

In [13]: for i in session.query(User.name).all():
    ...:     print(i)
    ...:     
('James',)
('Kobe',)
('Nash',)

In [14]: for i in session.query(User.name).all():
    ...:     print(i.name)
    ...:     
James
Kobe
Nash

like in_ and_ or_ 四个特殊查询方法,也是只有 filter 能用:

# like 模糊查询
# 查询 name 值中带 e 字符的数据
In [25]: session.query(User).filter(User.name.like('%e%')).all()
Out[25]: [<User: James>, <User: Kobe>]

# in_ 查询某个字段的值属于某个列表
In [31]: session.query(User).filter(User.name.in_(['Kobe', 'James'])).all()
Out[31]: [<User: James>, <User: Kobe>]

In [32]: session.query(User).filter(User.name.in_(['Kobe', 'haha'])).all()
Out[32]: [<User: Kobe>]

# and_ 多条件查询,需要引入,而且不如上面 filter_by 的写法简洁
In [34]: from sqlalchemy import and_

In [35]: session.query(User).filter(and_(User.name=='Kobe', User.id==1)).all()
Out[35]: [<User: Kobe>]

# or_ 符合任一条件即可,也是需要引入
In [37]: from sqlalchemy import or_

In [38]: session.query(User).filter(or_(User.name=='Kobe', User.id==2)).all()
Out[38]: [<User: Kobe>, <User: Nash>]

join 方法进行连表查询:

# 查询 name 值为 'Kobe' 的 user 对应的全部 course
In [13]: session.query(Course).join(User).filter(User.name=='Kobe').all()
Out[13]: [<Course: MySQL 基础>, <Course: Flask 快速入门>]

order_by 方法排序,limit 方法限制数量,count 方法统计数量:

# 对 User 表的 name 列进行排序
In [14]: session.query(User).order_by(User.name).all()
Out[14]: [<User: James>, <User: Kobe>, <User: Nash>]

# 降序排序
In [15]: session.query(User).order_by(User.name.desc()).all()
Out[15]: [<User: Nash>, <User: Kobe>, <User: James>]

# 限制数量
In [16]: session.query(User).order_by(User.name.desc()).limit(2).all()
Out[16]: [<User: Nash>, <User: Kobe>]

# 切片也可以
In [17]: session.query(User).order_by(User.name.desc())[1:]
Out[17]: [<User: Kobe>, <User: James>]

# 统计数量,注意这里没有 .all() 了
In [43]: session.query(User).order_by(User.name.desc()).count()
Out[43]: 3

flask_sqlalchemy 查询语句,跟上面的 sqlalchemy 差不多,基本操作如下:

In [18]: User.query.all()
Out[18]: [<User Kobe>, <User Nash>, <User: James>]

In [19]: User.query.first()
Out[19]: <User Kobe>

In [20]: User.query.filter(User.name=='Kobe').first()
Out[20]: <User Kobe>

In [21]: User.query.filter_by(name='Kobe').first()
Out[21]: <User Kobe>

In [22]: User.query.filter_by(name='Kobe', id=1).first()
Out[22]: <User Kobe>

以上是常用的几个查询语句,更多复杂的使用详见 官方文档 ~~