Using expression language to create temporary tables

[Sharon]

Greetings:

Just started looking at SqlAlchemy as we may want to use its sql expression language to abstract our (sometimes) database specific sql.

To create a temporary table in postgresql we would typically do something like this:

SELECT m._marker_key, m.symbol
INTO TEMPORARY TABLE tmp_markers
FROM mrk_marker m
WHERE m._organism_key == 1
AND m._marker_status_key IN (1,3)
 
To do this using sqlalchemy I have done the following, see below. I don't see a way to SELECT INTO using sqlalchemy. Is there a simpler way? Thanks for your help!!

# create the temporary table
tmp_markers = Table('tmp_markers', metadata,
    Column('_marker_key', Integer, index=True),
    Column('symbol', String(50) ),
    prefixes=['TEMPORARY']
)
tmp_markers.create()

# create the connection after create the temporary table
connection = engine.connect()

# Query with which to load 'tmp_markers'
query = select([m.c._marker_key, m.c.symbol],
   and_ (
        m.c._organism_key == 1,
        m.c._marker_status_key.in_([1,3]),
        )
)

# execute the query
results = connection.execute(query)

# create the insert expression
ins_tmp = tmp_markers.insert()

# create list of results to insert into 'tmp_markers'
ins_tmp_list = []
for r in results:
    ins_tmp_list.append({
        '_marker_key':  r[m.c._marker_key],
        'symbol':       r[m.c.symbol],
    })
results.close()

# insert into 'tmp_markers'
connection.execute(ins_tmp, ins_tmp_list)

[Michael Bayer]

On Mar 9, 2012, at 11:46 AM, Sharon wrote:

Greetings:

Just started looking at SqlAlchemy as we may want to use its sql expression language to abstract our (sometimes) database specific sql.

To create a temporary table in postgresql we would typically do something like this:

SELECT m._marker_key, m.symbol
INTO TEMPORARY TABLE tmp_markers
FROM mrk_marker m
WHERE m._organism_key == 1
AND m._marker_status_key IN (1,3)
 
To do this using sqlalchemy I have done the following, see below. I don't see a way to SELECT INTO using sqlalchemy. Is there a simpler way? Thanks for your help!!

I'd agree you should use that format, though SQLAlchemy doesn't have direct support for this idiom at the moment.     Here's a recipe that provides it:

from sqlalchemy.sql import Select

from sqlalchemy.ext.compiler import compiles

class SelectInto(Select):

    def __init__(self, columns, into, *arg, **kw):

        super(SelectInto, self).__init__(columns, *arg, **kw)

        self.into = into

@compiles(SelectInto)

def s_into(element, compiler, **kw):

    text = compiler.visit_select(element)

    text = text.replace('FROM', 

                'INTO TEMPORARY TABLE %s FROM' % 

                element.into)

    return text

if __name__ == '__main__':

    from sqlalchemy.sql import table, column

    marker = table('marker', 

        column('x1'),

        column('x2'),

        column('x3')

    )

    print SelectInto([marker.c.x1, marker.c.x2], "tmp_markers").\

            where(marker.c.x3==5).\

            where(marker.c.x1.in_([1, 5]))

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/B8Umq9y08EoJ.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.