sqlalchemy使用tip

https://docs.sqlalchemy.org/en/latest/orm/tutorial.html

http://docs.sqlalchemy.org/en/latest/core/sqlelement.html

条件过滤操作

#equal
query.filter(User.name == 'ed')

#not equal
query.filter(User.name != 'ed')

#LIKE
query.filter(User.name.like('%ed%'))


#ILIKE (case-insensitive LIKE)
query.filter(User.name.ilike('%ed%'))
#For guaranteed case-insensitive comparisons, use ColumnOperators.ilike().

#IN
query.filter(User.name.in_(['ed', 'wendy', 'jack']))

query.filter(User.name.in_(
    session.query(User.name).filter(User.name.like('%ed%'))
))

#NOT IN
query.filter(~User.name.in_(['ed', 'wendy', 'jack']))

#IS NULL
query.filter(User.name == None)
or
query.filter(User.name.is_(None))

#IS NOT NULL
query.filter(User.name != None)
or
query.filter(User.name.isnot(None))

#AND
from sqlalchemy import and_

query.filter(and_(User.name == 'ed', User.fullname == 'Ed Jones'))
or
query.filter(User.name == 'ed', User.fullname == 'Ed Jones')
or
query.filter(User.name == 'ed').filter(User.fullname == 'Ed Jones')

#OR
from sqlalchemy import or_

query.filter(or_(User.name == 'ed', User.name == 'wendy'))

#MATCH
query.filter(User.name.match('wendy'))

#返回数组all()
>>> query = session.query(User).filter(User.name.like('%ed')).order_by(User.id)
>>> query.all()
[<User(name='ed', fullname='Ed Jones', password='f8s7ccs')>,
      <User(name='fred', fullname='Fred Flinstone', password='blah')>]

#返回scalar标量first()
>>> query.first()
<User(name='ed', fullname='Ed Jones', password='f8s7ccs')>

去重与降序

        live_channels = Live_channel.query 
            .distinct(Live_channel.name) 
            .with_entities(Live_channel.name, Live_channel.cname) 
            .order_by(Live_channel.name.desc()) 
            .all()

# distinct() 过滤某一字段
# 字段默认是asc（升序）排列，后面加入desc()为降序排列

#生成的sql
SELECT DISTINCT live_channel.name AS live_channel_name, live_channel.cname AS live_channel_cname 
FROM live_channel ORDER BY live_channel.name DESC

bulk insert & update & delete

# https://qiita.com/yukiB/items/d6a70da802cb5731dc01
http://docs.sqlalchemy.org/en/latest/orm/persistence_techniques.html?highlight=bulk%20operations

db.session.add_all()

Items.query.filter(Items.status=='1').update({'Items.stauts': '0'})

Items.query.filter(Items.status=='0').delete()