CODEWITHSUNDEEP
pythonpostgresqlviewsqlalchemyschema
AlexisPa
AlexisPa

Reputation: 49

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

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!

Upvotes: 1

Views: 4454

Answers (1)

AlexisPa
AlexisPa

Reputation: 49

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!

Upvotes: 2

Related Questions