python - pandas读取excel示例 - 链接mysql示例

import os
import sys
import pymysql, traceback
import pandas as pd
from typing import List

# database_flag = sys.argv[1]
# DATABASE_CONFIG = {
#     "test": {
#         'user': 'xxxxxxx',
#         'passwd': 'xxxxxx',
#         'host': 'xx.xx.xx.xx',
#         'database': 'xxxx',
#         'port': 3306
#     },
#     "prod": {
#         'user': 'xxxxx',
#         'passwd': 'xxxxxx',
#         'host': 'xx.xx.xx.xx',
#         'database': 'xxxxxx',
#         'port': 3306
#     }
# }
# db = pymysql.connect(host=DATABASE_CONFIG.get(database_flag).get("host"),
#                      user=DATABASE_CONFIG.get(database_flag).get("user"),
#                      password=DATABASE_CONFIG.get(database_flag).get("passwd"),
#                      database=DATABASE_CONFIG.get(database_flag).get("database"),
#                      port=DATABASE_CONFIG.get(database_flag).get("port"),
#                      charset="utf8")
#
# cursor = db.cursor(cursor=pymysql.cursors.DictCursor)


def get_data_from_excel(excel_path, sheet_name, header, usecols, names):
    """
    读取excel表格
    """
    try:
        data = pd.read_excel(excel_path, sheet_name=sheet_name, header=header,
                             usecols=usecols,
                             names=names
                             )
        print(f"data = {data}")
        for index, row in data.iterrows():
            name = row['name']
            age = row['age']
            print(f"name = {name} | age = {age}")
    except:
        traceback.print_exc()


if __name__ == "__main__":
    try:
        # 读取excel
        excel_path = os.getcwd() + '/test.xlsx'  # TODO 配置excel文件路径

        print(f"os.getcwd() = {os.getcwd()}")
        print(f"excel_path = {excel_path}")

        # name: 如果没有表头, 可用此参数传入列表做表头
        # header: 指定数据表的表头, 默认值为0, 即将第一行作为表头
        # index_col: 用作行索引的列编号或者列名，如果给定一个序列则有多个行索引。一般可以设定index_col = False指的是pandas不适用第一列作为行索引。
        # usecols：读取指定的列, 也可以通过名字或索引值

        sheet_name = 0  # sheet定位，下标从0开始
        header = 0  # 定位表头
        usecols = "A,B"
        names = ['name', 'age']

        data_list = get_data_from_excel(excel_path, sheet_name, usecols, names)

    except BaseException:
        traceback.print_exc()

读取的Excel文件：text.xlsx

 

输出：

data =        name  age
0  xiaoming   18
1   xiaolin   19
name = xiaoming | age = 18
name = xiaolin | age = 19