How to create a temporary table in SA ORM

[junepeach]

In SQL language, we can create a temporary table like:
CREATE TEMPORARY TABLE t1 ( select t2.id, t3.name from t2, t3 where t2.id = t3.id);

Can we do the same thing in SA ORM? I searched online, and some answers seemed not clear to me.
By the way, when we should use view, and when to use temporary table?

Thanks and Best Regards!

YHL

[Jakub Zalewski]

On Friday, February 8, 2013 8:37:29 PM UTC+1, junepeach wrote:

By the way, when we should use view, and when to use temporary table?

temp tables are static, you manually populate them with data and process it as with any other table. views are populated every time you query them. there are many use cases, but to put it very simply: temp tables are (short-term) data storage, views are data access. I don't know what's your use case, but I'll risk to oversimplify it for a moment: if you need just convenience (to query a preprocessed/simplified set/subset of data) - use views, if you need a set/subset of data to be persistent across many queries in the same session - use temp tables.

[junepeach]

Thanks very much for the explanation about the difference between view and temporary table. But now, I am thinking about how to realize it in sqlalchemy and use it in as many databases as possible.

Best wishes,

LYH

[Audrius Kažukauskas]

If you're talking about temporary tables, then this will do:

class Test(Base):
__tablename__ = 'test_table'
__table_args__ = {'prefixes': ['TEMPORARY']}
# ...

Haven't tested this thoroughly, you may need to bind session to a single
connection instead of engine and use that same connection to create the
table, as at least in PostgreSQL temporary tables exist only for a
connection they were created in. Not sure how they work in other RDBMS.

If you meant the views, then map and query them as ordinary tables.

--
Audrius Kažukauskas
http://neutrino.lt/

[junepeach]

Thanks Audrius. Do you have an example for how to select the data from multiple tables and insert into that temporary table?

Best,
LYH