Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Python > Why do this?

Reply
Thread Tools

Why do this?

 
 
Duncan Booth
Guest
Posts: n/a
 
      10-06-2006
Corrado Gioannini <(E-Mail Removed)> wrote:

> I often do things like this:
>
> sql = a_complex_select_sql % (id_foo, value_bar, ...)
> cursor.execute(sql)
>
> inside the body of a function (or a class method), where
> a_complex_select_sql is a string, containing several %s, %d ecc.,
> that is defined globally (or somewhere else in the class).
>

I hope you have a good reason why you don't do:

cursor.execute(a_complex_select_sql, (id_foo, value_bar, ...))

instead.
 
Reply With Quote
 
 
 
 
Corrado Gioannini
Guest
Posts: n/a
 
      10-06-2006
On Fri, Oct 06, 2006 at 10:09:14AM +0000, Duncan Booth wrote:
> I hope you have a good reason why you don't do:
>
> cursor.execute(a_complex_select_sql, (id_foo, value_bar, ...))
>
> instead.


hehe.
i was just trying to be didactic, simplifying the actual situation.
(anyway, sometimes i had to print, or store, the sql statement, or
to execute the same sql statement on different databases etc. etc..)

regards,
c.
--
no, sono sempre io, non mi cambierete quel che ho dentro
forse ho solo un'altra faccia
ho pił cicatrici di prima, sorrido un po' meno,
forse penso di pił.
(Kina)

 
Reply With Quote
 
 
 
 
Lawrence D'Oliveiro
Guest
Posts: n/a
 
      10-09-2006
In message <Xns98547173763B6duncanbooth@127.0.0.1>, Duncan Booth wrote:

> Corrado Gioannini <(E-Mail Removed)> wrote:
>
>> I often do things like this:
>>
>> sql = a_complex_select_sql % (id_foo, value_bar, ...)
>> cursor.execute(sql)
>>
>> inside the body of a function (or a class method), where
>> a_complex_select_sql is a string, containing several %s, %d ecc.,
>> that is defined globally (or somewhere else in the class).
>>

> I hope you have a good reason why you don't do:
>
> cursor.execute(a_complex_select_sql, (id_foo, value_bar, ...))
>
> instead.


I use the above when I can, when I can't I fall back on
<http://groups.google.co.nz/groups?selm=efc5b3$can$(E-Mail Removed)>,
<http://groups.google.co.nz/groups?selm=efhn5v$fia$(E-Mail Removed)>.
 
Reply With Quote
 
Duncan Booth
Guest
Posts: n/a
 
      10-09-2006
Lawrence D'Oliveiro <(E-Mail Removed)_zealand> wrote:

> I use the above when I can, when I can't I fall back on
><http://groups.google.co.nz/groups?selm=efc5b3$can$(E-Mail Removed)>,
><http://groups.google.co.nz/groups?selm=efhn5v$fia$(E-Mail Removed)>.


Yes, so you said, but you never came up with a convincing use case where
that function was better than using the parameterised queries.
 
Reply With Quote
 
Lawrence D'Oliveiro
Guest
Posts: n/a
 
      10-09-2006
In message <Xns985755C711158duncanbooth@127.0.0.1>, Duncan Booth wrote:

> Lawrence D'Oliveiro <(E-Mail Removed)_zealand> wrote:
>
>> I use the above when I can, when I can't I fall back on
>><http://groups.google.co.nz/groups?selm=efc5b3$can$(E-Mail Removed)>,
>><http://groups.google.co.nz/groups?selm=efhn5v$fia$(E-Mail Removed)>.

>
> Yes, so you said, but you never came up with a convincing use case where
> that function was better than using the parameterised queries.


First of all, the parametrization doesn't handle wildcards, as we discussed
previously.

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
 
Reply With Quote
 
Fredrik Lundh
Guest
Posts: n/a
 
      10-09-2006
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>



 
Reply With Quote
 
Steve Holden
Guest
Posts: n/a
 
      10-09-2006
Fredrik Lundh wrote:
> 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)

[...]
> 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".
>

I especially liked the #end if comments after the single guarded lines.

regards
Steve
--
Steve Holden +44 150 684 7255 +1 800 494 3119
Holden Web LLC/Ltd http://www.holdenweb.com
Skype: holdenweb http://holdenweb.blogspot.com
Recent Ramblings http://del.icio.us/steve.holden

 
Reply With Quote
 
Duncan Booth
Guest
Posts: n/a
 
      10-09-2006
Lawrence D'Oliveiro <(E-Mail Removed)_zealand> wrote:

>>> I use the above when I can, when I can't I fall back on
>>><http://groups.google.co.nz/groups?selm=efc5b3$can$(E-Mail Removed)>,
>>><http://groups.google.co.nz/groups?selm=efhn5v$fia$(E-Mail Removed)>.

>>
>> Yes, so you said, but you never came up with a convincing use case
>> where that function was better than using the parameterised queries.

>
> First of all, the parametrization doesn't handle wildcards, as we
> discussed previously.


No, your separate function for escaping wildcards is fine, and works in
conjunction with parameterized queries. It's the SQLString function you
never managed to justify.

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


See Frederik's excellent response on this point.
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
why why why why why Mr. SweatyFinger ASP .Net 4 12-21-2006 01:15 PM
findcontrol("PlaceHolderPrice") why why why why why why why why why why why Mr. SweatyFinger ASP .Net 2 12-02-2006 03:46 PM
Cisco 2611 and Cisco 1721 : Why , why , why ????? sam@nospam.org Cisco 10 05-01-2005 08:49 AM
Why, why, why??? =?Utf-8?B?VGltOjouLg==?= ASP .Net 6 01-27-2005 03:35 PM
Why Why Why You HAVE NO IDEA MCSE 31 04-24-2004 06:40 PM



Advertisments