Step 1 – Create Ubuntu EC2 instance and install Python if not already installed
sudo apt update
python -V
sudo apt install python-minimal
python -V
2.7.17
sudo apt install python-pip
Step 2 – Install MySQL connector and client and prep Ubuntu EC2 instance
pip install mysql-connector-python
pip install mysql-connector-python-rf
sudo chown ubuntu:ubuntu -R /opt
cd /opt
mkdir rds
cd rds
sudo apt install mysql-client
wget https://demos3gl.s3.us-east-2.amazonaws.com/employees.sql
Step 3 – Create Amazon RDS MySQL instance
Step 4 – Create employees database
mysql -h database-1.cmfn1jtqhnyo.us-east-2.rds.amazonaws.com -u admin -p
create database employees;
use employees;
source employees.sql
select count(1) from employees;
exit
Step 5 – Create python script to connect to mysql and insert data
vi rds.py
import mysql.connector
hostname = 'database-1.cmfn1jtqhnyo.us-east-2.rds.amazonaws.com'
username = 'admin'
password = 'Oracle_Dba12#'
database = 'employees'
# Simple routine to run a query on a database and print the results:
def doQuery(conn) :
cur = conn.cursor()
cur.execute("SELECT emp_no, first_name, last_name, email_id FROM employees limit 10")
for emp, firstname, lastname, email in cur.fetchall() :
print emp, firstname, lastname, email
def mysqlConnector() :
print "\n\nUsing mysql.connector"
print "---------------------"
myConnection = mysql.connector.connect(host=hostname, user=username, passwd=password, db=database)
doQuery(myConnection)
myConnection.close()
def createOrder() :
print "\n\nUsing the above connector, insert a new record in the orders table"
conn = mysql.connector.connect(host=hostname, user=username, passwd=password, db=database)
cur = conn.cursor()
create_order_sql = (
"insert into orders ("
" OrderID, OrderUserID, OrderAmount, OrderShipName, OrderShipAddress, OrderShipAddress2,"
" OrderCity, OrderState, OrderZip, OrderCountry, OrderPhone, OrderFax, OrderShipping,"
" OrderTax, OrderEmail"
") values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
)
order_data = (
'ord00111', 16258, 125.50, 'Starfleet Academy', '1225 Audelia Dr', '#1971',
'Fort Baker', 'CA', '90210', 'US', '+1 555 444 3333', '+1 555 222 1111', 0,
10.75, 'kirk@starfleet.com'
)
cur.execute(create_order_sql, order_data)
conn.commit()
cur.close()
conn.close()
def main() :
mysqlConnector()
createOrder()
main()
Step 6 – Execute rds.py
mysql -h database-1.cmfn1jtqhnyo.us-east-2.rds.amazonaws.com -u admin -p
select * from employees.orders;
exit
python rds.py
mysql -h database-1.cmfn1jtqhnyo.us-east-2.rds.amazonaws.com -u admin -p
select * from employees.orders;
Step 7 – Create Amazon Redis instance
Step 8 – Create python script to demonstrate ElastiCache
sudo pip install redis
vi rdscache.py
import mysql.connector
import pickle
import redis
redis_host = 'democache-001.ipd5e9.0001.use2.cache.amazonaws.com'
db_host = 'database-1.cmfn1jtqhnyo.us-east-2.rds.amazonaws.com'
db_username = 'admin'
db_password = 'Oracle_Dba123#'
database = 'employees'
class Order:
def __init__(self, orderid, userid, shipping, email):
self.orderid = orderid
self.userid = userid
self.shipping = shipping
self.email = email
self.tostring()
def tostring(self):
print " Order is {0} {1} {2} {3}".format(self.orderid, self.userid, self.shipping, self.email)
def getAllOrders() :
print "\n\nUsing mysql.connector"
print "---------------------"
conn = mysql.connector.connect(host=db_host, user=db_username, passwd=db_password, db=database)
cur = conn.cursor()
cur.execute("SELECT OrderID, OrderUserID, OrderShipName, OrderEmail FROM orders")
for orderid, userid, shipping, email in cur.fetchall() :
order_obj = Order(orderid, userid, shipping, email)
cur.close()
conn.close()
def getOrder(order_id):
print "\nGetting the order", order_id
red = redis.StrictRedis(host=redis_host, port=6379, db=0)
red_obj = red.get(order_id)
if red_obj != None:
print "Object found in cache, not looking in DB"
#Deserialize the object coming from Redis
order_obj = pickle.loads(red_obj)
order_obj.tostring()
else:
print "No key found in redis, going to database to take a look"
conn = mysql.connector.connect(host=db_host, user=db_username, passwd=db_password, db=database)
cur = conn.cursor()
cur.execute("SELECT OrderID, OrderUserID, OrderShipName, OrderEmail FROM orders where OrderID='%s'" % (order_id))
for orderid, userid, shipping, email in cur :
order_obj = Order(orderid, userid, shipping, email)
#Serialize the object
ser_obj = pickle.dumps(order_obj)
red.set(order_id, ser_obj)
print " Order fetched from DB and pushed to redis"
cur.close()
conn.close()
def main() :
getAllOrders()
getOrder('ord00111')
main()
Step 9 – Execute rdscache.py
python rdscache.py
Using mysql.connector
---------------------
Order is ord00111 16258 Starfleet Academy kirk@starfleet.com
Getting the order ord00111
No key found in redis, going to database to take a look
Order is ord00111 16258 Starfleet Academy kirk@starfleet.com
Order fetched from DB and pushed to redis
python rdscache.py
Using mysql.connector
---------------------
Order is ord00111 16258 Starfleet Academy kirk@starfleet.com
Getting the order ord00111
Object found in cache, not looking in DB
Order is ord00111 16258 Starfleet Academy kirk@starfleet.com