我正在try 使用SQLModel在数据库中插入记录,其中的数据如下所示. 一个House对象,它有一种 colored颜色 和许多位置. 地点也将与许多房屋相关联.输入内容为:
[
{
"color": "red",
"locations": [
{"type": "country", "name": "Netherlands"},
{"type": "municipality", "name": "Amsterdam"},
],
},
{
"color": "green",
"locations": [
{"type": "country", "name": "Netherlands"},
{"type": "municipality", "name": "Amsterdam"},
],
},
]
这里有一个我正在try 做的可重复的例子:
import asyncio
from typing import List
from sqlalchemy.ext.asyncio import create_async_engine
from sqlalchemy.orm import sessionmaker
from sqlmodel import Field, Relationship, SQLModel, UniqueConstraint
from sqlmodel.ext.asyncio.session import AsyncSession
DATABASE_URL = "sqlite+aiosqlite:///./database.db"
engine = create_async_engine(DATABASE_URL, echo=True, future=True)
async def init_db() -> None:
async with engine.begin() as conn:
await conn.run_sync(SQLModel.metadata.create_all)
SessionLocal = sessionmaker(
autocommit=False,
autoflush=False,
bind=engine,
class_=AsyncSession,
expire_on_commit=False,
)
class HouseLocationLink(SQLModel, table=True):
house_id: int = Field(foreign_key="house.id", nullable=False, primary_key=True)
location_id: int = Field(
foreign_key="location.id", nullable=False, primary_key=True
)
class Location(SQLModel, table=True):
id: int = Field(primary_key=True)
type: str # country, county, municipality, district, city, area, street, etc
name: str # Amsterdam, Germany, My Street, etc
houses: List["House"] = Relationship(
back_populates="locations",
link_model=HouseLocationLink,
)
__table_args__ = (UniqueConstraint("type", "name"),)
class House(SQLModel, table=True):
id: int = Field(primary_key=True)
color: str = Field()
locations: List["Location"] = Relationship(
back_populates="houses",
link_model=HouseLocationLink,
)
# other fields...
data = [
{
"color": "red",
"locations": [
{"type": "country", "name": "Netherlands"},
{"type": "municipality", "name": "Amsterdam"},
],
},
{
"color": "green",
"locations": [
{"type": "country", "name": "Netherlands"},
{"type": "municipality", "name": "Amsterdam"},
],
},
]
async def add_houses(payload) -> List[House]:
result = []
async with SessionLocal() as session:
for item in payload:
locations = []
for location in item["locations"]:
locations.append(Location(**location))
house = House(color=item["color"], locations=locations)
result.append(house)
session.add_all(result)
await session.commit()
asyncio.run(init_db())
asyncio.run(add_houses(data))
问题是,当我运行这段代码时,它试图将重复的Location对象与House对象一起插入.
我很乐意在这里使用relationship
,因为它使访问house.locations
变得非常容易.
然而,我一直无法想出如何阻止它try 插入重复的位置.理想情况下,我应该有一个映射器功能来执行get_or_create
个位置.
我所见过的最接近实现这一点的是SQLAlChemy的association proxy.但看起来SQLModel不支持这一点.
有谁有办法实现这一点吗?如果您知道如何使用SQLAlChemy而不是SQLModel来完成此任务,我将有兴趣看看您的解决方案.我还没有开始这个项目,所以如果它能让我的生活更轻松的话,我还不如使用SQLAlChemy.
我也试着调整了sa_relationship_kwargs
个,
sa_relationship_kwargs={
"lazy": "selectin",
"cascade": "none",
"viewonly": "true",
}
但这阻止了关联条目被添加到HouseLocationLink
表中.
任何指点都将不胜感激.即使这意味着我要彻底改变我的方法.
谢谢!