CODEWITHSUNDEEP
sqlcachingstorage
Jwan622
Jwan622

Reputation: 11639

How to cache a sql database mapping independent of an application

I have an application that receives messages from a database via the write ahead logs and every row looks something like this

| id | prospect_id | school_id | something | something else |
|----|-------------|------------|-----------|----------------|
| 1  | 5           | 10         | who       | cares          |
| 2  | 5           | 11         | what      | this           |
| 3  | 6           | 10         | is        | blah           |

Eventually, I will need to query the database for mapping between prospect_id and school name. The query results are in the 10000s. The schools table has a name column that I can query in a simple join. However, I want to store this information somewhere on my server that would be easily accessibly by my application. I need this information:

What can be done? What are some options?

EDIT Is pickle a good idea? https://datascience.blog.wzb.eu/2016/08/12/a-tip-for-the-impatient-simple-caching-with-python-pickle-and-decorators/

What are limitations of pickle? The results of the sql query might be in the 10000s

Upvotes: 0

Views: 93

Answers (1)

MCH
MCH

Reputation: 2214

The drawback of using pickle is that it is a python specific protocol. If you intend for other programming languages to read this file, then the tooling might not exist to read it and you would be better storing it in something like a JSON or XML file. If you will only be reading it with python then pickle is fine.

Here are a few options you have:

  • Load the data from SQL when the application is started up (the SQL data can be stored locally, doesn't have to be on an external system) in a global value.
  • Use pickle to serialize deserialize the data from a file when needed.
  • Load the data into redis, an in-memory caching system.

Upvotes: 1

Related Questions