ORM应用

生成模板

pip install sqlacodegen

pip install pymysql

sqlacodegen mysql+pymysql://username:password@127.0.0.1:3306/db_name > models_tmp.py

操作实例

data = session.query(Emp).from_statement(text('select * from emp where id=2')).params(name='123').all()

生成单表

sqlacodegen --tables third_api "mysql+pymysql://username:password@host/db_name" >tmp.py

基础查询

等于查询：

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

不等于查询：

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

like查询：

query.filter(User.name.like('%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)

is not null查询

query.filter(User.name != None)

and查询：

from sqlalchemy import and_

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

# or send multiple expressions to .filter()
query.filter(User.name == 'ed', 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'))

order_by语句：

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

count计数

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

分页查询

session.query(Channel).filter(and_(
                filter_list
            )).order_by(desc(Channel.updateTime)).slice(offset, limit)