title: How to use SQLAlchemy models to generate raw SQL which is able to create all tables?
body: <p>It is possible to generate raw SQL statement to create any SQLAlchemy table <a href="https://stackoverflow.com/questions/2128717/sqlalchemy-printing-raw-sql-from-create">as shown here</a>.</p>
  <p>However, I would like to generate raw SQL which creates all tables, not just one.</p>
  <p>Let's assume I have such tables (this table design is not real-world, but it shows the issue well):</p>
  <pre class="lang-py prettyprint-override"><code>import sqlalchemy
  from sqlalchemy import Column, Integer, Text, ForeignKey
  from sqlalchemy.ext.declarative import declarative_base
  from sqlalchemy.schema import CreateTable
  from sqlalchemy.dialects import postgresql
  
  engine = sqlalchemy.create_engine('postgresql://postgres:postgres@127.0.0.1:5432/postgres', echo=True)
  Base = declarative_base()
  
  
  class Product(Base):
      __tablename__ = &quot;product&quot;
      id = Column(Integer, primary_key=True, autoincrement=True)
      name = Column(Text, nullable=True)
      user_id = Column(Integer, ForeignKey('user.id'), nullable=True)
  
  class User(Base):
      __tablename__ = &quot;user&quot;
      id = Column(Integer, primary_key=True, autoincrement=True)
      name = Column(Text, nullable=True)
      product_id = Column(Integer, ForeignKey('product.id'), nullable=True)
  
  
  open(&quot;create_tables.sql&quot;, 'w').close() # to clear the file
  
  for table in Base.metadata.tables.values():
      table_sql = str(CreateTable(table).compile(dialect=postgresql.dialect()))
      with open(&quot;create_tables.sql&quot;, &quot;a&quot;) as myfile:
          myfile.write(table_sql)
  
  
  Base.metadata.create_all(engine)
  </code></pre>
  <p>(this is complete program, you can run it as-is)</p>
  <p>In code above you can see that I use <code>for</code> loop to generate raw SQL:</p>
  <pre class="lang-py prettyprint-override"><code>for table in Base.metadata.tables.values():
      table_sql = str(CreateTable(table).compile(dialect=postgresql.dialect()))
      with open(&quot;create_tables.sql&quot;, &quot;a&quot;) as myfile:
          myfile.write(table_sql)
  </code></pre>
  <p>Resulting file contains:</p>
  <pre class="lang-sql prettyprint-override"><code>CREATE TABLE product (
      id SERIAL NOT NULL, 
      name TEXT, 
      user_id INTEGER, 
      PRIMARY KEY (id), 
      FOREIGN KEY(user_id) REFERENCES &quot;user&quot; (id)
  )
  
  CREATE TABLE &quot;user&quot; (
      id SERIAL NOT NULL, 
      name TEXT, 
      product_id INTEGER, 
      PRIMARY KEY (id), 
      FOREIGN KEY(product_id) REFERENCES product (id)
  )
  </code></pre>
  <p>But I can't execute this SQL code, it will give me error due to circular reference. (and I need to add at least some <code>;</code>)</p>
  <hr />
  <p>SQLAlchemy can however create those tables without problem when I use:</p>
  <pre class="lang-py prettyprint-override"><code>Base.metadata.create_all(engine)
  </code></pre>
  <p>With <code>echo=True</code> the generated SQL is visible in log:</p>
  <pre class="lang-sql prettyprint-override"><code>2021-10-18 05:37:24,442 INFO sqlalchemy.engine.Engine select version()
  2021-10-18 05:37:24,443 INFO sqlalchemy.engine.Engine [raw sql] {}
  2021-10-18 05:37:24,447 INFO sqlalchemy.engine.Engine select current_schema()
  2021-10-18 05:37:24,447 INFO sqlalchemy.engine.Engine [raw sql] {}
  2021-10-18 05:37:24,449 INFO sqlalchemy.engine.Engine show standard_conforming_strings
  2021-10-18 05:37:24,450 INFO sqlalchemy.engine.Engine [raw sql] {}
  2021-10-18 05:37:24,454 INFO sqlalchemy.engine.Engine BEGIN (implicit)
  2021-10-18 05:37:24,455 INFO sqlalchemy.engine.Engine select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where pg_catalog.pg_table_is_visible(c.oid) and relname=%(name)s     
  2021-10-18 05:37:24,456 INFO sqlalchemy.engine.Engine [generated in 0.00073s] {'name': 'product'}  
  2021-10-18 05:37:24,459 INFO sqlalchemy.engine.Engine select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where pg_catalog.pg_table_is_visible(c.oid) and relname=%(name)s     
  2021-10-18 05:37:24,460 INFO sqlalchemy.engine.Engine [cached since 0.004542s ago] {'name': 'user'}2021-10-18 05:37:24,463 INFO sqlalchemy.engine.Engine
  CREATE TABLE product (
          id SERIAL NOT NULL,
          name TEXT,
          user_id INTEGER,
          created_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL,
          PRIMARY KEY (id)
  )
  
  
  2021-10-18 05:37:24,464 INFO sqlalchemy.engine.Engine [no key 0.00116s] {}
  2021-10-18 05:37:24,480 INFO sqlalchemy.engine.Engine
  CREATE TABLE &quot;user&quot; (
          id SERIAL NOT NULL,
          name TEXT,
          product_id INTEGER,
          created_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL,
          PRIMARY KEY (id)
  )
  
  
  2021-10-18 05:37:24,481 INFO sqlalchemy.engine.Engine [no key 0.00128s] {}
  2021-10-18 05:37:24,494 INFO sqlalchemy.engine.Engine ALTER TABLE product ADD FOREIGN KEY(user_id) 
  REFERENCES &quot;user&quot; (id)
  2021-10-18 05:37:24,495 INFO sqlalchemy.engine.Engine [no key 0.00053s] {}
  2021-10-18 05:37:24,497 INFO sqlalchemy.engine.Engine ALTER TABLE &quot;user&quot; ADD FOREIGN KEY(product_id) REFERENCES product (id)
  2021-10-18 05:37:24,497 INFO sqlalchemy.engine.Engine [no key 0.00047s] {}
  2021-10-18 05:37:24,500 INFO sqlalchemy.engine.Engine COMMIT
  </code></pre>
  <p>My question is how to get this SQL, which is visible in the log above and is able to create all tables in one transaction, as Python string (or <code>.sql</code> file)?</p>
  <p>I want to be able to check it / review it and then apply it (or part of it) manually to production server.</p>