我正在用FastAPI做我的个人投资组合API并决定尝试SQLModel. 感觉很直观,到目前为止我很喜欢它,但我遇到了一个问题,我已经挣扎了好几天,试图理解如何使它正确。
我有一个项目模型:
from datetime import datetime
from typing import List, Optional, Set
from sqlmodel import SQLModel, Field
class ProjectBase(SQLModel):
name: Optional[str]
summary: Optional[str]
description: Optional[str]
category: Set[str] = ()
award: Optional[str] = None
url: Optional[str] = None
published: datetime = datetime.utcnow()
image: str = "placeholderMainImage"
images: List[str] = []
learning: Optional[str]
tech: Optional[str]
tools: Optional[str]
class Project(ProjectBase, table=True):
id: int = Field(default=None, primary_key=True)
created_at: datetime = Field(default_factory=datetime.utcnow)
updated_at: datetime = Field(default_factory=datetime.utcnow)
FastAPI工作正常,我检查了localhost:8000/docs并使类型验证正确:
{
"name": "string",
"summary": "string",
"description": "string",
"category": [],
"award": "string",
"url": "string",
"published": "2021-10-04T20:43:26.472364",
"image": "placeholderMainImage",
"images": [],
"learning": "string",
"tech": "string",
"tools": "string",
"id": 0,
"created_at": "2021-10-04T21:01:30.048Z",
"updated_at": "2021-10-04T21:01:30.048Z"
}
当我用上面的查询发出POST请求时,我得到内部服务器错误:
invalid input for query argument $4: (expected str, got set)
不幸的是,SQLModel在创建表时将任何奇怪的类型转换为VARCHAR,使得无法使用List或Set功能:
CREATE TABLE project (
name VARCHAR,
summary VARCHAR,
description VARCHAR,
category VARCHAR,
award VARCHAR,
url VARCHAR,
published TIMESTAMP WITHOUT TIME ZONE,
image VARCHAR,
images VARCHAR,
learning VARCHAR,
tech VARCHAR,
tools VARCHAR,
id SERIAL,
created_at TIMESTAMP WITHOUT TIME ZONE,
updated_at TIMESTAMP WITHOUT TIME ZONE,
PRIMARY KEY (id)
)
我明白postgres有一些数组类型,如:integer[]和文本[]它可以处理类别和图像字段的这种情况。尝试手动更改表列类型,结果相同。
尝试将类别和图片作为str:
{
"detail": [
{
"loc": [
"body",
"category"
],
"msg": "value is not a valid set",
"type": "type_error.set"
},
{
"loc": [
"body",
"images"
],
"msg": "value is not a valid list",
"type": "type_error.list"
}
]
}
如果不能使用这些神奇的功能来处理我收到的数据,那将是多么令人难过。我在网上看了看,找不到任何相关的或使用列表和设置SQLModel的例子
我能做些什么来支持这种情况?PD:我也使用asyncpg我得到了它的工作列表,但不为设置在postgres数据库。问题在于何时将结果映射回Project类。这是我的完整代码。
from datetime import datetime
from typing import List, Optional, Set
from sqlalchemy.sql.schema import Column
from sqlmodel import SQLModel, Field, create_engine, Session, select, String, ARRAY
from fastapi import FastAPI
class ProjectBase(SQLModel):
name: Optional[str]
category: Set[str] = Field(default=None, sa_column=Column(ARRAY(String())))
images: List[str] = Field(default=None, sa_column=Column(ARRAY(String())))
class Project(ProjectBase, table=True):
id: int = Field(default=None, primary_key=True)
engine = create_engine("postgresql://postgres:mysecretpassword@localhost:5432/testdb")
new_proj = Project(
name=f"{str(datetime.time(datetime.utcnow()))}",
category=("hi", "hello", "cat3"),
images=["img1", "img2"]
)
app = FastAPI()
@app.on_event("startup")
def on_startup():
SQLModel.metadata.create_all(engine)
print("BEFORE REFRESH:", new_proj)
with Session(engine) as session:
session.add(new_proj)
session.commit()
session.refresh(new_proj)
print("AFTER REFRESH:", new_proj)
@app.get("/", response_model=List[Project])
def home():
with Session(engine) as session:
projects = session.exec(select(Project)).all()
return projects
我已经简化了您的Project
类。在启动uvicorn运行此命令后,我得到以下输出(参考@app.on_event("startup")
):
BEFORE REFRESH: name='17:17:38.090595' category={'hoi', 'cat3', 'hello'} images=['img1', 'img2'] id=None
AFTER REFRESH: id=7 category=['hoi', 'cat3', 'hello'] images=['img1', 'img2'] name='17:17:38.090595'
注意category
值的差异,对象从数据库刷新前以{(表示集合)开始,对象从数据库刷新后以[开始。
sqlalchemy
的某种自定义映射规则。但我对那个包裹了解得不够,无法真正理解我在寻找什么。希望这至少能帮到你!我学到了很多东西:)
我认为您应该考虑为这种数据库插入实现一个非关系数据库。
但是如果你坚持SQL模型有一个解决方案,所以检查以下链接:https://github.com/tiangolo/sqlmodel/issues/178