DEV Community

SEO - NT Tech Innovation
SEO - NT Tech Innovation

Posted on Edited on

Caching an SMM Services List in SQLite So a Small PH Reseller Page Loads Fast

A reseller page that calls the provider API on every page load is slow and fragile. The services list changes maybe a few times a day. So cache it. SQLite is already on your machine and needs no server.

The plan

  • Keep one table with a key, a JSON value and a timestamp.
  • Serve from the table if the data is fresh.
  • Otherwise fetch, store and serve.

The code

import json
import sqlite3
import time

db = sqlite3.connect("cache.db")
db.execute("CREATE TABLE IF NOT EXISTS cache (k TEXT PRIMARY KEY, v TEXT, ts REAL)")


def cached(key, fetch, ttl=900):
    row = db.execute("SELECT v, ts FROM cache WHERE k = ?", (key,)).fetchone()
    if row and time.time() - row[1] < ttl:
        return json.loads(row[0])
    data = fetch()
    db.execute("REPLACE INTO cache VALUES (?, ?, ?)", (key, json.dumps(data), time.time()))
    db.commit()
    return data
Enter fullscreen mode Exit fullscreen mode

Using it

import os
import requests

API_URL = os.environ["PANEL_API_URL"]
API_KEY = os.environ["PANEL_API_KEY"]


def fetch_services():
    r = requests.post(API_URL, data={"key": API_KEY, "action": "services"}, timeout=20)
    r.raise_for_status()
    return r.json()


services = cached("services", fetch_services)
print(len(services), "services loaded")
Enter fullscreen mode Exit fullscreen mode

The first call hits the API. Every call in the next 15 minutes reads from disk in a few milliseconds.

Serve stale data when the API is down

Wrap the fetch in a try block and fall back to the old row. Customers see slightly old prices instead of an error page, which is nearly always the better outcome.

Show pesos properly

If your buyers are in the Philippines, convert once and store the peso rate with the row. Don't convert on every render.

References

Check your provider's documentation for the exact action names. The BoostingPH API page is one public example of the common panel API layout, and the BoostingPH services list shows the kind of table this cache ends up feeding.

Top comments (0)