Sqlite3 / python - Export from sqlite to csv text file does not exceed 20k

Question

I am attempting to export a sqlite table to a text file and I found some great help at this site. It works great for smaller outputs, but once I reach around 20k it appears to limit the output.

first attempt was:

Mark Bells UniCodeWriter as found in It is possible export table sqlite3 table to csv or similiar?

my table has 15 columns I just listed 5 here to make it easier to read

writer = UnicodeWriter(open("Export8.csv", "wb"))

writer.writerow(["RunID","JobNumber","StartTime","EndTime","Period"])

writer.writerows(results)

second attempt was:

response = cursor.execute("SELECT RunID, JobNumber, StartTime, EndTime, strftime('%s',substr(endtime,1,19)) - strftime('%s',substr(starttime,1,19)) FROM tblTest WHERE RunID <>0")

strfile = open('_output1.csv','wb')

for row in response:
    print >> strfile,row

third attempt was:

strfile = open('_output3.csv','wb')

while True:

    row = cursor.fetchone()

    if row == None:

        break
    print >> strfile,row
    enter code here

4th attempt/test:

response = cursor.execute("SELECT RunID, JobNumber, StartTime, EndTime, Period FROM tblTest WHERE RunID <>0")

print response

Result

In attempt 1: I get an output of 183 full records and the very first column of the 184 record

In attempt 2 and 3: I get an output of 181 full records and some columns of the 182

In attempt 4: I get all my data on the screen

When i check the sqlite database I see 205 records. I am aware that I can just output 100 lines at a time, but i am wondering why I am not getting all my rows outputted

I programmed this using WinPython and spyder. Now I just ran it in IDLE of Python27 directly and the output was correct. — user2565278, Jul 09 '13 at 19:40

score 5 · Accepted Answer · answered Jul 09 '13 at 19:59

5

You can try using pandas to load the sql data and then to dump it into a csv. You'd have to install the dependencies (notably NumPy) to use it though. It's really simple then:

import sqlite3
import pandas.io.sql as sql
con = sqlite3.connect('database.db')
table = sql.read_frame('select * from some_table', con)
table.to_csv('output.csv')

answered Jul 09 '13 at 19:59

1

Python always amazes me, this is a very clean way to export. Thank you – user2565278 Jul 10 '13 at 20:16
This did not work for me and I assume that the pandas module has been changed. However, just importing pandas and using `pandas.read_sql('select * from some_table', con)` gives the correct result. – wyattis May 15 '17 at 14:30

score 2 · Answer 2 · answered Jan 02 '14 at 13:54

Have you tried using the cursor itself as an argument for writerows?

cursor.execute("select * from test")

csv_path = "out.csv"
with open(csv_path, "wb") as csv_file:
    csv_writer = csv.writer(csv_file)
    # Write headers.
    csv_writer.writerow([i[0] for i in cursor.description])
    # Write data.
    csv_writer.writerows(cursor)

score 1 · Answer 3 · answered Apr 09 '15 at 20:42

I am using 3.4 and sqlite3. I managed to write the data without using pandas

c = cur.execute("select * from HChange")

with open('c:/tmp/bse/Output.csv','w', newline='') as file:
    for i in c:
        a=c.fetchone()
        csv.writer(file).writerow(a)