Why do this?

[Fredrik Lundh]

Lawrence D'Oliveiro wrote:

> Secondly, it's less convenient for cases where a dynamic query is being
> built. I previously gave the SQLStringList example. If that's not enough,
> here's another (simple) one:
>
> Conditions = []
> if Name != None :
>    Conditions.append("name = %s" % SQLString(Name))
> #end if
> if Address != None :
>    Conditions.append("address = %s" % SQLString(Address))
> #end if
> if PhoneNr != None :
>    Conditions.append("phone_nr = %s" % SQLString(PhoneNr))
> #end if
> Query = "select * from table"
> if len(Conditions) != 0 :
>    Query += " where " + " and ".join(Conditions)
> #end if

now that's some remarkably ugly Python code.  it's well-known that people can
write Fortran in all languages, but writing Visual Basic in Python?  (shudder)

here's a straight-forward and more efficient translation of that code:

    where = []; params = []
    if name is not None:
        where.append("name=?"); params.append(name)
    if address is not None:
        where.append("address=?"); params.append(address)
    if phone_nr is not None:
        where.append("phone_nr=?"); params.append(phone_nr)
    query = "select * from table"
    if where:
        query += " where " + " and ".join(where)
    cursor.execute(query, tuple(params))

which works for all data types and without any error-prone explicit quotation non-
sense, and which can be trivially refactored into

    where = []; params = []
    def append(column, value):
        if value is not None:
            where.append(column+"=?"); params.append(value)

    append("name", name)
    append("address", address)
    append("phone_nr", phone_nr)

    query = "select * from table"
    if where:
        query += " where %s " + " and ".join(where)

    cursor.execute(query, tuple(params))

which scales a lot better if when you're adding more fields, and which can be trivially
refactored into a full statement builder:

    def select(table, **kw):
        where = []; params = []
        for column, value in kw.items():
            if value is not None:
                where.append(column+"=?")
                params.append(value)
        query = "select * from " + table
        if where:
            query += " where " + " and ".join(where)
        return query, tuple(params)

    cursor.execute(*select("table", name=name, address=address, phone_nr=phone_nr))

where the "select" function can of course be put in a support library and reused every-
where you make simple selects; alternatively, you can wrap the whole thing into a some-
thing like

     class where_statement:
        def __init__(self, db):
            self.where = []
            self.params = []
            if db.paramstyle == "qmark":
                self.marker = "?"
            if db.paramstyle == "format":
                self.marker = "%s"
            else:
                raise NotImplementedError(
                    "unsupported parameter style: %r" % db.paramstyle
                    )
        def __setitem__(self, column, value):
            if value is not None:
                self.where.append(column+"="+self.marker)
                self.params.append(value)
        def __str__(self):
            if not self.where:
                return ""
            return " where " + " and ".join(self.where)
        def __iter__(self):
            return iter(self.params)

    where = where_statement(database)
    where["name"] = name
    where["address"] = address
    where["phone_nr"] = phone_nr
    cursor.execute("select * from table" + str(where), tuple(where))

which actually protests if you run it on a database that doesn't use the same para-
meter markers, and can be trivially extended to support more DB-API variants.

or, of course, refactored into something that's even easier to use for the actual
use cases.

this is Python, after all, and as we all know, "the joy of coding Python should be
in seeing short, concise, readable classes that express a lot of action in a small
amount of clear code - not in reams of trivial code that bores the reader to death".

</F>