目录
一、数据模型定义
在文件 models.py 中定义表 cat 的模型:
import sqlalchemy
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Cat(Base):
__tablename__ = "cat"
id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True, autoincrement=True)
name = sqlalchemy.Column(sqlalchemy.String(256), nullable=True)
addtime = sqlalchemy.Column(sqlalchemy.DateTime())
二、打印简单的 SQL
from sqlalchemy.orm import Query
import models
query = Query(models.Cat).filter(models.Cat.name == 'hellocat')
stat = query.statement
print(stat)
输出
SELECT cat.id, cat.name, cat.addtime
FROM cat
WHERE cat.name = :name_1
三、打印稍微复杂的 SQL
from sqlalchemy.orm import Query
import models
query = Query(models.Cat).filter(models.Cat.id > 10).order_by(models.Cat.name.desc()).offset(10).limit(5)
print(query.statement)
输出
SELECT cat.id, cat.name, cat.addtime
FROM cat
WHERE cat.id > :id_1 ORDER BY cat.name DESC
LIMIT :param_1 OFFSET :param_2
四、打印和方言相关的SQL
打印SQL方言,需要在 statement 编译的时候指定方言类型
- postgresql 方言:query.statement.compile(dialect=postgresql.dialect())
- mysql 方言:query.statement.compile(dialect=mysql.dialect())
from sqlalchemy.dialects import postgresql, mysql
from sqlalchemy.orm import Query
import models
query = Query(models.Cat).order_by(models.Cat.id.desc()).offset(10).limit(5)
print(query.statement.compile(dialect=postgresql.dialect()))
print(query.statement.compile(dialect=mysql.dialect()))
输出:
SELECT cat.id, cat.name, cat.addtime
FROM cat ORDER BY cat.id DESC
LIMIT %(param_1)s OFFSET %(param_2)s
SELECT cat.id, cat.name, cat.addtime
FROM cat ORDER BY cat.id DESC
LIMIT %s, %s
五、显示参数
在上面的例子中,我们发现,所有的参数都是占位符形式,我们传入的参数没有被带入SQL中。这是因为上面的例子中少加了参数: compile_kwargs
下面的代码可以打印带参数的SQL语句:
from sqlalchemy.dialects import postgresql, mysql
from sqlalchemy.orm import Query
import models
query = Query(models.Cat).order_by(models.Cat.id.desc()).offset(10).limit(5)
print(query.statement.compile(dialect=postgresql.dialect(), compile_kwargs={"literal_binds": True}))
输出:
SELECT cat.id, cat.name, cat.addtime
FROM cat ORDER BY cat.id DESC
LIMIT 5 OFFSET 10