"""
the box is in the pen.
spirit is willing but the flesh is weak.

从Excel文件中读取数据写入数据库
create database stock default character set utf8mb4;

use stock;

create table tb_baba_stock
(
stock_id bigint unsigned auto_increment comment '编号',
trade_date date not null comment '交易日',
high_price decimal(16, 6) not null comment '最高价',
low_price decimal(16, 6) not null comment '最低价',
open_price decimal(16, 6) not null comment '开盘价',
close_price decimal(16, 6) not null comment '收盘价',
trade_volume bigint unsigned not null comment '交易量',
primary key (stock_id)
);

grant insert, select on stock.* to 'guest'@'10.7.174.%';
"""
import openpyxl
import pymysql

wb = openpyxl.load_workbook('阿里巴巴2020年股票数据.xlsx')
ws = wb.active
params = []
for row_idx in range(2, ws.max_row + 1):
    values = []
    for col_idx in range(1, ws.max_column):
        values.append(ws.cell(row_idx, col_idx).value)
    params.append(values)

conn = pymysql.connect(host='10.7.174.103', port=3306,
                       user='guest', password='Guest.618',
                       database='stock', charset='utf8mb4')
try:
    with conn.cursor() as cursor:
        # 执行批量插入操作
        cursor.executemany(
            'insert into tb_baba_stock '
            '   (trade_date, high_price, low_price, open_price, close_price, trade_volume) '
            'values '
            '   (%s, %s, %s, %s, %s, %s)',
            params
        )
    conn.commit()
except pymysql.MySQLError as err:
    print(err)
    conn.rollback()
finally:
    conn.close()