sqlalchemy & #temp tables

[Frank Millman]

On 2019-10-07 5:30 PM, Albert-Jan Roskam wrote:
> Hi,
> 
> I am using sqlalchemy (SA) to access a MS SQL Server database (python 3.5, Win 10). I would like to use a temporary table (preferably #local, but ##global would also be an option) to store results of a time-consuming query. In other queries I'd like to access the temporary table again in various places in my Flask app. How do I do that, given that SA closes the connection after each request?
> 
> I can do:
> with engine.connect() as con:
>      con.execute('select * into #tmp from tbl')
>      con.execute('select  * from #tmp')
> 
> ... but that's limited to the scope of the context manager.
> 
> Oh, I don't have rights to create a 'real' table. :-(
> 
> Thanks!
> 
> Albert-Jan
> 

This does not answer your question directly, but FWIW this is what I do.

I do not use SA, but I have written my app to support Sql Server, 
PostgreSQL and sqlite3 as backend databases. However, no matter which 
one is in use, I also use sqlite3 as an in-memory database to store 
temporary information. It took me a little while to get it all working 
smoothly, but now it works well.

Of course this may not work for you if you have a large volume of temp 
data, but it may be worth trying.

Frank Millman