15天搭建ETF量化交易系统Day14—MySQL回测数据管理指南

搭建过程

每个交易者都应该形成一套自己的交易系统。

很多交易者也清楚知道，搭建自己交易系统的重要性。现实中，从０到１往往是最难跨越的一步。

授人鱼不如授人以渔，为了帮助大家跨出搭建量化系统的第一步，我们决定推出这个主题系列。

这个系列中，我们用Python从０开始一步步搭建出一套ETF量化交易系统（选择ETF标的是因为对于普通交易者来说，ETF相对于选强势股难度要小，而且没有退市风险）。大家可以跟随着我们的实现路径来一起学习，从过程中掌握方法。

掌握了方法之后，你可以换成期货系统、比特币系统、美股系统，然后在实战中不断去完善自己的系统了。

搭建一套ETF量化交易系统涉及多个模块和组件的协同工作，包括数据源模块、量化策略模块、可视化模块、数据库模块、回测评估模块、自动交易模块等等。

DAY1链接如下：15天搭建ETF量化交易系统Day1—数据源模块
DAY2链接如下：15天搭建ETF量化交易系统Day2—图形显示模块
DAY3链接如下：15天搭建ETF量化交易系统Day3—上手经典回测框架
DAY4链接如下：15天搭建ETF量化交易系统Day4—玩转海龟交易策略
DAY5链接如下：15天搭建ETF量化交易系统Day5—打造实盘量化机器人
DAY6链接如下：15天搭建ETF量化交易系统Day6—打通同花顺自动交易
DAY7链接如下：15天搭建ETF量化交易系统Day7—全自动化交易系统
DAY8链接如下：15天搭建ETF量化交易系统Day8—强化自动交易模块
DAY9链接如下：15天搭建ETF量化交易系统Day9—玩大A必学网格策略

DAY10链接如下：15天搭建ETF量化交易系统Day10—借用网格思想做仓位管理

DAY11链接如下：15天搭建ETF量化交易系统Day11—miniQMT自动交易真香！

DAY12链接如下：15天搭建ETF量化交易系统Day12—雏形系统关键代码讲解！

DAY13链接如下：15天搭建ETF量化交易系统Day13—入手MySQL搭建数据库

回测是量化系统中重要环节之一，它通过对历史数据的模拟交易，评估投资策略的有效性和盈利能力。搭建数据库为回测提供了数据的支持，是不可或缺的基础设施。

DAY13我们已经入手了MySQL数据库，但是要把MySQL用于量化策略回测阶段的数据管理还有很多问题要解决！比如：

“如何为每个股票在MySQL中创建表？”
“每天获取的股票数据怎么存入到MySQL数据库中？”
“如何从MySQL中取出我们想要的股票数据呢？”
……

本期我们以回测数据管理为例，分享MySQL作为量化回测数据库的操作方法。

• 如何创建个股数据表

• 如何避免重复创建数据表

• 如何插入股票数据表

• 如何更新股票数据表

• 如何查询股票数据表

• 如何删除股票数据表

如何创建单个ETF数据表

存储ETF数据需要先为该品种创建一个数据表。完整的创建表的语句如下所示：

为大家解读下语句的含义，遵循的SQL语法格式为：

CREATE TABLE <表名> ([表定义选项]);

其中，[表定义选项]的格式为：<列名1> <类型1> [,…] <列名n> <类型n>

“CREATE TABLE”是创建表的关键字。这里的表名是 “minute_tb”，表定义的选项包括了ID、时间戳、代码、最高价、最低价、收盘价、开盘价、成交量、成交额、涨跌幅、描述。

“PRIMARY KEY”关键字是来指定数据表的主键的，我们既可以为表中的一个字段设置主键，也可以为表中多个字段设置联合主键。但是不论使用哪种方法，在一个表中主键值必须唯一标识表中的每一行，且不能为 NULL，即表中不可能存在有相同主键值的两行数据。这是唯一性原则。此处我们把“ID”字段设置为了数据表的主键约束，

我们把“ID”字段添加 AUTO_INCREMENT属性来实现主键自增长。当主键定义为自增长后，这个主键的值就不再需要用户输入数据了，而由数据库系统根据定义自动赋值。每增加一条记录，主键会自动以相同的步长进行增长。需要注意的是：

一个表中只能有一个字段使用 AUTO_INCREMENT 约束，且该字段必须有唯一索引，以避免序号重复（即为主键或主键的一部分）。

AUTO_INCREMENT约束的字段必须具备 NOT NULL 属性。

AUTO_INCREMENT 约束的字段只能是整数类型（TINYINT、SMALLINT、INT、BIGINT 等）。

AUTO_INCREMENT 约束字段的最大值受该字段的数据类型约束，如果达到上限，AUTO_INCREMENT 就会失效。

如何避免重复创建数据表

在更新ETF数据时，如果数据库中不存在该ETF的数据表，那么我们需要为这个ETF创建一个数据表，但是如果已经存在数据表的话，只需要更新数据就行。

我们可以使用SQL语句“show tables”获取当前数据库中所有表的名称，然后查看其中是否包含该股的数据表即可。

如何插入ETF数据表

每天收盘后要把当天新增的股票数据插入到数据表中，可以使用“INSERT INTO”语句。

insert into有两种语法，分别如下：

一种语法无需指定要插入数据的列名，只需提供被插入的值即可，比如INSERT INTOtable_name VALUES (value1,value2,value3,...);  

另外一种是需要指定插入数据的列名，并且插入的值需要和列名一一对应：

cursor.execute("INSERT INTO minute_tb (ID, Time, Code, High, Low ,Close, Open, Volume, Amount, PctChg, Description) \
            VALUES (1, '2019-1-1', 600123, 10.12, 10.12, 10.12, 10.12, 111111, 10.12, 2.12, 'TP')")

如何更新股票数据表

如果要更新数据表中已有的数据，那么可以使用“UPDATE” 语句。需要注意的是，使用“UPDATE”时WHERE 子句规定哪条记录或者哪些记录需要更新，如果省略了 WHERE 子句，所有的记录都将被更新！

如下所示，是让ID为1的那条数据额Code更新为“510050”“

cursor.execute("UPDATE minute_tb set Code = 510050 where ID=1")

如何查询ETF数据表

在分析ETF的时候就需要从数据库中查询ETF数据表中的数据了，此时可以使用 “SELECT”语句，这里的“*”号是表示从数据表中获取全部的数据。如果要再针对性地获取数据，可以在语句中增加条件判断。

然后再通过fetchone()返回满足条件的一行数据，或者通过fetchall()返回满足条件的全部数据。

cursor.execute("select * from minute_tb")

如何删除ETF数据表

为了对数据库做有效率的管理，有时候我们会决定我们需要从数据库中删除一个表格。此时使用“DROP TABLE”语句删除股票数据表：

cursor.execute("DROP TABLE minute_tb")

封装接口

接下来我们把前面那些特定的操作方法封装为类，这样可以在系统的回测模块中进行调用。

class mysql_handler():


    # 需要更改为自己的数据库配置
    host = 'localhost'
    port = 3306
    db = 'quanttrader'
    user = 'root'
    password = ''
    table_name = 'minute_tb'


    def __init__(self):
        # 连接数据库
        self.conn = self.get_connection()
        # 创建执行SQL语句游标，通过游标对象就可对数据库进行增、删、查、改 
        self.cursor = self.conn.cursor()  # 得到一个可以执行SQL语句的光标对象, 执行完毕返回的结果集默认以元组显示


    # ---- 用pymysql 操作数据库
    def get_connection(self):
        conn = pymysql.connect(host=self.host, port=self.port, db=self.db, user=self.user, password=self.password)
        return conn


    def detect_table_exist(self, conn, cursor, table):


        status = False


        sql = "show tables"
        cursor.execute(sql)
        tables = cursor.fetchall()


        tables_list = re.findall('(\'.*?\')',str(tables))
        tables_list = [re.sub("'",'',each)for each in tables_list]


        if table in tables_list:
            print("table exists")
            status = True
        else:
            print("table not exists")
            status = False
        return status


    def creat_table(self, conn, cursor, sql):


        try :
            cursor.execute(sql)
            conn.commit()
            print("Successfully added table")
        except:
            conn.rollback()
            print("UnSuccessfully added table")


    def exe_sql(self, sql):
        try:
            self.cursor.execute(sql)
            self.conn.commit()
        except:
            traceback.print_exc()


    def quit(self):
        self.cursor.close()
        self.conn.commit()
        self.conn.close()


    def etf_data_source(self, code="510050", period="60"):


        data = ak.fund_etf_hist_min_em(symbol=code, period="60", adjust="")
        data.rename(columns={"时间": "datetime", "收盘": "close", "最高": "high", "最低": "low",
                            "成交量":"vol", "成交额":"amount", "涨跌幅":"pctchg"}, inplace=True)
        #data.set_index("date", drop=True, inplace=True)
        return data


    def check_it(self, type="ETF"):


        # 篇幅原因 此处省略部分代码
        # 创建表
        """
        create table 表名(字段名 字段类型，字段名 字段类型， ......)
        """
        if self.detect_table_exist(conn, cursor, self.table_name) == False:
        
            # TEXT 可变长度 65535个字符
            # CHAR 固定长度 255个字符
            createTable =   """
                             """
            self.creat_table(conn, cursor, createTable)


            # 插入表TP
            # 篇幅原因 此处省略部分代码
            
            # 更新表
            # 篇幅原因 此处省略部分代码
            
            # df格式转换入表
            # 篇幅原因 此处省略部分代码
            conn.commit()


            # 查询表
            # 篇幅原因 此处省略部分代码
            
        # 删除表(测试时候添加 正式使用时去除)
        cursor.execute("DROP TABLE minute_tb")


        # 关闭数据库连接
        cursor.close()
        conn.close()

总结

掌握了MySQL作为量化回测数据库的操作方法，接下来我们可以持续收集历史数据并存储起来，当需要优化和改进策略时，就可以从数据库中加载历史数据用于策略回测！

说明

此系列为连载专栏，完整代码会上传知识星球《玩转股票量化交易》！作为会员们的学习资料。

想要加入知识星球《玩转股票量化交易》的小伙伴记得先微信call我获取福利！

非星球会员需要的话，需要单独联系我购买！

知识星球介绍点击：知识星球《玩转股票量化交易》精华内容概览