# coding:utf-8

 # Author: liangjun.chen

 from datetime import date

 from datetime import datetime

 from datetime import time

 from random import random

 from sqlalchemy import create_engine, Column, Integer, String, Float, Text, and_, or_

 from sqlalchemy.ext.declarative import declarative_base

 from sqlalchemy.orm import sessionmaker

 HOSTNAME = '127.0.0.1'

 PORT = 3306

 DATABASE = 'sqlalchemy_first'

 USERNAME = 'root'

 PASSWORD = ''

 DB_URI = 'mysql+pymysql://{username}:{password}@{host}:{port}/{dbname}?charset=utf8'.format(

         username=USERNAME, password=PASSWORD, host=HOSTNAME, port=PORT, dbname=DATABASE

 )

 engine = create_engine(DB_URI)

 Base = declarative_base(engine)

 Session = sessionmaker(engine)

 session = Session()

 class Article(Base):

     __tablename__ = 'article'

     id = Column(Integer, primary_key=True, autoincrement=True)

     title = Column(String(50), nullable=False)

     price = Column(Float, nullable=False)

     content = Column(Text)

     def __repr__(self):

         return "Article<title: {}, price: {}>".format(self.title, self.price)

 # 1. equal

 article = session.query(Article).filter(Article.id == 1).first()

 print article

 # 2. not equal

 articles = session.query(Article).filter(Article.title != 'title0').all()

 print articles

 # 3. like, ilike->不区分大小写

 articles = session.query(Article).filter(Article.title.like('title%')).all()

 print articles

 # 4. in

 print '-' * 20

 articles = session.query(Article).filter(Article.title.in_(['title1', 'title2'])).all()

 print articles

 # 5. not in

 print '-' * 20

 articles = session.query(Article).filter(~Article.title.in_(['title2', 'title1'])).all()

 articles2 = session.query(Article).filter(Article.title.notin_(['title2', 'title1'])).all()

 print articles

 print articles2

 # 6. is null

 print '-' * 20

 articles = session.query(Article).filter(Article.content == None).all()

 print articles

 # 7. is not null

 print '-' * 20

 articles = session.query(Article).filter(Article.content != None).all()

 print articles

 # 8. and

 print '-' * 20

 articles = session.query(Article).filter(Article.title == 'title0', Article.content == 'test').all()

 articles_2 = session.query(Article).filter(and_(Article.title == 'title0', Article.content == 'test')).all()

 print articles

 print articles_2

 # 9. or

 print '-' * 20

 articles = session.query(Article).filter(or_(Article.title == 'title1', Article.content == 'test')).all()

 print articles