Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- import xlwt
- import pymysql
- def db2Excell(host,user,password,dbname,table_name,outputpath):
- ''' database(mysql) 数据转excell格式输出 '''
- #python操作数据库,创建一个连接 conn,和游标cursor,执行sql语句查询db中信息
- conn = pymysql.Connect(host=host,user=user,password=password,db=dbname,charset='utf8')
- cursor = conn.cursor()
- count = cursor.execute("select * from %s"%table_name) #返回查询到的条目数量
- cursor.scroll(0,mode='absolute') #游标重置会起点.(游标一旦遍历过数据就不再折回)
- results = cursor.fetchall() #以元组的形式返回查选到的所有数据(不包含字段名)
- fields = cursor.description # 对字段的描述 描述的第一项是字段名
- cursor.close()
- conn.close() #数据读取完毕后,关闭游标和连接
- workbook = xlwt.Workbook() #创建一个workbook实例
- sheet = workbook.add_sheet(table_name,cell_overwrite_ok=True) #workbook中添加sheet
- for field in range(0,len(fields)):
- sheet.write(0,field,fields[field][0]) #fields[field][0] ==> 字段名
- row,col = 1,0
- for row in range(1,len(results)+1):
- for col in range(0,len(fields)):
- sheet.write(row,col,'%s'%results[row-1][col])
- workbook.save(outputpath)
- #test
- #文件格式支持 .xls excell 2007版本以前
- if __name__ == '__main__':
- db2Excell('localhost','root','123456','lagou','lagou',r'./拉钩职位搜索.xls')
Add Comment
Please, Sign In to add comment