sqlmodel
sqlmodel copied to clipboard
pathlib.Path is probably an unsupported type
First Check
- [X] I added a very descriptive title to this issue.
- [X] I used the GitHub search to find a similar issue and didn't find it.
- [X] I searched the SQLModel documentation, with the integrated search.
- [X] I already searched in Google "How to X in SQLModel" and didn't find any information.
- [X] I already read and followed all the tutorial in the docs and didn't find an answer.
- [X] I already checked if it is not related to SQLModel but to Pydantic.
- [X] I already checked if it is not related to SQLModel but to SQLAlchemy.
Commit to Help
- [X] I commit to help with one of those options 👆
Example Code
from pathlib import Path
from typing import Optional
import pytest
from sqlmodel import VARCHAR, Column, Field, Session, SQLModel, create_engine
class MyFile(SQLModel, table=True):
id: Optional[int] = Field(default=None, primary_key=True)
path: Path = Field(sa_column=Column(VARCHAR))
@pytest.fixture
def session() -> Session:
engine = create_engine(
'sqlite://', connect_args={'check_same_thread': False})
SQLModel.metadata.create_all(engine)
with Session(engine) as session:
yield session
def test_myfile(session: Session, tmp_path: Path):
session.add(MyFile(path=tmp_path / 'test.txt'))
session.commit()
Description
- Create a model with a field of type
pathlib.Path
. - Create a table where the field is mapped as a VARCHAR.
- Insert into the table.
- Select from table and have the field mapped back as a
pathlib.Path
.
Operating System
Linux
Operating System Details
Ubuntu 22.04.
SQLModel Version
0.0.6
Python Version
3.10
Additional Context
Logs from the test case:
2022-08-10 10:56:17,187 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-10 10:56:17,188 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("myfile")
2022-08-10 10:56:17,188 INFO sqlalchemy.engine.Engine [raw sql] ()
2022-08-10 10:56:17,188 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info("myfile")
2022-08-10 10:56:17,188 INFO sqlalchemy.engine.Engine [raw sql] ()
2022-08-10 10:56:17,189 INFO sqlalchemy.engine.Engine
CREATE TABLE myfile (
path VARCHAR,
id INTEGER,
PRIMARY KEY (id)
)
2022-08-10 10:56:17,189 INFO sqlalchemy.engine.Engine [no key 0.00012s] ()
2022-08-10 10:56:17,189 INFO sqlalchemy.engine.Engine COMMIT
-------------------------------------------------------------------------- Captured stderr setup ---------------------------------------------------------------------------
sqlalchemy.engine.Engine - BEGIN (implicit)
sqlalchemy.engine.Engine - PRAGMA main.table_info("myfile")
sqlalchemy.engine.Engine - [raw sql] ()
sqlalchemy.engine.Engine - PRAGMA temp.table_info("myfile")
sqlalchemy.engine.Engine - [raw sql] ()
sqlalchemy.engine.Engine -
CREATE TABLE myfile (
path VARCHAR,
id INTEGER,
PRIMARY KEY (id)
)
sqlalchemy.engine.Engine - [no key 0.00012s] ()
sqlalchemy.engine.Engine - COMMIT
---------------------------------------------------------------------------- Captured log setup ----------------------------------------------------------------------------
INFO sqlalchemy.engine.Engine:base.py:953 BEGIN (implicit)
INFO sqlalchemy.engine.Engine:base.py:1772 PRAGMA main.table_info("myfile")
INFO sqlalchemy.engine.Engine:base.py:1777 [raw sql] ()
INFO sqlalchemy.engine.Engine:base.py:1772 PRAGMA temp.table_info("myfile")
INFO sqlalchemy.engine.Engine:base.py:1777 [raw sql] ()
INFO sqlalchemy.engine.Engine:base.py:1772
CREATE TABLE myfile (
path VARCHAR,
id INTEGER,
PRIMARY KEY (id)
)
INFO sqlalchemy.engine.Engine:base.py:1777 [no key 0.00012s] ()
INFO sqlalchemy.engine.Engine:base.py:1013 COMMIT
--------------------------------------------------------------------------- Captured stdout call ---------------------------------------------------------------------------
2022-08-10 10:56:17,194 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-10 10:56:17,195 INFO sqlalchemy.engine.Engine INSERT INTO myfile (path) VALUES (?)
2022-08-10 10:56:17,195 INFO sqlalchemy.engine.Engine [generated in 0.00029s] (PosixPath('/tmp/pytest-of-vscode/pytest-187/test_myfile0/test.txt'),)
2022-08-10 10:56:17,195 INFO sqlalchemy.engine.Engine ROLLBACK
--------------------------------------------------------------------------- Captured stderr call ---------------------------------------------------------------------------
sqlalchemy.engine.Engine - BEGIN (implicit)
sqlalchemy.engine.Engine - INSERT INTO myfile (path) VALUES (?)
sqlalchemy.engine.Engine - [generated in 0.00029s] (PosixPath('/tmp/pytest-of-vscode/pytest-187/test_myfile0/test.txt'),)
sqlalchemy.engine.Engine - ROLLBACK
---------------------------------------------------------------------------- Captured log call -----------------------------------------------------------------------------
INFO sqlalchemy.engine.Engine:base.py:953 BEGIN (implicit)
INFO sqlalchemy.engine.Engine:base.py:1772 INSERT INTO myfile (path) VALUES (?)
INFO sqlalchemy.engine.Engine:base.py:1777 [generated in 0.00029s] (PosixPath('/tmp/pytest-of-vscode/pytest-187/test_myfile0/test.txt'),)
INFO sqlalchemy.engine.Engine:base.py:981 ROLLBACK
========================================================================= short test summary info ==========================================================================
FAILED tests/test_misc.py::test_myfile - sqlalchemy.exc.InterfaceError: (sqlite3.InterfaceError) Error binding parameter 0 - probably unsupported type.
I don't know about this, but would not be surprising as a Path is not a string, but has also attributes like parents. It can be made from a string though, so that would at least work with the varchar in db.
Searched for the docs and happened to see this, might be a different thing though as is not with db code there. https://github.com/tiangolo/fastapi/issues/563
Ah the fix there is to make it jsonable: "This PR adds a case to jsonable_encoder so that pathlib.PurePath objects are serializable."
https://github.com/tiangolo/fastapi/pull/978
Other folks here use json db fields for some things I think, we haven't so far but just have strings and ints and such in the db like usually.
Am using strings for paths in the sqlmodel db models. Well or don't have paths but just filenames and paths come from runtime logic.
Any update on this ?
I think I can live with modeling Path
as str
for the moment and cast it back to Path
when I use the data, but something like Field(sa_column=Column(TEXT))
would obviously be much more elegant.
Can I somehow add the FastAPI fix to my code locally ?