AWS ElastiCache with Python.txt

Lab 1:
=======
from redis import Redis

# Read the Redis credentials from the REDIS_URL environment variable.
REDIS_ENDPOINT = '{}'
REDIS_PORT=''

# Initialize the cache
redis = Redis(host=REDIS_ENDPOINT,port=REDIS_PORT,decode_responses=True)

key="Sportsman"
value="Sachin Tendulkar"

redis.set(key,value)
redis.get(key)

redis.ttl(key)

redis.expire(key,10)  select count(*) from table1--10  15

redis.ttl(key)

redis.get(key)



SQL Queries:
================
CREATE SCHEMA sattu_schema;

CREATE TABLE sattu_schema.Persons (
    PersonID int,
    FullName varchar(255),
    City varchar(255),
    PRIMARY KEY (PersonID)
);


INSERT INTO sattu_schema.Persons VALUES (0,'Herman Zimmerman','Oklahoma City');
INSERT INTO sattu_schema.Persons VALUES (1,'Lisa Ray','Columbus');
INSERT INTO sattu_schema.Persons VALUES (2,'Terrell Reeves','Jacksonville');
INSERT INTO sattu_schema.Persons VALUES (3,'Steve Goodwin','Charlotte');
INSERT INTO sattu_schema.Persons VALUES (4,'Leah Tran','Detroit');
INSERT INTO sattu_schema.Persons VALUES (5,'Wilbert Holmes','Washington');
INSERT INTO sattu_schema.Persons VALUES (6,'Mindy George','Los Angeles');
INSERT INTO sattu_schema.Persons VALUES (7,'Rosa Huff','Phoenix');
INSERT INTO sattu_schema.Persons VALUES (8,'Clayton Jennings','Denver');
INSERT INTO sattu_schema.Persons VALUES (9,'Darla Hayes','Charlotte');
INSERT INTO sattu_schema.Persons VALUES (10,'Jack Hicks','Houston');
INSERT INTO sattu_schema.Persons VALUES (11,'Francis Davidson','Austin');
INSERT INTO sattu_schema.Persons VALUES (12,'Jerome Padilla','San Francisco');
INSERT INTO sattu_schema.Persons VALUES (13,'Mamie Duncan','Houston');
INSERT INTO sattu_schema.Persons VALUES (14,'Julia Cain','San Jose');
INSERT INTO sattu_schema.Persons VALUES (15,'Leslie Klein','Seattle');
INSERT INTO sattu_schema.Persons VALUES (16,'Isaac Bridges','Philadelphia');
INSERT INTO sattu_schema.Persons VALUES (17,'Martin Adkins','Chicago');
INSERT INTO sattu_schema.Persons VALUES (18,'Vincent Perry','Detroit');
INSERT INTO sattu_schema.Persons VALUES (19,'William Porter','Fort Worth');
INSERT INTO sattu_schema.Persons VALUES (20,'Domingo Byrd','Charlotte');
INSERT INTO sattu_schema.Persons VALUES (21,'Leslie Torres','San Francisco');
INSERT INTO sattu_schema.Persons VALUES (22,'Wanda Morrison','El Paso');
INSERT INTO sattu_schema.Persons VALUES (23,'Gwen Caldwell','Indianapolis');
INSERT INTO sattu_schema.Persons VALUES (24,'Tammy Daniels','Indianapolis');
INSERT INTO sattu_schema.Persons VALUES (25,'Claude Gilbert','Phoenix');
INSERT INTO sattu_schema.Persons VALUES (26,'Monique Bowers','Seattle');
INSERT INTO sattu_schema.Persons VALUES (27,'Virginia Phillips','San Francisco');
INSERT INTO sattu_schema.Persons VALUES (28,'Norman Graham','Columbus');
INSERT INTO sattu_schema.Persons VALUES (29,'Gerard Fleming','San Jose');
INSERT INTO sattu_schema.Persons VALUES (30,'Miranda Conner','Washington');
INSERT INTO sattu_schema.Persons VALUES (31,'Alicia Mcdaniel','Indianapolis');
INSERT INTO sattu_schema.Persons VALUES (32,'Miriam Erickson','Dallas');
INSERT INTO sattu_schema.Persons VALUES (33,'Tyrone Hughes','Houston');
INSERT INTO sattu_schema.Persons VALUES (34,'Peter Terry','Phoenix');
INSERT INTO sattu_schema.Persons VALUES (35,'Julie Boone','Jacksonville');
INSERT INTO sattu_schema.Persons VALUES (36,'Josephine Sherman','Boston');
INSERT INTO sattu_schema.Persons VALUES (37,'Bridget Stevenson','Dallas');
INSERT INTO sattu_schema.Persons VALUES (38,'Dave Thomas','San Antonio');
INSERT INTO sattu_schema.Persons VALUES (39,'Francisco Holland','Indianapolis');
INSERT INTO sattu_schema.Persons VALUES (40,'Garrett Hogan','Phoenix');
INSERT INTO sattu_schema.Persons VALUES (41,'Verna Abbott','El Paso');
INSERT INTO sattu_schema.Persons VALUES (42,'Mona Rios','Portland');
INSERT INTO sattu_schema.Persons VALUES (43,'Tara Glover','Dallas');
INSERT INTO sattu_schema.Persons VALUES (44,'Levi Arnold','Denver');
INSERT INTO sattu_schema.Persons VALUES (45,'Jasmine Gutierrez','San Jose');
INSERT INTO sattu_schema.Persons VALUES (46,'Abraham Ortega','San Francisco');
INSERT INTO sattu_schema.Persons VALUES (47,'Heidi Ellis','Columbus');
INSERT INTO sattu_schema.Persons VALUES (48,'Nathaniel Morales','Washington');
INSERT INTO sattu_schema.Persons VALUES (49,'Stephen Harvey','Phoenix');
INSERT INTO sattu_schema.Persons VALUES (50,'Kimberly Jordan','El Paso');
INSERT INTO sattu_schema.Persons VALUES (51,'Jessie Carson','Nashville-Davidson');
INSERT INTO sattu_schema.Persons VALUES (52,'Juan West','Phoenix');
INSERT INTO sattu_schema.Persons VALUES (53,'Ernestine Cummings','Columbus');
INSERT INTO sattu_schema.Persons VALUES (54,'Oscar Wolfe','San Antonio');
INSERT INTO sattu_schema.Persons VALUES (55,'Evan Smith','San Antonio');
INSERT INTO sattu_schema.Persons VALUES (56,'Regina Garrett','Seattle');
INSERT INTO sattu_schema.Persons VALUES (57,'Daisy Dixon','Detroit');
INSERT INTO sattu_schema.Persons VALUES (58,'Warren Cross','Jacksonville');
INSERT INTO sattu_schema.Persons VALUES (59,'Inez Mcgee','San Francisco');
INSERT INTO sattu_schema.Persons VALUES (60,'Ellis Pena','Philadelphia');
INSERT INTO sattu_schema.Persons VALUES (61,'Lois Parker','Fort Worth');
INSERT INTO sattu_schema.Persons VALUES (62,'Charlene King','Portland');
INSERT INTO sattu_schema.Persons VALUES (63,'Olga Luna','Oklahoma City');
INSERT INTO sattu_schema.Persons VALUES (64,'Maria Jenkins','Nashville-Davidson');
INSERT INTO sattu_schema.Persons VALUES (65,'Phyllis Scott','Washington');
INSERT INTO sattu_schema.Persons VALUES (66,'Sean Palmer','Detroit');
INSERT INTO sattu_schema.Persons VALUES (67,'Carroll Weber','Oklahoma City');
INSERT INTO sattu_schema.Persons VALUES (68,'Dorothy Hudson','Nashville-Davidson');
INSERT INTO sattu_schema.Persons VALUES (69,'Maurice Chandler','Boston');
INSERT INTO sattu_schema.Persons VALUES (70,'Lloyd Cruz','Seattle');
INSERT INTO sattu_schema.Persons VALUES (71,'Christian Stanley','San Francisco');
INSERT INTO sattu_schema.Persons VALUES (72,'Emma Carter','San Diego');
INSERT INTO sattu_schema.Persons VALUES (73,'Oliver Figueroa','Austin');
INSERT INTO sattu_schema.Persons VALUES (74,'Bessie Kelly','Philadelphia');
INSERT INTO sattu_schema.Persons VALUES (75,'Malcolm Nelson','Boston');
INSERT INTO sattu_schema.Persons VALUES (76,'Rick Gibson','Houston');
INSERT INTO sattu_schema.Persons VALUES (77,'Geraldine Mendez','Jacksonville');
INSERT INTO sattu_schema.Persons VALUES (78,'Sharon Weaver','Denver');
INSERT INTO sattu_schema.Persons VALUES (79,'Jerry Cox','Indianapolis');
INSERT INTO sattu_schema.Persons VALUES (80,'Ray Bowman','Philadelphia');
INSERT INTO sattu_schema.Persons VALUES (81,'Samantha Myers','Austin');
INSERT INTO sattu_schema.Persons VALUES (82,'Jesus Pierce','Oklahoma City');
INSERT INTO sattu_schema.Persons VALUES (83,'Pearl Jacobs','Phoenix');
INSERT INTO sattu_schema.Persons VALUES (84,'Clara Barnett','Los Angeles');
INSERT INTO sattu_schema.Persons VALUES (85,'Ronald Shaw','Columbus');
INSERT INTO sattu_schema.Persons VALUES (86,'Christine Quinn','Jacksonville');
INSERT INTO sattu_schema.Persons VALUES (87,'Alton Elliott','Philadelphia');
INSERT INTO sattu_schema.Persons VALUES (88,'Krystal Moreno','San Jose');
INSERT INTO sattu_schema.Persons VALUES (89,'Rhonda Owens','Seattle');
INSERT INTO sattu_schema.Persons VALUES (90,'Katie Robbins','Phoenix');
INSERT INTO sattu_schema.Persons VALUES (91,'Jeremy Chapman','Portland');
INSERT INTO sattu_schema.Persons VALUES (92,'Douglas Young','New York');
INSERT INTO sattu_schema.Persons VALUES (93,'Kate Mcbride','Chicago');
INSERT INTO sattu_schema.Persons VALUES (94,'Jackie Mckenzie','Washington');
INSERT INTO sattu_schema.Persons VALUES (95,'Debbie Keller','Jacksonville');
INSERT INTO sattu_schema.Persons VALUES (96,'Pam Casey','Las Vegas');
INSERT INTO sattu_schema.Persons VALUES (97,'Emily Coleman','Portland');
INSERT INTO sattu_schema.Persons VALUES (98,'Terence Ross','Detroit');
INSERT INTO sattu_schema.Persons VALUES (99,'Lorene Peterson','San Francisco');
INSERT INTO sattu_schema.Persons VALUES (100,'Muhammad Ahnmad','New York');


Python Code for Lab 2:
=======================
import os
import json
from redis import Redis
import pymysql
import sys
import time 
from time import sleep

# Time to live for cached data
TTL = 30

# Read the Redis credentials from the REDIS_URL environment variable.
REDIS_ENDPOINT = '{}'
REDIS_PORT={}

# Read the DB credentials from the DB_* environment variables.
DB_HOST = '{}'
DB_USER = '{}'
DB_PASS = '{}'
DB_NAME = '{}'


mysql=pymysql.connect(host=DB_HOST, user=DB_USER, password=DB_PASS, database=DB_NAME)

def record(sql):
  with mysql.cursor() as cursor:
    cursor.execute(sql)
    return cursor.fetchone()

# Initialize the cache
Cache = Redis(host=REDIS_ENDPOINT,port=REDIS_PORT,decode_responses=True)


def fetch(sql):
  """Retrieve records from the cache, or else from the database."""
  res = Cache.get(sql)
  if res:
    print("Data is available in Cache")
    return res
  print("Data is not available in Cache, Querying the database")
  sleep(10)
  res = record(sql)[0]
  print(res)
  Cache.set(sql, res)
  Cache.expire(sql,TTL)
  return res

if __name__ == "__main__":
  if len(sys.argv) != 2:
    print("Usage: python script.py 'SQL_QUERY'")
    sys.exit(1)

  sql_query = sys.argv[1]

  start_time = time.time()
  result = fetch(sql_query)
  end_time = time.time()

  print("Result:", result)
  print("Time taken:", end_time - start_time, "seconds")




python script.py 'SQL_QUERY'