[zzzeek]

> It supposedly promises you that you don't need to know SQL to use

please support this assertion with an ORM whose documentation promises this.

> ORM constantly will make unnecessary SQL queries and by default request all fields,

as does "SELECT * FROM table" if you don't write out the fields and use a buffering database adapter (which is the case for nearly all Python database adapters), so, when using an ORM, you need to give it instructions over what columns you need to fetch. This is not unusual nor even anything a library could possibly guess for you if you do not give it this intent.

[takeda]

> please support this assertion with an ORM whose documentation promises this.

I don't think any ORM would officially claim that, but many people decide to go that way to avoid SQL. For many simple examples it looks simpler than SQL.

I also experimented myself and wrote my code using SQLAlchemy's ORM. Then edited my code to use psycopg2 natively. I realized that SQLAlchemy didn't save any code for me, in fact it was more verbose. It also heavily encouraged me to get the raw data from database and do processing of it in the application. It also did unnecessary extra queries.

So with it now I need to:

1. still understand SQL

2. trying to figure how to write the SQL statement using ORM

3. figure out how to make ORM not to do extra queries

> as does "SELECT * FROM table" if you don't write out the fields and use a buffering database adapter (which is the case for nearly all Python database adapters), so, when using an ORM, you need to give it instructions over what columns you need to fetch. This is not unusual nor even anything a library could possibly guess for you if you do not give it this intent.

My problem is that in addition to knowing SQL, I also need to know how to do in that specific ORM that's for the specific language I'm using.

[int_19h]

The problem is that once you start fetching specific columns in your queries, the resulting objects aren't entities, just records - i.e. it's not really object-oriented, which is the main allure of ORM.

ORMs that are more honest about this, such as SQLAlchemy, are generally better than those that try to pretend that you really are dealing with entities.

[collinmanderson]

I use .values() and .values_list() to fetch raw records instead of objects. It still allows me to construct the query using the ORM which is nice.

[int_19h]

I think that "ORM" is a bit of a misnomer for that. I'd call it "language-integrated query", if not for the potential confusion with LINQ (which stands for exactly that, not coincidentally).

[takeda]

There's a clear distinction between query builder and ORM though you can especially see this with SQLAlchemy.

But anyway even query builder is not that great. Recently used PyCharm's integration with DataGrip. Basically the way it works is that if you configured a database in your project and let PyCharm fetch its schema, suddenly the IDE started recognizing the SQL statements providing autocomplete not only the statements but also table names etc, it also offered refactoring which created migration scripts.

After using that I think that's the proper way of solving the impedance mismatch and at that point you no longer need ORM or query builders. I hope other IDEs will start doing the same thing.

[collinmanderson]

> I think that "ORM" is a bit of a misnomer for that.

Good point: I'm no longer doing object mapping in that case. :) Though I think the most complicated part of the process that the Django "ORM" does for you is build the query. Maybe it should be called Django query generator.