python读取excel内容插入mysql

import pymysql
import time
import pandas as pd


# 连接数据库的函数
def connect_mysql():
    conn, cur = None, None
    host = ''
    port = 3306
    usr = ''
    pwd = ''
    database = ''
    for i in range(4):
        try:  # autocommit=True 表示在执行每个 SQL 语句后自动提交事务，不需要手动调用 conn.commit()
            conn = pymysql.connect(host=host, port=port, user=usr, 
                                   password=pwd, database=database, autocommit=True)
            cur = conn.cursor(pymysql.cursors.DictCursor)  # 使用 DictCursor，可以使用字段名来直接访问结果数据
        except Exception as e:   # Exception 类可以捕获任何类型的异常
            print("error------------------", e)
            time.sleep(1.2)
        else:
            break
    else:
        print("数据库异常，等待10分钟")
        time.sleep(600)
    return conn, cur


def sql_insert(col, item, conn, cur):
    cols = ','.join(item.keys())
    vals = str(tuple(item.values())).replace('null', 'None')
    sql = "insert into `{}`({}) values {}".format(col, cols, vals)
    try:
        cur.execute(sql)
        conn.commit()
        print(item)
        return 1
    except Exception as e:
        print(e)
        return 0


# 导入数据首先建立通信
conn, cur = connect_mysql()
# 读取excel中的文件
path_list = [('C:\\Users\\Desktop\\a', 'xlsx')]
path_info = path_list[-1]   # path_info 变量来访问路径和扩展名

sj_str_1 = pd.DataFrame(pd.read_excel(f'{path_info[0]}.{path_info[1]}', sheet_name='Sheet1'))
print('笔记总数({})'.format(len(sj_str_1)).center(150, '+'))

xs_list = []
xs_col = 'note_new'

for index in sj_str_1.index:  # index，行索引
    url = sj_str_1.loc[index, 'url']  # .loc属性，可以对DataFrame对象进行灵活的数据访问和修改
    project_type = sj_str_1.loc[index, 'project_type']
    xs_list.append(url, project_type)
print('笔记总数({})'.format(len(xs_list)).center(150, '+'))

for url, project_type in xs_list:
    item = {
        'url': url,
        'project_type': project_type,
    }
    status = sql_insert(col=xs_col, item=item, conn=conn, cur=cur)

try:
    conn.commit()
    cur.close()
    conn.close()
except:
    pass