python量化投资实战-股票实盘分析

数据库设计

CREATE TABLE `stock_lowest` (
	`stock_code` VARCHAR(50) NOT NULL COMMENT '股票代码' COLLATE 'utf8_unicode_ci',
	`lowest_price` DECIMAL(10,4) NOT NULL DEFAULT '0.0000' COMMENT '近一年最低价格',
	PRIMARY KEY (`stock_code`) USING BTREE
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
;

CREATE TABLE `stock_raise_stop` (
	`trade_date` DATE NOT NULL COMMENT '交易日期',
	`stock_code` VARCHAR(50) NOT NULL COMMENT '股票代码' COLLATE 'utf8_unicode_ci',
	`NAME` VARCHAR(50) NOT NULL COMMENT '股票名称' COLLATE 'utf8_unicode_ci',
	`rate` DECIMAL(10,4) NOT NULL DEFAULT '0.0000' COMMENT '上涨比例',
	PRIMARY KEY (`trade_date`, `stock_code`) USING BTREE
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
;

python数据库工具类

# !/usr/bin/python
# -*- coding:utf-8 -*-

import pymysql
import json

class DBUtil:
    """mysql util"""
    db = None
    cursor = None

    def __init__(self,dict):
        self.host = dict['host']
        self.port = dict['port']
        self.userName = dict['userName']
        self.password = dict['password']
        self.dbName = dict['dbName']
        self.charsets = dict['charsets']
        # print("配置文件：" + json.dumps(dict))

    # 链接数据库
    def get_con(self):
        """ 获取conn """
        self.db = pymysql.Connect(
            host=self.host,
            port=self.port,
            user=self.userName,
            passwd=self.password,
            db=self.dbName,
            charset=self.charsets
        )
        self.cursor = self.db.cursor()

    # 关闭链接
    def close(self):
        self.cursor.close()
        self.db.close()

    # 主键查询数据
    def get_one(self, sql):
        res = None
        try:
            self.get_con()
            self.cursor.execute(sql)
            res = self.cursor.fetchone()
            self.close()
        except Exception as e:
            print("查询失败！" + str(e))
        return res

    # 查询列表数据
    def get_all(self, sql):
        res = None
        try:
            self.get_con()
            self.cursor.execute(sql)
            res = self.cursor.fetchall()
            self.close()
        except Exception as e:
            print("查询失败！" + str(e))
        return res

    # 插入数据
    def __insert(self, sql):
        count = 0
        try:
            self.get_con()
            count = self.cursor.execute(sql)
            self.db.commit()
            self.close()
        except Exception as e:
            print("操作失败！" + str(e))
            self.db.rollback()
        return count

    # 保存数据
    def save(self, sql):
        return self.__insert(sql)

    # 更新数据
    def update(self, sql):
        return self.__insert(sql)

    # 删除数据
    def delete(self, sql):
        return self.__insert(sql)

利用python获取实盘数据并写入数据库

import sys
import time

import requests
import json
import re
from decimal import Decimal
import MySQLdb
from fund import mysql_utils

map = {}
mysql_config = {
    "host": "localhost",
    "port": 3306,
    "userName": "root",
    "password": "root",
    "dbName": "finance",
    "charsets": "UTF8"
}

dbUtil = mysql_utils.DBUtil(mysql_config)

# for stock_code in map.keys():
#     try:
#         sql = """INSERT INTO stock_lowest(stock_code,lowest_price) VALUES ('{}',{})""".format(
#             stock_code,
#             map.get(stock_code))
#         dbUtil.save(sql)
#     except Exception as e:
#         error_msg = sys.exc_info()
#         print(str(error_msg))
#         print("fail code ->{}".format(stock_code))
#         pass
#     continue

sql = "select * from stock_lowest where lowest_price <> 0"
for l in dbUtil.get_all(sql):
    map[l[0]] = l[1]

map['sh002390'] = 6.4
map['sz002241'] = 25.15

lst = map.keys()
url = 'http://hq.sinajs.cn/list={}'.format(','.join(lst))

resp = requests.get(url)
# print(resp.text)
# print(resp.status_code)
res_map = {}
cur_date=time.strftime("%Y-%m-%d", time.localtime())
if resp.ok:
    for line in resp.text.split("\n"):
        code = line.split('=')[0][11:]
        # print(code)
        arr = line.split("\"")
        if len(arr) > 2:
            data = arr[1].split(",")
            if len(data) > 5:
                name = data[0]
                current_price = Decimal(data[3])
                yesterday_price = Decimal(data[2])
                # print(name, current_price)
                raise_rate = (current_price / yesterday_price) - Decimal(1)

                if (Decimal(raise_rate) >= Decimal(0.09)):
                    print(name, (current_price / yesterday_price) - 1)
                    sql = """INSERT INTO stock_raise_stop(trade_date,stock_code,NAME,rate) VALUES ('{}','{}','{}',{})""".format(cur_date,code,name,raise_rate)
                    dbUtil.save(sql)

                # rate = (Decimal(data[3]) - Decimal(map.get(code))) / Decimal(map.get(code))
                rate = (Decimal(data[3]) - Decimal(map.get(code)))
                res_map.__setitem__(name, rate)

print('--------------')
min = Decimal(999.0)
for name in res_map.keys():
    if res_map.get(name) < min:
        # print(name)
        min = res_map.get(name)

# print(min)

# sort_map = {}
# for k in res_map.keys():
#     sort_map.__setitem__(res_map.get(k), k)
# sort_keys = sorted(sort_map.keys(), reverse=True)

sort_map = sorted(res_map.items(), key=lambda item: item[1], reverse=True)
for item in sort_map:
    print(item)

sql = "SELECT b.name FROM stock_lowest a JOIN (SELECT  stock_code,name FROM stock_raise_stop GROUP BY stock_code,name) b ON a.stock_code =b.stock_code where a.lowest_price <> 0"
hot_map={}
for l in dbUtil.get_all(sql):
    hot_map.__setitem__(l[0],res_map.get(l[0]))

print('----------------')
sort_hot_map = sorted(hot_map.items(), key=lambda item: item[1], reverse=True)
for item in sort_hot_map:
    print(item)

降序输出股票实时价格与年最低价格差

降序输出热门股票实时价格与年最低价格差