I want a controller that fetches a user-chosen subset of data via JSON from an external site, and then presents that for review using smartgrids. Being lazy, I could put the fetched data into a couple of tables. There is a one-to-many relationship in the data (orders and order lines). But the fetched subset is session specific: another user may be looking at a different subset. This application is backended with SQL Server but sqlite would be fine for this temporary data. There is no need to refer to any existing tables so a separate database is fine.
I could use tables and put a session id in a column, to keep different users 'sandboxed' to their own requests. But this means I have to clean up old sessions. sqlite has temporary tables which survive for the current database connection, which I suppose means they would survive for the current web2py action, not for the current session, although I think this could still work for my requirement. I think this problem has been solved before, although I couldn't find what I was looking for by searching. There was a discussion in 2009 about using sqlite:memory but it didn't seem very conclusive. --
