#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @File? : excleutil.py
# @Author: JinXudong
# @Date? : 2017-12-1
# @Desc? :
'''
導(dǎo)出mysql數(shù)據(jù)到excle
'''
importuuid
importMySQLdb
importxlrd
importxlwt
fromdbimportmysql
'''
導(dǎo)出mysql數(shù)據(jù)到excle
'''
defexport_excle(dbname, tablename, filepath):
conn = MySQLdb.connect('localhost','root','1', dbname,charset='utf8')
cursor = conn.cursor()
count= cursor.execute('select * from '+ tablename)
# 重置游標(biāo)的位置
cursor.scroll(0,mode='absolute')
# 搜取所有結(jié)果
results = cursor.fetchall()
# 獲取MYSQL里面的數(shù)據(jù)字段名稱
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])
# 獲取并寫入數(shù)據(jù)信息
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"導(dǎo)出"+ tablename +"完成........."
# demo
# export_excle("xdfstar_db","t_employee","F://t_employee.xls");
'''
讀取excle文件寫入到數(shù)據(jù)庫(kù)中
'''
defread_excel():
# 打開文件 r'防止內(nèi)容轉(zhuǎn)義
workbook = xlrd.open_workbook(r'D:\test.xlsx')
# 獲取所有sheet
printworkbook.sheet_names()
sheet2_name= workbook.sheet_names()[1]
# 根據(jù)sheet索引或者名稱獲取sheet內(nèi)容
sheet2= workbook.sheet_by_index(1)# sheet索引從0開始
sheet2 = workbook.sheet_by_name('Sheet1')
# sheet的名稱柬唯，行數(shù)，列數(shù)
print"sheet2name:"+ sheet2.name, sheet2.nrows, sheet2.ncols
# 獲取整行和整列的值（數(shù)組）
rows= sheet2.row_values(0)# 獲取第一行內(nèi)容
cols= sheet2.col_values(1)# 獲取第二列內(nèi)容
rownum= sheet2.nrows
print"高級(jí)寫法"*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")
