因为后端框架重度使用ORM,鉴于SQLAlchemy官网没有中文翻译,加上经常会被墙,所以搬运些工具到博客上,以备后用。(代码均为示例,不能直接使用)
1、基础操作
# -*- coding: utf-8 -*-
'''等于(equals)'''
query.filter(User.name == 'ed')
'''不等于(not equals)'''
query.filter(User.name != 'ed')
'''模糊匹配(like)'''
query.filter(User.name.like('%ed%'))
'''大小写敏感模糊匹配(ilkie)'''
query.filter(User.name.ilike('%ed%'))
'''集合查询(in)'''
query.filter(User.name.in_(['ed', 'wendy', 'jack']))
'''集合查询取反(not in)'''
query.filter(~User.name.in_(['ed', 'wendy', 'jack']))
'''等于空(is null)'''
query.filter(User.name == None)
query.filter(User.name.is_(None))
'''非空(is not null)'''
query.filter(User.name != None)
query.filter(User.name.isnot(None))
'''与(and)'''
from sqlalchemy import and_
query.filter(and_(User.name == 'ed', User.fullname == 'Ed Jones'))
query.filter(User.name == 'ed', User.fullname == 'Ed Jones')
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'))
2、嵌入SQL语句
使用字符串代替变量名:
from sqlalchemy import text
for user in session.query(User).filter(text("id<224")).order_by(text("id")):
print(user.name)
带参查找:
for user in session.query(User).filter(text("id<:value and name=:name")).\
params(value=224, name='fred').\
order_by(User.id):
print(user.name)
将SQL执行结果映射到对应ORM:
for user in session.query(User).\
from_statement(text("SELECT * FROM users where name=:name")).\
params(name='ed'):
print(user.name)
将SQL查找多个列对应到ORM的对应属性中:
stmt = text("SELECT name, id, fullname, password FROM users where name=:name").\
columns(User.name, User.id, User.fullname, User.password)
for user in session.query(User).from_statement(stmt).params(name='ed'):
print(user.name)
仅返回查询的列(个人觉得有些多余,不如直接执行SQL语句简单)
stmt = text("SELECT name, id, password FROM users where name=:name").\
columns(User.name, User.id)
for user_tuple in session.query(User.id, User.name).from_statement(stmt).params(name='ed'):
print(user_tuple)