sqlstring -- a library to build a SELECT statement

[grunar at gmail.com]

Jason Stitt wrote:
>
> I think some operator overloading, especially the obvious cases like
> ==, is cleaner than using only functions because it lets you order
> things normally. But some of the operator choices are non-intuitive.
> Personally, I would make something like 'alias' a function or class,
> rather than overloading XOR. Not sure about ** for where.
My strategy is to do both.  Have a "where" method that could be called
instead of ** (ie.  person.where(person.last_name=="smith"), but also
allow for the ** syntax.  After using it for a while, I'm finding the
** and ^ for alias very clear.  Alias function brings up the issue of
name-space (since table.alias could be a column if not a special
method.)  I'm assuming people don't use where, select, or other SQL key
words as column names, and if they do, they have a table["column"]
syntax to fall back on.  But I digress.  A method makes sense, though.

>
> Using // for 'in' looks really weird, too. It's too bad you can't
> overload Python's 'in' operator. (Can you? It seems to be hard-coded
> to iterate through an iterable and look for the value, rather than
> calling a private method like some other builtins do.)

// was a bit of a stretch.  I'd initially thought it for the "where"
clause, becuase it's lower precedence than ** (I think), and really
late at night // kind of looks like a W.  I decided against it because
it looks to close to a comment in some other languages.

Python "in" clause doesn't seem exploitable in any way--probably a good
thing.  I did add a "in_" method (name is arguable), which does the
same thing, also a not_in.

> Have you ever used Pyparsing? Its use of a combination of classes and
> operator overloading works pretty well.
I took a look at it.  Seems like a happy balance there--with the
overloading. <a
href="http://www.aminus.org/blogs/index.php/fumanchu/2005/08/11/where_dejavu_fits_in_the_orm_cosmos">Dejavu</a>
is another cool solution--using Lambda expressions.  But it goes into
scary bytecode stuff (though it's well contained).

>
> For SQL function calls, don't you also want to support stored
> procedures? In that case, you don't want pre-set magic functions so
> much as a function that takes a string parameter for a function name
> and then a list of function parameters.
This is what I had in mind (echo.py in the distribution).  The only
issue becomes knowing when to quote the parameters (is a string always
a string?).  I 've ended up quoting everything, and forcing the user to
supply table.column notation if they don't want it quoted.

>
> >>>> 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'
>
> See, this is where I'm not sure about operator precedence. If <<
> binds tighter than **, it works, because the << operator would make a
> 'join' object and then the ** could be interpreted as an 'on' clause.
> But if ** binds tighter you get an extra 'where' clause on the
> address table, and then a join...
>
There's a little dark magic going on with the precedence here.  The **
is higher precedence, so it happens first, and is applied to the
address table object, which is then joined into person (with the <<
operator), and pulling in the existing where on address.  This sounds
bad, but it allows for where clauses to trickle up through the python
expression, which is how I get a + b >> c to behave as expected.


Thanks for the positive feedback!

Runar