Nelson's log

simple database exploration

Following up on an earlier post wanting to be able to do database things simpler, some nice results.

I’m liking dataset as a quick way to write data from Python into a database. Basically you just insert dicts into a table; it takes care of generating a schema, selecting types for things, etc. I’m really liking it for writing data to databases. It helps read data too! But it doesn’t do much other than wrap rows in dicts which is nice, but I wanted more. (It also has support for fancy Python queries, but I just want raw SQL).

For reading data I’m liking ipython-sql. It adds a nice Jupyter interface on top of a raw SQL repl like that provided by sqlite or psql. It’s quite simple to install. And since it’s all implemented as an IPython magic you can mix it in with Python code in your notebook. I’m mostly just using it as a way to display SQL results as HTML tables; simple, but very useful. It has some support for fancier stuff like plotting and exporting the result sets into Python objects, Pandas DataFrames, and CSV files.