[Tutor] Building an SQL query

[Danny Yoo]

On Fri, 3 Jun 2005, Gabriel Farrell wrote:

> def sqlNice(valueList):
>     count = 1
>     y = '('
>     for x in valueList:
>         x = x.replace("'", "''")
>         y = y + "'" + x + "'"
>         if count < len(valueList):
>             y = y + ', '
>         count = count + 1
>     y = y + ')'
>     y = y.replace("'NULL'", 'NULL')
>     return y
>
> Does anyone see any major stumbling blocks in that?


Hi Gabriel,

Hmmm... there's got be something already in PyGreSQL that does most of
that, and if there is, let's avoid reinventing the wheel.  Let me check...
ok, there is an undocumented function in pg.py that does the individual
value quotation in pgdb._quote().


It looks like you might be able to get away with:

######
def sqlNice(valueList):
    quotedValues = [str(pgdb._quote(x)) for x in valueList]
    return '(' + ','.join(quotedValues) + ')'
######


That being said, I feel nervous about advocating using an underscored
function, since that's a hint that it's not a part of the interface to the
pydb module.  Maybe you can contact the author and ask if '_quote()' could
be renamed to 'quote()', or at least to provide some kind of official
access to the quote function.


... Wait a minute.  According to the content of the _quote() function, it
handles tuples and lists properly:

### pgdb.py, from PyGreSQL-3.6.2 ###
def _quote(x):
    if isinstance(x, DateTime.DateTimeType):
        x = str(x)
    elif isinstance(x, unicode):
        x = x.encode( 'utf-8' )
    if isinstance(x, types.StringType):
        x = "'" + string.replace(
            string.replace(str(x), '\\', '\\\\'), "'", "''") + "'"
    elif isinstance(x, (types.IntType, types.LongType, types.FloatType)):
        pass
    elif x is None:
        x = 'NULL'
    elif isinstance(x, (types.ListType, types.TupleType)):
        x = '(%s)' % string.join(map(lambda x: str(_quote(x)), x), ',')
    elif hasattr(x, '__pg_repr__'):
        x = x.__pg_repr__()
    else:
        raise InterfaceError, 'do not know how to handle type %s' % type(x)
    return x
######


So you should be able to pass lists without any problems.  Can you show us
what some of your SQL execution statements have looked like?  You should
be able to do something like:

######
cursor.execute("select name from foo where id in %s",
               ([1, 2, 3, 4, 5])
######



Best of wishes!