Python读取MySQL及Excel的xlsx文件和打包可执行文件

Python
- 案例说明
- 代码编写

Python

实践出真知，我们直接通过一个示例介绍如何使用Python操作数据库、导入导出Excel和打包成exe的可执行文件。

案例说明

我们先在当前环境下新建了个xlsx文件：

如图所示表格第一列用于查询数据库：

在数据库中根据barcode查询到对应的attribute_name和attribute_value后，写入到output.xlsx ：

注意input.xlsx的第一列单元格格式需要设置为文本格式，否则因为barcode为00开头的，会被截取掉。

代码编写

新建项目，选择Python环境，注意核对环境是否是当前的环境：

使用pycharm创建python项目后，自动生成virtualenv环境，点击左下角使用terminal进入该环境下：

# terminal进入当前的python环境
source venv/bin/activate

知道我们要做什么，接下来就是怎么做，首先是依赖包的选型，这个时候我们百度和Google到有如下选择：

Python读取xlsx文件，我们使用xlrd这个包。

# 在刚才打开的terminal下安装xlrd
pip install xlrd

Python写入xlsx文件，我们可以使用xlsx这个包，但是xlsx不支持写入的Excel超过256列，所以不用它了，用个更屌的，使用xlsxwriter这个包。

# xlwt不支持写入256列以上，所以换成xlsxwriter，否则会报：
ValueError: column index (256) not an int in range(256)

# 在刚才打开的terminal下安装xlsxwriter
pip install xlsxwriter

Python操作MySQL数据库，我们可以使用MySQLdb这个包，但是由于MySQLdb只支持Python2，所以也不用这个废物了，直接使用PyMySQL

# 在刚才打开的terminal下安装PyMySQL
pip install pymysql

最后我们安装pyinstaller ，它的作用是可以将python程序封装成exe文件，这样可以直接把exe文件给到其他人直接运行，而不用去安装Python环境。

# 在刚才打开的terminal下安装pyinstaller
pip install pyinstaller

# 打包成一个exe文件的命令，-F 选项可以打出一个exe文件，默认是 -D，意思是打成一个文件夹
pyinstaller -F TestDataGen.py

另外需要注意：

PyInstaller 支持 Python 2.7 / 3.4-3.7。可以在 Windows、Mac OS X 和 Linux 上使用，但是并不是跨平台的，而是说你要是希望打包成 .exe 文件，需要在 Windows 系统上运行 PyInstaller 进行打包工作。

打包成exe文件后，需要获取当前exe文件运行时的路径，这样就可以将input.xlsx和output.xlsx输入输出到当前的目录下。获取路径的方式有如下代码：

import sys
import os
print(sys.path[0])
print(sys.argv[0])
print(os.path.dirname(os.path.realpath(sys.executable)))
print(os.path.dirname(os.path.realpath(sys.argv[0])))

我们使用：

dirpath = os.path.dirname(os.path.realpath(sys.argv[0]))
inputpath = dirpath + '/input.xlsx'
outpath = dirpath + '/output.xlsx'

将代码同步到GitHub或者你的私有Git仓库，由于使用pip安装了很多依赖，参考Node.JS的package.json，Python也有类似的包管理方式，我们使用：

# 将pip安装的包版本写入文件
pip freeze > requirements.txt

需要时可以再引入、重新安装这些依赖：

pip install -r requirements.txt

其他用法可以参考python包管理-最佳实践

完整的代码如下：

# 这份代码要做的事是：
# 1、从input.xlsx这个excel文件读取第一个sheet的第一列
# 2、根据读取出的第一列的值，挨个去MySQL数据库查找数据
# 3、将每条数据库查找出的数据，写入到output.xlsx中去

import xlrd
import xlsxwriter
import pymysql.cursors
import sys
import os

dirpath = os.path.dirname(os.path.realpath(sys.argv[0]))
inputpath = dirpath + '/input.xlsx'
outpath = dirpath + '/output.xlsx'

print(os.path.dirname(os.path.realpath(sys.executable)))
print(os.path.dirname(os.path.realpath(sys.argv[0])))

# 写入工作簿
book = xlsxwriter.Workbook(outpath)     #创建工作簿
sheet = book.add_worksheet()            #创建工作表

# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='daguanren',
                             password='123456',
                             db='daguanren_demodb',
                             port=3306,
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)


# 使用xlrd读取文件
workbook = xlrd.open_workbook(inputpath)
# workbook = xlrd.open_workbook(r'F:\demo.xlsx')
# 获取所有sheet
print (workbook.sheet_names()) # [u'sheet1', u'sheet2']

# 根据sheet索引或者名称获取sheet内容
sheet1 = workbook.sheet_by_index(0) # sheet索引从0开始

sheet1_columns = sheet1.col_values(0)

print(sheet1_columns)

i = 0
for barcode in sheet1_columns:        # 第二个实例
   headers = ['barcode']
   body = [barcode]
   try:
       with connection.cursor() as cursor:
           sql = "SELECT `attribute_name`, `atrribute_value` FROM `tb_xlxs` WHERE `barcode`=%s"
           cursor.execute(sql, (barcode,))
           result = cursor.fetchall()
           sheet.write(i, 0, 'barcode')
           sheet.write(i+1, 0, barcode)
           j = 1
           for rs in result:
               sheet.write(i, j, rs['attribute_name'])
               sheet.write(i+1, j, rs['atrribute_value'])
               j = j + 1
   finally:
       i = i + 2
connection.close()

book.close()