python 记一次将数据库查询结果写入到表格经历

python 记一次将数据库查询结果写入到表格经历

说明：

    运营那边提了一些需求，在开发还没有在页面上实现此功能前，每天早上都要我们查询语句做成表格，然后发给他们，感觉好烦啊，就用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(‘use J_P2P‘)
    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语句全部写到一个文件里，注意：要以‘；’结尾

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文件里，且以‘；’结尾

    这次表格样式如下：

    表格格式感觉还是不好看

最终优化：

    这次优化了表格的输出格式

    优化了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‘;

本文出自 “见” 博客，请务必保留此出处http://732233048.blog.51cto.com/9323668/1732136

python 记一次将数据库查询结果写入到表格经历

标签：sql python xlwt 表格