记录实验1-将下载的股票数据存入MySQL

import datetime
import os
import pandas as pd
from sqlalchemy import create_engine


# 读取文件名称
def readFileName(filePath):
    print("正在读取文件名称。。。。")
    fileNameList = os.listdir(filePath)
    return fileNameList


# 通过获取的文件名称读取单个Excel文件内容
def readExcel(filePath, fileName):
    print("正在读取文件 " + fileName + " 。。。。")
    df = pd.read_excel(filePath + "\\" + fileName)
    if len(df.index) > 0:
        if filePath.__contains__("allstock"):
            df['股票代码'] = df['股票代码'][0].split(".")[0]    # 股票代码整理
            df = df.sort_values(by=['交易日期'])    # 日期升序
        elif filePath.__contains__("allindex"):
            df['代码'] = df['代码'][0].split(".")[0]
            df = df.sort_values(by=['日期'])
        return df
    else:
        return df


# 存入MySQL操作
def toSql(df, connect, tableName):
    print("正在将文件 " + tableName + " 存入MySQL。。。。")
    try:
        if len(df.index) > 0:
            # 将数据写入sql
            pd.io.sql.to_sql(df, tableName.split(".")[0], con=connect, if_exists='replace', index=False)
            return True
        elif len(df.index) == 0:
            print("文件 " + tableName + " 为空")
            return False
        else:
            return False
    except Exception:
        return False


# 将数据存入数据库
def getExcelDataToSQL(filePath, fileNameList, connect):
    print("从Excel到数据库。。。。")
    for i in fileNameList:
        if (not toSql(readExcel(filePath, i), connect, i)):  # i是带后缀的文件名称
            continue


def begin(path, connect):
    fileNameList = readFileName(path)  # 名称列表
    getExcelDataToSQL(path, fileNameList, connect)  # 将数据存入MySQL


if __name__ == '__main__':
    # create_engine('mysql+pymysql://用户名:密码@主机/库名?charset=utf8')
    path1 = "D:\\StudyFiles\\ClassFile\\股票证券实验报告\\证券日交易行情和文本基本信息\\ExcelFiles\\allindex"
    connect1 = create_engine('mysql+pymysql://root:747699@localhost/allindex?charset=utf8')
    path2 = "D:\\StudyFiles\\ClassFile\\股票证券实验报告\\证券日交易行情和文本基本信息\\ExcelFiles\\allstock"
    connect2 = create_engine('mysql+pymysql://root:747699@localhost/allstock?charset=utf8')
    print("******************** 程序开始 ******************** ")
    start = datetime.datetime.now()
    begin(path1, connect1)
    begin(path2, connect2)
    end = datetime.datetime.now()
    t = end - start
    print("******************** 程序结束 ******************** ")
    print(str(int(t.seconds / 60)) + " 分 - " + str(t.seconds % 60) + " 秒")