Ian Bicking: the old part of his blog

SQLObject 2

Not coincidentally, just before PyCon I am announcing the start of a new project, SQLObject 2 and SQL-API. To be quite honest, I have had a hard time maintaining SQLObject, and have not given it the attention it should. I expect I am not the only one who created something that I later found difficult to work with; that I myself am responsible for all the problems, and made all the bad decisions, makes it worse -- for every problem I could go and fix it, but I can't fix them all as they come in (or I might just be opening up other problems), and as a result I don't fix any. Oleg Broytmann has done a good job giving it the project the more consistent attention that I have not, but when you are working on a project written by someone else it can be hard to make certain changes. This is my own attempt to make those changes I see as necessary.

This package will both more and less than SQLObject 0.x. It does less because several things have been moved to SQL-API, and ideally SQLObject will only be asked to do what it does well, and will not have to address all needs simply because it will be friendlier to other techniques working in the same application.

SQL-API itself is intended to be a neutral front-end to DB-API compliant databases, providing many of the details that are important to usability but not included in the DB-API. Ideally SQL-API can provide infrastructure for other database packages; if you are interested please contact me, or catch me at PyCon, or IRC, or email, or whatever. I want SQL-API to be as neutral as possible, but the only way to really ensure that is for other people to use it in very different ways than I am using it in SQLObject. If you just plain can't use SQL-API for some technical reason, then that's a bug (though you don't have to use all of it to make use of some of it).

So... there you go. Read the pages for the nitty-gritty (though I apologize that the SQL-API page is a little light yet). Looking forward to seeing some of y'all at PyCon.

Created 23 Feb '06


Great news!

I've been strugg..er, working with SQLObject for some time and glad to see its development continued.

# Max Ischenko

One of the aims of SQL API is to allow creation of DB connections. You might want to take a look at the code I posted to the DB sig mailing list recently. The aim is to achieve exactly that: http://mail.python.org/pipermail/db-sig/2006-February/004600.html http://mail.python.org/pipermail/db-sig/2006-January/004597.html


# Richard Moore

That is similar in motivation; the code I have uses setuptools to find databases, and the thing it finds is a little more complex than just a connection factory. It's actually described here: http://sqlobject.org/sqlapi/class-sqlapi.interfaces.IPlugin.html

# Ian Bicking

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.

# Jonathan Ellis

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 :) )

# mike bayer

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.

# mike bayer

Like the non-capcha, before I forget to say it.

As the guy who decided to use SQLObject as his relational mapper example in the pyCon database tutorial allow me to say "thank you" for giving me at least one hour's notice that everything was going to change :-)

Seriously, it takes a lot of courage to admit an already-popular architecture needs to change. Good luck with 2.0 and beyond.

# Steve Holden