Thanks Mike. Just to clarify, so instead of caching a Query object, I should cache all those queried instance in my `self.search_result_cache`, is this the idea? Is there a way to just relay Query object from one method to another? Which seems a little simpler to me.
On Thu, Jul 13, 2017 at 7:20 PM, Mike Bayer <mike...@zzzcomputing.com> wrote: > you need to use Session.merge and/or Query.merge_result so that a > *copy* of the detached object is placed into the Session. In > particular, Query.merge_result was created for the use case of caching > result sets. > > http://docs.sqlalchemy.org/en/latest/orm/query.html? > highlight=merge_result#sqlalchemy.orm.query.Query.merge_result > > > this links to the dogpile cache example at > http://docs.sqlalchemy.org/en/latest/orm/examples.html# > module-examples.dogpile_caching > illustrates a method that I've used in production successfully. > > > > > > On Thu, Jul 13, 2017 at 7:38 PM, Jinghui Niu <niujing...@gmail.com> wrote: > > I have a web application served by cherrypy (, which is multi-threaded. ) > > > > I'm trying to cache a set of rows queried from database using > > `self.search_result_cache` variable on the GUI_Server object. On my > > front-end, the web first request `list_entries` to prepare the rows and > > stores them on `self.search_result_cache`. After that, on user's mouse > click > > the front-end initiats another request calling `Record_entries_count`, > which > > is expected to revive the Query from `self.search_result_cache` and > continue > > on to do some data refining, e.g. summing up the count in this case. > > > > class GUI_Server: > > > > > > def __init__(self): > > self.search_result_cache = None > > > > > > @cherrypy.expose > > def list_entries(self, **criteriaDICT): > > # always store the result to self cache > > > > ... > > > > > > db = cherrypy.request.db_session > > > > > > filter_func = getattr(self, 'filterCriteria_' + > > classmodel_obj.__name__) > > queryOBJ = filter_func(criteriaDICT, queryOBJ) > > self.search_result_cache = queryOBJ > > db.expunge_all() > > > > .... > > > > def Record_entries_count(self): > > db = cherrypy.request.db_session > > query_subset = self.search_result_cache > > result = query_subset.count() > > return result > > > > > > But this doesn't work. It always give me an error: > > > > sqlite3.ProgrammingError: SQLite objects created in a thread can only be > > used in that same thread.The object was created in thread id > 139937752020736 > > and this is thread id 139938238535424 > > > > > > I am already using `scoped_session` for each request session. I don't > > understand why I got this error. > > > > > > What is the best pratice to cache queried result across different request > > sessions like this? Thanks a lot. > > > > -- > > SQLAlchemy - > > The Python SQL Toolkit and Object Relational Mapper > > > > http://www.sqlalchemy.org/ > > > > To post example code, please provide an MCVE: Minimal, Complete, and > > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > > description. > > --- > > You received this message because you are subscribed to the Google Groups > > "sqlalchemy" group. > > To unsubscribe from this group and stop receiving emails from it, send an > > email to sqlalchemy+unsubscr...@googlegroups.com. > > To post to this group, send email to sqlalchemy@googlegroups.com. > > Visit this group at https://groups.google.com/group/sqlalchemy. > > For more options, visit https://groups.google.com/d/optout. > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to a topic in the > Google Groups "sqlalchemy" group. > To unsubscribe from this topic, visit https://groups.google.com/d/ > topic/sqlalchemy/CWvLr8GPra4/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.