Example of using redis with a dataframe for caching : Checks if a day has passed. If yes, retrieve from SQL server, else retrieve from Redis

redis_dataframe_example.py

import pandas as pd
import numpy as np
import redis
import pyodbc
from time import time
from datetime import date, timedelta, datetime

r = redis.Redis(host='localhost', port=6379, db=0)
dt_last = datetime.strptime(r.get("last_update_time").decode(), '%Y-%m-%d %H:%M:%S')
dt_now = datetime.now()

if abs((dt_now - dt_last).days) < 1:
    print("Reading Daily Data from Redis...")
    start = time()
    df = pd.read_msgpack(r.get("Daily_Revenue_Fleet_Level"))
    end = time()
    print("Time elapsed: {0:.2f}".format(end - start))
else:
    # get from SQL
    conn = pyodbc.connect(
        r'DRIVER={ODBC Driver 13 for SQL Server};'
        r'SERVER=10.223.142.13;'
        r'DATABASE=WKBRZ;'
        r'Trusted_Connection=yes;'
    )
    print("More than 1 day has passed, reading Daily Data from SQL Server DB...")
    start = time()
    sql = """
        DECLARE @Today Date
        DECLARE @DatabaseActiveDate Date
        SET @Today = convert(date, getdate())
        SET @DatabaseActiveDate = convert(date, '2015-04-01')
        SELECT IL.InventoryLegID,
               IL.CarrierCode,
               IL.[RouteRegion],
               IL.[Market],
               IL.[Citypair],
               IL.[FlightNumber],
               IL.[LegDepartureDate]      as [DepartureDate],
               IL.[Fleet],
               isnull(sum([Capacity]), 0) as [Capacity],
               isnull(sum([Booked]), 0)   as [Booked],
               isnull(sum([Rev]), 0)      as [Rev],
               isnull(sum([Base_Rev]), 0) as [Base_Rev]
        
        FROM (SELECT InventoryLegID,
                     OperatingCarrierCode                    as CarrierCode,
                     LegDepartureStation + LegArrivalStation as Citypair,
                     RIGHT(FleetGroup, 3)                    as Fleet,
                     CASE
                       WHEN LegDepartureStation < LegArrivalStation
                         THEN (LegDepartureStation + LegArrivalStation) --alphabetical order
                       WHEN LegArrivalStation < LegDepartureStation THEN (LegArrivalStation + LegDepartureStation)
                       END                                   as Market,
                     FlightNumber,
                     LegDepartureDate,
                     sum(capacity)                           as Capacity,
                     CASE
                       WHEN (LegArrivalStation IN ('TPE', 'DMK', 'KHH', 'KIX', 'NRT', 'CTS', 'ICN', 'HNL') OR
                             LegDepartureStation IN ('TPE', 'DMK', 'KHH', 'KIX', 'NRT', 'CTS', 'ICN', 'HNL'))
                         THEN 'Multi-cities'
                       WHEN LegDepartureCountry = 'SG' THEN LegArrivalCountry
                       WHEN LegArrivalCountry = 'SG' THEN LegDepartureCountry
                       WHEN LegDepartureCountry = LegArrivalCountry THEN LegDepartureCountry --For TAO/DLC and CEB/KLO cases
                       ELSE 'Others'
                       END                                   as RouteRegion,
                     Distance
        
              FROM qlik.QlikSenseInventoryLeg
              WHERE LegDepartureDate < convert(date, @Today)
                AND LegDepartureDate > convert(date, @DatabaseActiveDate)
                and LegStatus in ('Normal', 'Closed', 'ClosedPending')
                and RevenueFlightFlag = 1 -- QUESTION : What is this?
                and Capacity <> 0
              GROUP BY InventoryLegID,
                       OperatingCarrierCode,
                       LegDepartureStation + LegArrivalStation,
                       CASE
                         WHEN LegDepartureStation < LegArrivalStation THEN (LegDepartureStation + LegArrivalStation)
                         WHEN LegArrivalStation < LegDepartureStation THEN (LegArrivalStation + LegDepartureStation)
                         END,
                       FlightNumber,
                       LegDepartureDate,
                       RIGHT(FleetGroup, 3),
                       CASE
                         WHEN (LegArrivalStation IN ('TPE', 'DMK', 'KHH', 'KIX', 'NRT', 'CTS', 'ICN', 'HNL') OR
                               LegDepartureStation IN ('TPE', 'DMK', 'KHH', 'KIX', 'NRT', 'CTS', 'ICN', 'HNL'))
                           THEN 'Multi-cities'
                         WHEN LegDepartureCountry = 'SG' THEN LegArrivalCountry
                         WHEN LegArrivalCountry = 'SG' THEN LegDepartureCountry
                         WHEN LegDepartureCountry = LegArrivalCountry THEN LegDepartureCountry --For TAO/DLC and CEB/KLO cases
                         ELSE 'Others'
                         END,
                       Distance
             ) IL
               LEFT JOIN
             (SELECT InventoryLegID,
                     LegCityPair                                                  as Citypair,
                     LegMarket                                                    as Market,
                     SegmentDepartureDate                                         as SegmentDepartureDate,
                     count(distinct SegmentID)                                    as Booked,
                     isnull(sum(BaseFare_Net_SGD), 0)                             as Base_Rev,
                     (isnull(sum(BaseFare_Net_SGD), 0) + isnull(sum(Tax_SGD), 0)) as Rev
        
              FROM [qlik].[QlikSensePassengerJourneyLegCharge]
              WHERE SegmentDepartureDate < convert(date, @Today) --convert(date,dateadd(day,-1,dateadd(month,datediff(month,0,@Today)-1,0)))
                AND SegmentDepartureDate > convert(date, @DatabaseActiveDate)
              GROUP BY InventoryLegID,
                       SegmentDepartureDate,
                       LegMarket,
                       LegCityPair
             ) PJL
             ON
                 IL.InventoryLegID = PJL.InventoryLegID and
                 IL.Citypair = PJL.Citypair and
                 IL.Market = PJL.Market
        
        GROUP BY IL.InventoryLegID,
                 IL.[CarrierCode],
                 IL.[RouteRegion],
                 IL.[Market],
                 IL.[Citypair],
                 IL.[FlightNumber],
                 IL.[LegDepartureDate],
                 IL.[Fleet]
        """
    df = pd.read_sql(sql, conn)
    end = time()
    print("Time elapsed: {0:.2f}".format(end - start))
    conn.close()
    r.set("Daily_Revenue_Fleet_Level", df.to_msgpack(compress='zlib'))
    r.set("last_update_time", datetime.now().strftime("%Y-%m-%d %H:%M:%S"))