making an excel file out of mysql query in python

Question

i am making an excel file something like report my code as below

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)

when i run mysql query through different python to see the output then what i see is

('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')

i dont know why i m gettin datetime.datetime in thired column or at index position 2

when i try to see that particular column then

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

can someone pointout what is wrong i m doing i think something is going wrong while trying to convert mysql query into python but cant find

my excel looks like

olinox14 · Accepted Answer · 2019-05-02 12:51:09Z

1

The time_in field is probably typed as a date, so the mysql library automatically convert it to a python datetime.

A quick workaround cloud be replacing sheet.write(r, c, col) by something like:

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

answered May 2, 2019 at 12:51

olinox14

6,7223 gold badges27 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Divyang patel Over a year ago

hello @olinox i forgot to mention that time_in field is varchar

Pythia · Accepted Answer · 2019-05-02 12:48:15Z

0

Probably STR_TO_DATE is returning a datetime object instead of a str. Try calling strftime() to transform the datetime object to a string, then writing that to the desired file.

answered May 2, 2019 at 12:48

Pythia

265 bronze badges

1 Comment

Divyang patel Over a year ago

hello @Pythia i believe that str_to_date is function of mysql and i have used to convert in_time and out_time column to date for comparison purpose because both column are also varchar

Collectives™ on Stack Overflow

making an excel file out of mysql query in python

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related