Python操作Excel和Mysql

#!/usr/bin/env python

# -*- coding: utf-8 -*-

# @File  : excleutil.py

# @Author: JinXudong

# @Date  : 2017-12-1

# @Desc  :

'''

导出mysql数据到excle

'''

importuuid

importMySQLdb

importxlrd

importxlwt

fromdbimportmysql

'''

导出mysql数据到excle

'''

defexport_excle(dbname, tablename, filepath):

conn = MySQLdb.connect('localhost','root','1', dbname,charset='utf8')

cursor = conn.cursor()

count= cursor.execute('select * from '+ tablename)

# 重置游标的位置

cursor.scroll(0,mode='absolute')

# 搜取所有结果

results = cursor.fetchall()

# 获取MYSQL里面的数据字段名称

fields = cursor.description

workbook = xlwt.Workbook();

sheet = workbook.add_sheet("sheet1",cell_overwrite_ok=True)

# 写上字段信息

foriinrange(0,len(fields)):

sheet.write(0, i,u'%s'% fields[i][0])

# 获取并写入数据信息

row=1

col=0

forrowinrange(1,len(results) +1):

forcolinrange(0,len(fields)):

sheet.write(row, col,u'%s'% results[row -1][col])

workbook.save(filepath)

print"导出"+ tablename +"完成........."

# demo

# export_excle("xdfstar_db","t_employee","F://t_employee.xls");

'''

读取excle文件写入到数据库中

'''

defread_excel():

# 打开文件 r'防止内容转义

workbook = xlrd.open_workbook(r'D:\test.xlsx')

# 获取所有sheet

printworkbook.sheet_names()

sheet2_name= workbook.sheet_names()[1]

# 根据sheet索引或者名称获取sheet内容

sheet2= workbook.sheet_by_index(1)# sheet索引从0开始

sheet2 = workbook.sheet_by_name('Sheet1')

# sheet的名称，行数，列数

print"sheet2name:"+ sheet2.name, sheet2.nrows, sheet2.ncols

# 获取整行和整列的值（数组）

rows= sheet2.row_values(0)# 获取第一行内容

cols= sheet2.col_values(1)# 获取第二列内容

rownum= sheet2.nrows

print"高级写法"*3

forrinrange(1, sheet2.nrows):# 从第一行到尾行

sql ="INSERT INTO employee VALUES (%s, %s, %s)"

uid= sheet2.cell(r,0).value

name = sheet2.cell(r,1).value

address = sheet2.cell(r,2).value

# values = (uuid.uuid1(),"1130","公司")

values = (uuid.uuid1(), name, address)

mysql.committomysql("test", sql, values)

# demo

# mysql.truncattable("test","delete from employee")