SQLalchemy select from postgresql table-CodePudding

I have this model

import os

from dotenv import load_dotenv
from sqlalchemy import Column, Date, Float, Integer, String,Numeric
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import declarative_base, Session


 Base = declarative_base()

class MS(Base):
 
try:
  
    __tablename__ = 'ms'
    column_not_exist_in_db = Column(Integer, primary_key=True)
    PROVIDER = Column(String)      
except SQLAlchemyError as e:
    error = str(e.__dict__['orig'])
    raise error

this query

import pandas as pd
import sqlalchemy
from sqlalchemy import select,func,distinct
from sqlalchemy.orm import Session
from sqlalchemy.sql import func as sql_function
from sqlalchemy.exc import SQLAlchemyError

from db.models.market_share_postgresql import MS

def run_stmt(stmt,engine): df = pd.DataFrame()

try:
    
    session = Session(engine, future=True)
    data = session.execute(stmt)
   
    df = pd.DataFrame(data.all())
    if len(df) > 0:
        df.columns = data.keys()
    else:
        columns = data.keys()
        df = pd.DataFrame(columns=columns)
    df= df.rename(columns=str.lower)
except SQLAlchemyError as e:
    error = str(e.__dict__['orig'])
  
    session.rollback()
    raise error
else:

    session.commit()
finally:
    
    engine.dispose()
    session.close()

return df


def ms_providers(engine):

df = pd.DataFrame()

try:
    
   
    stmt = select(
                
       distinct( MS.PROVIDER).label("PROVIDER")
        
    )
 
    df=run_stmt(stmt,engine)
except SQLAlchemyError as e:
    error = str(e.__dict__['orig'])
    raise error
return df

and this engine:

dbschema='schema1,public' 
    ConnectionString="postgresql psycopg2://" POSTGRESQL_USER ":" POSTGRESQL_PASSWORD "@" POSTGRESQL_ACCOUNT ":" POSTGRESQL_PORT "/" POSTGRESQL_DATABASE
    print(ConnectionString)
    engine = create_engine(ConnectionString,connect_args={'options': '-csearch_path={}'.format(dbschema)})

but after running the query, I get this error: psycopg2.errors.UndefinedTable: relation "database1.schema1.ms" does not exist LINE 2: FROM "database1.schema1".ms

databse1,schema1,and ms table exist but seems sqlalchemy puts " in a wrong place as shown in the error: [SQL: SELECT DISTINCT "databse1.schema1".ms."PROVIDER" AS "PROVIDER" FROM "databse1.schema1".ms]

How can I fix this?

CodePudding user response：

The problem was that PostgreSQL is case-sensitive and the data model was using the upper-case spelling of a column in the table which is all in lower-case (in my case)

so fixed it as this

 PROVIDER = Column("provider",String)