Database&APIの作成 #SQLite3

本日はSQLite3を利用して、データベースの環境設定と作成成功しました。

今のところはデータがまだまだ書いていませんので、これから頑張ります。

データベースを作った後、APIの作成です。
一応、下で書いたコードは完成品です。

from fastapi import FastAPI, HTTPException
from pydantic import BaseModel
import sqlite3

app = FastAPI()

class Word(BaseModel):
    vocab: str

def db_connection():
    conn = None
    try:
        conn = sqlite3.connect("gameapi.sqlite")
    except sqlite3.Error as e:
        print(e)
    return conn

@app.get('/words/', response_model=list[dict])
def words():
    conn = db_connection()
    cursor = conn.cursor()

    cursor.execute("SELECT * FROM words")
    words = [
        {"id": row[0], "vocab": row[1], "definition": row[2], "element": row[3], "rank": row[4], "type": row[5]}
        for row in cursor.fetchall()
    ]
    
    if not words:
        raise HTTPException(status_code=404, detail="No vocabulary found")
    
    return words

@app.get("/words/meaning/{vocab}", response_model=str)
def meaning(vocab: str):
    conn = db_connection()
    cursor = conn.cursor()
    
    cursor.execute("SELECT * FROM words WHERE vocab=?", (vocab,))
    word = cursor.fetchone()

    if not word:
        raise HTTPException(status_code=404, detail=f"No vocabulary found for '{vocab}'")

    vocab_def = word[2]
    return vocab_def

@app.get("/words/element/{vocab}", response_model=str)
def element(vocab: str):
    conn = db_connection()
    cursor = conn.cursor()
    
    cursor.execute("SELECT * FROM words WHERE vocab=?", (vocab,))
    word = cursor.fetchone()

    if not word:
        raise HTTPException(status_code=404, detail=f"No vocabulary found for '{vocab}'")

    vocab_element = word[3]
    return vocab_element

@app.get("/words/rank/{vocab}", response_model=int)
def rank(vocab: str):
    conn = db_connection()
    cursor = conn.cursor()
    
    cursor.execute("SELECT * FROM words WHERE vocab=?", (vocab,))
    word = cursor.fetchone()

    if not word:
        raise HTTPException(status_code=404, detail=f"No vocabulary found for '{vocab}'")

    vocab_rank = word[4]
    return vocab_rank

@app.get("/words/type/{vocab}", response_model=str)
def word_type(vocab: str):
    conn = db_connection()
    cursor = conn.cursor()
    
    cursor.execute("SELECT * FROM words WHERE vocab=?", (vocab,))
    word = cursor.fetchone()

    if not word:
        raise HTTPException(status_code=404, detail=f"No vocabulary found for '{vocab}'")

    vocab_type = word[5]
    return vocab_type

if __name__ == "__main__":
    import uvicorn
    uvicorn.run(app, host="127.0.0.2", port=7780, debug=True)


#TO RUN THE APP USE THE COMMAND BELOW
# $ uvicorn main:app --reload