【Flask】filter 常用查询条件
1. equal 2. not equal 3. like, ilike 4.in 5. not in
6. is null 7. is not null 8. and 9. or
#### 如果想查看orm底层查询sql可以在filter函数后面不写人物的函数如：
articles = session.query(Article).filter(Article.title != 'title0')
1 # coding:utf-8 2 # Author: liangjun.chen 3 4 from datetime import date 5 from datetime import datetime 6 from datetime import time 7 from random import random 8 from sqlalchemy import create_engine, Column, Integer, String, Float, Text, and_, or_ 9 10 from sqlalchemy.ext.declarative import declarative_base 11 from sqlalchemy.orm import sessionmaker 12 13 HOSTNAME = '127.0.0.1' 14 PORT = 3306 15 DATABASE = 'sqlalchemy_first' 16 USERNAME = 'root' 17 PASSWORD = '123456' 18 19 DB_URI = 'mysql+pymysql://{username}:{password}@{host}:{port}/{dbname}?charset=utf8'.format( 20 username=USERNAME, password=PASSWORD, host=HOSTNAME, port=PORT, dbname=DATABASE 21 ) 22 engine = create_engine(DB_URI) 23 Base = declarative_base(engine) 24 Session = sessionmaker(engine) 25 session = Session() 26 27 28 class Article(Base): 29 __tablename__ = 'article' 30 id = Column(Integer, primary_key=True, autoincrement=True) 31 title = Column(String(50), nullable=False) 32 price = Column(Float, nullable=False) 33 content = Column(Text) 34 35 def __repr__(self): 36 return "Article<title: {}, price: {}>".format(self.title, self.price) 37 38 39 # 1. equal 40 article = session.query(Article).filter(Article.id == 1).first() 41 print article 42 43 # 2. not equal 44 articles = session.query(Article).filter(Article.title != 'title0').all() 45 print articles 46 47 # 3. like, ilike->不区分大小写 48 articles = session.query(Article).filter(Article.title.like('title%')).all() 49 print articles 50 51 # 4. in 52 print '-' * 20 53 articles = session.query(Article).filter(Article.title.in_(['title1', 'title2'])).all() 54 print articles 55 56 # 5. not in 57 print '-' * 20 58 articles = session.query(Article).filter(~Article.title.in_(['title2', 'title1'])).all() 59 articles2 = session.query(Article).filter(Article.title.notin_(['title2', 'title1'])).all() 60 61 print articles 62 print articles2 63 64 # 6. is null 65 print '-' * 20 66 articles = session.query(Article).filter(Article.content == None).all() 67 print articles 68 69 # 7. is not null 70 print '-' * 20 71 articles = session.query(Article).filter(Article.content != None).all() 72 print articles 73 74 # 8. and 75 print '-' * 20 76 articles = session.query(Article).filter(Article.title == 'title0', Article.content == 'test').all() 77 articles_2 = session.query(Article).filter(and_(Article.title == 'title0', Article.content == 'test')).all() 78 print articles 79 print articles_2 80 81 # 9. or 82 print '-' * 20 83 articles = session.query(Article).filter(or_(Article.title == 'title1', Article.content == 'test')).all() 84 print articles
