04 May 2016
From mysql to redis with python
#!/usr/bin/python
## SQL to Redis
# import Redis and MySQL drivers
import redis
import MySQLdb
from collections import Counter
# create class
class dataProcessor(object):
# Mysql server data
MYSQL_IP_ADDRESS_SERVER = 'localhost'
MYSQL_USER = 'root'
MYSQL_PASSWORD = 'my_strong_password'
MYSQL_DATABASE_NAME = 'database_name'
# Redis server data
REDIS_SERVER = 'localhost'
# function to get data from mysql and to transfer it to redis
@staticmethod
def sql_to_redis():
r_redis = redis.StrictRedis(dataProcessor.REDIS_SERVER)
print ""
print "Connected to Redis successfully!"
database = MySQLdb.connect(dataProcessor.MYSQL_IP_ADDRESS_SERVER, dataProcessor.MYSQL_USER, dataProcessor.MYSQL_PASSWORD, dataProcessor.MYSQL_DATABASE_NAME)
print "Connected to MySQL successfully!"
print ""
cursor = database.cursor()
select = 'SELECT * FROM records WHERE location_id = 9 LIMIT 100'
# select = 'SELECT * FROM records WHERE location_id = 9'
cursor.execute(select)
data = cursor.fetchall()
# Clean redis before run again
# This is for test purpose
r_redis.delete("all_records")
# Put all data from MySQL to Redis
for row in data:
r_redis.rpush("all_records", row[3])
# Close connection to DB and Cursor
cursor.close()
database.close()
@staticmethod
def get_data_from_redis():
r2_redis = redis.StrictRedis(dataProcessor.REDIS_SERVER)
list = []
list = r2_redis.lrange("all_records", 0, 100)
print list
print ""
print "Size of list:", len(list)
print ""
word_list = [word for line in list for word in line.split()]
print word_list
print ""
words_to_count = (word for word in word_list if word[:1].isupper())
top_ten = Counter(words_to_count)
print "Top 10 Most popular words:"
print top_ten.most_common(10), "\n"