3 ms·
This post is about a new feature in my sqlite-utils tool, which is a combination CLI utility and Python library for productively working with SQLite database fi
by simonw 5y ago
This post is about a new feature in my sqlite-utils tool, which is a combination CLI utility and Python library for productively working with SQLite database files: https://sqlite-utils.datasette.io/en/latest/changelog.html#v3-10 https://sqlite-utils.datasette.io/en/latest/changelog.html#v...
- raziel2p 5y agoThis tool seems amazing. I'm kinda upset I haven't discovered it before.
- mongol 5y agoBefore doing the select I would probably want to show the schema. Since the JSON could be arbitrarily complex... How would I do that?
- simonw 5y agoGood point, I should add that to the docs. You can see the schema in a couple of ways: sqlite-utils memory blah.json "select sql from sqlite_master" Or sqlite-utils memory blah.json --dump The second option will dump out both the SQL schema and the INSERT statements, so you probably want to | head that. You've inspired me to add a --schema option which does the dump without also including the inserted rows. Issue here: https://github.com/simonw/sqlite-utils/issues/288 https://github.com/simonw/sqlite-utils/issues/288
- DemocracyFTW 5y agoWould you like to speak to the rationale to incorporate a sort-of ORM into Datasette? According to the docs, I can iterate over a subset of rows by writing for row in db[ 'dogs' ].rows_where( 'age > 1', select = 'name, age', order_by = 'age desc' ): ... but how is that better than just writing the equivalent SQL statement, sql = "select name, age from dogs where age > 1 order by age desc;" for row in ...: Most of the arguments in the pythonized form are, after all, just SQL fragments, so it's not like you get column name parametrization (you'd still have to properly escape and concatenate the `select` argument). Perusing the docs I found that considerable effort is spent on explaining this and other ORM-like features, instead of just saying "use the SQL you already know" (people without this knowledge won't be easily able to use this API productively anyway).
- simonw 5y agosqlite-utils isn't (yet) a dependency of Datasette. The goal with sqlite-utils isn't to build an ORM - that .rows_where() method is definitely the most ORM-like piece of it, and it's purely there as a SQL-builder - it was a natural extension of the .rows property, which grew extra features (like order_by) over time as they were requested by users, eg https://github.com/simonw/sqlite-utils/issues/76 https://github.com/simonw/sqlite-utils/issues/76 The vast majority of the library is aimed at making getting data IN to SQLite as easy as possible. Datasette is mainly about executing SELECT queries, and I found myself writing a lot of code to populate those database files - which grew into a combination library and CLI tool. So yeah - I don't use the rows_where() feature much in my own code - I tend to use db.query() directly - but I've evolved the method over time based on user feedback. I don't have particularly strong opinions about it one way or the other.
- DemocracyFTW 5y agoAh OK that's interesting. I would've surmised the `.rows_where()` API having grown out of your own need to repeatedly stitch together pieces of SQL for dynamic queries, but it apparently did not go that way.