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>
以上是常用的几个查询语句,更多复杂的使用详见 官方文档 ~~
上一篇: K-th Number POJ - 2104 (主席树模板)
下一篇: 矩阵组合