sqlalchemy & #temp tables

[Albert-Jan Roskam]

On 8 Oct 2019 07:49, Frank Millman <frank at chagford.com> wrote:

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
>


>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.


Hi,

I tried your approach today but I ran into problems due to differences between the MS SQL and Sqlite dialect. However, I just came across this page: https://docs.sqlalchemy.org/en/13/dialects/sqlite.html#using-temporary-tables-with-sqlite. I haven't tried it yey, but using a StaticPool might work.


# maintain the same connection across all threads
from sqlalchemy.pool import StaticPool
engine = create_engine('sqlite:///mydb.db',
                    poolclass=StaticPool)