最近在一個使用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方言。使用這種生成也不需要考慮特殊字符轉義的問題。