ömer sarı ömer sarı - 3 months ago 13
Python Question

SQLITE3 database tables export in CSV

I have a database including 10 tables: (date ,day ,month ,year ,pcp1 ,pcp2 ,pcp3 ,pcp4,pcp5 ,pcp6) and each column has 41 years dataset. day, month and year columns are "Null" as l will add them later after exporting tables in csv file and l did this part but format is not correct as each column must be respectively separate.

here is my database example:

date day month year pcp1 pcp2 pcp3 pcp4 pcp5 pcp6
1.01.1979 0.431 2.167 9.375 9.375 0.431 9.375
2.01.1979 1.216 2.583 9.162 9.162 1.216 9.162
3.01.1979 4.041 9.373 23.169 23.169 4.041 23.169
4.01.1979 1.799 3.866 8.286 8.286 1.799 8.286
5.01.1979 0.003 0.051 0.342 0.342 0.003 0.342
6.01.1979 2.345 3.777 7.483 7.483 2.345 7.483
7.01.1979 0.017 0.031 0.173 0.173 0.017 0.173


I would like to get all tables like above. However, I got the following output:

Column 1,Column 2,Ellipsis

1979-01-01,,,,0.431,2.167,9.375,9.375,0.431,9.375

1979-01-02,,,,1.216,2.583,9.162,9.162,1.216,9.162

1979-01-03,,,,4.041,9.373,23.169,23.169,4.041,23.169

1979-01-04,,,,1.799,3.866,8.286,8.286,1.799,8.286

1979-01-05,,,,0.003,0.051,0.342,0.342,0.003,0.342


There are a few problems. Firstly the headers are absent, secondly jumping another row (1 to 3) instead of (1 to 2), and lastly all data come together under column1.

my code is:

import csv
import sqlite3

conn=sqlite3.connect("pcpnew6.db")
c=conn.cursor()

data = c.execute("SELECT * FROM pcp3")
with open('output.csv', 'w') as f:
writer = csv.writer(f)
writer.writerow(['Column 1', 'Column 2', ...])
writer.writerows(data)

Answer
  1. The query is not supposed to return the headers. Also I'm confident about both points below. This is untested, but the description attribute returns the last query table names, so it should work
  2. About the extra blank line every line: I suppose you're using windows. Opening the output as text file add an extra \r (carriage return char). It's handled differently between python 2 and python 3:
  3. It's actually OK, but you have the impression that it's not working because you're opening the csv with excel and excel requires a ; by default for csvs => you have to specify semicolon delimiter or Excel opens it on one column.

See my changes:

In python 3 (it is not possible to open a text file as binary):

    with open('output.csv', 'w', newline="") as f:
        writer = csv.writer(f,delimiter=';')
        writer.writerow(['Column 1', 'Column 2']) # do you need that?
        writer.writerow(data.description) # untested, should work according to documentation
        writer.writerows(data)

In python 2 (the newline option does not exist):

    with open('output.csv', 'wb') as f:
        writer = csv.writer(f,delimiter=';')
        writer.writerow(['Column 1', 'Column 2']) # do you need that?
        writer.writerow(data.description) # untested, should work according to documentation
        writer.writerows(data)