Re: [Zope-dev] Using db_connections from Zope products
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:
Connection Id
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 %s 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.
___
Zope-Dev maillist - [EMAIL PROTECTED]
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 )
Re: [zope-dev] Using db_connections from Zope products
In both products and External methods I do something like the following: def get_product(self, trans_id) : database = getattr(self, 'product_DA')# product_DA is the database res = database().query( # adaptor "select * from product_log where trans_id = '%s';"%(trans_id)) fields = res[0] results = res[1] This allows the database adaptor to pool connections and be setup from the zope management screens. --Bill Noon ___ Zope-Dev maillist - [EMAIL PROTECTED] 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 )
Re: [Zope-dev] Using db_connections from Zope products
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 ___ Zope-Dev maillist - [EMAIL PROTECTED] 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 )
Re: [Zope-dev] Using db_connections from Zope products
> 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/ ___ Zope-Dev maillist - [EMAIL PROTECTED] 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 )
Re: [Zope-dev] Using db_connections from Zope products
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 ___ Zope-Dev maillist - [EMAIL PROTECTED] 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 )
Re: [Zope-dev] Using db_connections from Zope products
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 <[EMAIL PROTECTED]> 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_Method > > This would work from Script (Python), as well. > > Jim Penny > > > > > Thanks, > > Tom Brown > > [EMAIL PROTECTED] > > __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/ ___ Zope-Dev maillist - [EMAIL PROTECTED] 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 )
Re: [Zope-dev] Using db_connections from Zope products
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_Method This would work from Script (Python), as well. Jim Penny > > Thanks, > Tom Brown > [EMAIL PROTECTED] > > __ > Do You Yahoo!? > Get personalized email addresses from Yahoo! Mail > http://personal.mail.yahoo.com/ > > ___ > Zope-Dev maillist - [EMAIL PROTECTED] > 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 ) > ___ Zope-Dev maillist - [EMAIL PROTECTED] 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 )
[Zope-dev] 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 [EMAIL PROTECTED] __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/ ___ Zope-Dev maillist - [EMAIL PROTECTED] 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 )
