SQLModel

This, is quite a useful library. A couple of things that I need to remember though

Converting the result of a query to a dataframe.

I think SQLModel is designed so that you should be returning an instance of a class that you've defined around a table in your database. Sometimes though, you want to get the results into a dataframe and do something with the data before you return it. In which case figuring out how to dump it into a data frame is a royal pain in the ass.

I think, SQL and pydantic and fastapi work it all out amongst themselves when you're returning classes, but to drill down into a result is painful - it's a chunkedIteratorResult, wrapped around a list of SQLAlchemy row objects, which are maybe tuples with a single entry that contain an instance of the class which can be turned into a dict. As I say,l annoying.

Documented almost nowhere, and knowing how these things go, probably not long for this world, is a function called .model_dump() that either SQLModel or SQLAlchemy (which library does what is a little blurry to me still) attaches to the table associated classes. I mean, it could even be Pydantic that does that, who knows. <shrugs>.

Anyway, using model_dump(), you can dump each instance of the class in a row into a dict, which can then be turned into dataframe. Useful to remember.

class One(SQLModel, table=True):
    __tablename__ = "tableOne"
    id: Optional[str] = Field(default=None, primary_key=True)
    propertyOneA: str
    propertyOneB: str

class Two(SQLModel, table=True):
    __tablename__ = "tableTwo"
    id: Optional[str] = Field(default=None, primary_key=True)
    propertyTwoA: str
    PropertyTwoB: str

with connector.get_session() as session:
    statement = select(One, Two).where(
        One.sample_id == Two.sample_id, 
    )

    result = session.execute(statement)
    rows = result.fetchall()

    combined_data = []
    for row in rows:
        tableOne_dict = row.One.model_dump()
        tableTwo_dict = row.Two.model_dump()
        combined_dict = {**tableOne_dict, **tableTwo_dict}
        combined_data.append(combined_dict)

    # Converting to DataFrame
    df = pd.DataFrame(combined_data)