3 ms·
I'm working on something right now with the Python ORM SQLAlchemy. It turns out that getting it to use RETURNING with INSERT is not trivial and requires you to
by nerdponx 3y ago
I'm working on something right now with the Python ORM SQLAlchemy. It turns out that getting it to use RETURNING with INSERT is not trivial and requires you to set the non-obvious option `expire_on_commit=False`, which doesn't guarantee use of RETURNING, but is supposed to use it if your db driver and database happen to support it and the ORM happens to support it for that particular combination of driver and database. And there's no API to actually inspect the generated SQL even though it's emitted in the logs, so there's no way to enforce the use of RETURNING in your test suite without capturing and scraping your own logs (which fortunately is very easy within the Pytest framework).
I like ORMs but this is just frustratingly complicated on so many levels. I also understand that SQLAlchemy is an enormous library and not everything will be easy. But I think this case exemplifies the trade-offs involved with using an ORM.
(Yes I am aware that using insert() itself in Core does what I want, I'm talking about .add()-ing an ORM object to an AsyncSession).
- bootsmann 3y agoThere is certainly an API to inspect your query, you can just call print() on the object iirc.
- nerdponx 3y agoThe problem is with using `session.add(obj)` instead of `session.scalars(insert(TheClass).returning(TheClass), data)`. If there's a way to get generated SQL from an AsyncSession, please do let me know.
- camgunz 3y agoI think you have a couple options [0], I currently have a project that uses 'echo' in debug mode. [0]: https://stackoverflow.com/questions/27748053/how-to-log-sql-statements-and-rows-returned-in-sqlalchemy-to-aid-in-debugging#27748054 https://stackoverflow.com/questions/27748053/how-to-log-sql-...
- nerdponx 3y agoYep, that's the same output that's emitted in the sqlalchemy.engine logger. If you use the logger you can use Pytest caplog to capture it.
- perlgeek 3y agoThis is a very blunt tool, but `engine.echo = True` prints all SQL going to the DB. (I don't have any experience with AsyncSession, so cannot contribute something more specific)
- roughly 3y agoI don’t mean this as a slight on SQLAlchemy - it does a lot of things very well and provides a ton of levers and knobs - but it’s absolutely the first place I look when I’m looking for performance improvements. A friend used to say Zookeeper was where the crazy lived in any application that used it - sqlalchemy is where the slow lives in any application that uses it.
- somsak2 3y agonot sure this is that specific to sqlalchemy, you could say this really about any ORM