data-service/clacHistData.py

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

__author__ = 'weijie'

import time
import datetime
from StockRestPojo import StockRestPojo
from StockHistPojo import StockHistPojo
from dbOperation import dbOperation
import db_config

try:
    db = dbOperation(db_config.db_gupiao)
    stockPojoList = db.query_list("select id, code, name, list_date listDate from t_stock_base_info where 1 = 1")
    for stockPojo in stockPojoList:
        stockRestPojo = db.query_one("SELECT max( cast( now AS DECIMAL ) ) high,	min( cast( now AS DECIMAL ) ) low,	substring_index( group_concat( now order by `addTime` ), ',', 1 ) as `open`,	substring_index( group_concat( now order by `addTime` desc ), ',', 1 ) as `close`,	substring_index( group_concat( averageprice ORDER BY `addTime` DESC ), ',', 1 ) AS `averageprice`,	substring_index( group_concat( volume ORDER BY `addTime` DESC ), ',', 1 ) volume, substring_index( group_concat( amount ORDER BY `addTime` DESC ), ',', 1 ) amount, substring_index( group_concat( date ORDER BY `addTime` DESC ), ',', 1 ) date FROM	`data_rt_"+stockPojo['code'].replace(".", "_").lower()+"` WHERE	to_days(now()) - to_days(date) = 1 and now != 0 GROUP BY	date")
        if stockRestPojo is None:
            continue
        # db.dele("delete from data_hist_" + stockPojo['code'].replace(".", "_").lower() + " where dates = '2020/10/28'")
        stockHistPojo = db.query_one("select realTime, `open`,`close`,`high`,`low`,`preclose`,`average`,`change`,`pctchange`,`volume`,`highlimit`,`amount`,`turn`,`tradestatus`,`lowlimit`,`amplitude`,`tnum`,`tafactor`,`fronttafactor`,`isststock`,`isxststock` from data_hist_" + stockPojo['code'].replace(".", "_").lower() + " ORDER BY id desc limit 1")
        if stockHistPojo is None:
            continue
        newStockHistPojo = StockHistPojo()
        newStockHistPojo.open = stockRestPojo['open']
        newStockHistPojo.close = stockRestPojo['close']
        newStockHistPojo.high = stockRestPojo['high']
        newStockHistPojo.low = stockRestPojo['low']
        newStockHistPojo.average = stockRestPojo['averageprice']
        newStockHistPojo.volume = stockRestPojo['volume']
        newStockHistPojo.highlimit = stockHistPojo['highlimit']
        newStockHistPojo.amount = stockRestPojo['amount']
        newStockHistPojo.turn = stockHistPojo['turn']
        newStockHistPojo.tradestatus = stockHistPojo['tradestatus']
        newStockHistPojo.lowlimit = stockHistPojo['lowlimit']
        newStockHistPojo.amplitude = stockHistPojo['amplitude']
        newStockHistPojo.tnum = stockHistPojo['tnum']
        newStockHistPojo.tafactor = stockHistPojo['tafactor']
        newStockHistPojo.fronttafactor = stockHistPojo['fronttafactor']
        newStockHistPojo.isststock = stockHistPojo['isststock']
        newStockHistPojo.isxststock = stockHistPojo['isxststock']

        newStockHistPojo.preclose = stockHistPojo['close']
        newStockHistPojo.change = float(stockRestPojo['close']) - float(stockHistPojo['close']) # 跌涨
        newStockHistPojo.pctchange = float(stockRestPojo['close']) - float(stockHistPojo['close']) * 100 / float(stockHistPojo['close']) #跌涨幅

        dateTime = time.mktime(time.strptime(stockRestPojo['date'] + "000000", "%Y%m%d%H%M%S"))
        year = time.localtime(dateTime).tm_year # 获取年份
        yearDatTime = time.mktime(time.strptime(str(year) + "/01/01", "%Y/%m/%d"))

        month = time.localtime(dateTime).tm_mon # 获取月份
        monthDatTime = ""
        if 10 > month:
            datTime = time.mktime(time.strptime(str(year) + "/0" + str(month) + "/01", "%Y/%m/%d"))
            monthDatTime = int(datTime)
        else:
            datTime = time.mktime(time.strptime(str(year) + "/" + str(month) + "/01", "%Y/%m/%d"))
            monthDatTime = int(datTime)

        # 获取周
        wday = time.localtime(dateTime).tm_wday
        out_date = (datetime.datetime.fromtimestamp(dateTime) + datetime.timedelta(days=-wday)).strftime("%Y/%m/%d")
        weekDatTime = time.mktime(time.strptime(out_date, "%Y/%m/%d"))

        #获取日
        dayDatTime = dateTime
        newStockHistPojo.dateYear = str(int(yearDatTime))
        newStockHistPojo.dateMonth = str(int(monthDatTime))
        newStockHistPojo.dateWeek = str(int(weekDatTime))
        newStockHistPojo.dateDay = str(int(dayDatTime))
        # newStockHistPojo.realTime = str(int(datTime))
        time_tuple = time.localtime(dayDatTime)  # 把时间戳转换成时间元祖
        result = time.strftime("%Y/%m/%d", time_tuple)  # 把时间元祖转换成格式化好的时间
        newStockHistPojo.dates = str(result)
        sql = "insert into data_hist_" + stockPojo['code'].replace(".", "_").lower() + "(`realTime`,`dates`,`open`,`close`,`high`,`low`,`preclose`,`average`,`change`,`pctchange`,`volume`,`highlimit`,`amount`,`turn`,`tradestatus`,`lowlimit`,`amplitude`,`tnum`,`tafactor`,`fronttafactor`,`isststock`,`isxststock`,`date_year`,`date_month`,`date_week`) values('"+str(newStockHistPojo.dateDay)+"','"+str(newStockHistPojo.dates)+"','" + str(newStockHistPojo.open) + "','" + str(newStockHistPojo.close) + "','" + str(newStockHistPojo.high)+ "','" + str(newStockHistPojo.low) + "','" + str(newStockHistPojo.preclose) + "','" + str(newStockHistPojo.average) + "','" + str(newStockHistPojo.change) + "','" + str(newStockHistPojo.pctchange) + "','" + str(newStockHistPojo.volume) + "','" + str(newStockHistPojo.highlimit) + "','" + str(newStockHistPojo.amount) + "','" + str(newStockHistPojo.turn) + "','" + str(newStockHistPojo.tradestatus) + "','" + str(newStockHistPojo.lowlimit) + "','" + str(newStockHistPojo.amplitude) + "','" + str(newStockHistPojo.tnum) + "','" + str(newStockHistPojo.tafactor) + "','" + str(newStockHistPojo.fronttafactor) + "','" + str(newStockHistPojo.isststock) + "','" + str(newStockHistPojo.isxststock) + "','" + str(newStockHistPojo.dateYear) + "','" + str(newStockHistPojo.dateMonth) + "','" + str(newStockHistPojo.dateWeek) + "');"
        db.insert(sql)
    print("运行完成")
    db.close
except Exception as ee:
    print("error >>>",ee)