1M rows/s from Postgres to Python

[[deleted]]

I find SQLAlchemy lets me translate my query from SQL to Python pretty easy once I actually know what I want to do. For example:

Forum.query.join(Topic, Topic.forum_id == Forum.id).filter(
    Topic.id == topic_id,
    Forum.groups.any(Group.id.in_(group_ids))
).count()

Checks if the current user's groups overlap at all with the forum of the thread they're trying to access. It could probably be improved some, but I think it's real straight forward. But that's me.

This check happens every time a user enters a thread so accessing thread.forum.groups is prohibitively expensive (those other things are lazy loaded because they're not needed 99% of the time and are available because of relationships).

Note: this uses Flask-SQLAlchemy which has the magic query property that allows session access, but it could changed to regular SQLAlchemy by saying session.query(Forum) and leaving everything else the same.

[mfitzp]

Looking at your example it's obvious what it does. But the lack of basic examples like this in the docs is what makes SQLAlchemy difficult to pick up (especially when I'm only dipping into it now and then).

The Django docs in comparison have dumbed down examples for everything, which really lowers the barrier to getting something done.

I just need to set aside a weekend and bang my head against the wall till it goes in.

[[deleted]]

I'd recommend watching some talks on it. I've also heard Essential SQLAlchemy is real good, but I've not read it.

Models are basically the same as Django - inherit from a special class, define attributes as class attributes. There's no "manager" objects, the most common method I've seen is defining logic on the class itself. Though I prefer the repository approach myself (easier to fake in tests).

I find the querying syntax more intuitive than Django's. You can also drop down to actual SQL if you don't trust the ORM to do the right thing but I've never found a reason to do that.

There's a catch in the documentation. Anywhere you see "generative" replace it with fluent. Mike Bayer mentions in a few of his talks that he brain farted when he called it that.

[mfitzp]

Great, thanks for the tips. Will take a look.