时间:2021-07-01 10:21:17 帮助过:4人阅读
cursor.scroll(0,mode=’absolute’)来重置游标的位置
获取数据简直更是轻而易举,但是我们必须在心里明白,数据项是一个类似于二维数组的存在。我们获取每一个cell项的时候应该注意。
results = cursor.fetchall()
同样,这里讲解的也是如何使用Python来操作excel数据。
工作薄的概念我们必须要明确,其是我们工作的基础。与下文的sheet相对应,workbook是sheet赖以生存的载体。
workbook = xlwt.Workbook()
我们所有的操作,都是在sheet上进行的。
sheet = workbook.add_sheet(‘table_message’,cell_overwrite_ok=True)
对于workbook 和sheet,如果对此有点模糊。不妨这样进行假设。
日常生活中记账的时候,我们都会有一个账本,这就是workbook。而我们记账则是记录在一张张的表格上面,这些表格就是我们看到的sheet。一个账本上可以有很多个表格,也可以只是一个表格。这样就很容易理解了吧。 :-)
下面看一个小案例。
- <code class=" hljs python"><span class="hljs-comment"># coding:utf8
- <span class="hljs-keyword">import sys
- reload(sys)
- sys.setdefaultencoding(<span class="hljs-string">‘utf8‘)
- <span class="hljs-comment"># __author__ = ‘郭 璞‘
- <span class="hljs-comment"># __date__ = ‘2016/8/20‘
- <span class="hljs-comment"># __Desc__ = 从数据库中导出数据到excel数据表中
- <span class="hljs-keyword">import xlwt
- <span class="hljs-keyword">import MySQLdb
- conn = MySQLdb.connect(<span class="hljs-string">‘localhost‘,<span class="hljs-string">‘root‘,<span class="hljs-string">‘mysql‘,<span class="hljs-string">‘test‘,charset=<span class="hljs-string">‘utf8‘)
- cursor = conn.cursor()
- count = cursor.execute(<span class="hljs-string">‘select * from message‘)
- <span class="hljs-keyword">print count
- <span class="hljs-comment"># 重置游标的位置
- cursor.scroll(<span class="hljs-number">0,mode=<span class="hljs-string">‘absolute‘)
- <span class="hljs-comment"># 搜取所有结果
- results = cursor.fetchall()
- <span class="hljs-comment"># 获取MYSQL里面的数据字段名称
- fields = cursor.description
- workbook = xlwt.Workbook()
- sheet = workbook.add_sheet(<span class="hljs-string">‘table_message‘,cell_overwrite_ok=<span class="hljs-keyword">True)
- <span class="hljs-comment"># 写上字段信息
- <span class="hljs-keyword">for field <span class="hljs-keyword">in range(<span class="hljs-number">0,len(fields)):
- sheet.write(<span class="hljs-number">0,field,fields[field][<span class="hljs-number">0])
- <span class="hljs-comment"># 获取并写入数据段信息
- row = <span class="hljs-number">1
- col = <span class="hljs-number">0
- <span class="hljs-keyword">for row <span class="hljs-keyword">in range(<span class="hljs-number">1,len(results)+<span class="hljs-number">1):
- <span class="hljs-keyword">for col <span class="hljs-keyword">in range(<span class="hljs-number">0,len(fields)):
- sheet.write(row,col,<span class="hljs-string">u‘%s‘%results[row-<span class="hljs-number">1][col])
- workbook.save(<span class="hljs-string">r‘./readout.xlsx‘)</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>
为了使用上的方便,现将其封装成一个容易调用的函数。
- <code class=" hljs python"><span class="hljs-comment"># coding:utf8
- <span class="hljs-keyword">import sys
- reload(sys)
- sys.setdefaultencoding(<span class="hljs-string">‘utf8‘)
- <span class="hljs-comment"># __author__ = ‘郭 璞‘
- <span class="hljs-comment"># __date__ = ‘2016/8/20‘
- <span class="hljs-comment"># __Desc__ = 从数据库中导出数据到excel数据表中
- <span class="hljs-keyword">import xlwt
- <span class="hljs-keyword">import MySQLdb
- <span class="hljs-function"><span class="hljs-keyword">def <span class="hljs-title">export<span class="hljs-params">(host,user,password,dbname,table_name,outputpath):
- conn = MySQLdb.connect(host,user,password,dbname,charset=<span class="hljs-string">‘utf8‘)
- cursor = conn.cursor()
- count = cursor.execute(<span class="hljs-string">‘select * from ‘+table_name)
- <span class="hljs-keyword">print count
- <span class="hljs-comment"># 重置游标的位置
- cursor.scroll(<span class="hljs-number">0,mode=<span class="hljs-string">‘absolute‘)
- <span class="hljs-comment"># 搜取所有结果
- results = cursor.fetchall()
- <span class="hljs-comment"># 获取MYSQL里面的数据字段名称
- fields = cursor.description
- workbook = xlwt.Workbook()
- sheet = workbook.add_sheet(<span class="hljs-string">‘table_‘+table_name,cell_overwrite_ok=<span class="hljs-keyword">True)
- <span class="hljs-comment"># 写上字段信息
- <span class="hljs-keyword">for field <span class="hljs-keyword">in range(<span class="hljs-number">0,len(fields)):
- sheet.write(<span class="hljs-number">0,field,fields[field][<span class="hljs-number">0])
- <span class="hljs-comment"># 获取并写入数据段信息
- row = <span class="hljs-number">1
- col = <span class="hljs-number">0
- <span class="hljs-keyword">for row <span class="hljs-keyword">in range(<span class="hljs-number">1,len(results)+<span class="hljs-number">1):
- <span class="hljs-keyword">for col <span class="hljs-keyword">in range(<span class="hljs-number">0,len(fields)):
- sheet.write(row,col,<span class="hljs-string">u‘%s‘%results[row-<span class="hljs-number">1][col])
- workbook.save(outputpath)
- <span class="hljs-comment"># 结果测试
- <span class="hljs-keyword">if __name__ == <span class="hljs-string">"__main__":
- export(<span class="hljs-string">‘localhost‘,<span class="hljs-string">‘root‘,<span class="hljs-string">‘mysql‘,<span class="hljs-string">‘test‘,<span class="hljs-string">‘datetest‘,<span class="hljs-string">r‘datetest.xlsx‘)</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>
- <code class=" hljs applescript"><span class="hljs-property">id <span class="hljs-property">name <span class="hljs-type">date
- <span class="hljs-number">1 dlut <span class="hljs-number">2016-<span class="hljs-number">07-<span class="hljs-number">06
- <span class="hljs-number">2 清华大学 <span class="hljs-number">2016-<span class="hljs-number">07-<span class="hljs-number">03
- <span class="hljs-number">3 北京大学 <span class="hljs-number">2016-<span class="hljs-number">07-<span class="hljs-number">28
- <span class="hljs-number">4 Mark <span class="hljs-number">2016-<span class="hljs-number">08-<span class="hljs-number">20
- <span class="hljs-number">5 Tom <span class="hljs-number">2016-<span class="hljs-number">08-<span class="hljs-number">19
- <span class="hljs-number">6 Jane <span class="hljs-number">2016-<span class="hljs-number">08-<span class="hljs-number">21
- </span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>
回顾一下,本次试验用到了哪些知识点。
数据库--->excel
标签:har res span 知识 lock 题解 处理 comment 网上