python - 在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 库会自动将其转换为 python datetime .

云的快速解决方法是将 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