SQLITE3 database tables export in CSV
<p>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.</p>

<p>here is my database example:</p>

<pre><code>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
</code></pre>

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

<pre><code>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
</code></pre>

<p>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.</p>

<p>my code is:</p>

<pre><code>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)
</code></pre>

<ol>
<li>The query is not supposed to return the headers. Also I'm confident about both points below. This is untested, but the <code>description</code> attribute returns the last query table names, so it should work</li>
<li>About the extra blank line every line: I suppose you're using windows. Opening the output as text file add an extra <code>\r</code> (carriage return char). It's handled differently between python 2 and python 3:</li>
<li>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 <code>;</code> by default for csvs => you have to specify semicolon delimiter or Excel opens it on one column.</li>
</ol>

<p>See my changes:</p>

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

<pre><code>    with open('output.csv', 'w', newline="") as f:
        writer = csv.writer(f,delimiter=';')
</code></pre>

<p>according to documentation
            writer.writerows(data)</p>

<p>In python 2 (the newline option does not exist):</p>

<pre><code>    with open('output.csv', 'wb') as f:
        writer = csv.writer(f,delimiter=';')
        writer.writerows(data)
</code></pre>

<p>Edit: python 2/3 compatible snippet, with title line:</p>

<pre><code>import sys

if sys.version_info &lt; (3,):
    f = open('output.csv', 'wb')
else:
    f = open('output.csv', 'w', newline="")

writer = csv.writer(f,delimiter=';')
first_item = next(data)  # get first item to get keys
writer.writerow(first_item.keys())  # keys=title you're looking for
writer.writerow(first_item)
# write the rest
writer.writerows(data)

f.close()
</code></pre>

python sqlite csv