python 记一次将数据库查询结果写入到表格经历
说明：
运营那边提了一些需求，在开发还没有在页面上实现此功能前，每天早上都要我们查询sql语句做成表格，然后发给他们，感觉好烦啊，就用python写了一个脚本，实现此功能
第一次尝试：
#!/usr/bin/env python
#encoding=UTF-8
import MySQLdb
import xlwt #excel write
hostIp = '10.10.94.157'
user = 'xxxx'
passwd = 'xxxx'
database = 'xxxx'
def chongZhiWeiTouZi(): #充值未投资用户
sql = '''SELECT
ctci.acc_no AS '账号',
ctci.cn_name AS '姓名',
ctci.job_phone AS '手机',
ctci.create_date AS '注册时间',
tta.amount AS '充值金额',
tta.create_time AS '充值时间'
FROM
TPP_T_ACCOUNT_DEAL tta,CRM_T_CUSTOMER_INFO ctci
WHERE
tta.customer_id = ctci.id AND
tta.type = 1
AND tta.order_status IN (1, 9)
AND tta.customer_id NOT IN (
SELECT DISTINCT customer_id FROM J_P2P_ORDER WHERE order_type = 1 and product_id !=1
)'''
columnName = ['账号','姓名','手机','注册时间','充值金额','充值时间'] #定义所有的列名，共6列
style1= xlwt.XFStyle() #设置单元格格式
style1.num_format_str= 'yyyy/m/d h:mm:ss'
wb=xlwt.Workbook(encoding='utf-8') #创建一个excel工作簿，编码utf-8，表格中支持中文
sheet=wb.add_sheet('sheet 1') #创建一个sheet
for i in range(len(columnName)): #将列名插入表格，共6列
sheet.write(0,i,columnName[i])
db = MySQLdb.connect(hostIp,user,passwd,database,charset="utf8") #连接数据库，编码utf-8
cursor = db.cursor() #创建一个指针对象
cursor.execute(sql) #执行sql语句
results = cursor.fetchall()
rows = len(results) #获取行数
for i in range(rows):
for j in range(3):
sheet.write(i+1,j,results[i][j])
sheet.write(i+1,3,results[i][3],style1) #设置时间列的单元格格式
sheet.write(i+1,4,results[i][4])
sheet.write(i+1,5,results[i][5],style1)
wb.save('员工信息表.xls') #保存表格，并命名为 员工信息表.xls
cursor.close()
db.close()
def yongHuDengLu(): #用户登录信息
sql = '''SELECT
login_type AS '来源',
cn_name AS '登录名',
login_addr AS '登录IP',
mobile AS '手机号',
area AS '登录地区',
mome AS '手机运营商',
login_time AS '登录时间'
FROM
SYS_T_LOGIN_LOGGER
ORDER BY login_time DESC'''
columnName = ['来源','登录名','登录IP','手机号','登录地区','手机运营商','登录时间'] #定义所有的列名
style1= xlwt.XFStyle() #设置单元格格式
style1.num_format_str= 'yyyy/m/d h:mm:ss'
wb=xlwt.Workbook(encoding='utf-8') #创建一个excel工作簿，编码utf-8，表格中支持中文
sheet=wb.add_sheet('sheet 1') #创建一个sheet
for i in range(len(columnName)): #将列名插入表格
sheet.write(0,i,columnName[i])
db = MySQLdb.connect(hostIp,user,passwd,database,charset="utf8")
cursor = db.cursor() #创建一个指针对象
cursor.execute('use J_P2P')
cursor.execute(sql) #执行sql语句
results = cursor.fetchall()
rows = len(results) #获取行数
for i in range(rows):
for j in range(6):
sheet.write(i+1,j,results[i][j])
sheet.write(i+1,6,results[i][6],style1) #设置时间列的单元格格式
wb.save('用户登录信息.xls') #保存表格
cursor.close()
db.close()
chongZhiWeiTouZi()
yongHuDengLu()分析：
因为有多个sql查询语句，然后制作成多个表格，所以写了多个函数，将每个sql语句分别写到每个函数里
而且表格的列名也是需要手动输入的，感觉好麻烦，而且好傻瓜
第二次尝试：
#!/usr/bin/env python
#encoding=UTF-8
import MySQLdb
import xlwt #excel write
hostIp = '10.10.94.157'
user = 'xxx'
passwd = 'xxxx'
database = 'xxxxx'
f = open('select.sql','r')
reSql = f.read().split(';') #将所有的sql语句赋值给reSql; select.sql文件里的sql语句要以';'结尾
f.close()
def chongZhiWeiTouZi(): #充值未投资用户
style1= xlwt.XFStyle() #设置单元格格式
style1.num_format_str= 'yyyy/m/d h:mm:ss'
wb=xlwt.Workbook(encoding='utf-8') #创建一个excel工作簿，编码utf-8，表格中支持中文
sheet=wb.add_sheet('sheet 1') #创建一个sheet
db = MySQLdb.connect(hostIp,user,passwd,database,charset="utf8") #连接数据库，编码utf-8
cursor = db.cursor(cursorclass = MySQLdb.cursors.DictCursor) #创建一个指针对象
cursor.execute(reSql[0]) #执行sql语句
results = cursor.fetchall()
columnName = []
for i in results[0].keys():
columnName.append(i)
columnLen = len(columnName)
for i in range(columnLen): #将列名插入表格，共6列
sheet.write(0,i,columnName[i])
rows = len(results) #获取行数
for i in range(rows):
for j in range(columnLen):
sheet.write(i+1,j,results[i][columnName[j]])
wb.save('员工信息表.xls') #保存表格，并命名为 员工信息表.xls
cursor.close()
db.close()
def yongHuDengLu(): #用户登录信息
style1= xlwt.XFStyle() #设置单元格格式
style1.num_format_str= 'yyyy/m/d h:mm:ss'
wb=xlwt.Workbook(encoding='utf-8') #创建一个excel工作簿，编码utf-8，表格中支持中文
sheet=wb.add_sheet('sheet 1') #创建一个sheet
db = MySQLdb.connect(hostIp,user,passwd,database,charset="utf8") #连接数据库，编码utf-8
cursor = db.cursor(cursorclass = MySQLdb.cursors.DictCursor) #创建一个指针对象
cursor.execute(reSql[1]) #执行sql语句
results = cursor.fetchall()
columnName = []
for i in results[0].keys():
columnName.append(i)
columnLen = len(columnName)
for i in range(columnLen): #将列名插入表格，共6列
sheet.write(0,i,columnName[i])
rows = len(results) #获取行数
for i in range(rows):
for j in range(columnLen):
sheet.write(i+1,j,results[i][columnName[j]])
wb.save('用户登录信息.xls') #保存表格，并命名为 员工信息表.xls
cursor.close()
db.close()
chongZhiWeiTouZi()
yongHuDengLu()分析：
这次把需要查询的sql语句全部写到一个文件里，注意：每个sql语句要以‘；’结尾
vim select.sql SELECT ctci.acc_no AS '账号', ctci.cn_name AS '姓名', ctci.job_phone AS '手机', ctci.create_date AS '注册时间', tta.amount AS '充值金额', tta.create_time AS '充值时间' FROM TPP_T_ACCOUNT_DEAL tta,CRM_T_CUSTOMER_INFO ctci WHERE tta.customer_id = ctci.id AND tta.type = 1 AND tta.order_status IN (1, 9) AND tta.customer_id NOT IN ( SELECT DISTINCT customer_id FROM J_P2P_ORDER WHERE order_type = 1 and product_id !=1 ); select a.acc_no as '用户名',a.cn_name as '姓名',a.mobile as '银行预留手机',a.job_phone as '登录手机',b.order_amount as '投资金额', c.name as '产品' ,b.lucky_numbers as '幸运号' from ACTIVITY_LUCKY_NUMBER b left join CRM_T_CUSTOMER_INFO a on b.customer_id = a.id left join J_P2P_PRODUCT c on b.product_id = c.id where b.period='4';
而且表格的列名会自己从查询结果里获取，不再手动输入
不过，感觉还是很乱，很复杂，而且依然用了多个函数，而且最终的表格格式还不好看，如下：
第三次尝试：
这次没有再用MySQLdb模块
#!/usr/bin/env python
#encoding=UTF-8
import os
import xlwt #excel write
hostIp = '10.10.94.157'
user = 'xxxx'
passwd = 'xxxx'
db = 'xxxx'
f = open('select.sql','r')
reSql = f.read().split(';') #将所有的sql语句赋值给reSql; select.sql文件里的sql语句要以';'结尾
f.close()
def createTable(selectSql,tableName):
results = os.popen('mysql -h'+hostIp+' -u'+user+' -p'+passwd+' -D'+db+' -e "'+selectSql+'"').read().strip().split('\n')
columnName = results[0].split('\t')
wb=xlwt.Workbook(encoding='utf-8') #创建一个excel工作簿，编码utf-8，表格中支持中文
sheet=wb.add_sheet('sheet 1') #创建一个sheet
rows = len(results) #获取行数
columns = len(columnName)
for i in range(rows):
for j in range(columns):
sheet.write(i,j,results[i].split('\t')[j])
wb.save(tableName) #保存表格
createTable(reSql[0],'充值未投资用户.xls')
createTable(reSql[1],'活动投资名单.xls')分析：
这次感觉还不错，只写了一个函数，语句看上去没有那么复杂
所有的sql语句同样写到select.sql文件里，且以‘；’结尾
vim select.sql SELECT ctci.acc_no AS '账号', ctci.cn_name AS '姓名', ctci.job_phone AS '手机', ctci.create_date AS '注册时间', tta.amount AS '充值金额', tta.create_time AS '充值时间' FROM TPP_T_ACCOUNT_DEAL tta,CRM_T_CUSTOMER_INFO ctci WHERE tta.customer_id = ctci.id AND tta.type = 1 AND tta.order_status IN (1, 9) AND tta.customer_id NOT IN ( SELECT DISTINCT customer_id FROM J_P2P_ORDER WHERE order_type = 1 and product_id !=1 ); select a.acc_no as '用户名',a.cn_name as '姓名',a.mobile as '银行预留手机',a.job_phone as '登录手机',b.order_amount as '投资金额', c.name as '产品' ,b.lucky_numbers as '幸运号' from ACTIVITY_LUCKY_NUMBER b left join CRM_T_CUSTOMER_INFO a on b.customer_id = a.id left join J_P2P_PRODUCT c on b.product_id = c.id where b.period='4';
这次表格样式如下：
表格格式感觉还是不好看
最终优化：
这次优化了表格的输出格式（列名加粗，列的宽度，居中，字体等）
优化了select.sql文件：里面可以写以“#”开头的注释（仍要以“；”结尾）
vim select.sql #充值未投资用户# SELECT ctci.acc_no AS '账号', ctci.cn_name AS '姓名', ctci.job_phone AS '手机', ctci.create_date AS '注册时间', tta.amount AS '充值金额', tta.create_time AS '充值时间' FROM TPP_T_ACCOUNT_DEAL tta,CRM_T_CUSTOMER_INFO ctci WHERE tta.customer_id = ctci.id AND tta.type = 1 AND tta.order_status IN (1, 9) AND tta.customer_id NOT IN ( SELECT DISTINCT customer_id FROM J_P2P_ORDER WHERE order_type = 1 and product_id !=1 ); #活动投资名单 select a.acc_no as '用户名',a.cn_name as '姓名',a.mobile as '银行预留手机',a.job_phone as '登录手机',b.order_amount as '投资金额', c.name as '产品' ,b.lucky_numbers as '幸运号' from ACTIVITY_LUCKY_NUMBER b left join CRM_T_CUSTOMER_INFO a on b.customer_id = a.id left join J_P2P_PRODUCT c on b.product_id = c.id where b.period='4';
#!/usr/bin/env python
#encoding=UTF-8
import os
import time
import xlwt #excel write
hostIp = '10.10.94.157'
user = 'xxxx'
passwd = 'xxxx'
db = 'xxxx'
sqlStr = ''
for i in open('select.sql','r').readlines():
if i.startswith('#') == True: #忽略掉#号开头的行
pass
else:
sqlStr = sqlStr + i
reSql = sqlStr.split(';') #将所有的sql语句赋值给reSql; select.sql文件里的sql语句要以';'结尾
def createTable(selectSql,tableName):
results = os.popen('mysql -h'+hostIp+' -u'+user+' -p'+passwd+' -D'+db+' -e "'+selectSql+'"').read().strip().split('\n')
columnName = results[0].split('\t') #获取列名
wb=xlwt.Workbook(encoding='utf-8') #创建一个excel工作簿，编码utf-8，表格中支持中文
sheet=wb.add_sheet('sheet 1') #创建一个sheet
rows = len(results) #获取行数
columns = len(columnName)
style = xlwt.XFStyle() #创建格式style
font = xlwt.Font() #创建font，设置字体
font.name = 'Times New Roman' #字体格式
style.font = font #将字体font，应用到格式style
alignment = xlwt.Alignment() #创建alignment，居中
alignment.horz = xlwt.Alignment.HORZ_CENTER #居中
style.alignment = alignment #应用到格式style
style1 = xlwt.XFStyle()
font1 = xlwt.Font()
font1.name = 'Times New Roman'
#font1.colour_index = 3 #字体颜色（绿色）
font1.bold = True #字体加粗
style1.font = font1
style1.alignment = alignment
for i in range(columns):
sheet.col(i).width = 5000 #设置列的宽度
for i in range(columns):
sheet.write(0,i,columnName[i],style1) #将数据插入表格
for i in range(1,rows):
for j in range(columns):
sheet.write(i,j,results[i].split('\t')[j],style)
#sheet.write(i,j,results[i].split('\t')[j])
wb.save(tableName) #保存表格，并命名为 充值未投资用户.xls
excelTime = time.strftime("%Y%m%d") #获取当前时间
createTable(reSql[0],excelTime+'充值未投资用户.xls')
createTable(reSql[1],excelTime+'活动投资名单.xls') 最终表格格式如下：
是不是很完美！！
