Python writerow method from CSV module skips a line

Question

I am working on a software to handle a firm's deadlines. I want to export the SQL table to a CSV of excel file. I have read this question How to export Mysql Table data to excel file using python script? and tried the offered solution but I encountered an issue. The CSV file skips a row (there is a blank line between each written line, see example).

rg,parti,oggetto,incombente,autorita,giudice,deadline_id,member_id,data,ora,minuti,notes,terminata

123,io,bo,a,ibi,prof,2,1,2022-11-13,10,0,adfggnadfnadf,0

123,io,bo,ia,ib,prof,3,1,2023-01-14,1,24,adfggnadfnadfadfggnadfnadf,0

1241426,sdfgn,ASDG,srtgnawetjn,hgdm,sry,4,1,2023-01-07,10,24,WQEGOUIB<IUSDBV,0

124512,wrtj,SADG,tgjw,rtyj,sfgjh,5,1,2023-01-07,10,31,srgoibn,0

The code is the following:

cursor.execute("SELECT * FROM `lawsuit` INNER JOIN `calendar` WHERE lawsuit.rg = calendar.rg;")
result = cursor.fetchall()
print(result)

        def save_file():
            file_path = asksaveasfile(initialfile='Untitled.csv',
                                      defaultextension=".csv",
                                      filetypes=[("CSV file", "*.csv")])
            if file_path:
                writer = csv.writer(file_path)
                writer.writerow(result[0].keys())
                for row in result:
                    print(row)
                    writer.writerow(row.values())
            else:
                messagebox.showerror("NO FILE SELECTED", "You have cancelled the action, the file has not been created")

btn = tk.Button(self.root, text="Export to CSV", command=lambda: save_file())
btn.place(x=10, y=10)

To simplify everyone's life I will simply attach the result of printing the result variable here (So that we don't need to work with the SQL tables.)

[{'rg': 123, 'parti': 'io', 'oggetto': 'bo', 'incombente': 'a', 'autorita': 'ibi', 'giudice': 'prof', 'deadline_id': 2, 'member_id': 1, 'data': datetime.date(2022, 11, 13), 'ora': 10, 'minuti': 0, 'notes': 'adfggnadfnadf', 'terminata': 0}, {'rg': 123, 'parti': 'io', 'oggetto': 'bo', 'incombente': 'ia', 'autorita': 'ib', 'giudice': 'prof', 'deadline_id': 3, 'member_id': 1, 'data': datetime.date(2023, 1, 14), 'ora': 1, 'minuti': 24, 'notes': 'adfggnadfnadfadfggnadfnadf', 'terminata': 0}, {'rg': 1241426, 'parti': 'sdfgn', 'oggetto': 'ASDG', 'incombente': 'srtgnawetjn', 'autorita': 'hgdm', 'giudice': 'sry', 'deadline_id': 4, 'member_id': 1, 'data': datetime.date(2023, 1, 7), 'ora': 10, 'minuti': 24, 'notes': 'WQEGOUIB<IUSDBV', 'terminata': 0}, {'rg': 124512, 'parti': 'wrtj', 'oggetto': 'SADG', 'incombente': 'tgjw', 'autorita': 'rtyj', 'giudice': 'sfgjh', 'deadline_id': 5, 'member_id': 1, 'data': datetime.date(2023, 1, 7), 'ora': 10, 'minuti': 31, 'notes': 'srgoibn', 'terminata': 0}, {'rg': 12453425, 'parti': 'arhnadfn', 'oggetto': 'sdfna', 'incombente': 'aedrh', 'autorita': 'sdfgn', 'giudice': 'aetn', 'deadline_id': 6, 'member_id': 1, 'data': datetime.date(2023, 1, 7), 'ora': 10, 'minuti': 30, 'notes': 'enqefnadfn\naethnadf', 'terminata': 0}]

Following @Adrian Klaver comment's idea the issue may reside in the asksaveasfile function. If I print the file_path variable I get: name='C:/Users/serax/OneDrive/Documenti/a.csv' mode='w' encoding='cp1252'> .

This is an io.TextIoWrapper (https://docs.python.org/3/library/io.html#io.TextIOWrapper) with some weird encoding in write mode. If i manage to retrieve the path of the file from this io.TextIoWrapper object I can then open it with the standard open() function and hopefully it will work.

Any help is very much appreciated!!! ;)

Probably has to do with whatever is being done in asksaveasfile. Per csv: If csvfile is a file object, it should be opened with newline=''. Is that being done? — Adrian Klaver
– Adrian Klaver, Commented Jan 7, 2023 at 22:01
Are you saying that there is a blank line between each written line? Your code shouldn't write that. How do you print the file to find this out? As an aside, instead of doing the row.values() thing, use csv.DictWriter instead. You run the risk that not all rows will iterate their values in the same order. — tdelaney
– tdelaney, Commented Jan 7, 2023 at 22:01
@tdelaney yes. I have opened the file with notepad and copied the content. Thanks for the tip! — serax
– serax, Commented Jan 8, 2023 at 0:34
@AdrianKlaver i have tried printing file_path and got <_io.TextIOWrapper name='C:/Users/serax/OneDrive/Documenti/a.csv' mode='w' encoding='cp1252'> Is that what csv.writer() should have as argument? — serax
– serax, Commented Jan 8, 2023 at 0:39
I have no idea, as I said it is what is being done in asksaveasfile that is important. Add that code as update to your question. — Adrian Klaver
– Adrian Klaver, Commented Jan 8, 2023 at 1:01

serax · Accepted Answer · 2023-01-08 14:05:05Z

SOLVED IT! :D

cursor.execute("SELECT * FROM `lawsuit` INNER JOIN `calendar` WHERE lawsuit.rg = calendar.rg;")
result = cursor.fetchall()
print(result)    
file_path = asksaveasfilename(initialfile='Untitled.csv',
                                          defaultextension=".csv",
                                          filetypes=[("CSV file", "*.csv")])
print(file_path)
if file_path:
      with open(file_path, "w", newline="") as file:
                    writer = csv.writer(file)
                    writer.writerow(result[0].keys())
                    for row in result:
                        print(row)
                        writer.writerow(row.values())
else:
     messagebox.showerror("NO FILE SELECTED", "You have cancelled the action, the file has not been created")

btn = tk.Button(self.root, text="Export to CSV", command=lambda: save_file())
btn.place(x=10, y=10)

Instead of using asksavefile which return an Io object I use asksavefilename which returns the path of the newly created file. Then I just open it normally and add the newline="" to open(). The file now does not leave any blank line!

Collectives™ on Stack Overflow

Python writerow method from CSV module skips a line

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related