使用Python读取Excel并插入到数据库（Demo）

"""
1、读取Excel
2、写入到Mysql
"""

import pandas as pd
import pymysql


# Excel中数据对应的模型
class Person:
    id = None
    name = None
    age = None
    desc = None

    def __init__(self, id, name, age, desc):
        print('init:[{}],[{}],[{}],[{}]'.format(id, name, age, desc))
        self.id = id
        self.name = name
        self.age = age
        self.desc = desc

    def println(self):
        print(f"id={self.id},name={self.name},age={self.age},desc={self.desc}")


# 1.1 读取Excel
sheets = pd.read_excel('example.xlsx', sheet_name=None)
print(sheets)

# 1.2 获取叫Sheet1工作表
sheet_1 = sheets['Sheet1']
# print(sheet_1)
# print(type(sheet_1))

person_list = []
# 1.3 循环获取Excel中的数据
for idx, data in sheet_1.iterrows():
    print('data--> ', data)
    person = Person(data.id, data.get('name'), data.age, data.desc)
    # person.id = data.id
    # person.name = data.name
    # person.age = data.age
    # person.desc = data.desc
    person_list.append(person)

# 2.1 mysql数据库链接
mysql_conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='yser',
    password='password',
    charset='utf8'
)
print("conn info:", mysql_conn.get_server_info())
cursor = mysql_conn.cursor()
# 指定库
cursor.execute("use test;")

# 2.2 循环插库
for p in person_list:
    insert_sql = """
        INSERT INTO person(id,code,name,age,sex) 
        VALUES( {}, '{}', '{}', {}, {}); 
        """.format(p.id, p.name, p.name, '1', '1')
    print(insert_sql)
    cursor.execute(insert_sql)

mysql_conn.commit()
cursor.close()

Excel内容如下