python提取数据库数据_Python读取xlsx并写入数据库

### 此程序是用来将表格的数据读入到数据库中

import xlrd

import re

import pymysql

def read_xlsx():

workbook = xlrd.open_workbook('author.xlsx')

booksheet = workbook.sheet_by_name('Sheet1')

j = 0

for i in range(0,booksheet.nrows):#按照行来读的

#print(booksheet.row_values(i)[0],booksheet.row_values(i)[1],booksheet.row_values(i)[2])

t1=booksheet.row_values(i)[0]

t2=booksheet.row_values(i)[1]

t3=booksheet.row_values(i)[2]

print("-------------------------------------------")

try:

conn = pymysql.connect(host='localhost', port=3306, user='*****',

password='******', database='*****',

charset='UTF8')

cur = conn.cursor()

except:

print("连接数据库失败")

try:

sql ="insert into authors (nm, addr, email) values (%s, %s, %s)"

cur.execute(sql,(t1,t2,t3))

conn.commit()

j = j + 1

except :

print('insert roadky failed ')

continue

print("共插入%d"% j)

conn.commit()

conn.close()

if __name__ == '__main__':

read_xlsx()