Peewee [1] is my tool of choice because of its convenience and flexibility combined. I can create pretty complex queries using its Query API, but also execute arbitrary queries when I know I need something a little more nonstandard. Plus the maintainer (Charles Leifer) is an awesome dude who has always been really helpful to his users.

[1] Peewee: https://github.com/coleifer/peewee

Thanks for the kind words!

That’s sort of like SQL Alchemy Core works[1]. The ORM stuff is all built on top of that and completely optional.

[1] http://docs.sqlalchemy.org/en/latest/core/tutorial.html

Unfortunately, no, that's not what I meant, sorry.

I can't give SQLAlchemy (Core or ORM) a string like "SELECT * FROM foo AS f LEFT JOIN bar AS b ON b.id = f.bar_id WHERE f.baz > %(baz)s" and then transform it, by, say, appending the LIMIT clause or adding extra WHERE condition. AFAIK, there's no way to provide a raw SQL string and then say something like `query.where("NOT f.fnord")` OR `query.limit(10)` and get the updated SQL.

With ORMs or non-object-mapping wrappers if I want transformations, I have to use their own language instead of SQL. I do, but don't really want to.

Or things have changed and this is what SA can do this nowadays? I'll be more than happy to learn that I'm wrong.

you know what the problem is there, that a. parsing your SQL string into a tokenized structure b. altering it based on your given instructions c. re-compiling it back into a string, all on top of an interpreted scripting language, and then d. sending it to a database so that the database can parse it a second time, is just so inefficient, for a particular API style that is arguably not even any better than just learning how to write Core expressions. Core and ORM expressions can be cached to their resultant SQL to save on the tokenized-structure->string step too.

there are certainly SQL parsers that can easily produce such tokenized structures and from a technical standpoint, your API is pretty simple to produce, with or without shallow or deep SQLAlchemy integrations. It's just there's not really any interest in such a system and it's never been requested.

You actually can do that with SA. See the example here: http://docs.sqlalchemy.org/en/latest/core/tutorial.html#spec...

If you specify the columns involved with your text query, then you get back a 'TextAsFrom' object, and you can apply other transformations to it like .where() or .unique().

At least you can do that by adding other SA objects.

I used to do exactly this kind of filtering, by building up by SA query, back in the day (about 2008) when I last used SA in anger.