I'm very new to sqlalchemy and I encountered a problem regards to Postgres databases. I can successfully connect to the postgresql database, and I think I've directed the engine to my desired schema.

cstr = f"postgresql psycopg2://{username}:{password}@{server}:{port}/{database}"
engine = create_engine(cstr,connect_args={'options': '-csearch_path={}'.format("schema_name")},echo=True)
con = engine.connect()

print(con.execute('SELECT * FROM table_name'))

This prints out the correct schema_name.

insp = inspect(con)
print(insp.default_schema_name)

However, I still get error messages saying that the table does not exist.

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedTable) relation "table_name" does not exist

I also tried without the ,connect_args={'options': '-csearch_path={}'.format("google")} clause and use schema_name.table_name in the sql query. Same error occurs. It's not a local database, so I can't do anything to the database except getting data from it. What should I do here？

CodePudding user response：

I don't know about your framework alchemy but the correct query should be something like that:

SELECT table_name FROM information_schema.tables WHERE table_schema='public' 

Reference docs

CodePudding user response：

It's interesting how I searched the answers for hours and decided to ask instead. And right after, I found the solution. Just in case anyone is interested in the answer. I got my solution from this answer Selecting data from schema based table in Postgresql using psycopg2

print(con.execute("""SELECT DISTINCT "column_name" FROM schema_name."table_name";"""))

This is the way to do it, with a looot of quotation marks