准备Excel(test.xlsx)
导库
import pymysql
import xlrd
完整代码
import pymysql
import xlrd
def connect_database(dataname,user,password):
db = pymysql.connect(host='127.0.0.1',port=3306,user=user,passwd=password,db=dataname, charset='utf8')
return db
def read_excel(url):
workbook = xlrd.open_workbook(r''+url)
# 获取所有sheet
workbook.sheet_names()
# 获取 第一个 sheet
sheet1_name = workbook.sheet_names()[0]
print("第一个Sheet名称："+sheet1_name)
# 根据sheet索引或者名称获取sheet内容
sheet1 = workbook.sheet_by_name('Sheet1')
# sheet的名称，行数，列数
# rows = sheet1.row_values(0) # 获取第1行内容
# cols = sheet1.col_values(0) # 获取第1列内容
# 获取单元格内容的三种方法
# sheet2.cell(1, 0).value.encode('utf-8')
# sheet2.cell_value(1,0).encode('utf-8')
# sheet2.row(1)[0].value.encode('utf-8')
return sheet1
if __name__ == '__main__':
conn = connect_database("school", "root", "root")
cursor = conn.cursor()
# 查
# check_sql = "select * from student"
# cursor.execute(check_sql)
# data = cursor.fetchall()
# for i in data:
# print(i)
# 改
# sql = " update student set age = 999 where name = 'liudehua'"
# cursor.execute(sql)
# mit()
# 增 和 改 差不多 除了sql改变其他不变
#------------------获取Excel表中的内容-----------
sheet1 = read_excel('D:\\test.xlsx')
cols_1 = sheet1.col_values(0) # 获取第1列内容
cols_2 = sheet1.col_values(1) # 获取第1列内容
cols_3 = sheet1.col_values(2) # 获取第1列内容
print(cols_1)
print(cols_2)
print(cols_3)
#------------------end-------------------------
for i in range(1,len(cols_1)):
# print(cols_1[i])
sql = "insert into student(name,sex,age) values('" +cols_1[i]+"',"+str(cols_2[i])+","+str(cols_3[i])+")"
print(sql)
cursor.execute(sql)
mit()
打印结果
第一个Sheet名称：Sheet1
['name', '张观博', '张欣竹', '张欣阳', '张刚军', '张扬阳', '张靖阳']
['sex', 1.0, 1.0, 1.0, 1.0, 1.0, 1.0]
['age', 12.0, 17.0, 13.0, 12.0, 16.0, 12.0]
insert into student(name,sex,age) values('张观博',1.0,12.0)
insert into student(name,sex,age) values('张欣竹',1.0,17.0)
insert into student(name,sex,age) values('张欣阳',1.0,13.0)
insert into student(name,sex,age) values('张刚军',1.0,12.0)
insert into student(name,sex,age) values('张扬阳',1.0,16.0)
insert into student(name,sex,age) values('张靖阳',1.0,12.0)
数据库结果
原文链接:/qq_43172476/article/details/112304557
如果觉得《python读取excel写入数据库_python读取Excel内容并写入MySQL数据库脚本》对你有帮助，请点赞、收藏，并留下你的观点哦！
