3 ms·
Would 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 f
by DemocracyFTW 5y ago
Would 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.