How to cache queries in sqlalchemy just like I do it in mysql by using SQL_CACHE keyword?

Question

I want to cache sqlalchemy, I have discovered that using keyword SQL_CACHE in mysql can be helpful in caching queries on demand. But how do I do it in sqlalchemy? Is it possible?

Answer 1

This is a rather old question, but deserves an answer.

You have to set the query_cache_type.

Here are the values and their meaning

• 0 or OFF : Do not cache results in or retrieve results from the query cache. Note that this does not deallocate the query cache buffer. To do that, you should set query_cache_size to 0.
• 1 or ON : Cache all cacheable query results except for those that begin with SELECT SQL_NO_CACHE.
• 2 or DEMAND Cache results only for cacheable queries that begin with SELECT SQL_CACHE.

By default, query_cache_type is 0 (Cache Nothing).

You can run this to cache every SELECT that does not have SQL_NO_CACHE directive in your session:

SET query_cache_type = 1;

You can run this to cache every SELECT that has the SQL_CACHE directive in your session:

SET query_cache_type = 2;

If you want it set globally, then run

SET GLOBAL query_cache_type = 2;

Then add that to the my.cnf

[mysqld]
query_cache_type = 2

PLEASE NOTE THIS TOTALLY GOES AWAY IN MySQL 8.0 !!!

This answer is only good for MySQL 5.7 and prior, also older MariaDB versions

What About SQLAlchemy ???

Since you want the caching on demand, please set query_cache_type to 2.

Then you can run

query = session.query(User.name).prefix_with('SQL_CACHE')

Answer 2

You need use a prefix_with. For example, this:

query = session.query(User.name).prefix_with('SQL_CACHE')

would render:

SELECT SQL_CACHE users.name AS users_name FROM users