On Oct 26, 2005, at 2:47 AM, m wrote:

>
>
>> PTL is the best new idea from Quixote IMO. I was thinking if it made
>> sense to extend the idea to SQL. For example:
>>
>> class Accounts:
>>     database = 'customer'
>>
>>     def getAccountName [sql] (account_id):
>>         "SELECT name FROM accounts WHERE account_id=%s" % account_id
>>
>> Calling the method would not only create the SQL string but also
>> execute it and return the query results.
>>
>> This would enable very clean Python-oriented database access layers.
>
> it would be hideous
>
> ptl outputs plain text, in what form would expect the result set to be
> in ?
>

An iterator over dictionaries or tuples?

> You would also have to escape the output to take into account newlines
> and tabs etc. in the result set.
>
> As an unbounded mass of text, goodbye memory when 500Mb of plain text
> suddenly gets returned.
>
> What about escaping the parameters.
> In your example the string is auto wrapped in ' ' and how will the
> engine know when to escape % ?
>

This would be SQL-escaped similar to the way HTML is escaped within PTL
currently (i.e. an sqltext object similar to the present htmltext).

> demonstrated here :
>
> t = 'the amount is 20% over 5 years'
>
> "select a from b where c = '%s' or d like '%%%s%%'" % (t, t)
>

The above would fire the SQL:
select a from b where c = 'the amount is 20\% over 5 years' or d like
'%the amount is 20\% over 5 years%'" % (t, t)

This works just like strings, except the SQL characters (like single
quote, percent sign) will be escaped by the % operator on sqltext.

There is definitely one problem with clutter - since % is generally
used a more in SQL than in HTML. This wouldn't replace the DB API, just
be a layer on top. On second thoughts, it doesn't seem to add a whole
lot of functionality, though it does seem to promote a single way of
writing SQL in your code.

Shalabh