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>

<p>Here is the answer of my question and it combined with the code posted by Jean-FranÃ§ois Fabre ,thanks for his help.</p>

<pre><code>import sys
import sqlite3
import csv

conn=sqlite3.connect("pcpnew6.db")
c=conn.cursor()
conn.row_factory=sqlite3.Row
crsr=conn.execute("SELECT * From pcp3")
row=crsr.fetchone()
titles=row.keys()

data = c.execute("SELECT * FROM pcp3")
if sys.version_info &lt; (3,):
    f = open('output.csv', 'wb')
else:
    f = open('output.csv', 'w', newline="")

writer = csv.writer(f,delimiter=';')
writer.writerow(titles)  # keys=title you're looking for
# write the rest
writer.writerows(data)
f.close()
</code></pre>

<p>here is output of code:</p>

<pre><code>date    day month   year    pcp1    pcp2    pcp3    pcp4    pcp5    pcp6
1.01.1979               0.431   2.167   9.375   9.375   2.167   0.431
2.01.1979               1.216   2.583   9.162   9.162   2.583   1.216
3.01.1979               4.041   9.373   23.169  23.169  9.373   4.041
4.01.1979               1.799   3.866   8.286   8.286   3.866   1.799
5.01.1979               0.003   0.051   0.342   0.342   0.051   0.003
6.01.1979               2.345   3.777   7.483   7.483   3.777   2.345
</code></pre>

python sqlite csv