SQLite还是平面文本文件?[英] SQLite or flat text file?






我知道python-> r有rpy或rpy2,但我想知道这个问题在更一般的意义上 - 我使用了许多没有RPY2的计算机,并且还使用了其他一些科学分析软件这需要在不同时间访问数据(处理和分析的阶段也分开).


如果所有语言都支持sqlite-使用它. SQL的功能现在可能对您没有用,但是它可能会在某个时候,它可以节省您在决定以更复杂的方式查询数据时必须重写的内容. 如果您只想访问数据存储中的某些数据位,则

sqlite可能会更快地更快 - 因为使用平面文件执行此操作的情况很难挑战,而无需阅读整个文件(尽管并非不可能).




sqlite允许您非常轻松地过滤数据(即使没有太多的SQL专业),正如您已经提到的那样可以自行进行一些计算(AVG,SUM,...).使用firefox插件 sqlitemanager 您可以在每个DB上使用DB计算机没有任何安装/配置故障,因此可以轻松管理您的数据(导入/导出,过滤器).




I process a lot of text/data that I exchange between Python, R, and sometimes Matlab.

My go-to is the flat text file, but also use SQLite occasionally to store the data and access from each program (not Matlab yet though). I don't use GROUPBY, AVG, etc. in SQL as much as I do these operations in R, so I don't necessarily require the database operations.

For such applications that requires exchanging data among programs to make use of available libraries in each language, is there a good rule of thumb on which data exchange format/method to use (even XML or NetCDF or HDF5)?

I know between Python -> R there is rpy or rpy2 but I was wondering about this question in a more general sense - I use many computers which all don't have rpy2 and also use a few other pieces of scientific analysis software that require access to the data at various times (the stages of processing and analysis are also separated).


If all the languages support SQLite - use it. The power of SQL might not be useful to you right now, but it probably will be at some point, and it saves you having to rewrite things later when you decide you want to be able to query your data in more complicated ways.

SQLite will also probably be substantially faster if you only want to access certain bits of data in your datastore - since doing that with a flat-text file is challenging without reading the whole file in (though it's not impossible).


A flat text file (e.g. in csv format) would be the most portable solution. Almost every program/library can work with this format: R and Python have good csv support and if your data set isn't too large you can even import the csv into Excel for smaller tasks.

However, text files are unhandily for larger data sets since you need to read them completely for almost all operations (depending on the structure of your data).

SQLite allows you to filter the data very easily (even without much SQL experties) and as you already mentioned can do some computation on its own (AVG, SUM, ...). Using the Firefox Plug-in SQLiteManager you can work with the DB on every computer without any installation/configuration trouble and thus easily manage your data (import/export, filter).

So I would recommend to use SQLite for larger data sets that needs a lot of filtering to extract the data that you need. For smaller data sets or if there is no need to select subsets of your data a flat (csv) text file should be fine.