Using db_connections from Zope products
I would like to make an SQL query directly from python code. Do I have to make a ZSQL Method dynamically, or is there another way without making the class database dependent (i.e. Gadfly, PoPy, etc), utilizing an existing db_con? Suppose I am using the ZPoPy DA and have established a database connection externally. How can I access this database and submit a query from my own class? Thanks, Tom Brown altafare@yahoo.com __________________________________________________ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/
On Wed, Jun 20, 2001 at 08:54:41AM -0700, Tom Brown wrote:
I would like to make an SQL query directly from python code. Do I have to make a ZSQL Method dynamically, or is there another way without making the class database dependent (i.e. Gadfly, PoPy, etc), utilizing an existing db_con? Suppose I am using the ZPoPy DA and have established a database connection externally. How can I access this database and submit a query from my own class?
You can use an established connection. If it is ZSQL based, you will have to deal with the results object. A good overview of the results object is http://www.zope.org/Members/spinwing/ZSQL_Results You might also look at the somewhat dated http://www.zope.org/Members/jpenny/Accessing_a_ZSQL_Method_from_an_External_... This would work from Script (Python), as well. Jim Penny
Thanks, Tom Brown altafare@yahoo.com
__________________________________________________ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/
_______________________________________________ Zope-Dev maillist - Zope-Dev@zope.org http://lists.zope.org/mailman/listinfo/zope-dev ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope )
Thanks fo the info! I had seen those How-Tos... but was really asking.. How can I use APIs directly instead of having an External ZSQL method. I have created a product and would like to be able to do sql queries in order to populate members of my class. For this example, I have the creation of a selection box as a member and the options will be generated by a select statement. I have gone through the product creation how to and was able to add, etc, my object. I can display html, make dtml on the fly, etc.. from an object, just wanted to do a query as well. .. would like to be able to do something like the Connection class without management interface, etc, or use pieces, just not sure how --- Jim Penny <jpenny@universal-fasteners.com> wrote:
On Wed, Jun 20, 2001 at 08:54:41AM -0700, Tom Brown wrote:
I would like to make an SQL query directly from python code. Do I have to make a ZSQL Method dynamically, or is there another way without making the class database dependent (i.e. Gadfly, PoPy, etc), utilizing an existing db_con? Suppose I am using the ZPoPy DA and have established a database connection externally.
How can I access this database and submit a query from my own class?
You can use an established connection.
If it is ZSQL based, you will have to deal with the results object.
A good overview of the results object is http://www.zope.org/Members/spinwing/ZSQL_Results
You might also look at the somewhat dated
http://www.zope.org/Members/jpenny/Accessing_a_ZSQL_Method_from_an_External_...
This would work from Script (Python), as well.
Jim Penny
Thanks, Tom Brown altafare@yahoo.com
__________________________________________________ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/
On Wed, Jun 20, 2001 at 09:56:23AM -0700, Tom Brown wrote:
Thanks fo the info! I had seen those How-Tos... but was really asking.. How can I use APIs directly instead of having an External ZSQL method. I have created a product and would like to be able to do sql queries in order to populate members of my class. For this example, I have the creation of a selection box as a member and the options will be generated by a select statement.
I have gone through the product creation how to and was able to add, etc, my object. I can display html, make dtml on the fly, etc.. from an object, just wanted to do a query as well. .. would like to be able to do something like the Connection class without management interface, etc, or use pieces, just not sure how
You want to recreate all of the machinery of ZSQL methods yourself? Well, actually it can be done if your database supports some reasonable API, like the python DB-SIG DB-API 2.0. But you are creating a fair amount of work for yourself. Just use a ZSQL method and be happy. (It will probably make your site more secure if you use ZSQL, as well, as you will not have to worry as much about argument quoting). Jim
You want to recreate all of the machinery of ZSQL methods yourself?
No, I want to be able to intelligently use pieces that are already there.
Well, actually it can be done if your database supports some reasonable API, like the python DB-SIG DB-API 2.0.
I know it can be done, which is why I was asking for input
But you are creating a fair amount of work for yourself.
I'm fine with the work, but I believe there must be a way to do it that doesn't require tons. I could do it from scratch using pg module, etc.. but figured there must be something I could use already there
Just use a ZSQL method and be happy. (It will probably
I don't want to use ZSQL methods, or I want to be able to define them Dynamically. I see this sight as a product with componenets. Why would I want to maintain 20 ZSQL methods when I could merely pass a couple of arguments such as DB name, which would be instantiated by a manage_addProduct form. I guess I could just trick it and pass everything (the whole select) into the one method as a var, but if I can do that I should be able to do it directly.
make your site more secure if you use ZSQL, as well, as you will not have to worry as much about argument quoting).
As far as argument quoting, why would there be any? I'm using a class member to take care of querying for me, why would I be passing arguments to it? Any arguments would have been dynamically created based on the class instantiation. There are database hooks for user administration, etc... all I was asking is for a good example of how to do it... I was trying to adhere to the principles used in designing ZClasses, make it look simple... tons of dtml and "external python methods" mixed in with 15-20 ZSQL methods just didn't seem to be simple when it could be made into a product... __________________________________________________ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/
Tom Brown writes:
I would like to make an SQL query directly from python code.... Suppose I am using the ZPoPy DA and have established a database connection externally. Database connections expose an interface to ZSQL methods, that you can use directly from your Python code.
I do not know of a documentation of this interface. However, when you look at Shared.DC.ZRDB.DA.DA (this is the Z SQL Method implementation, despite it name "Database Adapter"), you should easily find the few methods, you may need. Dieter
On 20 Jun 2001 11:25:01 -0500, Tom Brown wrote:
I would like to make an SQL query directly from python code. Do I have to make a ZSQL Method dynamically, or is there another way without making the class database dependent (i.e. Gadfly, PoPy, etc), utilizing an existing db_con? Suppose I am using the ZPoPy DA and have established a database connection externally. How can I access this database and submit a query from my own class?
I have a product which queries a specified database table from the Product Class and both executes queries entirely in Python, and builds ZSQL methods - populating them with std queries that can then be customized. You can directly use the database connection_id, but you will probably want to insert : import pdb; pdb.set_trace() in your class and use the debugger as you go. (I'll throw some probably bad code at you here - caveat emptor) To start out, somewhere you will want to be able to specify the connection id to use, such as: <TR> <TD ALIGN="LEFT" VALIGN="TOP"> <EM><STRONG>Connection Id</STRONG></EM> </TD> <TD ALIGN="LEFT" VALIGN="TOP"> <SELECT NAME="connection_id"> <dtml-in SQLConnectionIDs> <OPTION VALUE="&dtml-sequence-item;"> <dtml-var sequence-key></OPTION> </dtml-in> </SELECT> </TD> </TR> and in your class, somewhere, something like this: if REQUEST and REQUEST.has_key('connection_id'): self.connection_id = REQUEST['connection_id'] To create the zsql methods, I first have to obtain some info: def dbquery_handle(obj, connection_id): """Find and return the Zope database connector query method """ database_type = '' # Locate the Database Connector try: dbc=getattr(obj, connection_id) database_type = dbc.database_type except AttributeError: raise AttributeError, ( "The database connection <em>%s</em> cannot be found." % (connection_id)) # Prepare the Database Connector for a query try: DB__=dbc() except: raise 'Database Error', ( '%s is not connected to a database' % connection_id) # Return the query method return database_type, DB__.query # There's got to be a more universal way to do this, but I don't # know what it is def tableexists(dbtype, dbq, tablename): """Query the database to see if the table exists""" table_exists = [] if dbtype == 'MySQL': try: table_show_query = 'SHOW TABLES LIKE "%s"' meta, table_exists = dbq(table_show_query % tablename, 1) return table_exists except : pass elif dbtype == 'Sybase': try: table_show_query = "SELECT name FROM sysobjects \ WHERE id = object_id('%s')" meta, table_exists = dbq(table_show_query % tablename, 1) return table_exists except: pass return table_exists Now.. # ZSQL Method creation def create_zsqlmethods(self, id, connection_id, properties, maketable=0): """Create a series of Zope SQLMethods for this table """ schema = [] tableschema = [] dbtype, dbquery = dbquery_handle(self, connection_id) table_exists = tableexists(dbtype, dbquery, id) ... determine table schema from whatever, and instantiate the 'create' SQL method: create = SQL('createTable', title='', connection_id=connection_id, arguments='', template=table_create_query %(id, vars)) self._setObject('createTable', create) ... and even create the SQL table if you need to: if not table_exists and maketable: self.createTable() etc. etc.
participants (4)
-
Dieter Maurer -
Jim Penny -
kent＠goathill.org -
Tom Brown