定时读取mysql的数据

定时读取mysql的数据

# -*- coding: utf-8 -*-
import pandas as pd
import pymysql

#mysql数据库的配置
dbconn=pymysql.connect(
  host="127.0.0.1",
  database="MySQL",
  user="root",
  password="zhangmian",
  port=3306,
  charset='utf8'
 )

#用Python执行的sql语句
sqlcmd="select date from `foreign exchange`.usdjpy15"

#用pandas.read_sql的读取mysql的数据
a=pd.read_sql(sqlcmd,dbconn)
#取前5行数据
b=a.head()
print(b)

# -*- coding: utf-8 -*-
import apscheduler
import time
from apscheduler.schedulers.blocking import BlockingScheduler
import pandas as pd
import pymysql
import subprocess
import os

def my_job():
    subprocess.Popen('python F:/backtest/basic-check/basic-check/test_kang.testmysql.py')  #用Popen进程操作打开.py文件
    a=pd.read_sql(sqlcmd,dbconn)   #用pandas中的read_sql读取mysql数据
    b=a.head()
    print(b)


   # print (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time())))
  #  subprocess.popen()

sched = BlockingScheduler()
sched.add_job(my_job, 'interval', seconds=5)
sched.start()

import pandas as pd
import time
from datetime import datetime
import pymysql
import os
import xlwt
# 定义从MySQL数据库中取数的函数
def Get_Data_From_MySQL():
	db = pymysql.connect(
		host = '127.0.0.1',
		database = "world",
        user = "root",
        password = "******",# 此处password即MySQL数据库密码
        port = 3306,
        charset = 'utf8'
		)
	cursor = db.cursor()
	effect_row = cursor.execute(
        '''SELECT Code 编号, Name 名称, Continent 所在大州,Region 国家,HeadOFState 首脑,Capital 首都 From world.country LIMIT 100'''
		)
	row_1 = cursor.fetchall()
# 查询结果存储到Excel文件中
	write_in_time = time.strftime('%Y%m%d_%H%M%S')
	workbook = xlwt.Workbook(encoding = 'utf-8')
	booksheet = workbook.add_sheet(write_in_time,cell_overwrite_ok = True)
	for i,field_desc in enumerate(cursor.description):
		booksheet.write(0,i,field_desc[0])
		i +=1
	for i,row in enumerate(row_1):
		for j,col in enumerate(row):
			booksheet.write(i+1,j,col)
	folder_name = os.path.abspath(r'C:\\Users\\rjs\\Desktop\\查询结果') # Excel文件存储路径
	save_file_way = folder_name + r'//' +str(write_in_time)+ '本地数据库查询结果''.xls'  # Excel文件名
	workbook.save(save_file_way) 

while True:
	if datetime.now() < datetime(2018,5,10,23,59,59):  #查询截止时间为2018-5-10 23:59:59
		Get_Data_From_MySQL()
		time.sleep(3600) #每隔一个小时查询一次
	else:
		print("已到截止时间，停止取数。")
		break