It depends on what you're writing. I've seen enough projects writing raw SQL because of aversion to ORMs being bogged down in reinventing a lot of what ORMs offer. Like with other choices it is too often a premature optimization (for perf or DX) and a sign of prioritizing a sense of craftsmanship at the expense of the deliverables and the sanity of other team members.
1. Mapping result rows back to objects, especially from joins where you will get back multiple rows per "object" that need to be collated.
2. Automatic handling of many-to-many relationships so you don't have to track which ids to add/remove from the join table yourself.
3. Identity mapping so if you query for the same object in different parts of your UI you always get the same underlying instance back.
4. Unit of work tracking so if you modify two properties of one object and one property of another the correct SQL is issued to only update those three particular columns.
5. Object change events so if you fetch a list of objects to display in the UI and some other part of your UI (or a background thread) add/updates/deletes an object, your list is automatically updated.
6. And finally in cases where your SQL is dynamic having a query builder is way cleaner than concatenating strings together.
For those who are against ORMs I am curious how you deal with these problems instead.
Also, infrastructure guys and DBA types tend not to like ORMs. But they are not the ones trying to manage the complexity in the business process. They just see our queries are not optimal, and it is everything to them.
SQLAlchemy is the real deal, but it's more difficult and people prefer easy.