import pymysql
from openpyxl import load_workbook
from time import time
start = time()
# 连接数据库
conn = pymysql.connect(host='localhost', user='***', password='***', db='zhifu', port=3306, charset='utf8')
# 利用连接对象获取游标对象
cur = conn.cursor()
# 读取Excel表格
wb = load_workbook(r'c:\users\administrator\desktop\信息.xlsx')
ws = wb.active #获取默认工作表
rows = ws.max_row + 1 #获取最大行数，加1是为了方便下面计算
cols = ws.max_column + 1 #获取最大列数
for row in range(1, rows):
hang = []
for col in range(1, cols):
zhi = ws.cell(row, col).value #获取单元格的值
hang.append(zhi)
hang.append(row)
cur.execute('insert into xinxi (title, url, yjid, ejid, logo_path, dh_sort) values (%s, %s, %s, %s, %s, %s)', tuple(hang)) #插入表数据
cur.close() #先关闭游标，防止占用资源
conn.close() #关闭数据库连接
end = time()
shi = end - start
print('已成功导入数据库！共耗时{}秒。'.format(shi))
