我正在制作一个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时出错了,但是找不到
time_in
字段的类型可能是date
,因此mysql
库会自动将其转换为pythondatetime一个快速的解决方案云将
sheet.write(r, c, col)
替换为如下内容:可能
STR_TO_DATE
返回的是datetime
对象,而不是str
。尝试调用strftime()将datetime
对象转换为字符串,然后将其写入所需的文件相关问题 更多 >
编程相关推荐