Re: [Zope] ZSQLMethodFile?
I usually just create ZSQL Methods as attributes of my (product) class:
import Acquisition from Products.ZSQLMethods.SQL import SQL
class mySQLClass(Acquisition.Implicit): # We need Acquisition in order to find our db connection
# a database connection with this id must exist! dbconnection = "the_db_connection"
_sql_null = "select 1 as one" zsql_null = SQL('null', 'A dumb zsql method for testing purposes', dbconnection, '', _sql_null, )
An instance of "mySQLClass" then could call self.zsql_null just like a standard ZSQL Method
I'm not sure I understand. Suppose my sql query string looks like this: sqlstatement='''<params>admin_login_name admin_password</params> SELECT * FROM service_provider WHERE <dtml-let admin_login_name="_.string.upper(admin_login_name)"> UPPER(admin_login_name) = <dtml-sqlvar admin_login_name type="string"> </dtml-let> AND admin_password = <dtml-sqlvar admin_password type="string">''' How do I then pass the keyword arguments?
If I do some os.listdir(...) on my files, I'll be able to hack-parse the parameters and create the methods on the fly with parameters. Added benefit is that I'll be able to use arguments instead of only keyword arguments.
I THINK you still have to pass keyword args (but I could be mistaken)
IF I wrap them are class methods, will it correctly look like this: def Mysqlstuff(Aq...): def zsql_method(self, name, age): return SQL(..., ..., ...)(name=name, age=age) Or do I need anything else? Thanks again
Thank you
No problem!
Peter Bengtsson wrote:
I usually just create ZSQL Methods as attributes of my (product) class:
import Acquisition from Products.ZSQLMethods.SQL import SQL
class mySQLClass(Acquisition.Implicit): # We need Acquisition in order to find our db connection
# a database connection with this id must exist! dbconnection = "the_db_connection"
_sql_null = "select 1 as one" zsql_null = SQL('null', 'A dumb zsql method for testing purposes', dbconnection, '', _sql_null, )
An instance of "mySQLClass" then could call self.zsql_null just like a standard ZSQL Method
I'm not sure I understand. Suppose my sql query string looks like this:
sqlstatement='''<params>admin_login_name admin_password</params>
Lose the <params /> part. You specify the parameters to the ZSQL method when instantiating the ZSQL object. The signature of the ZSQL __init__ looks like this: def __init__(self, id, title, connection_id, arguments, template): self is self id is the id of the zope object title is the title string of the zope object connection_id is the id of the database connection arguments is a string specifying the keyword args, eg. """admin_login_name:string admin_password:string""" template is your sql statement incl. dtml
SELECT * FROM service_provider WHERE <dtml-let admin_login_name="_.string.upper(admin_login_name)"> UPPER(admin_login_name) = <dtml-sqlvar admin_login_name type="string"> </dtml-let> AND admin_password = <dtml-sqlvar admin_password type="string">'''
How do I then pass the keyword arguments?
If I do some os.listdir(...) on my files, I'll be able to hack-parse the parameters and create the methods on the fly with parameters. Added benefit is that I'll be able to use arguments instead of only keyword arguments.
I THINK you still have to pass keyword args (but I could be mistaken)
IF I wrap them are class methods, will it correctly look like this:
def Mysqlstuff(Aq...): def zsql_method(self, name, age): return SQL(..., ..., ...)(name=name, age=age)
Or do I need anything else?
This should about work, but it would be more efficient if you stored the ZSQL object somewhere. Otherwise you'd have to reparse the dtml each time you call zsql_method() and won't be able to use zope caching. hth, peter.
I'm not sure I understand. Suppose my sql query string looks like this:
sqlstatement='''<params>admin_login_name admin_password</params>
Lose the <params /> part.
You specify the parameters to the ZSQL method when instantiating the ZSQL object.
The signature of the ZSQL __init__ looks like this:
def __init__(self, id, title, connection_id, arguments, template):
self is self
id is the id of the zope object
title is the title string of the zope object
connection_id is the id of the database connection
arguments is a string specifying the keyword args, eg. """admin_login_name:string admin_password:string"""
template is your sql statement incl. dtml
Ok. I understand.
SELECT * FROM service_provider WHERE <dtml-let admin_login_name="_.string.upper(admin_login_name)"> UPPER(admin_login_name) = <dtml-sqlvar admin_login_name type="string"> </dtml-let> AND admin_password = <dtml-sqlvar admin_password type="string">'''
How do I then pass the keyword arguments?
If I do some os.listdir(...) on my files, I'll be able to hack-parse the parameters and create the methods on the fly with parameters. Added benefit is that I'll be able to use arguments instead of only keyword arguments.
I THINK you still have to pass keyword args (but I could be mistaken)
IF I wrap them are class methods, will it correctly look like this:
def Mysqlstuff(Aq...): def zsql_method(self, name, age): return SQL(..., ..., ...)(name=name, age=age)
Or do I need anything else?
This should about work, but it would be more efficient if you stored the ZSQL object somewhere. Otherwise you'd have to reparse the dtml each time you call zsql_method() and won't be able to use zope caching.
You're right. So, if I don't wrap them like that but do define them as attributes, they don't have to reparse them? Then I can do this: class Mysqlstuff(Aq...): _login = SQL('null','dummy title', dbconnection, 'name:string pass:string', sqlfile.read()) def login(self, name, pass): ...do other things such as debug... return _login(name=name, pass=pass) What do you think about that?
Peter Bengtsson wrote:
You're right. So, if I don't wrap them like that but do define them as attributes, they don't have to reparse them? Then I can do this:
class Mysqlstuff(Aq...):
_login = SQL('null','dummy title', dbconnection, 'name:string pass:string', sqlfile.read()) def login(self, name, pass): ...do other things such as debug... return _login(name=name, pass=pass)
What do you think about that?
I'd guess that this has trouble finding the db connection. I have had your problem, too, and after a lot of looking around, I found this (maybe it's the right thing for you). http://www.zope.org/Members/jccooper/extzsql Since I didn't have a lot of SQL-connections, I didn't do it that way. My product looks like this: from Products.ZSQLMethods.SQL import SQLConnectionIDs class my_product(...) def __init__(self,container): wrapped_self = self.__of__(container) wrapped_self._addSQLMethods() def _addSQLMethods(self): self.manage_addFolder('SQLFolder') SQLFolder = getattr(self,'SQLFolder') addSQL = SQLFolder.manage_addProduct['ZSQLMethods'].manage_addZSQLMethod SQLConnectionID = SQLConnectionIDs(SQLFolder)[0][0] insertSQL = """INSERT INTO bla VALUES (<dtml-sqlvar ... ) """ inserSQLArguments = """name:string ...""" addSQL('insertSQL',...) ... def refresh(self): """ refresh all instance SQL_methods""" self.manage_delObjects('SQLFolder') self._addSQLMethods() This is surely not the most elegant way to do it, but it works. And it helps in development to be able to edit/test the sql methods directly. cheers, oliver
Oliver Bleutgen wrote:
Peter Bengtsson wrote:
You're right. So, if I don't wrap them like that but do define them as attributes, they don't have to reparse them? Then I can do this:
class Mysqlstuff(Aq...):
_login = SQL('null','dummy title', dbconnection, 'name:string pass:string', sqlfile.read()) def login(self, name, pass): ...do other things such as debug... return _login(name=name, pass=pass)
What do you think about that?
I'd guess that this has trouble finding the db connection.
No it finds the dbconnection all right, as long as the class is Acquisition capable Look into Shared.DC.ZRDB.DA.DA.__call__, around line 392
I have had your problem, too, and after a lot of looking around, I found this (maybe it's the right thing for you). http://www.zope.org/Members/jccooper/extzsql
Since I didn't have a lot of SQL-connections, I didn't do it that way. My product looks like this:
from Products.ZSQLMethods.SQL import SQLConnectionIDs
class my_product(...)
def __init__(self,container): wrapped_self = self.__of__(container) wrapped_self._addSQLMethods()
def _addSQLMethods(self): self.manage_addFolder('SQLFolder') SQLFolder = getattr(self,'SQLFolder') addSQL = SQLFolder.manage_addProduct['ZSQLMethods'].manage_addZSQLMethod SQLConnectionID = SQLConnectionIDs(SQLFolder)[0][0] insertSQL = """INSERT INTO bla VALUES (<dtml-sqlvar ... ) """ inserSQLArguments = """name:string ...""" addSQL('insertSQL',...) ...
def refresh(self): """ refresh all instance SQL_methods""" self.manage_delObjects('SQLFolder') self._addSQLMethods()
This is surely not the most elegant way to do it, but it works. And it helps in development to be able to edit/test the sql methods directly.
cheers, oliver
_______________________________________________ Zope maillist - Zope@zope.org http://mail.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://mail.zope.org/mailman/listinfo/zope-announce http://mail.zope.org/mailman/listinfo/zope-dev )
Peter Bengtsson wrote: [snip]
IF I wrap them are class methods, will it correctly look like this:
def Mysqlstuff(Aq...): def zsql_method(self, name, age): return SQL(..., ..., ...)(name=name, age=age)
Or do I need anything else?
This should about work, but it would be more efficient if you stored the ZSQL object somewhere. Otherwise you'd have to reparse the dtml each time you call zsql_method() and won't be able to use zope caching.
You're right. So, if I don't wrap them like that but do define them as attributes, they don't have to reparse them? Then I can do this:
class Mysqlstuff(Aq...):
_login = SQL('null','dummy title', dbconnection, 'name:string pass:string', sqlfile.read()) def login(self, name, pass): ...do other things such as debug... return _login(name=name, pass=pass)
What do you think about that?
Should work -- I use a somewhat different pattern though: class AllSQL(Acquisition.Implicit): zsql = SQL('id', 'title', 'dbconnection', 'arg:type', \ "select foo from bar where <dtml- ...") class ZopeObject(OFS.SimpleItem, ...): sql = AllSQL() def a_method(self): zsql = self.sql.zsql result_set = zsql(arg="spam") ... So I can keep all SQL in one place and I like to separate SQL from Python :-) cheers, peter.
participants (3)
-
Oliver Bleutgen -
Peter Bengtsson -
Peter Sabaini