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).
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
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.
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