MoinMoin: attachment:DataBase-1.0beta.py of MacroMarket/DataBase

   1 """
   2     DataBase.py - MoinMoin macro to display query results from databases
   3     
   4     @copyright: 2007 Wolfgang Fischer
   5     @license: GNU GPL, see COPYING for details.
   6 
   7     based on SQL.py by Tim Cera
   8 
   9     Information on prerequisites, installation an usage can be found at
  10         http://moinmo.in/MacroMarket/DataBase
  11 
  12 """
  13 import re
  14 
  15 Dependencies = ['External Data']  # TODO: what do comparable macros use?
  16 
  17 
  18 def error(macro, message):
  19     try:
  20         display_keys = macro.request.cfg.database_macro_display_keys
  21     except AttributeError:
  22         display_keys = False
  23     if display_keys:
  24         source_comment = 'one of the following data sources: %s' % data_sources.keys()
  25     else:
  26         source_comment = 'data source key'
  27     return """
  28 <pre>
  29 DataBase macro error:
  30     %s
  31 
  32 Usage:
  33     << DataBase(source, header, "query") >> or
  34     << DataBase(source, headers) >>
  35     source   %s
  36     header   specifies if field names should be displayed as table header
  37     "query"  SQL select query (only if defined source allows/requires queries)
  38 </pre>
  39 """ % (message, source_comment)
  40 
  41 
  42 def execute(macro, args):
  43     param_re = re.compile(r'^(?P<source>.+?),(?P<header>[^,"]*?)($|,\s*"(?P<query>.*)"\s*$)')
  44     match_object = re.match(param_re, args)
  45     if match_object:
  46         source = match_object.group('source')
  47         header = match_object.group('header')
  48         query = match_object.group('query')
  49     else:
  50         return error(macro, 'Parameter syntax error.')
  51     source = source.strip().lower()
  52     try:
  53         data_sources = macro.request.cfg.database_macro_sources
  54     except AttributeError:
  55         return error(macro, 'No data sources specified')
  56     if source not in data_sources:
  57         return error(macro, 'The data source "%s" specified is unknown' % source)
  58     if query and not query.lower().startswith('select'):
  59         return error(macro, 'Only "select" queries processed')
  60 
  61     # Determine the query
  62     if len(data_sources[source]) == 3:
  63         if query:
  64             return error(macro, 'The data source "%s" doesn\'t allow queries' % source)
  65         query = data_sources[source][2]
  66     if not query:
  67         return error(macro, 'Data source "%s" requires a query' % source)
  68 
  69     # Connect to data source
  70     connection_type = data_sources[source][0].lower()
  71     parameters = data_sources[source][1]
  72 
  73     if connection_type == 'odbc':
  74         import pyodbc
  75         connection = pyodbc.connect(parameters)
  76     elif connection_type == 'mysql':
  77         import MySQLdb
  78         connection = MySQLdb.connect(host = parameters[0], user = parameters[1], passwd = parameters[2],db = parameters[3])
  79     elif connection_type == 'oracle':
  80         try:
  81             oracle_home = macro.request.cfg.database_macro_oracle_home
  82         except AttributeError:
  83             return error(macro, 'No oracle home directory specified in wikiconfig.py. Please contact you system administrator.')
  84         import os
  85         os.environ['ORACLE_HOME'] = _oracle_home
  86         import cx_Oracle
  87         connection = eval(parameters)
  88     else:
  89         return error(macro, 'Connection type of data source "%s" is incorrect. Please contact you system administrator.' % source)
  90 
  91     cursor = connection.cursor()
  92     cursor.execute(query)
  93 
  94     formatter = macro.request.formatter
  95     result = formatter.table(True)
  96 
  97     # Display header
  98     if header:
  99         fields = cursor.description
 100         result += formatter.table_row(True)
 101         for i in range(0, len(fields)):
 102             result += formatter.table_cell(True)
 103             result += formatter.strong(True)
 104             result += unicode(str(fields[i][0]), 'ISO-8859-1')
 105             result += formatter.strong(False)
 106             result += formatter.table_cell(False)
 107         result += formatter.table_row(False)
 108 
 109     # Display rows
 110     for row in cursor:
 111         result += formatter.table_row(True)
 112         for i in range(0, len(row)):
 113             result += formatter.table_cell(True)
 114             result += unicode(str(row[i]), 'ISO-8859-1') 
 115             result += formatter.table_cell(False)
 116         result += formatter.table_row(False)
 117     result += formatter.table(False)
 118 
 119     connection.close()
 120     return result