Sqlite3/python - 从sqlite导出到csv文本文件不超过20k [英] Sqlite3 / python - Export from sqlite to csv text file does not exceed 20k

问题描述

我试图将一个 sqlite 表导出到一个文本文件，我在这个站点上找到了一些很好的帮助.它适用于较小的输出，但一旦我达到 20k 左右，它似乎限制了输出.

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.

Mark Bells UniCodeWriter 见于可以将表 sqlite3 表导出到 csv 或类似吗?

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

我的表格有 15 列，我在这里只列出了 5 列以便于阅读

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)

第二次尝试是:

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

第三次尝试是:

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

while True:

    row = cursor.fetchone()

    if row == None:

        break
    print >> strfile,row
    enter code here

第四次尝试/测试:

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

print response

结果

在尝试 1 中:我得到 183 条完整记录的输出和 184 条记录的第一列

Result

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

在尝试 2 和 3 中:我得到 181 条完整记录的输出和 182 条的一些列

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

在尝试 4 中:我在屏幕上获取所有数据

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

当我检查 sqlite 数据库时，我看到 205 条记录.我知道我一次只能输出 100 行，但我想知道为什么我没有输出所有行

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

推荐答案

您可以尝试使用 pandas 加载 sql 数据，然后将其转储到 csv 中.不过，您必须安装依赖项(特别是 NumPy)才能使用它.那么这真的很简单:

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

这篇关于Sqlite3/python - 从sqlite导出到csv文本文件不超过20k的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！