2019年12月21日
一.基本思路
1.从数据库提取数据
2.绘制成交量折线图
3.绘制OHLC柱状图（开高低收）
4.绘制k线
二.各自实现
1.取数据
def findall_hisq_data(symbol):
"""根据股票代码查询其股票历史数据"""
# 1. 建立数据库连接
connection = pymysql.connect(host='localhost',
user='root',
password='wy123456',
database='nasdaq',
charset='utf8')
# 要返回的数据
data = []
try:
# 2. 创建游标对象
with connection.cursor() as cursor:
# 3. 执行SQL操作
sql = 'select HDate, Open, High, Low, Close, Volume,Symbol ' \
'from historicalquote where Symbol = %s '
cursor.execute(sql, [symbol])
# 4. 提取结果集
result_set = cursor.fetchall()
for row in result_set:
fields = {}
fields['Date'] = row[0]
fields['Open'] = float(row[1])
fields['High'] = float(row[2])
fields['Low'] = float(row[3])
fields['Close'] = float(row[4])
fields['Volume'] = row[5]
data.append(fields)
# with代码块结束 5. 关闭游标
except pymysql.DatabaseError as error:
print('数据查询失败' + error)
finally:
# 6. 关闭数据连接
connection.close()
return data
from com.pkg1.db.db_access import findall_hisq_data
def main():
"""主函数"""
data = findall_hisq_data('AAPL')
print(data)
if __name__ == '__main__':
main()
image.png
2.成交量折线图
image.png
# coding=utf-8
import matplotlib.pyplot as plt
from com.pkg1.db.db_access import findall_hisq_data
def pot_hisvolume(dates, volumes):
"""苹果股票历史成交量折线图"""
# 设置中文字体
plt.rcParams['font.family'] = ['SimHei']
plt.rcParams['axes.unicode_minus'] = False
# 设置图表大小 x轴大一点，长一倍
plt.figure(figsize=(16, 4))
# 绘制线段
plt.plot(dates, volumes)
plt.title('苹果股票历史成交量') # 添加图表标题
plt.ylabel('成交量') # 添加y轴标题
plt.xlabel('交易日期') # 添加x轴标题
plt.show() # 显示图形
def main():
"""主函数"""
data = findall_hisq_data('AAPL')
# 从data中提取成交量数据
volume_map = map(lambda it: it['Volume'], data)
# 将volume_map转换为交量列表
volume_list = list(volume_map)
# 从data中提取日期数据
date_map = map(lambda it: it['Date'], data)
# 将date_map转换为日期列表
date_list = list(date_map)
pot_hisvolume(date_list, volume_list)
if __name__ == '__main__':
main()
3.柱状图
image.png
image.png
# coding=utf-8
import matplotlib.pyplot as plt
from com.pkg1.db.db_access import findall_hisq_data
# 设置中文字体
plt.rcParams['font.family'] = ['SimHei']
plt.rcParams['axes.unicode_minus'] = False
def pot_his_bar(date_list, p_list, ylabel):
"""绘制OHLC柱状图"""
# 绘制柱状图
plt.bar(date_list, p_list)
plt.title('苹果股票{0}历史数据'.format(ylabel)) # 添加图表标题
plt.ylabel(ylabel) # 添加y轴标题
plt.xlabel('交易日期') # 添加x轴标题
def main():
"""主函数"""
data = findall_hisq_data('AAPL')
# 从data中提取日期数据
date_map = map(lambda it: it['Date'], data)
# 将date_map转换为日期列表
date_list = list(date_map)
# 从data中提取开盘价数据
open_map = map(lambda it: it['Open'], data)
# 将open_map转换为开盘价列表
open_list = list(open_map)
# 从data中提取成最高价数据
high_map = map(lambda it: it['High'], data)
# 将high_map转换为最高价列表
high_list = list(high_map)
# 从data中提取最低价数据
low_map = map(lambda it: it['Low'], data)
# 将open_map转换为最低价列表
low_list = list(low_map)
# 从data中提取收盘价数据
close_map = map(lambda it: it['Close'], data)
# 将open_map转换为收盘价列表
close_list = list(close_map)
# 设置图表大小
plt.figure(figsize=(10, 6))
plt.subplot(4, 1, 1)
pot_his_bar(date_list, open_list, '开盘价')
plt.subplot(4, 1, 2)
pot_his_bar(date_list, close_list, '收盘价')
plt.subplot(4, 1, 3)
pot_his_bar(date_list, high_list, '最高价')
plt.subplot(4, 1, 4)
pot_his_bar(date_list, low_list, '最低价')
plt.tight_layout() # 调整布局
plt.show() # 显示图形
if __name__ == '__main__':
main()
4.绘制k线（金融库）
4.1安装如下库 mpl_finance 和 pandas
4.1.1 mpl_finance
官网 https://github.com/matplotlib/mpl-finance
安装命令
pip install https://github.com/matplotlib/mpl_finance/archive/master.zip
image.png
4.1.2 pandas （大概装了40分钟）
pip install pandas
image.png
4.2.完整实现
image.png
# coding=utf-8
import csv
import matplotlib.dates as mdates
import matplotlib.pyplot as plt
import mpl_finance
import pandas
from com.pkg1.db.db_access import findall_hisq_data
from pandas.plotting import register_matplotlib_converters
# 设置中文字体
plt.rcParams['font.family'] = ['SimHei']
plt.rcParams['axes.unicode_minus'] = False
def pot_candlestick_ohlc(datafile):
register_matplotlib_converters()
"""绘制K线图"""
# 从CSV文件中读入数据DataFrame数据结构中 (DataFrame是pandas的一种数据结构，类似于二维表格)
quotes = pandas.read_csv(datafile,
index_col=0,
parse_dates=True,
infer_datetime_format=True)
# 绘制一个子图，并设置子图大小
fig, ax = plt.subplots(figsize=(10, 5))
# 调整子图参数SubplotParams
fig.subplots_adjust(bottom=0.2)
mpl_finance.candlestick_ohlc(ax, zip(mdates.date2num(quotes.index.to_pydatetime()),
quotes['Open'], quotes['High'],
quotes['Low'], quotes['Close']),
width=1, colorup='r', colordown='g')
ax.xaxis_date()
ax.autoscale_view()
plt.setp(plt.gca().get_xticklabels(), rotation=45, horizontalalignment='right')
plt.show()
def main():
"""主函数"""
data = findall_hisq_data('AAPL')
# 列名
colsname = ['Date', 'Open', 'High', 'Low', 'Close', 'Volume']
# 临时数据文件名
datafile = 'temp.csv'
# 写如数据到临时数据文件
with open(datafile, 'w', newline='', encoding='utf-8') as wf:
writer = csv.writer(wf)
writer.writerow(colsname)
for quotes in data:
row = [quotes['Date'], quotes['Open'], quotes['High'],
quotes['Low'], quotes['Close'], quotes['Volume']]
writer.writerow(row)
# 调用绘图函数
pot_candlestick_ohlc(datafile)
if __name__ == '__main__':
main()
如果您发现本文对你有所帮助，如果您认为其他人也可能受益，请把它分享出去。
网友评论