sqlstring -- a library to build a SELECT statement

[Jason Stitt]

On Oct 19, 2005, at 9:18 PM, grunar at gmail.com wrote:

<snip>

> 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.

<snip>

First of all, I like this idea. I've been thinking about doing  
something similar but am stuck with SQLObject for the moment. The  
ability to construct complex expressions in pieces and then mix and  
match them would be killer.

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.

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

Also, you have to be really careful of operator precedence.

Have you ever used Pyparsing? Its use of a combination of classes and  
operator overloading works pretty well.

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.

>>>> 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...

Some functions might be more workable.

- Jason