SQLAlchemy has this kind of separation -- http://sqlalchemy.org/docs/sqlconstruction.myt looks a lot like what I imagine SQL-API would look like. Of course, I've done a remarkably poor job of reading your mind at times, but it might be worth a look. :)
Also, take a look at Jonathan Lacour's sqlobject-ish wrapper for the SQLAlchemy ORM: http://cleverdevil.org/computing/35
As Olin Shivers wrote (in the introduction to http://www-static.cc.gatech.edu/~shivers/papers/sre.txt), open-source hackers tend to come up with solutions that solve 80% of a problem. Then someone else comes along and covers a different 80% of the same problem. This is the Python ORM situation right now. You are arguably the most influential active python blogger (and ORM author) out there. If it's not too late (and it looks like you got pretty deep into the code before announcing this, so I guess I understand if it is) to urge you to rethink balkanizing things further, please consider yourself so urged. :)
See you at PyCon.
There's certainly overlap with SQLObject 2/SQL-API and SQLAlchemy. Just like there's obviously overlap with SQLObject 0.x. There's always overlap; we just have to keep trying to refactor so that pieces fall out that don't require overlap. That's what I'm trying to find in SQL-API.
The major difference between the SQL expressions in SQL-API and the expressions in SQLAlchemy is that the SQL-API expressions are not created from a database. Instead they are totally abstract, and are just ultimately rendered for a specific database at the time of execution. It does make some things harder in terms of portability, but in my experience it is also very convenient to be able to use those expressions without having to be bound to a particular environment at that specific moment. You have to be bound to a database when you execute a query; I defer that requirement as long as possible with SQL-API and SQLObject 2. In part because I start building up expressions at module import time.
That said, it might be worth trying harder to pursue some commonalities with SQLAlchemy. I did bring the idea of SQL-API up to Michael Bayer early on before SQLAlchemy had really been made public. He didn't really jump at the idea, but then it was just an idea at the time, not code, and I didn't pursue it aggressively.
I do feel that SQL-API needs to be its own package with its own life, seperate from any higher-level ideas or code. I also think that those higher-level ideas that may or may not be supersets of each other are hard to work with from that perspective. That is, I'm not particularly comfortable phrasing SQLObject in terms of SQLAlchemy, though maybe that is more reasonable than it seems to me now. I don't know; I'm open to suggestions and changes. Code isn't final. And I'm certainly not trying to balkanize anything with this; quite the contrary really.# Ian Bicking
SQLAlchemy sql expressions are bound to an "engine" at creation time, but this is not any hard requirement, some sql expressions dont have an engine at all; you can send "None" for the engine just as easily. it was mostly just for convenience that a SQL expression has an engine at creation time, so you can just execute() it without specifying the engine each time.
When you want to compile the expression, thats when the engine is delivered; this engine instantiates a compiler object that is bound to a particular database's SQL grammar.
so SQLAlchemy sql expressions are totally standalone as well, though I perhaps didnt make that so clear in its construction. the same expression can be compiled over and over with different database engines to produce different results (though there are a few hacks in the oracle module right now that modify the original expression, those are on the list to be fixed).
also it didnt come across to me that you were thinking about SQL-API until I saw your blog post on it, at which point SQLAlchemy was already publically available via SVN. we are definitely experiencing some significant overlap in our work, but im not sure if thats really such a problem (better two code paths than butting heads over every small decision :) )
ok, I should correct myself, while the expressions are pretty much engine-agnostic, the table objects at the base of it are wired to a specific engine....which also is mostly so an individual Table instance can be a singleton against its name/schema name/database connection (sort of like a SQLObject class). there was the notion that the Table of different databases would have different behavior but this hasnt come to pass, except in the case of MySQL where you can specify the "table type", i.e. ISAM/InnoDB etc. if you want to make Table objects that are agnostic of any database there is a generic engine you can use; but that still binds the Table to a certain grammar.
So, I should probably think of a way to have not just SQL expressions but the Tables at the base of them to exist without a connection to any kind of lexical engine, since its close to that but not quite.