Why can't list all tables in the postgres database with sqlalchemy?

Question

Enter into my postgres database and show all tables in the target database:

psql -U postgres
Pager usage is off.
Timing is on.
psql (13.10 (Debian 13.10-0+deb11u1))
Type "help" for help.

postgres=# \c target
You are now connected to database "target" as user "postgres".
target=# \d
         List of relations
 Schema |  Name  | Type  |  Owner   
--------+--------+-------+----------
 public | pe     | table | postgres
 public | sector | table | postgres
 public | subnew | table | postgres

There are three tables in the target database. I want to list all the tables with sqlalchemy.

from sqlalchemy import create_engine
from sqlalchemy import MetaData
from sqlalchemy import Table
db_pass = 'xxxxxx'
db_ip='127.0.0.1'
engine = create_engine('postgresql://postgres:{}@{}/target'.format(db_pass,db_ip))

# Create MetaData instance
metadata = MetaData(engine).reflect()
print(metadata.tables)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
AttributeError: 'NoneType' object has no attribute 'tables'

How can list all tables in the postgres database with sqlalchemy?

Answer 1

Leverage the inspect function from the sqlalchemy module e.g.:

from sqlalchemy import create_engine, inspect

# Replace the following with your actual PostgreSQL connection string
connection_string = "postgresql://username:password@localhost/dbname"

engine = create_engine(connection_string)
inspector = inspect(engine)

# Get the list of table names
table_names = inspector.get_table_names()

# Print the table names
for table_name in table_names:
    print(table_name)

https://docs.sqlalchemy.org/en/20/core/reflection.html#sqlalchemy.engine.reflection.Inspector