csvs-to-sqlite

The datasette author offers this tool for conversion: https://github.com/simonw/csvs-to-sqlite

Don't miss out on the homepage's link to Datasette's ecosystem, which includes a variety of CLI tools that are just fantastically convenient for creating and working with sqlite databases: https://docs.datasette.io/en/stable/ecosystem.html I'm a dork who likes using the built-in SQLite interface (and Bash hacks) for bulk CSV import, but it's really hard to beat the convenience of csvs-to-sqlite: https://github.com/simonw/csvs-to-sqlite

The python ecosystem is really good here. I do a lot of exploratory coding in ipython, though the threshold for "switch to a real editor and run git init and poetry new" is pretty low. Want to munge CSV? stdlib, or pandas(https://pandas.pydata.org/pandas-docs/stable/) Want to munge JSON? stdlib. Want to munge a database? pandas, stdlib, anything that speaks sqlalchemy Want to validate your json/sql/CSV/whatever and have it come in as a bunch of structured classes not std types? jsonschema https://pypi.org/project/jsonschema/, attrs+marshmallow, attrs+cattrs. http://www.attrs.org/en/stable/, https://desert.readthedocs.io/en/latest/ https://marshmallow.readthedocs.io/en/stable/ https://github.com/Tinche/cattrs Want to transform csv into sqlite? https://github.com/simonw/csvs-to-sqlite Want to serve sqlite as a REST api? https://datasette.r

I've been approaching this problem from a slightly different direction: I'm constructing a set of tools for converting different data formats into SQLite, because once your data is in SQLite you can join it, filter it and do all kinds of other useful SQL things on top of it. Tools I've written so far include: https://github.com/simonw/csvs-to-sqlite - convert CSV files to a SQLite database https://sqlite-utils.readthedocs.io/en/stable/cli.html - convert JSON files https://github.com/simonw/db-to-sqlite - export data from any SQL database supported by SQLAlchemy (e.g. MySQL and PostgreSQL) into a SQLite file More tools here: https://datasette.readthedocs.io/en/stable/ecosystem.html#to... The end goal is to have databases I can load up in my Datasette web application tool so I can run SQL and export the results as JSON/CSV: https://datasett

Related, but with a free web interface https://github.com/simonw/datasette , https://github.com/simonw/csvs-to-sqlite

Replacing CSV with SQLite is one of my goals for the combination of https://github.com/simonw/datasette and https://github.com/simonw/csvs-to-sqlite

The "REST API for your database" thing isn't actually the part of this I'm most excited about - there are quite a few entries in that category already, such as https://github.com/jeffknupp/sandman2 The thing that excites me is being able to take almost any dataset, package it up into a simple, well-understood format and then deploy that in a read-only, highly scalable way, taking advantage of modern containerization systems. Read-only SQLite is /perfect/ for this use-case. It's fast, widely supported, has a great feature-set and can be distributed as a single file. My favourite feature of Datasette isn't the web UI, it's the publish command: datasette publish now ~/path-to-my/sqlite.db Combined with my https://github.com/simonw/csvs-to-sqlite tool it means you can take a CSV file and turn it into a live-on-the-internet REST SQL API i

A couple of demos: https://fivethirtyeight.datasettes.com/ contains SQLite versions of every CSV published by the FiveThirtyEight crew to https://github.com/fivethirtyeight/data I built a separate tool on top of pandas which can mass-convert CSVs to SQLite - https://github.com/simonw/csvs-to-sqlite - so building the database for https://fivethirtyeight.datasettes.com/ is now a one-liner: csvs-to-sqlite ~/fivethirtyeight/data fivethirtyeight.db https://parlgov.datasettes.com/ uses the SQLite database from http://www.parlgov.org/ - this is a more interesting demo as it includes things like SQL views - e.g. https://parlgov.datasettes.com/parlgov/view_election Both of these demos are served through Cloudflare - since the URL to each database incorporates a sha256 hash of the database contents, I can send a far-future cache expiry header