【python量化交易学习】pandas获取mysql数据，使用pyecharts画K线图，ma移动均线。

将pyecharts官方提供的数据源，替换成已经存在mysql中的数据。画出专业的k线图（k线+ma移动均线+交易额柱状图（单位是千））

参考：

【python量化交易学习】pandas获取tushare股票交易数据，写入mysql数据库中。

pyecharts 配置项

pyecharts专业K线图代码示例

**

主要修改获取数据这一块的函数。

**

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