fastapi session with sqlalchemy bugging out

Question

I'm using fastapi to create a basic API to do some statistics from a postgres database.

I have just started using sqlalchemy as I want to do connection pooling, and based off of my googling it seems the route to go down.

I've implemented this in my main.py file,

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

Then using depends from fastapi with my URL params,

async def get_data(xxx ,db: SessionLocal = Depends(get_db)):
   conn = db()

With the sessions function being,

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

from environment.config import settings


SQLALCHEMY_DATABASE_URL = settings.DATABASE_URL
engine = create_engine(SQLALCHEMY_DATABASE_URL)

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

I'm receiving a type error of SessionLocal not being callable, wondering what I'm missing here?

The issue I was having was when testing the API against being called, multiple calls to the API were essentially recreating a connection to the database, which was super laggy just testing locally - so wanting to make that...well work :)

Imports for Main.py

from plistlib import UID
import string
from typing import Optional

from pandas import concat

from fastapi import FastAPI, HTTPException, Header,Depends

from fastapi.middleware.cors import CORSMiddleware


from pydantic import BaseModel

from db.session import SessionLocal

from biometrics.TaskGet import GetSentiment, GetScheduled, GetAllActualSubstance, GetEmotions

import aggregate.biometric_aggregators as biometric_aggregators

import os

Based on answer below, I just let it use DB. Now I get this error weirdly though.

web_1  | AttributeError: 'Session' object has no attribute 'cursor'

Made sqlalchemy functions to do the same calls, now getting the same error as before.

I originally tried to just return the function without using pd.read_sql, but it didn't work - any idea on what I've done wrong here?

from sqlalchemy.orm import Session

import pandas as pd

from . import models


def sentimentDataframe(db: Session, user_id: str):
    Sentiment = pd.read_sql((get_sentiment(db,user_id)),con=db)
    Sentiment['created'] =pd.to_datetime(Sentiment['created'], unit='s')
    return Sentiment.set_index('created')


def get_sentiment(db: Session, user_id: str, skip: int = 0, limit: int = 100):
    return db.query(models.Sentiment).filter(models.Sentiment.user_id == user_id).order_by(models.Sentiment.created.desc()).offset(skip).limit(limit).all()

You're sending a db session to a function written to use a dbapi compatible connection. Use `db.bind` to get the underlying bound connection. See https://stackoverflow.com/questions/29525808/sqlalchemy-orm-conversion-to-pandas-dataframe for a full example (using the ORM layer as well) — MatsLindh, Apr 18 '22 at 09:25

score 2 · Accepted Answer · answered Apr 18 '22 at 08:37

2

You should not be doing this

conn = db()

as db is already an instance of session

You can already use it like so

db.add(<SQLAlchemy Base Instance>)
db.commit()

answered Apr 18 '22 at 08:37

sudden_appearance

1,968
1
4
15

Thanks - When i do this, I get this error now, – LeCoda Apr 18 '22 at 08:38
web_1 | AttributeError: 'Session' object has no attribute 'cursor' – LeCoda Apr 18 '22 at 08:38
What is cursor? Update your question then) – sudden_appearance Apr 18 '22 at 08:39
Ahhh, I am not sure what a cursor actually is :) – LeCoda Apr 18 '22 at 08:40
Provide full code snippet for `get_data` then, please – sudden_appearance Apr 18 '22 at 08:41
have done above, should probably be more mindful with SQL queries but eh – LeCoda Apr 18 '22 at 08:43
1

I think you should either rewrite your raw sql to sqlalchemy pythonic sql query builder (or much better - to orm queries) or use `psycopg2` instead of sqlalchemy's session (I do not suggest using that as it's plain headache to integrate it with sqlalchemy and fastapi) – sudden_appearance Apr 18 '22 at 08:46
Ahhhh, so i can't just use plain old SQL queries? I feel like learning an ORM versus using SQL and building that knowledge is a better bad idea overall – LeCoda Apr 18 '22 at 08:47
SQLAlchemy does not only provide ORM for building sql, but transactions, local sessions, connection pools and more. Using raw SQL and default Postgres driver (`psycopg2`) does not give you any of that. And most of the times writing ORM queries is much easier, faster and more robust. SQLAlchemy core provides very easy to understand query builder as it looks as native sql in pythonic syntax – sudden_appearance Apr 18 '22 at 08:53
Made changes to use the ORM based on your advice, still not working :/ – LeCoda Apr 18 '22 at 15:06

fastapi session with sqlalchemy bugging out

Imports for Main.py

1 Answers1