Python实现数据库一键导出为Excel表格

版权声明：本作品采用知识共享署名-非商业性使用-相同方式共享 4.0 国际许可协议进行许可。转载时请标注http://blog.csdn.net/marksinoberg.

目录(?)[+]

数据库数据导出为excel表格，也可以说是一个很常用的功能了。毕竟不是任何人都懂数据库操作语句的。
下面先来看看完成的效果吧。

• 数据源

• 导出结果
  

---

依赖

由于是Python实现的，所以需要有Python环境的支持

Python2.7.11

我的Python环境是2.7.11。虽然你用的可能是3.5版本，但是思想是一致的。

xlwt

pip install xlwt

MySQLdb

pip install MySQLdb
如果上述方式不成功的话，可以到sourceforge官网上去下载windows上的msi版本或者使用源码自行编译。

数据库相关

本次试验，数据库相关的其实也就是如何使用Python操作数据库而已，知识点也很少，下述为我们本次用到的一些简单的语句。

连接

conn = MySQLdb.connect(host=’localhost’,user=’root’,passwd=’mysql’,db=’test’,charset=’utf8’)

这里值得我们一提的就是最后一个参数的使用，不然从数据库中取出的数据就会使乱码。关于乱码问题，如果还有不明白的地方，不妨看下这篇文章http://blog.csdn.net/marksinoberg/article/details/52254401。

获取字段信息

fields = cursor.description

至于cursor，是我们操作数据库的核心。游标的特点就是一旦遍历过该条数据，便不可返回。但是我们也可以手动的改变其位置。

cursor.scroll(0,mode=’absolute’)来重置游标的位置

获取数据

获取数据简直更是轻而易举，但是我们必须在心里明白，数据项是一个类似于二维数组的存在。我们获取每一个cell项的时候应该注意。

results = cursor.fetchall()

Excel基础

同样，这里讲解的也是如何使用Python来操作excel数据。

workbook

工作薄的概念我们必须要明确，其是我们工作的基础。与下文的sheet相对应，workbook是sheet赖以生存的载体。

workbook = xlwt.Workbook()

sheet

我们所有的操作，都是在sheet上进行的。

sheet = workbook.add_sheet(‘table_message’,cell_overwrite_ok=True)

对于workbook 和sheet，如果对此有点模糊。不妨这样进行假设。

日常生活中记账的时候，我们都会有一个账本，这就是workbook。而我们记账则是记录在一张张的表格上面，这些表格就是我们看到的sheet。一个账本上可以有很多个表格，也可以只是一个表格。这样就很容易理解了吧。 :-)

案例

下面看一个小案例。

<code class="hljs python has-numbering"><span class="hljs-comment"># coding:utf8</span>
<span class="hljs-keyword">import</span> sys

reload(sys)
sys.setdefaultencoding(<span class="hljs-string">'utf8'</span>)
<span class="hljs-comment">#    __author__ = '郭 璞'</span>
<span class="hljs-comment">#    __date__ = '2016/8/20'</span>
<span class="hljs-comment">#    __Desc__ = 从数据库中导出数据到excel数据表中</span>

<span class="hljs-keyword">import</span> xlwt
<span class="hljs-keyword">import</span> MySQLdb

conn = MySQLdb.connect(<span class="hljs-string">'localhost'</span>,<span class="hljs-string">'root'</span>,<span class="hljs-string">'mysql'</span>,<span class="hljs-string">'test'</span>,charset=<span class="hljs-string">'utf8'</span>)
cursor = conn.cursor()

count = cursor.execute(<span class="hljs-string">'select * from message'</span>)
<span class="hljs-keyword">print</span> count
<span class="hljs-comment"># 重置游标的位置</span>
cursor.scroll(<span class="hljs-number">0</span>,mode=<span class="hljs-string">'absolute'</span>)
<span class="hljs-comment"># 搜取所有结果</span>
results = cursor.fetchall()

<span class="hljs-comment"># 获取MYSQL里面的数据字段名称</span>
fields = cursor.description
workbook = xlwt.Workbook()
sheet = workbook.add_sheet(<span class="hljs-string">'table_message'</span>,cell_overwrite_ok=<span class="hljs-keyword">True</span>)

<span class="hljs-comment"># 写上字段信息</span>
<span class="hljs-keyword">for</span> field <span class="hljs-keyword">in</span> range(<span class="hljs-number">0</span>,len(fields)):
    sheet.write(<span class="hljs-number">0</span>,field,fields[field][<span class="hljs-number">0</span>])

<span class="hljs-comment"># 获取并写入数据段信息</span>
row = <span class="hljs-number">1</span>
col = <span class="hljs-number">0</span>
<span class="hljs-keyword">for</span> row <span class="hljs-keyword">in</span> range(<span class="hljs-number">1</span>,len(results)+<span class="hljs-number">1</span>):
    <span class="hljs-keyword">for</span> col <span class="hljs-keyword">in</span> range(<span class="hljs-number">0</span>,len(fields)):
        sheet.write(row,col,<span class="hljs-string">u'%s'</span>%results[row-<span class="hljs-number">1</span>][col])

workbook.save(<span class="hljs-string">r'./readout.xlsx'</span>)</code><ul style="" class="pre-numbering"><li>1</li><li>2</li><li>3</li><li>4</li><li>5</li><li>6</li><li>7</li><li>8</li><li>9</li><li>10</li><li>11</li><li>12</li><li>13</li><li>14</li><li>15</li><li>16</li><li>17</li><li>18</li><li>19</li><li>20</li><li>21</li><li>22</li><li>23</li><li>24</li><li>25</li><li>26</li><li>27</li><li>28</li><li>29</li><li>30</li><li>31</li><li>32</li><li>33</li><li>34</li><li>35</li><li>36</li><li>37</li><li>38</li><li>39</li></ul>

封装

为了使用上的方便，现将其封装成一个容易调用的函数。

封装之后

<code class="hljs python has-numbering"><span class="hljs-comment"># coding:utf8</span>
<span class="hljs-keyword">import</span> sys

reload(sys)
sys.setdefaultencoding(<span class="hljs-string">'utf8'</span>)
<span class="hljs-comment">#    __author__ = '郭 璞'</span>
<span class="hljs-comment">#    __date__ = '2016/8/20'</span>
<span class="hljs-comment">#    __Desc__ = 从数据库中导出数据到excel数据表中</span>

<span class="hljs-keyword">import</span> xlwt
<span class="hljs-keyword">import</span> MySQLdb

<span class="hljs-function"><span class="hljs-keyword">def</span> <span class="hljs-title">export</span><span class="hljs-params">(host,user,password,dbname,table_name,outputpath)</span>:</span>
    conn = MySQLdb.connect(host,user,password,dbname,charset=<span class="hljs-string">'utf8'</span>)
    cursor = conn.cursor()

    count = cursor.execute(<span class="hljs-string">'select * from '</span>+table_name)
    <span class="hljs-keyword">print</span> count
    <span class="hljs-comment"># 重置游标的位置</span>
    cursor.scroll(<span class="hljs-number">0</span>,mode=<span class="hljs-string">'absolute'</span>)
    <span class="hljs-comment"># 搜取所有结果</span>
    results = cursor.fetchall()

    <span class="hljs-comment"># 获取MYSQL里面的数据字段名称</span>
    fields = cursor.description
    workbook = xlwt.Workbook()
    sheet = workbook.add_sheet(<span class="hljs-string">'table_'</span>+table_name,cell_overwrite_ok=<span class="hljs-keyword">True</span>)

    <span class="hljs-comment"># 写上字段信息</span>
    <span class="hljs-keyword">for</span> field <span class="hljs-keyword">in</span> range(<span class="hljs-number">0</span>,len(fields)):
        sheet.write(<span class="hljs-number">0</span>,field,fields[field][<span class="hljs-number">0</span>])

    <span class="hljs-comment"># 获取并写入数据段信息</span>
    row = <span class="hljs-number">1</span>
    col = <span class="hljs-number">0</span>
    <span class="hljs-keyword">for</span> row <span class="hljs-keyword">in</span> range(<span class="hljs-number">1</span>,len(results)+<span class="hljs-number">1</span>):
        <span class="hljs-keyword">for</span> col <span class="hljs-keyword">in</span> range(<span class="hljs-number">0</span>,len(fields)):
            sheet.write(row,col,<span class="hljs-string">u'%s'</span>%results[row-<span class="hljs-number">1</span>][col])

    workbook.save(outputpath)


<span class="hljs-comment"># 结果测试</span>
<span class="hljs-keyword">if</span> __name__ == <span class="hljs-string">"__main__"</span>:
    export(<span class="hljs-string">'localhost'</span>,<span class="hljs-string">'root'</span>,<span class="hljs-string">'mysql'</span>,<span class="hljs-string">'test'</span>,<span class="hljs-string">'datetest'</span>,<span class="hljs-string">r'datetest.xlsx'</span>)</code><ul style="" class="pre-numbering"><li>1<span id="transmark"></span></li><li>2</li><li>3</li><li>4</li><li>5</li><li>6</li><li>7</li><li>8</li><li>9</li><li>10</li><li>11</li><li>12</li><li>13</li><li>14</li><li>15</li><li>16</li><li>17</li><li>18</li><li>19</li><li>20</li><li>21</li><li>22</li><li>23</li><li>24</li><li>25</li><li>26</li><li>27</li><li>28</li><li>29</li><li>30</li><li>31</li><li>32</li><li>33</li><li>34</li><li>35</li><li>36</li><li>37</li><li>38</li><li>39</li><li>40</li><li>41</li><li>42</li><li>43</li><li>44</li><li>45</li></ul>

测试结果

<code class="hljs applescript has-numbering"><span class="hljs-property">id</span>  <span class="hljs-property">name</span>    <span class="hljs-type">date</span>
<span class="hljs-number">1</span>   dlut    <span class="hljs-number">2016</span>-<span class="hljs-number">07</span>-<span class="hljs-number">06</span>
<span class="hljs-number">2</span>   清华大学    <span class="hljs-number">2016</span>-<span class="hljs-number">07</span>-<span class="hljs-number">03</span>
<span class="hljs-number">3</span>   北京大学    <span class="hljs-number">2016</span>-<span class="hljs-number">07</span>-<span class="hljs-number">28</span>
<span class="hljs-number">4</span>   Mark    <span class="hljs-number">2016</span>-<span class="hljs-number">08</span>-<span class="hljs-number">20</span>
<span class="hljs-number">5</span>   Tom <span class="hljs-number">2016</span>-<span class="hljs-number">08</span>-<span class="hljs-number">19</span>
<span class="hljs-number">6</span>   Jane    <span class="hljs-number">2016</span>-<span class="hljs-number">08</span>-<span class="hljs-number">21</span>
</code><ul style="" class="pre-numbering"><li>1</li><li>2</li><li>3</li><li>4</li><li>5</li><li>6</li><li>7</li><li>8</li></ul>

总结

回顾一下，本次试验用到了哪些知识点。

• Python简易操作数据库
• Python简易操作Excel
• 数据库取出数据乱码问题解决之添加charset=utf-8
• 以二维数组的角度来处理获取到的结果集。