filter⽅法常⽤过滤条件
#encoding: utf-8
from sqlalchemy import create_engine,Column,Integer,String,Float,func,and_,or_
declarative import declarative_ba
import ssionmaker
from random import randint
HOSTNAME = '127.0.0.1'
PORT = 3306
DATABASE = 'first_sqlalchemy'
USERNAME = 'root'
PASSWORD = '123456'
#dialect+driver://urname:password@host:port/databa
DB_URI = "mysql+pymysql://{urname}:{password}@{host}:{port}/" \
"{db}?chart=utf8".format(urname=USERNAME,password=PASSWORD,host=HOSTNAME,port=PORT,db=DATABASE) engine = create_engine(DB_URI)
Ba = declarative_ba(engine)
# Session = ssionmaker(engine)
# ssion = Session()
ssion = ssionmaker(engine)() #Session(**local_kw)
class Article(Ba):
__tablename__ = 'article'
id = Column(Integer,primary_key=True,autoincrement=True)
title = Column(String(50),nullable=Fal)
price = Column(Float,nullable=Fal)
content = Column(String(100))
def__repr__(lf):
return'<Article(title:%s)>'%lf.title
# Ba.metadata.drop_all()
#
# ate_all()
#
# for x in range(6):
# article = Article(title='title%s'%x,price=randint(0,100))
# ssion.add(article)
# it()
# 1、equal 等于的意思
article = ssion.query(Article).filter(Article.id == 1).first()
article_title = ssion.query(Article).filter(Article.title == 'title1').first()
print(article)
print(article_title)
# 2、not equal 不等于的意思
article_title_not_equal = ssion.query(Article).filter(Article.title != 'title0').all()
print(article_title_not_equal)
#3、like
#注意：如果不查询，直接打印显⽰原⽣sql，不⽤first（）或 all()⽅法
article_title_like = ssion.query(Article).filter(Article.title.like('title%') ).all()
#相当于sql lect * from article where title like '%title%';
print(article_title_like)
#插⼊⼀条数据的sql ：inrt into article values(null,'abc','100');
# 4、ilike(不区分⼤⼩写)
article_title_ilike = ssion.query(Article).filter(Article.title.ilike('title%') )
print(article_title_ilike)
#SELECT article.id AS article_id, article.title AS article_title, article.price AS article_price FROM article # WHERE lower(article.title) LIKE lower(%(title_1)s)
# 5、in (在某某⾥⾯)
#为什么⽤in_，因为要避开关键字in
article_title_in = ssion.query(Article).filter(Article.title.in_(['title1','title2'])).all()
print(article_title_in)
#原⽣sql SELECT article.id AS article_id, article.title AS article_title, article.price AS article_price
# FROM article
# WHERE article.title IN (%(title_1)s, %(title_2)s)%（'title1','title2'），这是格式化字符串，防⽌sql注⼊#6、not in (不在某某⾥⾯)
article_title_not_in = ssion.query(Article).filter(in_(['title1','title2'])).all()
print(article_title_not_in)
#not in (另⼀种写法) ~取反的意思
article_title_not_in_1 = ssion.query(Article).filter(~Article.title.in_(['title1','title2'])).all()
print(article_title_not_in_1)
# 7、is null 判断某个字段是否为空
# alter table article add column content text; 添加⼀列，
# 修改原先好的类别 alter table article modify column content varchar(100);
# update article t content='python or go' where id =7; 修改⼀条记录
is_null_content = ssion.query(Article).t == None).all()
print(is_null_content)
# not is null 不为空
not_is_null_content = ssion.query(Article).t != None)
print(not_is_null_content)
'''
SELECT article.id AS article_id, article.title AS article_title, article.price AS article_price,
FROM article
t IS NOT NULL
'''
#8、and 和
title_and_content = ssion.query(Article).\
filter(Article.title == 'python or go',t == 'python or go').all()
print(title_and_content)
#另⼀种显⽰⽅式
title_and_content_1 = ssion.query(Article).\
filter(and_(Article.title == 'python or go',t == 'python or go'))
print(title_and_content_1)
#原⽣sql显⽰SELECT article.id AS article_id, article.title AS article_title,
# article.price AS article_price, t AS article_content
# FROM article
# WHERE article.title = %(title_1)s t = %(content_1)s
#9.or
title_or_content = ssion.query(Article).filter\
(or_(Article.title == 'python or go',t == 'python or go')).all()
print(title_or_content)
