SQLALchemy如何将SQL语句编译为特定数据库方言

最近在一个使用fastapi+tortoise-orm的项目中，需要将orm的语句编译成特定数据库方言，但是查询了官方文档及一些资料却找不到合适的方法论😔，于是乎我就把目光放到了sqlalchemy身上，东找西找给我找着了。话不多说，请看代码。

方法1：

import sqlalchemy.dialects.mysql
from sqlalchemy import Integer, String, Column
from sqlalchemy.orm import declarative_base
from sqlalchemy.sql import insertBase = declarative_base()class Student(Base):__tablename__ = 'student'id = Column(Integer, primary_key=True, index=True)name = Column(String, index=True, comment="名称")age = Column(Integer, index=True, comment="年龄")def generate_sql(instance):stmt = insert(instance.__class__).values({c.name: getattr(instance, c.name) for c in instance.__table__.columns})return stmt.compile(dialect=sqlalchemy.dialects.mysql.dialect(), compile_kwargs={"literal_binds": True})

ikun = Student(id=1, name="ikun", age=30)
jay = Student(id=1, name="jay", age=26)
print(generate_sql(ikun)) # INSERT INTO student (id, name, age) VALUES (1, 'ikun', 30)
print(generate_sql(jay))  # INSERT INTO student (id, name, age) VALUES (1, 'jay', 26)

上面代码通过insert() 创建一个 INSERT 语句对象，然后获取模型实例的对应列的值，使用stmt.compile编译成mysql的方言。但是这个方法对于json类型的字段会编译失败，出现

sqlalchemy.exc.CompileError: No literal value renderer is available for literal value "['唱跳', 'rap', '篮球']" with datatype JSON

的错误提示。

因此，介绍下一个方法。

方法2：

import sqlalchemy.dialects.mysql
from sqlalchemy import Integer, String, Column, JSON, text
from sqlalchemy.orm import declarative_base
from sqlalchemy.sql.compiler import SQLCompilerBase = declarative_base()class Student(Base):__tablename__ = 'student'id = Column(Integer, primary_key=True, index=True)name = Column(String, index=True, comment="名称")age = Column(Integer, index=True, comment="年龄")hobby = Column(JSON, comment="爱好")def generate_sql(instance: Student) -> SQLCompiler:columnsmap = {c.name: getattr(instance, c.name) for c in instance.__table__.columns}columns = columnsmap.keys()stmt = text(f"INSERT INTO {instance.__tablename__} ({', '.join(columns)}) VALUES ({', '.join([f":{c}" for c in columns])});").bindparams(**columnsmap)return stmt.compile(dialect=sqlalchemy.dialects.mysql.dialect(), compile_kwargs={"literal_binds": True})

输出：

ikun = Student(id=1, name="ikun", age=30, hobby=json.dumps(["唱跳", "rap", "篮球"]))
jay = Student(id=1, name="jay", age=26, hobby=json.dumps(["唱歌", "足球"]))
print(generate_sql(ikun)) # INSERT INTO student (id, name, age, hobby) VALUES (1, 'ikun', 30, '["\\u5531\\u8df3", "rap", "\\u7bee\\u7403"]');
print(generate_sql(jay)) # INSERT INTO student (id, name, age, hobby) VALUES (1, 'jay', 26, '["\\u5531\\u6b4c", "\\u8db3\\u7403"]');

如果text() 创建原始SQL文本语句，使用参数占位符 :name, :age, :hobby，bindparams() 将实际值绑定到SQL语句中的占位符，dialect=sqlalchemy.dialects.mysql.dialect() 指定使用MySQL方言。使用这种生成也不需要考虑特殊字符转义的问题。