CODEWITHSUNDEEP
pythonsqlalchemyfastapi
LeCoda
LeCoda

Reputation: 1016

fastapi session with sqlalchemy bugging out

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()

Upvotes: 0

Views: 2285

Answers (1)

sudden_appearance
sudden_appearance

Reputation: 2197

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()

Upvotes: 2

Related Questions