Question 89225 in PostgreSQL Question posted in
The official documentation can be found here.

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

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?

Tags:

3

Answers


  1. 0

    It seems there is no "easy way" to do it yet :/

    You need to adapt the create view recipe to have it, check here:
    https://github.com/sqlalchemy/sqlalchemy/issues/5687#issuecomment-724083781

    how to do it with a bit more lines..

    keep it strong there

    Login or Signup to reply.
  2. 0

    You can simply do

    with engine.begin() as conn:
    conn.exec_driver_sql("create table newtable  as select * from dbtable")
print('Table "newtable" created.')
    Login or Signup to reply.
  3. 0

    To create a table using CREATE TABLE AS SELECT statement with SQLAlchemy, you can use the text function to write the SQL statement and execute it using the execute method of a SQLAlchemy engine object.

    Here is an example code snippet that demonstrates how to do this:

    from sqlalchemy import create_engine, text

# create a SQLAlchemy engine object
engine = create_engine('postgresql://user:password@localhost/mydatabase')

# write the SQL statement using the `text` function
sql = text('CREATE TABLE new_table AS SELECT * FROM existing_table')

# execute the SQL statement using the `execute` method of the engine object
with engine.begin() as conn:
    conn.execute(sql)

    In this example, we first create a SQLAlchemy engine object using the connection string for our database. We then write the SQL statement using the text function and store it in a variable named sql. Finally, we execute the SQL statement using the execute method of the engine object.

    Note that in the above example, we used a PostgreSQL database, but the same code should work for other databases as well. Also, make sure to replace the user, password, localhost, and mydatabase placeholders with the appropriate values for your database.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search