sqlalchemy connect "ERROR:function schema_name does not exist"

Question

I have the following python code

cred_dict = load_credentials()
user = cred_dict["user"]
pwd = cred_dict["pwd"]
host = cred_dict["host"]
port = cred_dict["port"]
db = cred_dict["db"]
schema = cred_dict["schema"]
con_string = f'Driver={{PostgreSQL Unicode}};Server={host};Database={db};Port={port};UID={user};PWD={pwd};'
params = urllib.parse.quote_plus(con_string)
con = sqlalchemy.create_engine("mssql:///?odbc_connect=%s" % params,fast_executemany=True)
con = con.connect()

but I keep getting ERROR: function schema_name() does not exist.

I have tested different drivers but im using the PostgreSQL Unicode for my DSN setting in ODBC, and it is working w/o any issues. I have also tried mssql+pyodbc but it does not do any difference.

Note, I cannot use pyodbc.connect("DSN="+DSN) since I have to use an sqlalchemy-connection for our production-environment

Answer 1

I managed to work around it by installing psycopg2 and then do

con = create_engine(f'postgresql://{self.user}:{self.pwd}@{self.host}:{self.port}/{self.db}')

which then is passed to

df = pd.read_sql(query=query, con=con)