fastapi using ORM not able to convert to pandas

Question

I've been developing a fastapi way to query my database, instead of directly using SQL with pg.

For some reason, I'm having issues converting the ORM query returned values into something usable, within a pandas dataframe.

for example,

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

Is returning an error of

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

The sessions function is like this,

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)

My main.py file where I'm calling this has these imports and helper functions etc.

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 db.biometric import sentimentDataframe,expressionDataframe,scheduledDataframe


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

import aggregate.biometric_aggregators as biometric_aggregators

import os

app = FastAPI()


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

@app.get("/{uid}/{substance}/Emotions.json", status_code=200)
async def get_emotions(uid, substance,startDate:Optional[str] = None,endDate:Optional[str] = None, analysis:Optional[str] = None, emotion:Optional[str] = None, x_token: Optional[str] = Header(None, convert_underscores=False),db: SessionLocal = 
        Sentiment = sentimentDataframe(db,uid)
        Expressions = expressionDataframe(db,uid)

Confused about what exactly I'm doing wrong here?

You're trying to send a result set returned from `all` into a function that reads from SQL; what you're sending in isn't an DBAPI compatible connection, and thus, doesn't have a `.cursor` method. You already have the data fetched, there is no need to attempt to read it from an SQL connection _again_. As mentioned in a comment on your previous question about this, you can use `.bind` og `.statement` to still use `read_sql` if you want: https://stackoverflow.com/questions/29525808/sqlalchemy-orm-conversion-to-pandas-dataframe — MatsLindh, Apr 19 '22 at 09:52

score 1 · Answer 1 · answered May 04 '22 at 08:56

To load data from sql

df = pd.read_sql(query.statement, query.session.bind)

from pandas docs

pandas.read_sql(sql, con, index_col=None, coerce_float=True, params=None, parse_dates=None, columns=None, chunksize=None)

Let's update your code as below

def sentimentDataframe(db: Session, user_id: str):
    query = get_sentiment(db, user_id)
    Sentiment = pd.read_sql(query.statement, query.session.bind)
    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):
    query = db.query(models.Sentiment).filter(
        models.Sentiment.user_id == user_id
    ).order_by(
        models.Sentiment.created.desc()
    ).offset(skip).limit(limit).all()
    return query

Try above code it should work. I didn't tested it.

fastapi using ORM not able to convert to pandas

1 Answers1