Timeseries modeling from a pandas dataframe

[aropele]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question 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

import pandas as pd
from sqlmodel import Field, Session, SQLModel, create_engine
from datetime import datetime


class StockTimeSeries(SQLModel, table=True):
    id: int = Field(primary_key=True)
    symbol: str
    timestamp: datetime
    price: float


sqlite_file_name = "stock_database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"
engine = create_engine(sqlite_url, echo=True)


def create_db_and_tables():
    SQLModel.metadata.create_all(engine)


def insert_dataframe_to_db(df):
    with Session(engine) as session:
        for _, row in df.iterrows():
            time_series_entry = StockTimeSeries(
                id=row["id"],
                symbol=row["symbol"],
                timestamp=row["timestamp"],
                price=row["price"],
            )
            session.add(time_series_entry)

        session.commit()


def main():
    create_db_and_tables()

    # Simulating a Pandas DataFrame for demonstration
    data = {
        "id": [1, 2, 3],
        "symbol": ["AAPL", "AAPL", "AAPL"],
        "timestamp": [
            datetime(2023, 8, 1, 9, 0),
            datetime(2023, 8, 1, 10, 0),
            datetime(2023, 8, 1, 11, 0),
        ],
        "price": [150.0, 155.0, 160.0],
    }
    df = pd.DataFrame(data)

    insert_dataframe_to_db(df)


if __name__ == "__main__":
    main()

Description

Hi, I just started using sqlmodel and I would like to know if this way of managing time series is the optimal one.
In particular I need to write timeseries that are stored in side a pandas df. So:

• I iterate over the dataframe and trasforming each row to a StockTimeSeries instance, before writing each instance
• the class StockTimeSeries is modeling a single time series row, not an entire series

Is this correct or there are some other way to solve this requirement?

Thanks.

Operating System

Windows

Operating System Details

No response

SQLModel Version

0.0.8

Python Version

3.9

Additional Context

No response

[YuriiMotov]

Your approach looks good.

For big data volume, you can use bulk insert:

data = [
    {
        "id": row["id"],
        "symbol": row["symbol"],
        ...
    }
    for _, row in df.iterrows()
]

session.exec(StockTimeSeries, params=data)
session.commit()