以前做报表从Mysql数据里面提取数据很简单，几条简单的SHELL脚本就可以实现该功能，因为是自己使用，所以对于格式无所谓，以前使用的都是.csv格式，因为其是以逗号区分的，方便脚本处理。这次不一样了，虽然这次接到需求也是从Mysql中导出数据，但是！！导出文件的格式必须是.XLSX这是微软EXCEL软件自有的格式，包含特殊的文件头，所以之前的方法失效了，失效了… 折腾了好久，也请教了别人，终于，终于解决了这个需求。

环境：
　　　Centos 7.2.1511
　　　Python 2.7.5 -> 3.5

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
[root@localhost ~]# python -V
Python 2.7.5
[root@localhost ~]#
#安装easy_install命令
[root@localhost ~]# wget https://bootstrap.pypa.io/ez_setup.py -O - | python
#安装pip命令(python包管理软件)
[root@localhost ~]# wget https://pypi.python.org/packages/11/b6/abcb525026a4be042b486df43905d6893fb04f05aac21c32c638e939e447/pip-9.0.1.tar.gz#md5=35f01da33009719497f01a4ba69d63c9
[root@localhost ~]# tar -zxf pip-9.0.1.tar.gz 
[root@localhost ~]# cd pip-9.0.1
[root@localhost pip-9.0.1]# python setup.py install
#安装python module
[root@localhost ~]# python select3.py 
Traceback (most recent call last):
  File "select3.py", line 3, in <module>
    from sqlalchemy import create_engine
ImportError: No module named sqlalchemy
[root@localhost ~]#
#安装sqlalchemy模块
[root@localhost ~]# pip install sqlalchemy
Collecting pymysql
  Downloading PyMySQL-0.7.11-py2.py3-none-any.whl (78kB)
    100% |████████████████████████████████| 81kB 48kB/s 
Installing collected packages: pymysql
Successfully installed pymysql-0.7.11
[root@localhost ~]#
#重复执行python select3.py 安装缺失的模块
[root@localhost ~]# pip install pymysql
[root@localhost ~]# pip install pandas
# Python 2.7不支持中文，报错如下，需要升级到 Python ，测试 Python 3.5.4 支持
[root@localhost ~]# python select3.py 
Traceback (most recent call last):
  File "select3.py", line 10, in <module>
    sql.encode('gb18030')
UnicodeDecodeError: 'ascii' codec can't decode byte 0xe6 in position 20: ordinal not in range(128)
[root@localhost ~]#

升级 Python 2.7.5 至 Python 3.5
不要动系统自带的Python，否则系统的某些功能都无法正常使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
[root@localhost ~]# wget https://www.python.org/ftp/python/3.5.4/Python-3.5.4.tgz
[root@localhost ~]# yum install openssl-devel -y
[root@localhost ~]# tar -zxf Python-3.5.4.tgz
[root@localhost ~]# mkdir -p /usr/local/Python-3.5.4
[root@localhost ~]# cd Python-3.5.4
[root@localhost Python-3.5.4]# ./configure --prefix=/usr/local/Python-3.5.4/
[root@localhost Python-3.5.4]# make && make install
[root@localhost ~]# /usr/local/Python-3.5.4/bin/python3.5 -V
Python 3.5.4
[root@localhost ~]#
# 现在系统同时存在两个版本的 Python，因此脚本需要的模块需要重新安装，因为Python 3.5.2之后自带 pip3 和 setuptools，因此需要使用 pip3 安装所需要的模块
[root@localhost ~]# /usr/local/Python-3.5.4/bin/python3.5 select3.py 
Traceback (most recent call last):
  File "select3.py", line 3, in <module>
    from sqlalchemy import create_engine
ImportError: No module named 'sqlalchemy'
[root@localhost ~]#
#安装所需要的 Python 模块
[root@localhost ~]# /usr/local/Python-3.5.4/bin/pip3.5 install sqlalchemy
[root@localhost ~]# /usr/local/Python-3.5.4/bin/pip3.5 install pymysql
[root@localhost ~]# /usr/local/Python-3.5.4/bin/pip3.5 install pandas     #这个很慢
[root@localhost ~]# /usr/local/Python-3.5.4/bin/pip3.5 install openpyxl
[root@localhost ~]# cat /etc/profile.d/Python.sh 
#!/bin/sh
Python3_5_home='/usr/local/Python-3.5.4'
export PATH=${Python3_5_home}/bin:$PATH
[root@localhost ~]# source /etc/profile.d/Python.sh
[root@localhost ~]# /usr/local/Python-3.5.4/bin/python3.5 select3.py
[root@localhost ~]# ll | grep output.xlsx 
-rw-r--r--   1 root root      4854 Oct 15 06:37 output.xlsx
[root@localhost ~]#

Py_mysql_to_excel

如果报这个错，好像是 查询SQL 时超时了，在同级目录下会生成 __pycache__文件夹，脚本换个目录（不要在含有__pycache__这个目录的目录中执行 py脚本），然后删掉其父目录，再执行 py 脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
Traceback (most recent call last):
  File "/usr/local/Python-3.5.4/lib/python3.5/site-packages/pandas/compat/__init__.py", line 47, in <module>
    import __builtin__ as builtins
ImportError: No module named '__builtin__'
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
  File "select.py 
", line 4, in <module>
    import pymysql
  File "/usr/local/Python-3.5.4/lib/python3.5/site-packages/pymysql/__init__.py", line 92, in <module>
    from . import connections as _orig_conn
  File "/usr/local/Python-3.5.4/lib/python3.5/site-packages/pymysql/connections.py 
", line 13, in <module>
    import socket
  File "/usr/local/Python-3.5.4/lib/python3.5/socket.py 
", line 52, in <module>
    import os, sys, io, selectors
  File "/usr/local/Python-3.5.4/lib/python3.5/selectors.py 
", line 11, in <module>
    import select
  File "/data/soros/job/report/jinrongban_data_back/test/select.py 
", line 5, in <module>
    import pandas as pd
  File "/usr/local/Python-3.5.4/lib/python3.5/site-packages/pandas/__init__.py", line 23, in <module>
    from pandas.compat.numpy import *
  File "/usr/local/Python-3.5.4/lib/python3.5/site-packages/pandas/compat/__init__.py", line 60, in <module>
    import http.client as httplib
  File "/usr/local/Python-3.5.4/lib/python3.5/http/client.py 
", line 739, in <module>
    class HTTPConnection:
  File "/usr/local/Python-3.5.4/lib/python3.5/http/client.py 
", line 749, in HTTPConnection
    def __init__(self, host, port=None, timeout=socket._GLOBAL_DEFAULT_TIMEOUT,
AttributeError: module 'socket' has no attribute '_GLOBAL_DEFAULT_TIMEOUT'

Python脚本：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
[root@localhost ~]# cat select3.py
#!/usr/bin/env python
#-*- coding: utf8 -*-
from sqlalchemy import create_engine
import pymysql
import pandas as pd
from pandas import DataFrame,Series
engine=create_engine('mysql+pymysql://username:password@192.168.1.1/database_name?charset=gbk,pool_timeout=3000')
sql="select count(*) as '总数' from database.table1;"
sql.encode('gb18030')
df=pd.read_sql(sql,engine)
writer = pd.ExcelWriter('/root/output.xlsx')
df.to_excel(writer,'Sheet1',index=False)
writer.save()
[root@localhost ~]#

附件：
Python-3.5.4.tar.gz
select3.py
numpy-1.13.3-cp35-cp35m-manylinux1_x86_64.whl

本文出自”Jack Wang Blog”：http://www.yfshare.vip/2020/10/16/Python-读取Mysql生成EXCEL（XLSX）/