SQLAlchemy does not detect any existing table in postgres db

I have a local postgres DB with a couple of tables and want to reflect their structure into Python.

import sqlalchemy.ext.automap as am
from sqlalchemy.orm import Session
from sqlalchemy import create_engine

base_automap_schema: am.AutomapBase = am.automap_base()
engine = create_engine("postgresql+psycopg://user:pw@localhost:5432/db")
engine.echo = True
engine.connect()

# reflect the tables
base_automap_schema.prepare(autoload_with=engine)

print(base_automap_schema.classes.keys())
print(base_automap_schema.metadata.tables)

output is lots of SQL plus

[]
FacadeDict({})

Seems like my tables are not detected. But why?

I read that sometimes this happens due to a missing primary key, but all of my tables have it. Example:

CREATE TABLE IF NOT EXISTS public.manufacturer
(
    "manu_ID" integer NOT NULL,
    manu_name character varying[] COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT "manu_ID" PRIMARY KEY ("manu_ID")
)

What else could be the problem?

Solution

There was actually a permissions error in the background which the prepare function didn't show.

You can check whether your sqlalchemy connection works like this:

connection: Connection
with engine.connect() as connection:
    connection.execute(text("select * from <your_schema>.<your_table>"))

If you get a message like "Permission denied", you know the error is not your code, but in your database users setup.