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

Question

I am trying to export a sqlite table to a text file, and I found excellent help on this site. It works great for small exits, but as soon as I reached around 20,000, it seems to limit the output.

first try:

Mark Bells UniCodeWriter found in Can I export sqlite3 table to csv table or similar?

There are 15 columns in my table that I just listed here 5 to make it easier to read

writer = UnicodeWriter(open("Export8.csv", "wb")) writer.writerow(["RunID","JobNumber","StartTime","EndTime","Period"]) writer.writerows(results) 

Second attempt:

 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:

 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 output from 183 complete records and the very first column of record 184

In attempt 2 and 3: I get output from 181 complete records and some 182 columns

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

When I check the sqlite database, I see 205 records. I know that I can just output 100 lines at a time, but I wonder why I don’t get all my lines issued

Answer 1

You can try using pandas to load sql data and then dump it in csv. You will need to install dependencies (especially NumPy) in order to use them. It is really simple:

 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') 

Answer 2

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) 

Answer 3

I am using 3.4 and sqlite3. I managed to write 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)