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:

  <EM><STRONG>Connection Id</STRONG></EM>
  <SELECT NAME="connection_id">
            <dtml-in SQLConnectionIDs>
              <OPTION VALUE="&dtml-sequence-item;">
              <dtml-var sequence-key></OPTION>

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
        dbc=getattr(obj, connection_id)
        database_type = dbc.database_type
    except AttributeError:
        raise AttributeError, (
            "The database connection <em>%s</em> cannot be found." %

    # 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':
            table_show_query = 'SHOW TABLES LIKE "%s"'
            meta, table_exists = dbq(table_show_query % tablename, 1)
            return table_exists
        except :

    elif dbtype == 'Sybase':
            table_show_query = "SELECT name FROM sysobjects \
                                WHERE id = object_id('%s')"
            meta, table_exists = dbq(table_show_query % tablename, 1)
            return table_exists

    return table_exists


    # 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:

etc. etc.

Zope-Dev maillist  -  [EMAIL PROTECTED]
**  No cross posts or HTML encoding!  **
(Related lists - 
 http://lists.zope.org/mailman/listinfo/zope )

Reply via email to