Open Spenhouet opened 2 years ago
I used JSON type
from typing import List
from sqlmodel import Field, Session, SQLModel, create_engine, JSON, Column
class Block(SQLModel, table=True):
id: int = Field(..., primary_key=True)
values: List[str] = Field(sa_column=Column(JSON))
# Needed for Column(JSON)
class Config:
arbitrary_types_allowed = True
engine = create_engine("sqlite:///test_database.db", echo=True)
SQLModel.metadata.create_all(engine)
b = Block(id=0, values=['test', 'test2'])
with Session(engine) as session:
session.add(b)
session.commit()
with partial success as a workaround for a small project.
With Postgres, you can use an array of e.g. strings or ints. I'm having it as a Set on Python side to verify that don't get duplicates, but List works too. I think they are not supported in Sqlite though.
from sqlalchemy.dialects import postgresql #ARRAY contains requires dialect specific type
tags: Optional[Set[str]] = Field(default=None, sa_column=Column(postgresql.ARRAY(String())))
(...)
tagged = session.query(Item).filter(Item.tags.contains([tag]))
With Postgres, you can use an array of e.g. strings or ints. I'm having it as a Set on Python side to verify that don't get duplicates, but List works too. I think they are not supported in Sqlite though.
from sqlalchemy.dialects import postgresql #ARRAY contains requires dialect specific type tags: Optional[Set[str]] = Field(default=None, sa_column=Column(postgresql.ARRAY(String()))) (...) tagged = session.query(Item).filter(Item.tags.contains([tag]))
Thank you @antont !! perfectly work for postgres database
Why this is not supported by default?
I mean, is it possible for the user to accomplish the sames as OP wants but without using values: List[str]
, maybe representing it on another way that SQLModel allows
I ended up doing what @mkarbo suggested
@FilipeMarch - I guess one issue is that SQLite does not have arrays, whereas Postgres does. I'm using List but it means I can't use SQLite. Which is fine in our case, we need pg support only.
Are there any updates on this?
Are there any updates on this?
This is probably not that high on the priority list as there are workarounds, but even just having a list
column use a Column(JSON)
under the hood and not needing arbitrary_types_allowed
in the Pydantic config would be a massive upgrade.
Although of course, it would be best to use Column(ARRAY(...))
when possible, but that's probably a bit harder
It's not perfect, as it introduces code redundancy. Assume you have a model for Fastapi endpoint input validation:
class ItemBase(SQLModel):
heroes: list[Hero]
names: list[str] | None
value: int
Then you have to redeclare those in your actual SQLModel table:
class Item(ItemBase, table=True):
heroes: list[Hero] = Relationship(back_populates="item")
names: list[str] = Field(sa_column=Column(ARRAY(String), nullable=True))
@tiangolo this would be alot cleaner to have it recognized out of the box! :)
First Check
Commit to Help
Example Code
Description
I'm trying to store a list or similar types directly to the database.
Currently this does not seem to be the case. The above example code gives me the following error:
Wanted Solution
I would like to directly use the List type (or similar types like Dicts) to store data to a database column. I would expect SQLModel to serialize them.
Wanted Code
Alternatives
From another thread I tried to use this:
values: List[str] = Field(sa_column=Column(ARRAY(String)))
But this results in another error.
Operating System
Linux, Windows
Operating System Details
I'm working on the WSL.
SQLModel Version
0.0.4
Python Version
3.7.12
Additional Context