'fastapi using ORM not able to convert to pandas
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?
Solution 1:[1]
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.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|---|
Solution 1 | anjaneyulubatta505 |