MoinMoin: attachment:DataBase-1.0beta2.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 execute(macro, args):
  19     param_re = re.compile(r'^(?P<source>.+?),(?P<header>[^,"]*?)($|,\s*"(?P<query>.*)"\s*$)')
  20     match_object = re.match(param_re, args)
  21     if match_object:
  22         source = match_object.group('source')
  23         header = match_object.group('header')
  24         query = match_object.group('query')
  25     else:
  26         return error(macro, 'Parameter syntax error.')
  27     source = source.strip().lower()
  28     try:
  29         data_sources = macro.request.cfg.database_macro_sources
  30     except AttributeError:
  31         return error(macro, 'No data sources specified')
  32     if source not in data_sources:
  33         return error(macro, 'The data source "%s" specified is unknown' % source)
  34     if query and not query.lower().startswith('select'):
  35         return error(macro, 'Only "select" queries processed')
  36 
  37     # Determine the query
  38     if len(data_sources[source]) == 3:
  39         if query:
  40             return error(macro, 'The data source "%s" doesn\'t allow queries' % source)
  41         query = data_sources[source][2]
  42     if not query:
  43         return error(macro, 'Data source "%s" requires a query' % source)
  44 
  45     # Connect to data source
  46     connection_type = data_sources[source][0].lower()
  47     parameters = data_sources[source][1]
  48 
  49     if connection_type == 'odbc':
  50         import pyodbc
  51         connection_string = ''
  52         for (k, v) in parameters.iteritems():
  53             connection_string += '%s=%s;' % (k, v)
  54         connection = pyodbc.connect(connection_string)
  55 
  56     elif connection_type == 'mysql':
  57         import MySQLdb
  58         if 'port' in parameters:
  59             port = parameters['port']
  60         else:
  61             port = 3306
  62         connection = MySQLdb.connect(host = parameters['server'], port = port, user = parameters['uid'], passwd = parameters['pwd'],db = parameters['database'])
  63 
  64     elif connection_type == 'oracle':
  65         import cx_Oracle
  66         if 'port' in parameters:
  67             port = parameters['port']
  68         else:
  69             port = 1521
  70         connection = cx_Oracle.connect(parameters['uid'], parameters['pwd'], cx_Oracle.makedsn(parameters['server'], port, parameters['database']))
  71 
  72     else:
  73         return error(macro, 'Connection type of data source "%s" is incorrect. Please contact you system administrator.' % source)
  74 
  75     cursor = connection.cursor()
  76     cursor.execute(query)
  77 
  78     formatter = macro.request.formatter
  79     result = ['', formatter.table(True)]
  80 
  81     # Display header
  82     if header:
  83         fields = cursor.description
  84         result.append(formatter.table_row(True))
  85         for i in range(0, len(fields)):
  86             result.append(formatter.table_cell(True))
  87             result.append(formatter.strong(True))
  88             result.append(unicode(str(fields[i][0]), 'ISO-8859-1'))
  89             result.append(formatter.strong(False))
  90             result.append(formatter.table_cell(False))
  91         result.append(formatter.table_row(False))
  92 
  93     # Display rows
  94     for row in cursor:
  95         result.append(formatter.table_row(True))
  96         for i in range(0, len(row)):
  97             result.append(formatter.table_cell(True))
  98             result.append(unicode(str(row[i]), 'ISO-8859-1') )
  99             result.append(formatter.table_cell(False))
 100         result.append(formatter.table_row(False))
 101     result.append(formatter.table(False))
 102 
 103     connection.close()
 104     return u'\n'.join(result)
 105 
 106 
 107 def error(macro, message):
 108     formatter = macro.request.formatter
 109     try:
 110         display_keys = macro.request.cfg.database_macro_display_keys
 111     except AttributeError:
 112         display_keys = False
 113     if display_keys:
 114         source_comment = 'one of the following data sources: %s' % data_sources.keys()
 115     else:
 116         source_comment = 'data source key'
 117 
 118     result = [ '', formatter.preformatted(True) ]
 119     result.append("""
 120 DataBase macro error:
 121     %s
 122 
 123 Usage:
 124     << DataBase(source, headers) >> or
 125     << DataBase(source, header, "query") >>
 126     source   %s
 127     header   specifies if field names should be displayed as table header
 128     "query"  SQL select query (only if source allows/requires queries)
 129 """ % (message, source_comment))
 130     result.append(formatter.preformatted(False))
 131     return u'\n'.join(result)