3 ms·

I've found that sqlAlchemy core (the layer below the ORM) works exceedingly well for me–it's composable, exposes SQL easily as needed, makes abstraction easy et

I've found that sqlAlchemy core (the layer below the ORM) works exceedingly well for me–it's composable, exposes SQL easily as needed, makes abstraction easy etc. The major benefit is that the objects in the expression builder represent the parse tree, rather than representing SQL strings directly.

weberc2 10y ago
I've had the opposite experience. The concept of a query builder is great, but SQLAlchemy is a pain. Its documentation is particularly hard to traverse or search, and it's super unintuitive. Perhaps the nature of query building is deceptively complex, but it seems like the library just tries to be too magical and ends up making things worse instead of better.
- SatvikBeri 10y ago
  Yeah, the documentation is really bad. I found that there were two types of objects returned by sqlAlchemy: "query objects" (which can then be executed or compiled into SQL) and "selectables" (which are tables, joins, etc. and would compile to queries that have select statement, just something you could select from.) Almost all operations consist of creating or composing these. But this wasn't anywhere in the documentation! Once you figure out these tools there's actually no magic–it's pretty easy to figure out exactly what the expression builder is doing–but finding these took a bunch of trial and error.
  - weberc2 10y ago
    > it's pretty easy to figure out exactly what the expression builder is doing I don't know about that. I touched SQLAlchemy again this very morning for the first time in several months. I was just trying to get the count of a table with several rows, so I ran `table.count()`, but it kept returning 0. The problem happened on MySQL and Postgres. It turns out that this doesn't compile to `select count()`, but instead it returns the count of the first column in the SQLAlchemy table object's column list. In my case, that column was all NULL, so the count was coming back as zero. I thought maybe this was an accident, but here's the body of the count() function from SQLAlchemy's source: ``` if self.primary_key: col = list(self.primary_key)[0] else: col = list(self.columns)[0] return Select( [functions.func.count(col).label('tbl_row_count')], whereclause, from_obj=[self], *params) ``` I run into this sort of unintuitive behavior every time I touch this library. :(

weberc2 10y ago

I've had the opposite experience. The concept of a query builder is great, but SQLAlchemy is a pain. Its documentation is particularly hard to traverse or search, and it's super unintuitive. Perhaps the nature of query building is deceptively complex, but it seems like the library just tries to be too magical and ends up making things worse instead of better.

SatvikBeri 10y ago
Yeah, the documentation is really bad. I found that there were two types of objects returned by sqlAlchemy: "query objects" (which can then be executed or compiled into SQL) and "selectables" (which are tables, joins, etc. and would compile to queries that have select statement, just something you could select from.) Almost all operations consist of creating or composing these. But this wasn't anywhere in the documentation! Once you figure out these tools there's actually no magic–it's pretty easy to figure out exactly what the expression builder is doing–but finding these took a bunch of trial and error.
- weberc2 10y ago
  > it's pretty easy to figure out exactly what the expression builder is doing I don't know about that. I touched SQLAlchemy again this very morning for the first time in several months. I was just trying to get the count of a table with several rows, so I ran `table.count()`, but it kept returning 0. The problem happened on MySQL and Postgres. It turns out that this doesn't compile to `select count()`, but instead it returns the count of the first column in the SQLAlchemy table object's column list. In my case, that column was all NULL, so the count was coming back as zero. I thought maybe this was an accident, but here's the body of the count() function from SQLAlchemy's source: ``` if self.primary_key: col = list(self.primary_key)[0] else: col = list(self.columns)[0] return Select( [functions.func.count(col).label('tbl_row_count')], whereclause, from_obj=[self], *params) ``` I run into this sort of unintuitive behavior every time I touch this library. :(