07 字段查询详细

# @ Time    : 2021/1/31
# @ Author  : ecithy
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
# localhost
HOSTNAME = '127.0.0.1'
DATABASE = 'demo'
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))
    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
# result = session.query(Article).filter(Article.title == 'title0').all()
# not eq  mysql <>
# result = session.query(Article).filter(Article.title != 'title0').all()
# like 模糊查询   居  手机
# title 包含 title   xxxtitlexxx  2title
# result = session.query(Article).filter(Article.title.like('%title%')).all()
# print(result)
# in Python关键字  _ 私有的
# in [1, 4]  查询的是 id 为1,4 不是范围
# result = session.query(Article).filter(Article.title.in_(['title0', 'title4'])).all()
# not in
# result = session.query(Article).filter(~Article.title.in_(['title1', 'title2'])).all()
# result = session.query(Article).filter(Article.title.notin_(['title1', 'title2'])).all()
# Null != 空
# 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 != None).all()
# result = session.query(Article).filter(Article.title.isnot(None)).all()
# and
# result = session.query(Article).filter(Article.title == 'title0', Article.price == 48).all()
# result = session.query(Article).filter(and_(Article.title == 'title0', Article.price == 48)).all()
# result = session.query(Article).filter(Article.title == 'title0').filter(Article.price == 48).all()
# print(result)
# or  或者 满足一个条件就可以
result = session.query(Article).filter(or_(Article.title == 'title0', Article.price == 17, Article.title == 'title2')).all()
for data in result:
    print(data)
# print(result)