sql_openapi.py

from sqlalchemy import create_engine
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session
from openapi_core import create_spec
from openapi_core.schema.schemas.models import Schema
from openapi_core.shortcuts import RequestBody, Response

# Connect to the SQL database
engine = create_engine("postgresql://user:password@host/dbname")
Base = automap_base()
Base.prepare(engine, reflect=True)

# Create a session to query the database
session = Session(engine)

# Get a list of all the table names in the schema
table_names = Base.metadata.tables.keys()

# Initialize the OpenAPI specification
spec = create_spec("3.0.2")

# Iterate over the table names to create endpoints for each table
for table_name in table_names:
    table = Base.classes[table_name]
    endpoint = f'/{table_name}'
    columns = table.__table__.columns.keys()

    # Create the GET endpoint
    request_body = RequestBody(content={'application/json': Schema(type='object', properties={col: Schema(type='string') for col in columns})})
    spec.path(path=endpoint, operations={'get': {'responses': {'200': {'description': 'Success', 'content': {'application/json': {'schema': request_body}}}}}})
    
    # Create the POST endpoint
    request_body = RequestBody(content={'application/json': Schema(type='object', properties={col: Schema(type='string') for col in columns})})
    response_body = Response(content={'application/json': Schema(type='string')})
    spec.path(path=endpoint, operations={'post': {'requestBody': request_body, 'responses': {'201': {'description': 'Success', 'content': response_body}}}})