food_sql.py

import pandas as pd
from fasthtml.common import *

df = pd.read_csv(
    "ar_en_nutrition.csv",
    index_col=0,
)


def create_food_model(df):
    # Remove the 'Unnamed: 0' column if it exists
    if "Unnamed: 0" in df.columns:
        df = df.drop("Unnamed: 0", axis=1)

    # Create a dictionary of column names and their types
    dtype_map = {"int64": int, "object": str, "float64": float}

    # Start with the id column
    column_types = {"id": int}

    # Add other columns, excluding any id column from the DataFrame
    for col in df.columns:
        if col.lower() != "id":
            column_types[col] = dtype_map[str(df[col].dtype)]

    # Create the app with dynamically generated columns
    return fast_app(
        "data/foods.db",
        hdrs=[Style(":root { --pico-font-size: 100%; }")],
        pk="id",
        **column_types,
    )


def update_database(df):
    # Create the model based on DataFrame structure
    app, rt, foods, Food = create_food_model(df)

    # Convert DataFrame rows to Food objects, letting SQLite handle the ID
    for _, row in df.iterrows():
        row_dict = row.to_dict()
        if "id" in row_dict:
            del row_dict["id"]
        if "Unnamed: 0" in row_dict:
            del row_dict["Unnamed: 0"]
        foods.insert(Food(**row_dict))
    foods.enable_fts(["name", "arabic_name"])


try:
    app, rt, foods, Food = create_food_model(df)
    update_database(df)
    print("Database updated successfully!")
except Exception as e:
    print(f"Error updating database: {e}")

items = foods()
print(len(items))

for item in items[:5]:
    print(item.arabic_name, item.name, item.calories)

el = foods.search("banana")
for e in el:
    print(e["name"])
    print(e["arabic_name"])
    print(e["calories"])
    print(e)
    break