oceanicdayi/sqlite_api

1# app.py2 3from fastapi import FastAPI, HTTPException4from pydantic import BaseModel5from typing import Union6import sqlite37import os8import pandas as pd # Import pandas for CSV reading9 10# Define the Pydantic model for Item creation/update11class Item(BaseModel):12    date: str13    time: str14    lat: float15    lon: float16    depth: float17    ML: float18    nstn: int19    dmin: float20    gap: int21    trms: float22    ERH: float23    ERZ: float24    fixed: str25    nph: int26    quality: str27 28# Define the Pydantic model for Item update specifically (allowing partial updates)29class ItemUpdate(BaseModel):30    date: Union[str, None] = None31    time: Union[str, None] = None32    lat: Union[float, None] = None33    lon: Union[float, None] = None34    depth: Union[float, None] = None35    ML: Union[float, None] = None36    nstn: Union[int, None] = None37    dmin: Union[float, None] = None38    gap: Union[int, None] = None39    trms: Union[float, None] = None40    ERH: Union[float, None] = None41    ERZ: Union[float, None] = None42    fixed: Union[str, None] = None43    nph: Union[int, None] = None44    quality: Union[str, None] = None45 46app = FastAPI()47 48# Define the path for your SQLite database file49DATABASE_FILE = os.path.join(os.getcwd(), "data.db")50CSV_FILE_PATH = "GDMScatalog.csv" # Path to your CSV file51 52def get_db_connection():53    """Establishes and returns a SQLite database connection."""54    try:55        conn = sqlite3.connect(DATABASE_FILE)56        conn.row_factory = sqlite3.Row57        return conn58    except sqlite3.Error as err:59        print(f"Error connecting to database: {err}")60        raise HTTPException(status_code=500, detail="Database connection error")61 62@app.on_event("startup")63async def startup_event():64    """65    Initializes the database: creates the 'items' table if it doesn't exist.66    Also, populates the database with data from the CSV file if the table is empty.67    This runs once when the FastAPI application starts.68    """69    conn = None70    try:71        conn = get_db_connection()72        cursor = conn.cursor()73 74        # Create table if it doesn't exist75        cursor.execute("""76            CREATE TABLE IF NOT EXISTS items (77                id INTEGER PRIMARY KEY AUTOINCREMENT,78                date TEXT NOT NULL,79                time TEXT NOT NULL,80                lat REAL NOT NULL,81                lon REAL NOT NULL,82                depth REAL NOT NULL,83                ML REAL NOT NULL,84                nstn INTEGER NOT NULL,85                dmin REAL NOT NULL,86                gap INTEGER NOT NULL,87                trms REAL NOT NULL,88                ERH REAL NOT NULL,89                ERZ REAL NOT NULL,90                fixed TEXT NOT NULL,91                nph INTEGER NOT NULL,92                quality TEXT NOT NULL93            )94        """)95        conn.commit()96        print(f"Database table 'items' checked/created successfully at {DATABASE_FILE}.")97 98        # Check if the table is empty and populate from CSV99        cursor.execute("SELECT COUNT(*) FROM items")100        count = cursor.fetchone()[0]101 102        if count == 0:103            print("Table 'items' is empty. Populating from CSV file...")104            if os.path.exists(CSV_FILE_PATH):105                try:106                    df = pd.read_csv(CSV_FILE_PATH)107                    # Convert DataFrame to a list of tuples for insertion108                    # Ensure the order of columns matches the INSERT query109                    data_to_insert = [110                        (row['date'], row['time'], row['lat'], row['lon'], row['depth'],111                         row['ML'], row['nstn'], row['dmin'], row['gap'], row['trms'],112                         row['ERH'], row['ERZ'], row['fixed'], row['nph'], row['quality'])113                        for index, row in df.iterrows()114                    ]115 116                    insert_query = """117                        INSERT INTO items (date, time, lat, lon, depth, ML, nstn, dmin, gap, trms, ERH, ERZ, fixed, nph, quality)118                        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)119                    """120                    cursor.executemany(insert_query, data_to_insert)121                    conn.commit()122                    print(f"Successfully inserted {len(data_to_insert)} records from {CSV_FILE_PATH}.")123                except Exception as e:124                    print(f"Error populating database from CSV: {e}")125                    conn.rollback() # Rollback if CSV insertion fails126            else:127                print(f"CSV file not found at {CSV_FILE_PATH}. Database not populated.")128        else:129            print(f"Table 'items' already contains {count} records. Skipping CSV import.")130 131    except Exception as e:132        print(f"Error during database startup: {e}")133    finally:134        if conn:135            conn.close()136 137@app.get("/")138async def root():139    """Root endpoint for the API."""140    return {"message": "Welcome to the API! https://cwadayi-sqlite-api.hf.space/items/"}141 142@app.post("/items/")143async def create_item(item: Item):144    """Creates a new item in the database."""145    conn = get_db_connection()146    cursor = conn.cursor()147    try:148        query = """149            INSERT INTO items (date, time, lat, lon, depth, ML, nstn, dmin, gap, trms, ERH, ERZ, fixed, nph, quality)150            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)151        """152        cursor.execute(query, (153            item.date,154            item.time,155            item.lat,156            item.lon,157            item.depth,158            item.ML,159            item.nstn,160            item.dmin,161            item.gap,162            item.trms,163            item.ERH,164            item.ERZ,165            item.fixed,166            item.nph,167            item.quality168        ))169        conn.commit()170        return {"message": "Item created successfully", "id": cursor.lastrowid}171    except Exception as e:172        conn.rollback()173        raise HTTPException(status_code=500, detail=f"Error creating item: {e}")174    finally:175        cursor.close()176        conn.close()177 178@app.get("/items/")179async def read_items():180    """Retrieves all items from the database."""181    conn = get_db_connection()182    cursor = conn.cursor()183    try:184        cursor.execute("SELECT * FROM items")185        items = cursor.fetchall()186        return {"items": [dict(item) for item in items]}187    except Exception as e:188        raise HTTPException(status_code=500, detail=f"Error reading items: {e}")189    finally:190        cursor.close()191        conn.close()192 193@app.get("/items/{item_id}")194async def read_item(item_id: int):195    """Retrieves a single item by its ID."""196    conn = get_db_connection()197    cursor = conn.cursor()198    try:199        cursor.execute("SELECT * FROM items WHERE id = ?", (item_id,))200        item = cursor.fetchone()201        if item is None:202            raise HTTPException(status_code=404, detail="Item not found")203        return dict(item)204    except Exception as e:205        raise HTTPException(status_code=500, detail=f"Error reading item: {e}")206    finally:207        cursor.close()208        conn.close()209 210@app.put("/items/{item_id}")211async def update_item(item_id: int, item: ItemUpdate):212    """Updates an existing item by its ID."""213    conn = get_db_connection()214    cursor = conn.cursor()215    try:216        updates = []217        params = []218 219        if item.date is not None:220            updates.append("date = ?")221            params.append(item.date)222        if item.time is not None:223            updates.append("time = ?")224            params.append(item.time)225        if item.lat is not None:226            updates.append("lat = ?")227            params.append(item.lat)228        if item.lon is not None:229            updates.append("lon = ?")230            params.append(item.lon)231        if item.depth is not None:232            updates.append("depth = ?")233            params.append(item.depth)234        if item.ML is not None:235            updates.append("ML = ?")236            params.append(item.ML)237        if item.nstn is not None:238            updates.append("nstn = ?")239            params.append(item.nstn)240        if item.dmin is not None:241            updates.append("dmin = ?")242            params.append(item.dmin)243        if item.gap is not None:244            updates.append("gap = ?")245            params.append(item.gap)246        if item.trms is not None:247            updates.append("trms = ?")248            params.append(item.trms)249        if item.ERH is not None:250            updates.append("ERH = ?")251            params.append(item.ERH)252        if item.ERZ is not None:253            updates.append("ERZ = ?")254            params.append(item.ERZ)255        if item.fixed is not None:256            updates.append("fixed = ?")257            params.append(item.fixed)258        if item.nph is not None:259            updates.append("nph = ?")260            params.append(item.nph)261        if item.quality is not None:262            updates.append("quality = ?")263            params.append(item.quality)264 265        if not updates:266            raise HTTPException(status_code=400, detail="No fields to update provided")267 268        query = f"UPDATE items SET {', '.join(updates)} WHERE id = ?"269        params.append(item_id)270 271        cursor.execute(query, tuple(params))272        conn.commit()273 274        if cursor.rowcount == 0:275            raise HTTPException(status_code=404, detail="Item not found")276        return {"message": "Item updated successfully"}277    except HTTPException:278        raise279    except Exception as e:280        conn.rollback()281        raise HTTPException(status_code=500, detail=f"Error updating item: {e}")282    finally:283        cursor.close()284        conn.close()285 286@app.delete("/items/{item_id}")287async def delete_item(item_id: int):288    """Deletes an item by its ID."""289    conn = get_db_connection()290    cursor = conn.cursor()291    try:292        cursor.execute("DELETE FROM items WHERE id = ?", (item_id,))293        conn.commit()294        if cursor.rowcount == 0:295            raise HTTPException(status_code=404, detail="Item not found")296        return {"message": "Item deleted successfully"}297    except Exception as e:298        conn.rollback()299        raise HTTPException(status_code=500, detail=f"Error deleting item: {e}")300    finally:301        cursor.close()302        conn.close()