Query data from schema.table in Postgres

Question

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?

• Ilja Everilä: Is table_name actually a mixed case / upper case name?
• JimmyLi: it doesn't matter in my case.
• Ilja Everilä: So the table has been created using a regular identifier, not a delimited identifier? Your comment seems to hint at the former, but your answer hints at the latter.

Answer 1

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

Answer 2

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

• JimmyLi: how do I incorporate this to my query so I can query data from the table?

Answer 3

Rather than manually quoting the identifiers in queries you can let SQLAlchemy do the work for you.

Given this table and data:

test# create table "Horse-Bus" (
test(# id integer generated always as identity,
test(# name varchar, 
test(# primary key(id) 
test(# );
CREATE TABLE
test#
test# insert into "Horse-Bus" (name) values ('Alice'), ('Bob'), ('Carol');
INSERT 0 3

You can create a Table object and query it like this:

>>>import sqlalchemy as sa
>>> engine = sa.create_engine('postgresql:///test', echo=False, future=True)
>>> tbl = sa.Table('Horse-Bus', sa.MetaData(), autoload_with=engine)
>>> with engine.connect() as conn:
... rows = conn.execute(sa.select(tbl))
... for row in rows:
... print(row)
... 
(1, 'Alice')
(2, 'Bob')
(3, 'Carol')
>>>

• JimmyLi: I have to use raw sqls to query, and there's more than one table to query from. But it does seem like a sound solution, I'll try it out later.