Use of ORMs is often a performance choke point. Raw DB queries are often much, much faster.
Almost always, the more you abstract, the worse you perform. It's great as a developer but not so great as a user.
They're clunky monstrosities that act only as guard-rails for inexperienced developers. Far better to invest a few days (which is realistically all you need) to improve their SQL skills and/or code-review practices.
The exceptions are SQL elements that cannot be (easily, or at all) parametrized, such as the column lists in SELECT, ORDER BY, GROUP BY, or changing the WHERE "shape" and so on.
In my experience, this tends to be a minority of queries, although an important minority.
----
P.S. If string concatenations are your bottleneck, then your database is screaming fast! The real-life bottlenecks are usually in excessive database round-trips and unoptimized query plans, and are orders-of-magnitude larger.
If you connect the IDE to a database it starts to recognize the SQL to the point it behaves like rest of the code (you have autocomplete etc). I am starting to think that this is the correct approach and ORMs were just a hack trying to achieve that.
> I think there is a fairly high performance cost to doing so many string concatenations every time the code runs
String concatenation is extremely cheap compared to any sort of IO or computation.
ORMs are there for mapping objects and well, relations. Most ORMs provide additional features, but at the core they can, and for more complicated queries they should be used with native SQL queries. They are made for OLTP not for OLAP
There are "simple ORMs" that only map results of SQL queries to objects. They do not provide a magic query API - which is the source of most problems. I don't do Python, but for .NET there is Dapper https://github.com/StackExchange/Dapper, you can have a look what I mean. You write the SQL query, explicitly execute it, the library maps the results of that query into objects (it's C#, so you have to declare the class. In Python I'd imagine it would create the object for you)
In the case of SQLAlchemy (referenced in the article), many people use it as a database connection abstraction rather than an ORM. It's kinda the equivalent of "ODBC" for certain Python libraries like Pandas.
For instance, in Pandas you can write your dataframe to the database by going `df.to_sql(tblname, sqlachemycon)` where sqlalchemycon is the connection instance to any database that SQLAlchemy supports.
https://pandas.pydata.org/pandas-docs/version/0.23.4/generat...
I use SQLAlchemy for this purpose alone and write straight up SQL. I've never used the ORM parts of SQLAlchemy.