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？

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. — Ilja Everilä
– Ilja Everilä, Commented Jan 1, 2022 at 9:04

JimmyLi · Accepted Answer · 2021-12-30 22:52:31Z

2

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

answered Dec 30, 2021 at 22:52

JimmyLi

213 bronze badges

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

Comments

Franz Gastring · Accepted Answer · 2021-12-30 22:45:01Z

1

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

answered Dec 30, 2021 at 22:45

Franz Gastring

1,1222 gold badges15 silver badges14 bronze badges

1 Comment

JimmyLi Over a year ago

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

snakecharmerb · Accepted Answer · 2022-01-02 09:10:23Z

1

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')
>>>

answered Jan 2, 2022 at 9:10

snakecharmerb

57.1k13 gold badges136 silver badges200 bronze badges

1 Comment

JimmyLi Over a year ago

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.

Collectives™ on Stack Overflow

Query data from schema.table in Postgres

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related