Skip to content

pathlib.Path is probably an unsupported type #397

Closed
Closed
pathlib.Path is probably an unsupported type#397
Labels
questionFurther information is requested

Description

@matutter
matutter
opened on Aug 10, 2022

First Check

  • I added a very descriptive title to this issue.
  • I used the GitHub search to find a similar issue and didn't find it.
  • I searched the SQLModel documentation, with the integrated search.
  • I already searched in Google "How to X in SQLModel" and didn't find any information.
  • I already read and followed all the tutorial in the docs and didn't find an answer.
  • I already checked if it is not related to SQLModel but to Pydantic.
  • I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

  • 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.

Activity

  1. matutter
    added
    questionFurther information is requested
    on Aug 10, 2022

  2. antont commented on Aug 10, 2022

    @antont
    antont
    on Aug 10, 2022

    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. fastapi/fastapi#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."

    fastapi/fastapi#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.

  3. dd-ssc commented on Mar 25, 2024

    @dd-ssc
    dd-ssc
    on Mar 25, 2024

    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 ?

  4. strfx commented on Oct 21, 2024

    @strfx
    strfx
    on Oct 21, 2024

    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 ?

    You maybe could use a SQLAlchemy Custom Type to convert Path to / from string:

    from pathlib import Path
from sqlalchemy.types import TypeDecorator, String

class PathType(TypeDecorator):
    impl = String

    def process_bind_param(self, value, dialect):
        if isinstance(value, Path):
            return str(value)
        return value

    def process_result_value(self, value, dialect):
        if value is not None:
            return Path(value)
        return value

    And use it like this:

    class MyFile(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    path: Path = Field(sa_column=Column(PathType))

    Stumbled on this thread by accident while researching a similar issue. Hope it helps!

  5. fastapi
    locked and limited conversation to collaborators on Aug 7, 2025
  6. YuriiMotov
    converted this issue into a discussion #1470 on Aug 7, 2025
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    questionFurther information is requested

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions