复杂查询

复杂查询及查询值输出

基于 Query 的 subquery

with get_session() as session:
    # <class 'sqlalchemy.orm.query.Query'>
    query = (session
            .query(User.id)
            .filter(User.username == "asd")
            .filter_by(user)
            .limit(10)
            )
    subquery = query.subquery()
    query2 = session.query(User).filter(
        User.id.in_(subquery)
    )
    print query2#<-打印展开成的SQL，此处没有SQL查询

SQLAlchemy and or 查询方式范例：

逻辑查询

AND：

# use and_()

from sqlalchemy import and_
query.filter(and_(User.name == 'ed', User.fullname == 'Ed Jones'))
# or send multiple expressions to .filter()
query.filter(User.name == 'ed', User.fullname == 'Ed Jones')
# or chain multiple filter()/filter_by() calls
query.filter(User.name == 'ed').filter(User.fullname == 'Ed Jones')

OR:

from sqlalchemy import or_
query.filter(or_(User.name == 'ed', User.name == 'wendy'))

MATCH:

query.filter(User.name.match('wendy'))

理解了 Query、Column 的概念，也很容易自行构造出这样的 SQL

所有在 Column 级别上的使用 详见 Column API 文档

限定时间范围的查询

过滤日期字段

#模型
class User(Base):
    ...
    birthday = Column(Date, index=True)   #in database it's like '1987-01-17'
    ...

查询指定日期范围出生的

qry = DBSession.query(User).filter(
        and_(User.birthday <= '2022-01-17', User.birthday >= '1985-01-17'))
# or same:
qry = DBSession.query(User).filter(User.birthday <= '2022-01-17').\
        filter(User.birthday >= '1985-01-17')

或者

qry = DBSession.query(User).filter(User.birthday.between('1985-01-17', '2022-01-17'))#推荐

近期和之前数据

from datetime import datetime, timedelta
NOW = datetime.now()


#最近一周数据
macroEconomyTable = Scrapy.query.filter(Scrapy.date >= NOW - timedelta(days=7)).all()
#最近1天数据
macroEconomyTable = Scrapy.query.filter(Scrapy.date >= NOW - timedelta(days=1)).all()
#最近12小时
macroEconomyTable = Scrapy.query.filter(Scrapy.date >= NOW - timedelta(hours=12)).all()
#最近半小时
macroEconomyTable = Scrapy.query.filter(Scrapy.date >= NOW - timedelta(seconds=30)).all()

#1天之前
macroEconomyTable = Scrapy.query.filter(Scrapy.date <= NOW - timedelta(days=1)).all()
#12小时之前
macroEconomyTable = Scrapy.query.filter(Scrapy.date <= NOW - timedelta(hours=12)).all()

我最中意的:

• 方法1

from datetime import date, timedelta

from flask import jsonify

from app import db, ma
from app.models import User

from . import main

class UserSchema(ma.Schema):
    class Meta:
        fields = ('forename', 'surname', 'birthday', ...)


@main.route('/', methods=('GET',))
def get_users():

    start_range = date.today() + timedelta(years=-30)
    end_range = date.today() + timedelta(years=-18)

    users = db.session.query(User).filter(User.birthday.between(start_range, end_range)).all()

    users_schema = UserSchema(many=True)

    return jsonify(users_schema.dump(users))     

• 方法2

  from app import SQLAlchemyDB as db
  
  Chance.query.filter(Chance.repo_id==repo_id, 
                      Chance.status=="1", 
                      db.func.date(Chance.apply_time)<=end, 
                      db.func.date(Chance.apply_time)>=start).count()
  
  ```
  等价sql语句
  select
     count(id)
  from
     Chance
  where
     repo_id=:repo_id 
     and status='1'
     and date(apple_time) <= end
     and date(apple_time) >= start
  ```

SQLAlchemy ORM查询结果转换为Pandas DataFrame输出

df = pd.read_sql(query.statement, query.session.bind)
#query为具体查询	    query.session.bind绑定对应数据表

实例:

import pandas as pd


logtables = pd.read_sql(
            self.user_log_sql_session.query(Logs).filter(Logs.create_time >= NOW - timedelta(days=6)).statement,
            self.user_log_sql_session.bind)
print(logtables[:][:])
#self.user_log_sql_session这是我自定义的一个获取session的变量