sqlstring -- a library to build a SELECT statement

[grunar at gmail.com]

After some thought on what I need in a Python ORM (multiple primary
keys, complex joins, case statements etc.), and after having built
these libraries for other un-named languages, I decided to start at the
bottom. What seems to plague many ORM systems is the syntactic
confusion and string-manipulation required to build the SQL Statements.
If you want to do a Left Outer Join, support nested functions, and a
nested conditional clause, you'd be building more of a string library
than an ORM. Let's not even mention syntactical differences between
databases, data types, and such.

My solution is sqlstring. A single-purpose library: to create SQL
statement objects. These objects (such as sqlstring.Select), represent
complex SQL Statements, but as Python objects. The benefit is that you
can, at run-time, "build" the statement pythonically, without
getting bogged down in String Manipulation. The theory is that once in
use, things that were complex (string magic) become simpler, and allow
the program to worry about higher-level issues.

An Example:

>>> import sqlstring
>>> model = sqlstring.TableFactory()
>>> print model.person
SELECT
person.*
FROM
[person] person
>>> person_smith = model.person \
** (model.person.first_name == "Smith")
>>> print person_smith
SELECT
person.*
FROM
[person] person
WHERE person.first_name = 'Smith'
>>> print person_smith("party_id", "first_name") \
< < model.address ** (\
model.address.party_id == model.person.party_id)
SELECT
party_id party_id,
first_name first_name,
address.*
FROM
[person] person
LEFT OUTER JOIN [address] address ON
address.party_id = person.party_id
WHERE person.first_name = 'Smith'

Things can get much more interesting than this, including nested
sub-selects (anywhere), conditional groups (and/or groups nested using
& and |) and even CASE statements.

Some of this stuff has been around for a while (using "magic" objects
to build where clauses, etc.).  But I'm trying to take it all the
way--to a legit Select statement.

While still in the early stages, it does work with a great many sql
statements, as seen in the test suite.  Currently supported are CASE
statements, Nested conditional clauses, nested queries and most join
types. At this point, I'm interested in getting feedback from the
community on several fronts:

   1. The Operator Overload model. I've chosen to overload Python's
operators to give a short-hand syntax to most of the things you'd
want to do with a select statement. The rest are accessable via
methods. Currently ** is the "where" operator, // is the "in"
operator, % the "like" operator and ^ aliases columns. Other
overloads are as you'd expect- + / - * == all result in Expression
Objects that dish out the right SQL string. The question is, is the
"leap" in syntax to confusing? Is there a cleaner way to do this?
(Functions for example)
   2. How to best add further sql function support? Adding magic
callable objects to columns came to mind, but this has it's own set
of issues. I'm leaning towards a magic object in the sqlstring
module. For example:

      sqlstring.F.substring(0, 4, person.first_name)

      would result in: substring(0, 4, person.first_name). the F object
could be put in the local scope for short-hand.
   3. I'm undecided on how best to handle database specific
overwrites. I want this to be as easy as possible. I'm thinking about
subclassing Expressions with a naming scheme on the Sub-Class (such as
CaseExpression_oracle). Then the __init__ factory could dish out the
right version of the object based on the the requestor. This brings up
lots of questions, such as how to support multiple types of databases
at the same time.

      Eventually the library should include all of the basic SQL
Statements, including UPDATE, INSERT and CREATE statements. This is
mostly for completeness, though. SELECT statements tend to be the most
complex.

      The library can be downloaded at http://betur.net/download.php

      Any other thoughts or comments are very much appreciated