ERROR = 1054 (42S22): Unknown column 'company_name' in 'field list'

Question

i know there some question already asked on this error code but in my case query was working on my test file but after converting in to a flask method it is giving me an error i am working on flask and my task is to create and download excel of particular user between given dates my flask method code is as below

@app.route('/excel_sheet')
def mk_excel_sheet():

    sort_user = request.args.get('sort_user2', 'abc1')
    from_date = request.args.get('from_date2', 'abc2')
    to_date = request.args.get('to_date2', 'abc3')
    company_name = session.get('company_name', 'abc4')

    print(f"{sort_user}\n{from_date}\n{to_date}\n{company_name}")

    file_name = f"{sort_user}-{from_date[:2]}-{from_date[3:5]}-{from_date[6:10]}-{to_date[:2]}-{to_date[3:5]}-{to_date[6:10]}.xlsx"
    print("file_name = ",file_name)
    file_path = 'static/'+str(file_name)
    print("file_path = ", file_path)
    #print(from_date, to_date)

    cur = connn.cursor()

    query = """
        SELECT date_format, type, username, time_in, address_in, time_out, address_out 
        FROM (
            SELECT date_today AS 'date_format', 'attendence' AS 'type', user AS 'username', 
                company_name AS 'comp_nam', 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 add_time, '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.comp_nam = '"""+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(query)
        data = cur.fetchall()
        print("data = ", data)

        book = xlsxwriter.Workbook(file_path)
        sheet = book.add_worksheet('sheet 1')
        bold = book.add_format({'bold': True})
        text_wrap = book.add_format({'text_wrap': True})

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

        r = 2
        for r, row in enumerate(data):
            print(f"r = {r} | row = {row}")
            date = row[0]
            row_date = row[0]
            print(f"{date}|{row_date}")
            #if row_date is date

            for c, col in enumerate(row):
                print(f"c = {c} | col = {col}")
                sheet.set_column('{0}:{0}'.format(chr(c + ord('A'))), len(str(col)) + 2)
                sheet.write(r, c, col if type(col) is not datetime else col.strftime('%d-%m-%Y %H:%M:%S'), text_wrap)

        book.close()

    except Exception as e:
        print("ERROR = ",e)
    try:
        return send_file(file_path, mimetype='text/xlsx', attachment_filename=file_name, as_attachment=True)
    except Exception as e:
        print("FILE ERROR = ",e)

i am getting ERROR = 1054 (42S22): Unknown column 'company_name' in 'field list' what is field list in my mysql query i don't know why because query was working in normal test.py file so can some one point me out what i am doing wrong

Do you have `company_name` columns in both the `attendance` and `visits` tables? — Barmar, May 06 '19 at 08:27
BTW, it's generally better to put the `WHERE` test on the inner queries before doing the `UNION`. — Barmar, May 06 '19 at 08:29
@Barmar can you also please look after this issue https://stackoverflow.com/questions/56005424/custom-css-not-working-when-using-bootstrap-with-flask — Himanshu Poddar, May 06 '19 at 13:29
"field list" is the list of column names in the `SELECT` list. So the error message means that one of the tables doesn't have a column named `company_name`. Make sure you have the spelling correct in both cases. — Barmar, May 06 '19 at 15:03
hello @Barmar i have doubled checked my whole query problem is i am using same query in my test file where i was checking that excel file gets created as per my requirement so in that test every thing goes as it has to be with same query i have just copy pasted that query into my flask method but giving me an error so i tried writing it then also same error comes — Divyang patel, May 07 '19 at 05:28
Please add the output of `show create table attendance` and show create table visits` to the question. — Barmar, May 07 '19 at 13:37
hello @Barmar i found my mistake it was in my mysql connection BTW thx — Divyang patel, May 07 '19 at 15:14

ERROR = 1054 (42S22): Unknown column 'company_name' in 'field list'

0 Answers0