import xlrd,xlwt,xlutils
import pymysql
def sql_connect(sql):
conn = pymysql.connect(host='127.0.0.1',port=3306, user='root', password='123456',db='sql_python',charset='utf8')
cur = conn.cursor()
cur.execute(sql)
data = cur.fetchall()
cur.close()
conn.close()
return data
从mysql中读取数据写入excel中
#写数据
def write_excel(filename, data):
book = xlwt.Workbook() #创建excel对象
sheet = book.add_sheet('sheet1') #添加一个表
c = 0 #保存当前列
for d in data: #取出data中的每一个元组存到表格的每一行
for index in range(len(d)): #将每一个元组中的每一个单元存到每一列
sheet.write(c,index,d[index])
c += 1
book.save(filename) #保存excel
sql = 'select * from stu'
res = sql_connect(sql)
write_excel('firstsheet.xls', res)
从excel中读取数据
#读数据
def read_excel(filename):
book = xlrd.open_workbook(filename)
sheet = book.sheet_by_name('sheet1')
rows = sheet.nrows #获取行数
cols = sheet.ncols #获取列数
for c in range(cols): #读取每一列的数据
c_values = sheet.col_values(c)
print(c_values)
for r in range(rows): #读取每一行的数据
r_values = sheet.row_values(r)
print(r_values)
print(sheet.cell(1,1)) #读取指定单元格的数据
read_excel('firstsheet.xls')
从excel读取数据写入mysql
#从excel读取数据写入mysql
def excel_to_mysql(filename):
conn = pymysql.connect(host='127.0.0.1', port=3306, user='jessica', password='123456', db='sql_python',charset='utf8')
cur = conn.cursor() #连接数据库
book = xlrd.open_workbook(filename)
sheet = book.sheet_by_name('sheet1')
rows = sheet.nrows #获取行数
for r in range(1,rows): #将标题之外的其他行写入数据库
r_values = sheet.row_values(r)
sql = 'insert into stu2 values(%s,%s)'
data = cur.execute(sql,r_values) #将每一行插入sql
conn.commit() #插入所有数据后提交
cur.close()
conn.close()
excel_to_mysql('firstsheet.xls')