过滤条件
过滤是数据提取的一个很重要的功能,一下对一些常用的过滤条件进行解释,并且这些过滤条件都是只能通过filter方法来实现的。
基本代码:
from sqlalchemy import create_engine, and_, or_
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Float
from sqlalchemy.orm import sessionmaker
import random
HOSTNAME = '127.0.0.1'
DATABASE = 'demo0417'
PORT = 3306
USERNAME = 'root'
PASSWORD = 'root'
DB_URL = 'mysql+mysqlconnector://{}:{}@{}:{}/{}?charset=utf8'.format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)
engine = create_engine(DB_URL)
Base = declarative_base(engine)
class Article(Base):
__tablename__ = 'article2'
id = Column(Integer, primary_key=True, autoincrement=True)
# title = Column(String(50), nullable=False)
title = Column(String(50)) # 测试null方法,重新建表
price = Column(Float, nullable=False)
# def __repr__(self): 方法类似
def __str__(self):
return "Article(title:{},price:{})".format(self.title, self.price)
# Base.metadata.drop_all()
Base.metadata.create_all()
Session = sessionmaker(bind=engine)
session = Session()
# for i in range(6):
# article = Article(title="title%s" % i, price=random.randint(1, 50))
# session.add(article)
# session.commit()
eq
# eq
# result = session.query(Article).filter(Article.title == 'title3').all()
# for data in result:
# print(data)
not eq
# not eq mysql: <>
# result = session.query(Article).filter(Article.title != 'title3').all()
# for data in result:
# print(data)
like
# like 模糊查询
# result = session.query(Article).filter(Article.title.like('%title%')).all()
# for data in result:
# print(data)
in
# in
# result = session.query(Article).filter(Article.title.in_(['title1', 'title3'])).all()
# for data in result:
# print(data)
not in
# not in
# result = session.query(Article).filter(Article.title.notin_(['title2', 'title3'])).all()
# for data in result:
# print(data)
null
# null != 空
# result = session.query(Article).filter(Article.title == None).all()
# result = session.query(Article).filter(Article.title != None).all()
# result = session.query(Article).filter(Article.title.is_(None)).all()
# result = session.query(Article).filter(Article.title.isnot(None)).all()
#
# for data in result:
# print(data)
and
# and
# result = session.query(Article).filter(Article.title == 'title3', Article.price == 28).all()
# result = session.query(Article).filter(and_(Article.title == 'title3', Article.price == 28)).all()
# result = session.query(Article).filter(Article.title == 'title3').filter(Article.price == 28).all()
#
# for data in result:
# print(data)
or
# or
result = session.query(Article).filter(or_(Article.title == 'title3', Article.price == 3)).all()
for data in result:
print(data)