[Zope] Calling SQL from Python products

[Johan Carlsson]

I'm "bounching" this thread back to the list :-)

Dario Lopez-Kästen wrote:
> Johan Carlsson wrote:
>>
>> I guess if your using ZSQL it's just easier to create ZSQLMethod 
>> object in the class?
>>
>> I just realized that I want ZSQL, to do escaping for instance...
>> Passing string doesn't feel secure if there's ttw data in it.
>>
> 
> I think that there are some advantages of your solution that perhaps can 
> be of use - and I think it would be interesting with some testing too.
> 
> Yes, you can create the ZSQL from inside the product (look at how 
> ExUserFodler does that for instance), and if you use FSbased ZSQL 
> methods it is even easier.

One problem that I find with ZSQL Methods is that I need to
delete the connection_id attribute from the ZSQLMethods
I create at class level:

class MyClass(BaseClass):
     connection_id="MyConnectionID"

     _zsql_meth=ZSQLMethod('','','', arguments, template)
     del _zsql_meth.connection_id
     #Now connection_id will be acquiried from MyClass

     #Well I noticed that I could override connection_id
     #by using a connection_hook

     def getConnectionId(self):
	return self.connection_id

     _zsql_meth=ZSQLMethod('','','', arguments, template)
     _zsql_meth.connection_hook ="getConnectionId"
     #Now connection_id will be looked up by calling getConnectionId
     #on MyClass.

Quite similare solutions really.

> You want to have ZSQL-merthods for the SQL-escaping, but also for the 
> dynamism it gives you. THe trick would be to render the ZSQL to a string 
> and then pass it on to your methods.
> 
> If you are lookin for speed in processing the results, (apologies if you 
> know this allready) then perhaps there is an easier way. i.e. in a 
> Script(Python) (can be used in Product code as well, this is just to 
> illustrate):
> 
> my_data_set = context.my_sql(param1='apa', ...).dictionaries()
> 
> my_data_set will be a list of dictionaries, and most of the values in 
> the dictionary will be standard Python types - standard python types are 
> not checked for security, which makes traversing my_data_set a really 
> fast operation.
> 
> If you use the
> 
> for a in my_data_set:
>   print a.col_1
> 
> or
> 
> <dtml-in my_data_set>
> <dtml-var col_1>
> </dtml-in>
> 
> patterns then col_1 will be a Zope object and as such must be checked by 
> security. In the worst case this will give you rows*cols security checks 
> just to display the result-set!
> 
> Was this the kind of speedup you where interested in?

I hadn't thought about speed, so thanks for the tips :-)
I'm using the Result object directly in Python as you describe,
but mostly because I don't want to display tre result.
I'm building a PluggableAuthenticationPlugin you see :-)

The speed-up was a nice plus :-)

Cheers,
Johan


-- 
Johan Carlsson          Tel: + 46 8 31 24 94
Colliberty              Mob: + 46 70 558 25 24
Torsgatan 72            Email: johanc at easypublisher.com
SE-113 37 STOCKHOLM