I found this to be an interesting little problem. The task can be
accomplished by using the text of a statement to construct a new
string then executing.
Here is a simple recipe using SQLite. I'm sure it can be extended
fairly easily, and should work in any standard SQL environment
import sqlalchemy
print 'SQLAlchemy version',sqlalchemy.__version__
from sqlalchemy import *
print '##### set up some data'
engine = create_engine('sqlite:///', echo=False)
meta = MetaData(bind=engine)
t1 = Table('t1', meta,
Column('id', Integer, primary_key=True),
Column('data1', String),
Column('data2', String)
)
meta.create_all()
conn = engine.connect()
conn.execute(t1.insert(), [
dict(data1='r1-d1',data2='r1-d2'),
dict(data1='r2-d1',data2='r2-d2')])
print '##### create new table with select'
# you could automate creating this select
stmt = select([t1.c.data1.label('data1')])
# Note: the label is important otherwise column is named t1.data1
from cStringIO import StringIO
buffer = StringIO()
print >>buffer, 'CREATE TABLE t2 AS'
print >>buffer, stmt
sql = buffer.getvalue()
print 'SQL statement is:\n',sql
conn.execute(text(sql))
print '##### autoload the new table into SQLAlchemy metadata'
t2 = Table('t2', meta, autoload=True)
print '##### show it is in metadata structure'
for tbl in meta.sorted_tables:
print tbl
for col in tbl.c:
print ' ',col
print '##### look inside SQLite to verify table looks good'
for col in conn.execute(text('pragma table_info(t2)')):
print col
print '##### select data via SQLAlchemy'
for row in conn.execute(select([t2])):
print row
On Jan 27, 12:59 pm, Roger Demetrescu <roger.demetre...@gmail.com>
wrote:
> On Tue, Jan 27, 2009 at 14:31, Michael Bayer <mike...@zzzcomputing.com> wrote:
>
> > there's nothing stopping you from just executing the SQL directly.
> > I've never actually heard of CREATE TABLE AS before, but I just
> > checked it out and you can certainly generate the SQL programmatically
> > from a select, by inspecting each column in the select statement's "c"
> > collection. It appears to be part of the SQL standard so is a
> > syntax we would accept (it would be appropriate for the 0.6 series
> > where we have CreateTable structures available). But SQLA is a
> > "toolkit" and the tools are there already to automate this process
> > without a pre-made feature.
>
> I recently had to code something like that in a stored procedure
> (postgresql database):
>
> create TEMP table temp_boleto_guia as
> select .... (huge select);
>
> Does this CreateTable support temporary table syntax ?
>
> []s
> Roger
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
