#encoding: utf-8from sqlalchemy import create_engine,Column,Integer,String,Float,func,and_,or_
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from random import randint
HOSTNAME ='127.0.0.1'
PORT =3306
DATABASE ='first_sqlalchemy'
USERNAME ='root'
PASSWORD ='123456'#dialect+driver://username:password@host:port/database
DB_URI ="mysql+pymysql://{username}:{password}@{host}:{port}/" \
"{db}?charset=utf8".format(username=USERNAME,password=PASSWORD,host=HOSTNAME,port=PORT,db=DATABASE)
engine = create_engine(DB_URI)
Base = declarative_base(engine)# Session = sessionmaker(engine)# session = Session()
session = sessionmaker(engine)()#Session(**local_kw)classArticle(Base):
__tablename__ ='article'id= Column(Integer,primary_key=True,autoincrement=True)
title = Column(String(50),nullable=False)
price = Column(Float,nullable=False)
content = Column(String(100))def__repr__(self):return'<Article(title:%s)>'%self.title
# Base.metadata.drop_all()# Base.metadata.create_all()# for x in range(6):# article = Article(title='title%s'%x,price=randint(0,100))# session.add(article)# session.commit()# 1、equal 等于的意思
article = session.query(Article).filter(Article.id==1).first()
article_title = session.query(Article).filter(Article.title =='title1').first()print(article)print(article_title)# 2、not equal 不等于的意思
article_title_not_equal = session.query(Article).filter(Article.title !='title0').all()print(article_title_not_equal)#3、like#注意:如果不查询,直接打印显示原生sql,不用first()或 all()方法
article_title_like = session.query(Article).filter(Article.title.like('title%')).all()#相当于sql select * from article where title like '%title%';print(article_title_like)#插入一条数据的sql :insert into article values(null,'abc','100');# 4、ilike(不区分大小写)
article_title_ilike = session.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 = session.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 = session.query(Article).filter(Article.title.notin_(['title1','title2'])).all()print(article_title_not_in)#not in (另一种写法) ~取反的意思
article_title_not_in_1 = session.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 set content='python or go' where id =7; 修改一条记录
is_null_content = session.query(Article).filter(Article.content ==None).all()print(is_null_content)# not is null 不为空
not_is_null_content = session.query(Article).filter(Article.content !=None)print(not_is_null_content)'''
SELECT article.id AS article_id, article.title AS article_title, article.price AS article_price,
article.content AS article_content
FROM article
WHERE article.content IS NOT NULL
'''#8、and 和
title_and_content = session.query(Article).\
filter(Article.title =='python or go',Article.content =='python or go').all()print(title_and_content)#另一种显示方式
title_and_content_1 = session.query(Article).\
filter(and_(Article.title =='python or go',Article.content =='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, article.content AS article_content# FROM article# WHERE article.title = %(title_1)s AND article.content = %(content_1)s#9.or
title_or_content = session.query(Article).filter\
(or_(Article.title =='python or go',Article.content =='python or go')).all()print(title_or_content)