Most Sequel-like Python ORM?

[rgalanakis]

I have a client that really wants their new backend in Python. I have extensive experience in Python, but have never found an ORM that compares to Sequel (actually this is true in every other language I've used...). I am not using Django; I will fall back to SQLAlchemy if needed. But I was wondering if anyone knows of a Python ORM that 'feels' like Sequel. For me that means:

• Takes the approach of a 'database toolkit' rather than an all-in ORM (being able to use Sequel without models is amazing).
• Focused on people that plan to take advantage of the database. For example, migrations use the Sequel DSL and are more like traditional non-ORM migrations, rather than being derived from models. Similarly, you can focus on creating a nice schema, with things like null: false, and it's sent to the DB, rather than only enforced by model validations.
• Something like datasets, where you can express chaining easily (ie like dataset_module allows you to define methods that chain).
• Explicit relationships. Sequel having association_dataset as a dataset and association as an Array removes so much confusion around when queries are reified.

Because ORM problems often don't manifest until you're well past a tutorial, I'd love to hear from other Sequel fans of what Python ORMs they prefer. Or, if you've decided against an ORM entirely, what you did instead- I've looked at Python DB toolkits, and all of them have had pretty severe problems. I started to roll my own against psycopg2 but it was a lot of work so something off-the-shelf (and extensible) would be good.

[benkoshy]

I have a client that really wants their new backend in Python

What is compelling the client to choose: python vs another language?

In my experience, it's unusual for them to be that specific about implementation........unless there are some cogent reasons I'm missing.

Why not just use sequel rather than a python equivalent of sequel (if such a thing exists (?) - sorry but i can't help you here ), and convince the client of that?

It's not the answer you're looking for, but it might be the most pragmatic.

[rgalanakis]

Thanks for your thoughts, Ben. I would encourage you next time to assume folks asking a question have valid reasons for stated constraints, especially when they aren't in complete control of those constraints. For that reason it's not actually a pragmatic answer at all.

[HoneyryderChuck]

I have extensive experience in Python, but have never found an ORM that compares to Sequel (actually this is true in every other language I've used...).

I share that opinion. Everytime I'm forced to use some ORM for another language (and even ruby), I regret it.

I don't have as much extensive XP as you may have in python, but I've been doing plenty of work in it. I can recommend you SQLAlchemy, it's definitely the best python has to offer. Coming from sequel, you will hate it. Coming from db-based experience in ruby, you'll hate python even more (I mean, the dbapi common to all python db tools, the fact that it implicitly opens a transaction on first use even if you just want to SELECT... why...). But it's still a decent query builder; a tad verbose, but it does the trick. It supports model-less usage, even if less documented.

[rgalanakis]

Thanks. I am still hopeful SQLAlchemy is not the best Python has to offer 😛 Thanks for the heads up on the model-less usage.

And yes don't get me started on DBAPI. I have been trying to not to be too negative in the question 😆 It is so awful.

[rgalanakis]

Not sure how I missed this, but Sequel's eager loading, especially the tactical eager loading plugin, is a thing I've not seen elsewhere and I sorely miss in other ORMs (to the point I had to end up building it on top of DjangoORM to transparently fix N+1 performance problems on a legacy application)