Raw SQL with FastApi and SqlAlchemy (get all columns)

I have a simple FastApi endpoint that connects to a MySQL database using SqlAlchemy (based of the tutorial: https://fastapi.tiangolo.com/tutorial/sql-databases/)

I create a session using:

engine = create_engine(
    SQLALCHEMY_DATABASE_URL
)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

I create the dependency:

def get_db():
   db = SessionLocal()
   try:
      yield db
   finally:
      db.close()

In my route I want to execute an arbitrary SQL statement but I am not sure how to handle session, connection, cursor etc. correctly (including closing) which I learned the hard way is super important for correct performance

@app.get("/get_data")
def get_data(db: Session = Depends(get_db)):
    ???

Ultimately the reason for this is that my table contains machine learning features with columns that are undetermined beforehand. If there is a way to define a Base model with "all columns" that would work too, but I couldnt find that either.

Solution

I solved this using the https://www.encode.io/databases/ package instead. It handles all connections / sessions etc. under the hood. Simplified snippet:

database = databases.Database(DATABASE_URL)
    
@app.get("/read_db")
async def read_db():
    data = await database.fetch_all("SELECT * FROM USER_TABLE")
    return data