[GeoffWozniak]

What I didn't mention in my post was that I did use SQLAlchemy Core to write some pretty complicated queries. It's actually quite good. I like it.

There were some spots that things got hairy though, and the code was pretty hard to follow. I don't fault SQLAlchemy here, but I wrote the query in SQL and it was simpler to work with.

SQLAlchemy is absolutely on the right track, but using the core doesn't diminish the fact that you need to know SQL to use it effectively.

I agree with your assertions that just an ORM is not enough, raw SQL is hideous, and that raw SQL spits in the face of programming language advancements. Sadly, it's the assembly language of databases and, unlike CPUs, doesn't have a good abstraction model.

[zzzeek]

So you didn't use SQLAlchemy ORM at all, yet you wrote a whole article about how ORMs "don't work", naming SQLAlchemy (strongly implying the ORM) as an example... if so, it would explain why all the complaints you have about ORMs seem to indicate a misunderstanding of the SQLAlchemy ORM ("attribute creep": query for individual attributes or use `load_only()`, `deferred()`, or other variants; "foreign keys": the ORM only selects from the relational model you've created, if your model has N number of foreign keys and the objects you're querying from span M of them, that's how many it will use, there is no "overuse" or "underuse" of foreign keys possible; "data retrieval": SQLAlchemy's Query object maps to SQL joins in a fully explicit fashion, no bending over necessary (see http://docs.sqlalchemy.org/en/rel_0_9/orm/tutorial.html#quer... ); "Dual schema dangers" - use metadata.create_all() in one direction, or metadata.reflect() in the other, the "dual schema" problem is only in systems like Hibernate that don't offer such features (and actually it does, just not as easily); "Identities" - manual flushing and hand-association of primary key values to foreign keys is not necessary, use relationship(); "transactions"- ORMs don't create this problem and only help to solve it by providing good transactional patterns and abstractions).

I'd appreciate if you amend your article to clarify that you only used SQLAlchemy Core, if this is in fact the case. Your key point that one needs to know SQL in order to use an ORM is absolutely true. However, the value of the ORM is not that it hides awareness of relational databases and SQL; it is in that of automating the task of generating database-specific SQL as well as that of mapping SQL statement execution and result sets, specific to the database driver in use, to object-oriented application state, and keeping these two states in sync without the need for explicit and inconsistent boilerplate throughout the application. I discuss this in many of my talks (see http://www.sqlalchemy.org/library.html#talks).

If you worked in soda bottling company, you probably still know how to fill a bottle of soda by hand. It's the complex machinery that does this automatically which allows this task to scale upwards dramatically. Configuring and using this machinery wouldn't make much sense if you didn't understand its fundamental task of putting soda in bottles, however. The situation is similar when using an ORM to automate the task of generating SQL and mapping in-application data to the rows it represents. Removing the need for "knowledge" has nothing to do with it. The goal instead is to automate work that is tedious and repetitive on a manual scale.

[chipotle_coyote]

The OP wrote: "What I didn't mention in my post was that I did use SQLAlchemy Core to write some pretty complicated queries."

What you seem to have read: "What I didn't mention in my post was that I did use SQLAlchemy Core to write some pretty complicated queries and didn't use SQLAlchemy ORM at all."

It seems to me that it would be more plausible to read: "What I didn't mention in my post was that I did use SQLAlchemy Core to write some pretty complicated queries, in addition to using SQLAlchemy ORM."