用python中的mysql查询生成excel文件

Question

我正在制作一个excel文件，类似下面的代码报告

import mysql.connector
import xlwt
from xlsxwriter.workbook import Workbook

connn = mysql.connector.connect(user='root',password='root',host='localhost',port='3306',database='attendence')

company_name = 'MCC-INDIA'
sort_user = 'MCCuser0036'
from_date = '01-04-2019'
to_date = '27-04-2019'

cur = connn.cursor()

query3 = "SELECT type, username, time_in, address_in, time_out, address_out FROM (SELECT 'attendence' AS 'type', user AS 'username', company_name AS 'cn', STR_TO_DATE(in_time, '%d-%m-%Y %H:%i:%s') AS 'time_in', in_address AS 'address_in', STR_TO_DATE(out_time, '%d-%m-%Y %H:%i:%s') AS 'time_out', out_address AS 'address_out' FROM attendence UNION ALL SELECT 'visit', username, company_name, STR_TO_DATE(visit_time_in, '%d-%m-%Y %H:%i:%s'), location_in, STR_TO_DATE(visit_time_out, '%d-%m-%Y %H:%i:%s'), location_out FROM visits) t WHERE t.username = '"+str(sort_user)+"' AND t.cn = '"+str(company_name)+"' AND time_in BETWEEN STR_TO_DATE('"+str(from_date)+"', '%d-%m-%Y %H:%i:%s') AND STR_TO_DATE('"+str(to_date)+"', '%d-%m-%Y %H:%i:%s') ORDER BY time_in ASC;"


try:
    cur.execute(query3)
    #data = cur.fetchall()
    book = xlwt.Workbook('record.xlsx')
    sheet = book.add_sheet('sheet 1', cell_overwrite_ok=True)
    aaa = 1

    sheet.write(0,0,'TYPE')
    sheet.write(0,1,'USERNAME')
    sheet.write(0,2,'TIME IN')
    sheet.write(0,3,'LOCATION IN')
    sheet.write(0,4,'TIME OUT')
    sheet.write(0,5,'LOCATION OUT')


    r = 2

    for r, row in enumerate(cur.fetchall()):
        r+=1
        for c, col in enumerate(row):
            sheet.write(r, c, col)

    book.save('record.xlsx')

except Exception as e:
    print("ERROR = ",e)

当我通过不同的python运行mysql查询来查看输出时，我看到的是

('attendence', 'MCCuser0036', datetime.datetime(2019, 4, 1, 15, 29, 8), '736, Aala Hazrat Rd, Jagruti Nagar, Nehru Nagar, Kurla East, Mumbai, Maharashtra 400071, India', None, '')
('visit', 'MCCuser0036', datetime.datetime(2019, 4, 1, 15, 55, 45), '736, Aala Hazrat Rd, Jagruti Nagar, Nehru Nagar, Kurla East, Mumbai, Maharashtra 400071, India', None, '')
('attendence', 'MCCuser0036', datetime.datetime(2019, 4, 11, 15, 17, 56), 'Kokri Agar Road, Koliwada, Kokri Agar, Sion, Mumbai, Maharashtra 400037, India', None, '')
('attendence', 'MCCuser0036', datetime.datetime(2019, 4, 23, 17, 41, 5), 'Kokri Agar Road, Koliwada, Kokri Agar, Sion, Mumbai, Maharashtra 400037, India', datetime.datetime(2019, 4, 23, 17, 42, 54), 'Kokri Agar Road, Koliwada, Kokri Agar, Sion, Mumbai, Maharashtra 400037, India')

我不知道为什么要在第三列或索引位置2处获取datetime.datetime

当我想看那个专栏的时候

2019-04-01 15:29:08
2019-04-01 15:55:45
2019-04-11 15:17:56
2019-04-23 17:41:05

有人能指出我做错了什么吗 我认为在尝试将mysql查询转换为python时出错了，但是找不到

我的excel看起来像

Answer 1

time_in字段的类型可能是date，因此mysql库会自动将其转换为pythondatetime

一个快速的解决方案云将sheet.write(r, c, col)替换为如下内容：

sheet.write(r, c, col if type(col) is not datetime else col.strftime('desired format here))

See: datetime.strftime() for more details about date formatting