如何在SQLAlchemy中使用CREATE AS SELECT语句创建表？

问题描述 #

我查阅了许多关于使用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)

在psql控制台中，我可以使用CREATE TABLE AS SELECT结构创建新表：

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

将该语句嵌入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_str)
conn.commit()

我已经通过SQLAlchemy连接数据库：

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

请问如何在SQLAlchemy中嵌入create table newtable as select * from dbtable;语句？

解决方案 #

方法1：直接执行原生SQL #

这是最直接的实现方式，用SQLAlchemy的text()函数包裹原生SQL语句，通过引擎连接执行：

from sqlalchemy import create_engine, text

# 修正连接字符串格式（正确格式：postgresql://用户名:密码@主机/数据库名）
engine = create_engine("postgresql://postgres:你的密码@localhost/dbname")

# 定义要执行的SQL语句
sql_stmt = text("create table newtable as select * from dbtable;")

# 执行并提交事务
with engine.connect() as conn:
    conn.execute(sql_stmt)
    conn.commit()  # PostgreSQL下DDL操作需手动提交事务

方法2：用SQLAlchemy表达式构建（ORM风格） #

如果想避免直接写原生SQL，可以用SQLAlchemy的表达式API构建语句，更贴合ORM使用习惯：

from sqlalchemy import create_engine, select, CreateTable, MetaData, Table

engine = create_engine("postgresql://postgres:你的密码@localhost/dbname")
meta = MetaData()

# 自动加载原表的元数据
dbtable = Table("dbtable", meta, autoload_with=engine)

# 构建CREATE TABLE AS SELECT语句
create_table_stmt = CreateTable(
    Table("newtable", meta),
    select(dbtable)
)

# 执行并提交事务
with engine.connect() as conn:
    conn.execute(create_table_stmt)
    conn.commit()

注意事项 #

• 务必修正连接字符串：原代码中的postgresql://postgres:localhost@postgres/dbname格式错误，正确格式需要包含密码（如果设置）、正确的主机地址和数据库名。
• 部分数据库（如PostgreSQL）执行DDL语句后需要手动提交事务，不要遗漏conn.commit()。

内容的提问来源于stack exchange，提问作者showkey