SQLalchemy query PostgreSQL database view ERROR: function schema_name() does not exist

Question

I am trying to use python sqlalchemy to query our PostgreSQL database view using ODBC but I am getting the error

{ProgrammingError}(pyodbc.ProgrammingError) ('42883', '[42883] ERROR: function schema_name() does not exist;\nError while executing the query (1) (SQLExecDirectW)') [SQL: SELECT schema_name()] (Background on this error at: https://sqlalche.me/e/14/f405)

Using the code below, I successfully create the connection engine but executing the query seems to be the problem.

When using 'pyodbc' or 'psycopg2' establishing the connection and querying data does work perfectly, but with a warning

'UserWarning: pandas only support SQLAlchemy connectable(engine/connection) ordatabase string URI or sqlite3 DBAPI2 connectionother DBAPI2 objects are not tested, please consider using SQLAlchemy warnings.warn('

as to why we are looking into establishing the connection the sqlalchemy-way

import config
import sqlalchemy

if __name__ == '__main__':

    connection_string = (config.odbc('database_odbc.txt'))
    connection_url = sqlalchemy.engine.url.URL.create("mssql+pyodbc", query={"odbc_connect": connection_string})
    conn = sqlalchemy.create_engine(connection_url)

    query_string = """SELECT [column name in view] FROM public.[name of view]"""
    df1 = pd.read_sql(query_string, conn)
    print(df1.to_string())
    conn.close()
    print('Database connection closed.')

As mentioned, the query runs perfectly using the other methods. I already tried different syntax of the database view including

SELECT [column name in view] FROM [database name].public.[name of view]

SELECT [column name in view] FROM [name of view]

and more without success.

Any help is appreciated, thank you!

mssql+pyodbc is the dialect for Microsoft SQL Server. Use postgresql+psycopg2 for PostgreSQL. — Gord Thompson
– Gord Thompson, Commented Mar 31, 2022 at 9:39
Awesome, I followed the default postgresql syntax at docs.sqlalchemy.org/en/14/core/engines.html 'engine = create_engine('postgresql://scott:tiger@localhost/mydatabase')' and it worked now perfectly thank you — AlexisPa
– AlexisPa, Commented Mar 31, 2022 at 11:08

AlexisPa · Accepted Answer · 2022-03-31 11:13:08Z

2

Thank you @Gord Thompson,

I followed the default postgresql syntax at https://docs.sqlalchemy.org/en/14/core/engines.html

engine = create_engine('postgresql://scott:tiger@localhost/mydatabase')

now the code looks like

import sqlalchemy

if __name__ == '__main__':

    engine = create_engine('postgresql://[user]:[password]@[host]/[db]')
    conn = engine.connect()

    query_string = """SELECT [column name in view] FROM public.[name of view]"""
    df1 = pd.read_sql(query_string, conn)
    print(df1.to_string())
    conn.close()
    print('Database connection closed.')

and now it works perfectly, thank you!

answered Mar 31, 2022 at 11:13

AlexisPa

492 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQLalchemy query PostgreSQL database view ERROR: function schema_name() does not exist

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related