时间戳

首页 > 解决方案 > 在python中用mysql查询制作一个excel文件

python - 在python中用mysql查询制作一个excel文件

问题描述

我正在制作一个类似于报告我的代码的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看起来像 在此处输入图像描述

标签： pythonmysqlexcel

解决方案

该time_in字段可能键入为 a 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))

有关日期格式的更多详细信息，请参见：datetime.strftime()

推荐阅读