How can create table using 'create as select' statement with sqlalchemy?

Question

I have searched many tutors on how to create table with sqlalchemy:

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
engine = create_engine('sqlite:///college.db', echo = True)
meta = MetaData()

students = Table(
   'students', meta, 
   Column('id', Integer, primary_key = True), 
   Column('name', String), 
   Column('lastname', String),
)
meta.create_all(engine)

With create table as select structure,i can create a new table in psql console :

\c  dbname
create table newtable  as select * from dbtable;

It is simple to embed the create table as select command in psycopg2:

import psycopg2
conn = psycopg2.connect(database="dbname", user="postgres", password="xxxxxx", host="127.0.0.1")
sql_str = "create table newtable  as select * from dbtable;"
cur = conn.cursor()
cur.execute(sql_stsr)
conn.commit()

I want to embed it with sqlalchemy.After connecting the database with sqlalchemy:

from sqlalchemy import create_engine
create_engine("postgresql://postgres:localhost@postgres/dbname")

How can embed the create table newtable as select * from dbtable; in sqlalchemy then?

Answer 1

You can simply do

with engine.begin() as conn:
    conn.exec_driver_sql("create table newtable  as select * from dbtable")
print('Table "newtable" created.')