Fast API

Basic example of how to create a FastAPI application, connect it to a database, and pass values using User-Defined Types (UDTs) in stored procedures. For this example, I'll use PostgreSQL as the database.

First, you need to install FastAPI and databases library:

pip install fastapi

pip install databases

from fastapi import FastAPI, HTTPException

import databases

import sqlalchemy

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, ARRAY

# Database Connection

DATABASE_URL = "postgresql://username:password@localhost/db_name"

database = databases.Database(DATABASE_URL)

metadata = MetaData()

# Define your table

stores = Table(

    "stores",

    metadata,

    Column("id", Integer, primary_key=True),

    Column("name", String),

    Column("items", ARRAY(Integer)),

)

# Create FastAPI app

app = FastAPI()

# Connect to the database when the application starts

@app.on_event("startup")

async def startup():

    await database.connect()

# Disconnect from the database when the application stops

@app.on_event("shutdown")

async def shutdown():

    await database.disconnect()

# Define stored procedure to add a store

async def add_store(name: str, items: list):

    query = stores.insert().values(name=name, items=items)

    return await database.execute(query)

# Define stored procedure to get all stores

async def get_stores():

    query = stores.select()

    return await database.fetch_all(query)

# FastAPI routes

@app.post("/stores/")

async def create_store(name: str, items: list):

    store_id = await add_store(name, items)

    return {"id": store_id, "name": name, "items": items}

@app.get("/stores/")

async def read_stores():

    return await get_stores()

# Run the FastAPI application with uvicorn

# uvicorn main:app --reload

Here : 

• We create a FastAPI application.

• Connect to the PostgreSQL database using the databases library.

• Define a table stores to store store information, including a column items which is an array of integers.

• Define stored procedures to add a store and retrieve all stores.

• Implement FastAPI routes to create and retrieve stores.

• Ensure the database connection is established and closed properly using event handlers.

• You can run the application using uvicorn with the command provided in the comment.

Make sure to replace username, password, and db_name in the DATABASE_URL variable with your actual PostgreSQL database credentials and database name. Additionally, modify the code as needed to fit your specific requirements for handling UDTs in stored procedures.