python读取数据库表数据并写入excel

2、读取mysql数据库中表数据　

<span style="color: #008080;">1</span> sql = <span style="color: #800000;">‘</span><span style="color: #800000;">select * from %s;</span><span style="color: #800000;">‘</span> %table_name <span style="color: #008000;">#</span><span style="color: #008000;">需要写入excel表数据</span>
<span style="color: #008080;">2</span> <span style="color: #008000;">#</span><span style="color: #008000;">读取数据</span>
<span style="color: #008080;">3</span> cur.execute(sql) <span style="color: #008000;">#</span><span style="color: #008000;">读取数据</span>
<span style="color: #008080;">4</span> fileds = [filed[0] <span style="color: #0000ff;">for</span> filed <span style="color: #0000ff;">in</span> cur.description] <span style="color: #008000;">#</span><span style="color: #008000;">读取表结构定义</span>
<span style="color: #008080;">5</span> all_date = cur.fetchall() <span style="color: #008000;">#</span><span style="color: #008000;">所有数据</span>
<span style="color: #008080;">6</span> <span style="color: #0000ff;">for</span> result <span style="color: #0000ff;">in</span><span style="color: #000000;"> all_date:
</span><span style="color: #008080;">7</span>      <span style="color: #0000ff;">print</span><span style="color: #000000;">(result)
</span><span style="color: #008080;">8</span>     

3、数据写入excel

<span style="color: #008080;"> 1</span>     book = xlwt.Workbook() <span style="color: #008000;">#</span><span style="color: #008000;">创建一个book</span>
<span style="color: #008080;"> 2</span> 
<span style="color: #008080;"> 3</span>     sheet = book.add_sheet(<span style="color: #800000;">‘</span><span style="color: #800000;">result</span><span style="color: #800000;">‘</span>) <span style="color: #008000;">#</span><span style="color: #008000;">创建一个sheet表</span>
<span style="color: #008080;"> 4</span> 
<span style="color: #008080;"> 5</span>     <span style="color: #0000ff;">for</span> col,filed <span style="color: #0000ff;">in</span><span style="color: #000000;"> enumerate(fileds):
</span><span style="color: #008080;"> 6</span>         sheet.write(0,col,filed)  <span style="color: #008000;">#</span><span style="color: #008000;">将表字段描述写入excel第一行</span>
<span style="color: #008080;"> 7</span> 
<span style="color: #008080;"> 8</span>     <span style="color: #008000;">#</span><span style="color: #008000;">从第一行开始写</span>
<span style="color: #008080;"> 9</span> 
<span style="color: #008080;">10</span>     row = 1
<span style="color: #008080;">11</span>     <span style="color: #0000ff;">for</span> data <span style="color: #0000ff;">in</span><span style="color: #000000;"> all_date:
</span><span style="color: #008080;">12</span>         <span style="color: #0000ff;">for</span> col,filed <span style="color: #0000ff;">in</span><span style="color: #000000;"> enumerate(data):
</span><span style="color: #008080;">13</span>             sheet.write(row,col,filed)<span style="color: #008000;">#</span><span style="color: #008000;">将数据写入excel单元格中</span>
<span style="color: #008080;">14</span>         row += 1

4、保存excel

　　book.save(‘%s.xls‘ %table_name)

5、完整代码

<span style="color: #008000;">#</span><span style="color: #008000;">!/usr/bin/env python</span><span style="color: #008000;">
#</span><span style="color: #008000;"> -*- coding: utf-8 -*-</span>
<span style="color: #800000;">‘‘‘</span><span style="color: #800000;">
 @Time : 2020/1/1 18:08
 @Author : Jason.Jia
 @contact: jiajunp@163.com
 @Version : 1.0
 @file :mysql_write_excel.py
 @desc :
 从mysql读取数据，写入excel中
</span><span style="color: #800000;">‘‘‘</span>
<span style="color: #0000ff;">import</span><span style="color: #000000;"> pymysql,xlwt
</span><span style="color: #0000ff;">def</span><span style="color: #000000;"> export_excel(table_name):
    conn </span>= pymysql.connect(user=<span style="color: #800000;">‘</span><span style="color: #800000;">root</span><span style="color: #800000;">‘</span>,host=<span style="color: #800000;">‘</span><span style="color: #800000;">127.0.0.1</span><span style="color: #800000;">‘</span>,port=3306,passwd=<span style="color: #800000;">‘</span><span style="color: #800000;">root</span><span style="color: #800000;">‘</span>,db=<span style="color: #800000;">‘</span><span style="color: #800000;">python</span><span style="color: #800000;">‘</span>,charset=<span style="color: #800000;">‘</span><span style="color: #800000;">utf8</span><span style="color: #800000;">‘</span><span style="color: #000000;">)
    cur </span>=<span style="color: #000000;"> conn.cursor()
    sql </span>= <span style="color: #800000;">‘</span><span style="color: #800000;">select * from %s;</span><span style="color: #800000;">‘</span> %<span style="color: #000000;">table_name
    </span><span style="color: #008000;">#</span><span style="color: #008000;">读取数据</span>
<span style="color: #000000;">    cur.execute(sql)
    fileds </span>= [filed[0] <span style="color: #0000ff;">for</span> filed <span style="color: #0000ff;">in</span><span style="color: #000000;"> cur.description]
    all_date </span>= cur.fetchall() <span style="color: #008000;">#</span><span style="color: #008000;">所有数据</span>
    <span style="color: #0000ff;">for</span> result <span style="color: #0000ff;">in</span><span style="color: #000000;"> all_date:
        </span><span style="color: #0000ff;">print</span><span style="color: #000000;">(result)
    </span><span style="color: #008000;">#</span><span style="color: #008000;">写excel</span>
<span style="color: #000000;">
    book </span>= xlwt.Workbook() <span style="color: #008000;">#</span><span style="color: #008000;">创建一个book</span>
<span style="color: #000000;">
    sheet </span>= book.add_sheet(<span style="color: #800000;">‘</span><span style="color: #800000;">result</span><span style="color: #800000;">‘</span>) <span style="color: #008000;">#</span><span style="color: #008000;">创建一个sheet表</span>
    <span style="color: #0000ff;">for</span> col,filed <span style="color: #0000ff;">in</span><span style="color: #000000;"> enumerate(fileds):
        sheet.write(0,col,filed)
    </span><span style="color: #008000;">#</span><span style="color: #008000;">从第一行开始写</span>
<span style="color: #000000;">
    row </span>= 1
    <span style="color: #0000ff;">for</span> data <span style="color: #0000ff;">in</span><span style="color: #000000;"> all_date:
        </span><span style="color: #0000ff;">for</span> col,filed <span style="color: #0000ff;">in</span><span style="color: #000000;"> enumerate(data):
            sheet.write(row,col,filed)
        row </span>+= 1<span style="color: #000000;">
    book.save(</span><span style="color: #800000;">‘</span><span style="color: #800000;">%s.xls</span><span style="color: #800000;">‘</span> %<span style="color: #000000;">table_name)
</span><span style="color: #0000ff;">if</span> <span style="color: #800080;">__name__</span> == <span style="color: #800000;">‘</span><span style="color: #800000;">__main__</span><span style="color: #800000;">‘</span><span style="color: #000000;">:
    export_excel(</span><span style="color: #800000;">‘</span><span style="color: #800000;">stocks</span><span style="color: #800000;">‘</span>)

python读取数据库表数据并写入excel

标签：单元格   ble   print   开始   pre   excel   fetch   har   sele