Get DDL from existing databases SQLAlchemy

Question

I'm connecting to a postgresql database in AWS Redshift using SQLAlchemy to do some data processing, I need to extract the DDL information of each table in a particular Schema. I cant run any commands like pg_dump --schema-only. What will be the simplest way of extracting the DDL?

Answer 1

you can get all tables with reflection system and print CreateTable construct of each table:

from sqlalchemy.schema import MetaData
from sqlalchemy.schema import CreateTable

meta = MetaData()
meta.reflect(bind=engine)
for table in meta.sorted_tables:
    print(CreateTable(table).compile(engine))