I have fought with orms for many, many years. I think if you sit behind someone and watch them build an application with an ORM, and you sit behind the same person and watch them use SQL and data access functions and other boring things, the person using the ORM will spend many, many hours fighting to get the ORM to do what they want, or fixing bugs that pop up because the ORM didn't do what they thought it was going to do in subtle ways.
Generally, with threadlocal sessions and an application passing orm data class instances around the code freely (which is by far the most common pattern of use), the application will end up doing 10,000x more queries than the programmer would have guessed (this is a literal number and not an exaggeration). Trying to tell the ORM to preload the tree of objects that is going to be accessed is nearly impossible, since the instances go up and down the stack from function to function, each potentially accessing an attribute of an instance loaded as an attribute of an instance many levels back to the original object intentionally pulled from the database.
ORMs make writing the application 90% faster for the first 2 weeks and then 50% slower from then on.
That doesn't mean you are stuck writing straight SQL queries and passing around rows of data, you can sit for a bit and build data access functions that make your life easier and write classes that represent entities from the data, but without your data going through tens of thousands of lines of (extremely well engineered and thoughtful) ORM code that you have no hope of ever understanding well enough that you will avoid catastrophic mistakes that are extremely hard to fix.
And you will make catastrophic mistakes, mistakes you would probably never make with SQL. SqlAlchemy has 5 states that orm objects can be in. If you are using SqlAlchemy and you can't instantly tell me what those 5 states are, and the detailed description of each, you are already making huge mistakes and corrupting data.
To quote one section from the many pages of SQLAlchemy documentation about 'session state management' (and if you don't know all this stuff by heart you will end up learning a lot of it the hard way):
==============================================
The SELECT statement that’s emitted when an object marked with expire() or loaded with refresh() varies based on several factors, including:
The load of expired attributes is triggered from column-mapped attributes only. While any kind of attribute can be marked as expired, including a relationship() - mapped attribute, accessing an expired relationship() attribute will emit a load only for that attribute, using standard relationship-oriented lazy loading. Column-oriented attributes, even if expired, will not load as part of this operation, and instead will load when any column-oriented attribute is accessed.
relationship()- mapped attributes will not load in response to expired column-based attributes being accessed.
Regarding relationships, refresh() is more restrictive than expire() with regards to attributes that aren’t column-mapped. Calling refresh() and passing a list of names that only includes relationship-mapped attributes will actually raise an error. In any case, non-eager-loading relationship() attributes will not be included in any refresh operation.
relationship() attributes configured as “eager loading” via the lazy parameter will load in the case of refresh(), if either no attribute names are specified, or if their names are included in the list of attributes to be refreshed.
Attributes that are configured as deferred() will not normally load, during either the expired-attribute load or during a refresh. An unloaded attribute that’s deferred() instead loads on its own when directly accessed, or if part of a “group” of deferred attributes where an unloaded attribute in that group is accessed.
For expired attributes that are loaded on access, a joined-inheritance table mapping will emit a SELECT that typically only includes those tables for which unloaded attributes are present. The action here is sophisticated enough to load only the parent or child table, for example, if the subset of columns that were originally expired encompass only one or the other of those tables.
When refresh() is used on a joined-inheritance table mapping, the SELECT emitted will resemble that of when Session.query() is used on the target object’s class. This is typically all those tables that are set up as part of the mapping.
=========================================
Yep, sounds like an ORM makes life a lot simpler!