sqlalchemy 進階

filter() of session.query()

1.equals

query.filter(User.name =='zhanglinpeng')

2. not equals

query.filter(User.name != 'zhanglinpeng')

3.LIKE

query.filter(User.name.like(%ed%))

4.in

query.filter(User.name.in_([’ed’, ’wendy’, ’jack’]))

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

5.not in

query.filter(~User.name.in_([’ed’, ’wendy’, ’jack’]))

6.is NULL

query.filter(User.name == None)

7.is not NULL

query.filter(User.name != None)

8.AND
from sqlalchemy import and_

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

9.OR

from sqlalchemy import or_

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

10.match

query.filter(User.name.match(’wendy’))


query's method of database result

1.all()

query = session.query(User).filter(User.name.like(’%ed’)).order_by(User.id)

query.all()

返回一個列表,包含所以查詢到的結果

2.first()

query.first()

返回第一個結果

3.one()

提取所以的行,獲取到多行或者零行都會報錯

from sqlalchemy.orm.exc import MultipleResultsFound,NoResultFound

try:

        user = query.one()

except MultipleResultsFound, e:

        print e

try:

        user = query.one()

except NoResultFound, e:

         print e

4.scalar()

query.scalar()

調用one(),如果成功,返回行的第一列

5.count()

結果計數

from sqlalchemy import func

session.query(func.count(User.name), User.name).group_by(User.name).all()

=//SELECT count(users.name) AS count_1, users.name AS users_name

FROM users GROUP BY users.name


SQL

for user in session.query(User).filter("id<224").order_by("id").all():

          print user.name

= //SELECT users.id AS users_id,users.name AS users_name,users.fullname AS users_fullname,users.password AS users_password FROM user

 WHERE id<224 ORDER BY id

session.query(User).filter("id<:value and name=:name").params(value=224,name=’fred’).order_by(User.id).one()

=//SELECT users.id AS users_id,users.name AS users_name,users.fullname AS users_fullname,users.password AS users_password FROM users

WHERE id<?and name=? ORDER BY users.id (224, ’fred’)

from sqlalchemy import func

ua = aliased(User)

q = q.from_self(User.id, User.name, ua.name).filter(User.name < ua.name).filter(func.length(ua.name) != func.length(User.name)).order_by("name").all()

=//SELECT anon_1.users_id AS anon_1_users_id,

anon_1.users_name AS anon_1_users_name,

users_1.name AS users_1_name FROM

(SELECT users.id AS users_id, users.name AS users_nam FROM users) AS anon_1

users AS users_1 

WHERE anon_1.users_name < users_1.name

AND length(users_1.name) != length(anon_1.users_name)

ORDER BY name

推荐阅读更多精彩内容

  • pip install sqlalchemy 1.安装mysqlapt-get install mysql-ser...
    残风疏影阅读 660评论 0 1
  • 转载,觉得这篇写 SQLAlchemy Core,写得非常不错。不过后续他没写SQLAlchemy ORM... ...
    非梦nj阅读 4,074评论 1 14
  • 2017.7.18日 常听到这一句话: “ 凭感觉 ” !我为什么打一个惊叹号(感叹号)? 人的脑海里对做事想事沒...
    爱花儿美阅读 68评论 0 1
  • 子曰:学而时习之,不亦说乎?有朋自远方来,不亦说乎?人不知而不愠,不亦乐乎?学习真的很快乐吗?如果不快乐哪里还可能...
    轻舞凋零阅读 151评论 0 0
  • 北京梦之翼传统文化家塾 学子今日成长的力量 : 1,郑淇同学今日...
    梦之翼教育的简书阅读 56评论 0 0
  • 我去过的地方不多,只有那么寥寥几处。但西塘我已去过两次。据说乌镇和西塘的景色极为相似,可我从未去过乌镇,甚至从未打...
    竹溪非梦阅读 366评论 8 6