mysql定时取redis数据_Python定时从Mysql提取数据存入Redis的实现

设计思路：

1.程序一旦run起来，python会把mysql中最近一段时间的数据全部提取出来

2.然后实例化redis类，将数据简单解析后逐条传入redis队列

3.定时器设计每天凌晨12点开始跑

ps：redis是个内存数据库，做后台消息队列的缓存时有很大的用处，有兴趣的小伙伴可以去查看相关的文档。

# -*- coding:utf-8 -*-

import MySQLdb

import schedule

import time

import datetime

import random

import string

import redis

# get the data from mysql

class FromSql(object):

def __init__(self, conn):

self.conn = conn

def acquire(self):

cursor = self.conn.cursor()

try:

sql = "SELECT * FROM test WHERE TO_DAYS(NOW()) - TO_DAYS(t) <= 1"

cursor.execute(sql)

rs = cursor.fetchall()

#print (rs)

for eve in rs:

print('%s, %s, %s, %s' % eve)

copy_rs = rs

cursor.close()

return copy_rs

except Exception as e:

print("The error: %s" % e)

class RedisQueue(object):

def __init__(self, name, namespace='queue', **redis_kwargs):

"""The default connection parameters are: host='localhost', port=6379, db=0"""

self.__db= redis.Redis(**redis_kwargs)

self.key = '%s:%s' %(namespace, name)

def qsize(self):

return self.__db.llen(self.key)

def put(self, item):

self.__db.rpush(self.key, item)

def get(self, block=True, timeout=None):

if block:

item = self.__db.blpop(self.key, timeout=timeout)

else:

item = self.__db.lpop(self.key)

if item:

item = item[1]

return item

def get_nowait(self):

return self.get(False)

if __name__ == "__main__":

# connect mysqldb

conn_sql = MySQLdb.connect(

host = '127.0.0.1',

port = 3306,

user = 'root',

passwd = '',

db = 'test',

charset = 'utf8'

)

def job_for_redis():

get_data = FromSql(conn_sql)

data = get_data.acquire()

q = RedisQueue('test',host='localhost', port=6379, db=0)

for single_data in data:

for meta_data in single_data:

q.put(meta_data)

print(meta_data)

print("All data had been inserted.")

"""

try:

schedule.every().day.at("00:00").do(job_for_redis)

except Exception as e:

print('Error: %s'% e)

# finally:

# conn.close()

while True:

schedule.run_pending()

time.sleep(1)

"""

补充知识：python定时获取汇率存入数据库

python定时任务：

我们可以使用 轻量级的第三方模块schedule。首先先安装：pip install schedule

定时任务的的小测试：

import schedule

import time

def job():

print("I'm working...")

schedule.every(10).minutes.do(job) # 每隔10分钟执行一次任务

schedule.every().hour.do(job) # 每隔一小时执行一次任务

schedule.every().day.at("10:30").do(job) # 每天10：30执行一次任务

schedule.every(5).to(10).days.do(job) # 每5-10天执行一次任务

schedule.every().monday.do(job) # 每周一的这个时候执行一次任务

schedule.every().wednesday.at("13:15").do(job) # 每周三13:15执行一次任务

while True:

schedule.run_pending()