[Zope-dev] Using db_connections from Zope products

[Kent Polk]

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.