数据库设计
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数据库工具类
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']
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)
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)
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:]
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])
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)))
res_map.__setitem__(name, rate)
print('--------------')
min = Decimal(999.0)
for name in res_map.keys():
if res_map.get(name) < min:
min = res_map.get(name)
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)
降序输出股票实时价格与年最低价格差
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/85dc3add909c03c59a967af66d3e3875.png)
降序输出热门股票实时价格与年最低价格差
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/9cc4630a6c1692d55ad2b368f880a405.png)