5 ms·

Does anyone use SQLite as their daily-driver in lieu of R or pandas for data analysis? I don't think I can use the sqlite command-line since I'd want a fully-de

by tgb 6y ago

Does anyone use SQLite as their daily-driver in lieu of R or pandas for data analysis? I don't think I can use the sqlite command-line since I'd want a fully-developed plotting utility, and it seems less convenient to do the actual analysis part through a sqlite connection in python, say.

nalgeon 6y ago
I do. Apache Superset, Metabase, Redash - all support SQLite. Other BI tools also do.
iagovar 6y ago
You can perform sql queries with many of the DB clients available, such as Heidi, or Deaver, and also through python. Sqlite is OLTP though, for analytical purposes I'd use an OLAP DB like DuckDB. IMO dumping workload in DB is nice, specially when your dataset doesn't fit in RAM.
6gvONxR4sf7o 6y ago
You can always execute SQL in pandas by passing it a connection, getting the best of both worlds.
kwhitefoot 6y ago
Take a look at DB Browser for SQLite: https://sqlitebrowser.org/ https://sqlitebrowser.org/ It has some plotting built in. Not sure if it is sophisticated enough for what you need but it might be worth a try.
tfehring 6y ago
It's complementary, since unlike R/Pandas it can fit too-big-for-memory data - I've used it ad-hoc to store big-ish datasets to pull into R. Though if you're using Python I think that use case is mostly superseded by Dask.
abhgh 6y ago
Not "in lieu", but I have found SQLite to be a good companion to pandas. pandas is good when you want to lend some easily navigable structure to your data without leaving python, but becomes cumbersome (it feels cumbersome esp if you know SQL) when you want to do some serious processing. At such times, I just dump my data into a SQLite DB, and from then on my code has SQL queries to it, which I find are far more readable than corresponding pandas statements (also easier for the reviewer). Another advantage you get is if you wanted to look at these intermediate data, you don't need to run your code in debug mode and view the dataframe at a breakpoint - you can use something like datasette[1] or a standard SQLite DB viewer. So a function that does complex data processing has approximately this structure my code: (1) <non-pandas python code to fetch data, other "normal" stuff> (2) <pandas code to do basic navigation, filtering, grouping etc> (3) <dump to SQLite, followed by SQL queries for the really heavy stuff> (4) <back to python, possibly pandas, to return results, write to a file, plot etc> Step (3) used to be pandas for me before, but depending on how complex your operations are this can become hard to read and/or review. [1] https://github.com/simonw/datasette https://github.com/simonw/datasette - datasette doesnt replace a standard DB IDE, but is a very good lightweight alternative to one if don't intend to perform updates/inserts directly on a table.
- steve_gh 6y ago
  I have exactly the opposite workflow. Import data into salute, perform as much heavy lifting as possible in salute, and export into a python data science workflow via led to if necessary.
simonw 6y ago
I do. I load data into SQLite using various tools I've written (geojson-to-sqlite, db-to-sqlite etc) and run the analysis in https://datasette.io https://datasette.io
qbasic_forever 6y ago
Yes, setup jupyter magics to execute SQL and it becomes MUCH more useful for data analysis in a notebook. There's a great extension that lets you just write and run SQL queries right in cells: https://blog.jupyter.org/a-jupyter-kernel-for-sqlite-9549c5dcf551 https://blog.jupyter.org/a-jupyter-kernel-for-sqlite-9549c5d... Hook it up to an in memory or on disk SQLite instance and you can kick pandas to the curb.
hokkos 6y ago
I use extensively DbBrowser for sqlite to plot simple graph from sql queries of imported csv files, it is easier than to start a jupyter environment with python and pandas or r studio.
dmitrykoval 6y ago
That's one of the core use cases for Vinum - to provide SQL query engine, complementing Pandas in data analysis (thanks to Arrow), yet retain an ability to use native python and numpy funcs as UDFs. Also, it doesn't require input dataset to fit into memory. https://github.com/dmitrykoval/vinum https://github.com/dmitrykoval/vinum

by tgb 6y ago

nalgeon 6y ago

I do. Apache Superset, Metabase, Redash - all support SQLite. Other BI tools also do.

iagovar 6y ago

You can perform sql queries with many of the DB clients available, such as Heidi, or Deaver, and also through python. Sqlite is OLTP though, for analytical purposes I'd use an OLAP DB like DuckDB. IMO dumping workload in DB is nice, specially when your dataset doesn't fit in RAM.

6gvONxR4sf7o 6y ago

You can always execute SQL in pandas by passing it a connection, getting the best of both worlds.

kwhitefoot 6y ago

Take a look at DB Browser for SQLite: https://sqlitebrowser.org/ https://sqlitebrowser.org/ It has some plotting built in. Not sure if it is sophisticated enough for what you need but it might be worth a try.

tfehring 6y ago

It's complementary, since unlike R/Pandas it can fit too-big-for-memory data - I've used it ad-hoc to store big-ish datasets to pull into R. Though if you're using Python I think that use case is mostly superseded by Dask.

abhgh 6y ago

Not "in lieu", but I have found SQLite to be a good companion to pandas. pandas is good when you want to lend some easily navigable structure to your data without leaving python, but becomes cumbersome (it feels cumbersome esp if you know SQL) when you want to do some serious processing. At such times, I just dump my data into a SQLite DB, and from then on my code has SQL queries to it, which I find are far more readable than corresponding pandas statements (also easier for the reviewer). Another advantage you get is if you wanted to look at these intermediate data, you don't need to run your code in debug mode and view the dataframe at a breakpoint - you can use something like datasette[1] or a standard SQLite DB viewer. So a function that does complex data processing has approximately this structure my code: (1) <non-pandas python code to fetch data, other "normal" stuff> (2) <pandas code to do basic navigation, filtering, grouping etc> (3) <dump to SQLite, followed by SQL queries for the really heavy stuff> (4) <back to python, possibly pandas, to return results, write to a file, plot etc> Step (3) used to be pandas for me before, but depending on how complex your operations are this can become hard to read and/or review. [1] https://github.com/simonw/datasette https://github.com/simonw/datasette - datasette doesnt replace a standard DB IDE, but is a very good lightweight alternative to one if don't intend to perform updates/inserts directly on a table.

steve_gh 6y ago
I have exactly the opposite workflow. Import data into salute, perform as much heavy lifting as possible in salute, and export into a python data science workflow via led to if necessary.

simonw 6y ago

I do. I load data into SQLite using various tools I've written (geojson-to-sqlite, db-to-sqlite etc) and run the analysis in https://datasette.io https://datasette.io

qbasic_forever 6y ago

Yes, setup jupyter magics to execute SQL and it becomes MUCH more useful for data analysis in a notebook. There's a great extension that lets you just write and run SQL queries right in cells: https://blog.jupyter.org/a-jupyter-kernel-for-sqlite-9549c5dcf551 https://blog.jupyter.org/a-jupyter-kernel-for-sqlite-9549c5d... Hook it up to an in memory or on disk SQLite instance and you can kick pandas to the curb.

hokkos 6y ago

I use extensively DbBrowser for sqlite to plot simple graph from sql queries of imported csv files, it is easier than to start a jupyter environment with python and pandas or r studio.

dmitrykoval 6y ago

That's one of the core use cases for Vinum - to provide SQL query engine, complementing Pandas in data analysis (thanks to Arrow), yet retain an ability to use native python and numpy funcs as UDFs. Also, it doesn't require input dataset to fit into memory. https://github.com/dmitrykoval/vinum https://github.com/dmitrykoval/vinum