将pyecharts官方提供的数据源，替换成已经存在mysql中的数据。画出专业的k线图（k线+ma移动均线+交易额柱状图（单位是千））
参考：
【python量化交易学习】pandas获取tushare股票交易数据，写入mysql数据库中。
**
主要修改获取数据这一块的函数。
**
def get_data(input_share_code):
# 建立mysql数据库的连接
conn = create_engine('mysql+pymysql://root:123456@localhost:3306/qtrade', encoding='utf8')
# "SELECT * FROM dailytrade WHERE ts_code = '000001.SZ' "
mysql = "SELECT * FROM dailytrade WHERE ts_code = '" + input_share_code + "' order by trade_date"
# 读取交易数据
df = pd.read_sql(mysql, conn)
# df[['a','b']]#取a、b列
trade_values = df[['trade_date', 'open', 'close', 'low', 'high', 'amount', 'pre_close']]
# 将格式转化成pyechart使用的格式
trade_values_tolist = [trade_values.iloc[i].tolist() for i in range(len(trade_values))]
# 解析数据
return split_data(data=trade_values_tolist, share_code=input_share_code)
def split_data(data, share_code):
# 日期
category_data = []
# 日期，交易4个价格，交易量
values = []
# 序号，收盘价，涨跌序号，涨为1，跌为-1
volumes = []
for i, tick in enumerate(data):
category_data.append(tick[0])
values.append(tick)
volumes.append([i, tick[5], 1 if tick[6] > tick[2] else -1])
return {"categoryData": category_data, "values": values, "volumes": volumes, "share_code": share_code}
# 计算移动均线。移动平均线(MA)计算方法就是求连续若干天的收盘价的算术平均
def calculate_ma(day_count: int, data):
result: List[Union[float, str]] = []
for i in range(len(data["values"])):
if i < day_count:
result.append("-")
continue
sum_total = 0.0
for j in range(day_count):
sum_total += float(data["values"][i - j][2])
result.append(abs(float("%.3f" % (sum_total / day_count))))
return result
完整代码：
from typing import List, Union
from pyecharts.charts import Line, Bar, Grid, Page
import pandas as pd
from sqlalchemy import create_engine
from pyecharts import options as opts
from pyecharts.charts import Kline
def get_data(input_share_code):
# 建立mysql数据库的连接
conn = create_engine('mysql+pymysql://root:123456@localhost:3306/qtrade', encoding='utf8')
# "SELECT * FROM dailytrade WHERE ts_code = '000001.SZ' "
mysql = "SELECT * FROM dailytrade WHERE ts_code = '" + input_share_code + "' order by trade_date"
# 读取交易数据
df = pd.read_sql(mysql, conn)
# df[['a','b']]#取a、b列
trade_values = df[['trade_date', 'open', 'close', 'low', 'high', 'amount', 'pre_close']]
# 将格式转化成pyechart使用的格式
trade_values_tolist = [trade_values.iloc[i].tolist() for i in range(len(trade_values))]
# 解析数据
return split_data(data=trade_values_tolist, share_code=input_share_code)
def split_data(data, share_code):
# 日期
category_data = []
# 日期，交易4个价格，交易量
values = []
# 序号，收盘价，涨跌序号，涨为1，跌为-1
volumes = []
for i, tick in enumerate(data):
category_data.append(tick[0])
values.append(tick)
volumes.append([i, tick[5], 1 if tick[6] > tick[2] else -1])
return {"categoryData": category_data, "values": values, "volumes": volumes, "share_code": share_code}
# 计算移动均线。移动平均线(MA)计算方法就是求连续若干天的收盘价的算术平均
def calculate_ma(day_count: int, data):
result: List[Union[float, str]] = []
for i in range(len(data["values"])):
if i < day_count:
result.append("-")
continue
sum_total = 0.0
for j in range(day_count):
sum_total += float(data["values"][i - j][2])
result.append(abs(float("%.3f" % (sum_total / day_count))))
return result
# 开始画图
def draw_charts():
share_code = chart_data["share_code"]
# 获得数据
kline_data = [data[1:-1] for data in chart_data["values"]]
# 画K线
kline = (
Kline()
# 加载x轴数据
.add_xaxis(xaxis_data=chart_data["categoryData"])
# 加载y轴数据
.add_yaxis(
# K线名称
series_name=share_code,
# 加载y轴数据
y_axis=kline_data,
# 设置y轴数据的颜色
itemstyle_opts=opts.ItemStyleOpts(color="#ec0000", color0="#00da3c"),
)
# 设置全局变量
.set_global_opts(
# 图例配置项，隐藏图例
legend_opts=opts.LegendOpts(
is_show=False, pos_bottom=10, pos_left="center"
),
# DataZoomOpts：区域缩放配置项。type_: str = "slider",组件类型，可选 "slider", "inside"。
# is_show: bool = True, 是否显示 组件。如果设置为 false，不会显示，但是数据过滤的功能还存在。
datazoom_opts=[
opts.DataZoomOpts(
is_show=False,
type_="inside",
xaxis_index=[0, 1],
range_start=98,
range_end=100,
),
opts.DataZoomOpts(
is_show=True,
xaxis_index=[0, 1],
type_="slider",
pos_top="85%",
range_start=98,
range_end=100,
),
],
yaxis_opts=opts.AxisOpts(
is_scale=True,
splitarea_opts=opts.SplitAreaOpts(
is_show=True, areastyle_opts=opts.AreaStyleOpts(opacity=1)
),
),
tooltip_opts=opts.TooltipOpts(
trigger="axis",
axis_pointer_type="cross",
background_color="rgba(245, 245, 245, 0.8)",
border_width=1,
border_color="#ccc",
textstyle_opts=opts.TextStyleOpts(color="#000"),
),
visualmap_opts=opts.VisualMapOpts(
is_show=False,
dimension=2,
series_index=5,
is_piecewise=True,
pieces=[
{"value": 1, "color": "#00da3c"},
{"value": -1, "color": "#ec0000"},
],
),
axispointer_opts=opts.AxisPointerOpts(
is_show=True,
link=[{"xAxisIndex": "all"}],
label=opts.LabelOpts(background_color="#777"),
),
brush_opts=opts.BrushOpts(
x_axis_index="all",
brush_link="all",
out_of_brush={"colorAlpha": 0.1},
brush_type="lineX",
),
title_opts=opts.TitleOpts(
title = chart_data["share_code"]
)
)
)
# 画 MA 移动均线图
line = (
Line()
.add_xaxis(xaxis_data=chart_data["categoryData"])
.add_yaxis(
series_name="MA5",
y_axis=calculate_ma(day_count=5, data=chart_data),
is_smooth=True,
is_hover_animation=False,
linestyle_opts=opts.LineStyleOpts(width=3, opacity=0.5),
label_opts=opts.LabelOpts(is_show=False),
)
.add_yaxis(
series_name="MA10",
y_axis=calculate_ma(day_count=10, data=chart_data),
is_smooth=True,
is_hover_animation=False,
linestyle_opts=opts.LineStyleOpts(width=3, opacity=0.5),
label_opts=opts.LabelOpts(is_show=False),
)
.add_yaxis(
series_name="MA20",
y_axis=calculate_ma(day_count=20, data=chart_data),
is_smooth=True,
is_hover_animation=False,
linestyle_opts=opts.LineStyleOpts(width=3, opacity=0.5),
label_opts=opts.LabelOpts(is_show=False),
)
.add_yaxis(
series_name="MA30",
y_axis=calculate_ma(day_count=30, data=chart_data),
is_smooth=True,
is_hover_animation=False,
linestyle_opts=opts.LineStyleOpts(width=3, opacity=0.5),
label_opts=opts.LabelOpts(is_show=False),
)
.set_global_opts(xaxis_opts=opts.AxisOpts(type_="category"))
)
# 画交易量柱状图
bar = (
Bar()
.add_xaxis(xaxis_data=chart_data["categoryData"])
.add_yaxis(
series_name="Volume",
y_axis=chart_data["volumes"],
xaxis_index=1,
yaxis_index=1,
label_opts=opts.LabelOpts(is_show=False),
)
.set_global_opts(
xaxis_opts=opts.AxisOpts(
type_="category",
is_scale=True,
grid_index=1,
boundary_gap=False,
axisline_opts=opts.AxisLineOpts(is_on_zero=False),
axistick_opts=opts.AxisTickOpts(is_show=False),
splitline_opts=opts.SplitLineOpts(is_show=False),
axislabel_opts=opts.LabelOpts(is_show=False),
split_number=20,
min_="dataMin",
max_="dataMax",
),
yaxis_opts=opts.AxisOpts(
grid_index=1,
is_scale=True,
split_number=2,
axislabel_opts=opts.LabelOpts(is_show=False),
axisline_opts=opts.AxisLineOpts(is_show=False),
axistick_opts=opts.AxisTickOpts(is_show=False),
splitline_opts=opts.SplitLineOpts(is_show=False),
),
legend_opts=opts.LegendOpts(is_show=False),
)
)
# Kline And Line
overlap_kline_line = kline.overlap(line)
# Grid Overlap + Bar
grid_chart = Grid(
init_opts=opts.InitOpts(
width="1000px",
height="800px",
animation_opts=opts.AnimationOpts(animation=False),
)
)
grid_chart.add(
overlap_kline_line,
grid_opts=opts.GridOpts(pos_left="10%", pos_right="8%", height="50%"),
)
grid_chart.add(
bar,
grid_opts=opts.GridOpts(
pos_left="10%", pos_right="8%", pos_top="63%", height="16%"
),
)
grid_chart.render("1111111.html")
if __name__ == "__main__":
chart_data = get_data('000004.SZ')
draw_charts()
查询不同代码数据，进行检验。和实际符合。
股票代码000001
股票代码000004