3 ms·

BTW, if you are interested there is an application of sqlbind in a non reporting context (https://github.com/baverman/wadwise/blob/main/wadwise/model.py https:/

by bvrmn 2y ago

BTW, if you are interested there is an application of sqlbind in a non reporting context (https://github.com/baverman/wadwise/blob/main/wadwise/model.py https://github.com/baverman/wadwise/blob/main/wadwise/model....) It's a small app with an attempt (and a goal) to model persistence without ORM. I think it suits quite well, could be fully type hinted (with some raw force though) and somewhat less verbose in this particular context. But also I see how it could be a maintenance hell for a medium/large scale apps.

murkt 2y ago
Yeah! With SQLAlchemy as a query builder I can easily find all usages of a particular column with Find Usages action in PyCharm. With strings it’s not as easy, and some columns are not really greppable (id, author_id, user_id, etc). Also, haven’t seen how sqlbind handles optional JOINs and especially optional LATERAL.
- bvrmn 2y ago
  In this case it's hard to make something fancy :) from sqlbind import WHERE, FIELDS, join_fragments q = Q() # a QueryParams factory fields = [] joins = [] filters = [] if some_condition: fields.append('sub_table.field') joins.append('INNER JOIN sub_table ON (subid)') filters.append(q.sub_table.date > since) sql = f'''\ SELECT {FIELDS('table.field', *fields)} FROM table {join_fragments(' ', joins)} {WHERE(*filters)} ''' Personally I prefer this explicitness to ORM for complex queries which include recursive or multiple joins to the same table. But I agree ORM shines with simple joins especially if models include proper relationship and there is no need to specify 'ON' condition every time.
  - murkt 2y ago
    Whenever we do recursive, or really long queries (not often), we’re dropping to basically raw SQL with sqlalchemy.text(). It’s really a lot to keep it wholly in SQLAlchemy: write a complex query in SQL, convert to SQLA, and remember how all the moving parts should be connected. Should be much easier now with LLMs, though :)

by bvrmn 2y ago

murkt 2y ago

Yeah! With SQLAlchemy as a query builder I can easily find all usages of a particular column with Find Usages action in PyCharm. With strings it’s not as easy, and some columns are not really greppable (id, author_id, user_id, etc). Also, haven’t seen how sqlbind handles optional JOINs and especially optional LATERAL.

bvrmn 2y ago
In this case it's hard to make something fancy :) from sqlbind import WHERE, FIELDS, join_fragments q = Q() # a QueryParams factory fields = [] joins = [] filters = [] if some_condition: fields.append('sub_table.field') joins.append('INNER JOIN sub_table ON (subid)') filters.append(q.sub_table.date > since) sql = f'''\ SELECT {FIELDS('table.field', *fields)} FROM table {join_fragments(' ', joins)} {WHERE(*filters)} ''' Personally I prefer this explicitness to ORM for complex queries which include recursive or multiple joins to the same table. But I agree ORM shines with simple joins especially if models include proper relationship and there is no need to specify 'ON' condition every time.
- murkt 2y ago
  Whenever we do recursive, or really long queries (not often), we’re dropping to basically raw SQL with sqlalchemy.text(). It’s really a lot to keep it wholly in SQLAlchemy: write a complex query in SQL, convert to SQLA, and remember how all the moving parts should be connected. Should be much easier now with LLMs, though :)