MoinMoin: attachment:SQL.py of MacroMarket/SQL

   1 """
   2 MoinMoin macro to display query results from an SQL database
   3 SQL.py
   4 
   5 by Tim Cera
   6 
   7 Based on macro/MySQLOutput.py by
   8 sergio rodrigues giraldo sergiorgiraldo at gmail dot com
   9 
  10     Install:
  11       Oracle prerequesites:
  12         * Oracle client libraries (http://www.oracle.com)
  13         * set '_oracle_home' variable below to ORACLE_HOME
  14         * cx_Oracle - http://www.cxtools.net/default.aspx?nav=cxorlb
  15           you have to compile it yourself, or to use the pre-compiled
  16           binaries you have to have control over the environment 
  17           variables within the web server environment.  I don't 
  18           have control over the server, but I think all you need to
  19           do is set LD_LIBRARY_PATH to $ORACLE_HOME/lib.
  20           When compiling cx_Oracle:
  21           1) set the environment variable FORCE_RPATH to anything
  22           2) python setup.py install
  23              cx_Oracle needs to find a couple of libraries and the
  24              only way it works is for cx_Oracle to be compiled with
  25              the run time paths to the libraries it needs.  The
  26              compilation flags to include the run time paths are 
  27              triggered by the FORCE_RPATH environment variable.
  28       MySQL:
  29         I don't know what the requirements are here, but I think
  30         everything is unchanged from MySQLOutput.py
  31 
  32     Usage:
  33         [[SQL(host|user|password|database|type|query)]]
  34 
  35          Note that this macro expects parameters to be separated by '|' 
  36          This is so the query can contain commas.
  37 
  38     Example : 
  39               [[SQL(localhost|root|root|mysql|mysql|
  40                 SELECT NAME, EXAMPLE FROM help_topic LIMIT 5)]]
  41 
  42     Demo : Try pasting the above line into a MoinMoin sandbox page.
  43 """
  44 
  45 # Add new databases here and in the if/elif statement later on.
  46 _database_types = ['mysql', 'oracle']
  47 
  48 # Need to set this to the ORACLE_HOME if using Oracle
  49 _oracle_home = '/beodata/software/relink_package_manager/vapp/oracle_home'
  50 
  51 
  52 
  53 def usage(num_args, text, err_text):
  54     return """
  55 <pre>
  56 SQL macro is called with the format:
  57         [[SQL(hostname|user|password|database_name|database_type|
  58               SELECT NAME, EXAMPLE FROM help_topic LIMIT 5)]]
  59 
  60 Note the use of the '|' symbol to separate arguments.  This allows commas
  61 to be used in the 'SELECT' SQL statement.
  62 
  63 You gave the following %i arguments:
  64  %s
  65 
  66 This macro supports the following database_type(s):
  67  %s
  68 
  69  Error: %s
  70 </pre>
  71 """ % (num_args, text, ' '.join(_database_types), err_text)
  72 
  73 
  74 
  75 def execute(macro, text):
  76 
  77     # Argument error checking.
  78     if text is None: # macro call without parameters
  79         return usage(0, ' ', 'no parameters')
  80     else:
  81         text_list = text.split('|')
  82         try:
  83             pHost, pUser, pPassword, pDatabase, pDatabaseType, pQuery = text_list
  84         except ValueError:
  85             return usage(len(text_list), text, 'incorrect number of parameters ')
  86     if not pQuery.lower().startswith('select'):
  87         return usage(len(text_list), text, 'macro will only process "select" queries')
  88 
  89     # Get the data.
  90     if pDatabaseType == 'mysql':
  91         import MySQLdb
  92         db = MySQLdb.connect (host = pHost,user = pUser,passwd = pPassword,db = pDatabase)
  93     elif pDatabaseType == 'oracle':
  94         import os
  95         os.environ['ORACLE_HOME'] = _oracle_home
  96         import cx_Oracle
  97         db = eval('cx_Oracle.connect("%s", "%s", cx_Oracle.makedsn("%s", 1521, "%s"))' % (pUser, pPassword, pHost, pDatabase))
  98     else:
  99         return usage(len(text_list), text, 'database_type is incorrect')
 100     c = db.cursor()
 101     c.execute(str(pQuery))
 102 
 103     # Format
 104     formatter = macro.request.formatter
 105     result = formatter.table(True)
 106     for l in c.fetchall():
 107         result += formatter.table_row(True)
 108         for i in range(0, len(l)):
 109             result += formatter.table_cell(True)
 110             result += unicode(str(l[i]), 'ISO-8859-1') 
 111             result += formatter.table_cell(False)
 112         result += formatter.table_row(False)
 113     result += formatter.table(False)
 114 
 115     # Cleanup
 116     db.close()
 117 
 118     # Display
 119     return result