MoinMoin: attachment:MySQL2-1.0.py of ParserMarket/MySQL

   1 """
   2     MoinMoin - Parser for MySQL data
   3     
   4     Take care that a MySQL based database for source is assumed.
   5     For additional information performing these parser without any data given. 
   6 
   7     @copyright: 2004,2005 by Alexander Fischer <mail@fischer-alexander.net>
   8     @copyright: 2006 by Gouichi Iisaka <iisaka51 at hotmail dot com>
   9         Add error handling for procedure.
  10         Add HEADATTR, EVENATTR, ODDATTR.
  11             Attributes is depend on wiki engine.
  12             Currently follows attributes have been checked.
  13                 bgcolor, align, valign
  14     @license: GNU GPL, see http://www.gnu.org/licenses/gpl for details
  15 
  16 """
  17 
  18 import sys
  19 from MoinMoin import wikiutil, config
  20 import MySQLdb
  21 import re
  22 
  23 _re_dbh=re.compile("DBDATA (?P<DBDATA>.*)")
  24 _re_query=re.compile("QUERY (?P<QUERY>.*)")
  25 _re_headline=re.compile("HEADLINE (?P<HEADLINE>.*)")
  26 _re_headattr=re.compile("HEADATTR (?P<HEADATTR>.*)")
  27 _re_evenattr=re.compile("EVENATTR (?P<EVENATTR>.*)")
  28 _re_oddattr=re.compile("ODDATTR (?P<ODDATTR>.*)")
  29 _re_wikicol=re.compile("WIKICOLUMN (?P<WIKICOLUMN>.*)")
  30 _re_htmlcol=re.compile("HTMLCOLUMN (?P<HTMLCOLUMN>.*)")
  31 _re_mailcol=re.compile("MAILCOLUMN (?P<MAILCOLUMN>.*)")
  32 _re_hidden=re.compile("HIDDENCOLUMN (?P<HIDDENCOLUMN>.*)")
  33 
  34 _format_href='''
  35 <a class="external" href="%s">
  36 <img src="/wiki/rightsidebar/img/moin-www.png"
  37 alt="[www]" width="11" height="11">%s</a>'''
  38 _format_wikiref=' <a href="%s">%s</a>'
  39 _format_mailto='<a href="mailto:%s">%s</a>'
  40 
  41 Dependencies = ["Time"] # no cahce
  42 
  43 class Preference:
  44     def __init__(self):
  45         self.linebreak = ''
  46         self.headattrs={}
  47         self.evenattrs={}
  48         self.oddattrs={}
  49         self.dbh=''
  50         self.query=''
  51         self.header=''
  52         self.html=''
  53         self.wiki=''
  54         self.mail=''
  55         self.hidden=''
  56 
  57 class Parser:
  58     def __init__(self, raw, request, **kw):
  59         self.raw = raw
  60         self.request = request
  61 
  62     def format(self, formatter):
  63         self.request.write(process(self.request, formatter, self.raw.split('\n')))
  64 
  65 def process(request, formatter, lines):
  66     params = Preference()
  67     params.linebreak = request.formatter.linebreak(0) 
  68     outbuffer=[request.formatter.paragraph(0)]                                                   
  69     output=[]
  70 
  71     # parameter lines are not exist ----------------
  72     if not lines[1:]:
  73 	output.append(params.linebreak)
  74         output.append('Transfer connectiondata and query for correct output!')
  75 	output.append(params.linebreak)
  76         output.append('{{{#!MySQL')
  77 	output.append(params.linebreak)
  78         output.append('DBDATA host,database,[user],[password]')
  79 	output.append(params.linebreak)
  80         output.append('QUERY dbquery')
  81 	output.append(params.linebreak)
  82         output.append('[HEADLINE columnname1,columnname2,...,...]')
  83 	output.append(params.linebreak)
  84         output.append('[HEADATTR attr1=val[,attr2=val,...]')
  85 	output.append(params.linebreak)
  86         output.append('[EVENATTR attr1=val[,attr2=val,...]')
  87 	output.append(params.linebreak)
  88         output.append('[ODDATTR attr1=val[,attr2=val,...]')
  89 	output.append(params.linebreak)
  90         output.append('[HTMLCOLUMN columnnumber1,columnnumber2,...,...]')
  91 	output.append(params.linebreak)
  92         output.append('[WIKICOLUMN columnnumber1,columnnumber2,...,...]')
  93 	output.append(params.linebreak)
  94         output.append('[MAILCOLUMN columnnumber1,columnnumber2,...,...]')
  95 	output.append(params.linebreak)
  96         output.append('[HIDDENCOLUMN columnnumber1,columnnumber2,...,...]')
  97 	output.append(params.linebreak)
  98         output.append('}}}')
  99 	output.append(params.linebreak)
 100 	output.append(params.linebreak)
 101         output.append('Replace host, database, user, password, dbquery, columname, columnnumber with practical data!')
 102         output.append('<b>Take care for lower and upper case. Squared bracket = Optional data, Large letters = Syntax</b>')
 103   
 104     # section1 : parse parameter lines ----------------
 105     parameterlist=[] 
 106     for index in range(len(lines)):
 107         parameterlist.append(lines[index])
 108 
 109     for parameterstring in parameterlist : 
 110         match=_re_dbh.search(parameterstring)
 111         if match and match.groupdict().has_key('DBDATA'):
 112             params.dbh=match.group('DBDATA').split(',')
 113   
 114         match=_re_query.search(parameterstring)
 115         if match and match.groupdict().has_key('QUERY'):
 116             params.query=match.group('QUERY')
 117         
 118         match=_re_headattr.search(parameterstring)
 119         if match and match.groupdict().has_key('HEADATTR'):
 120             for attr in match.group('HEADATTR').split(','):
 121                 attrName, attrVal = attr.split('=')
 122                 try:
 123                     params.headattrs[attrName]=attrVal
 124                 except:
 125                     pass
 126         match=_re_evenattr.search(parameterstring)
 127         if match and match.groupdict().has_key('EVENATTR'):
 128             for attr in match.group('EVENATTR').split(','):
 129                 attrName, attrVal = attr.split('=')
 130                 try:
 131                     params.evenattrs[attrName]=attrVal
 132                 except:
 133                     pass
 134         match=_re_oddattr.search(parameterstring)
 135         if match and match.groupdict().has_key('ODDATTR'):
 136             for attr in match.group('ODDATTR').split(','):
 137                 attrName, attrVal = attr.split('=')
 138                 try:
 139                     params.oddattrs[attrName]=attrVal
 140                 except:
 141                     pass
 142 
 143         match=_re_headline.search(parameterstring)
 144         if match and match.groupdict().has_key('HEADLINE'):
 145             params.header=match.group('HEADLINE').split(',')
 146         
 147         match=_re_wikicol.search(parameterstring)
 148         if match and match.groupdict().has_key('WIKICOLUMN'):
 149             params.wiki=match.group('WIKICOLUMN').split(',')
 150         
 151         match=_re_htmlcol.search(parameterstring)
 152         if match and match.groupdict().has_key('HTMLCOLUMN'):
 153             params.html=match.group('HTMLCOLUMN').split(',')
 154        
 155         match=_re_mailcol.search(parameterstring)
 156         if match and match.groupdict().has_key('MAILCOLUMN'):
 157             params.mail=match.group('MAILCOLUMN').split(',')
 158 
 159         match=_re_hidden.search(parameterstring)
 160         if match and match.groupdict().has_key('HIDDENCOLUMN'):
 161             params.hidden=match.group('HIDDENCOLUMN').split(',')
 162   
 163     # section1 : execute query and fetch all lines ----------------
 164     output.append(formatter.table(1)) 
 165       
 166     if params.header!='':
 167         output.append(formatter.table_row(1)) 
 168         for element in params.header:
 169                output.append(formatter.table_cell(1,params.headattrs))
 170                output.append(formatter.strong(1))
 171                output.append(formatter.text(element))
 172                output.append(formatter.strong(0)) 
 173                output.append(formatter.table_cell(0)) 
 174         output.append(formatter.table_row(0)) 
 175 
 176     dboutputs=''
 177     try:
 178         dbh=MySQLdb.connect(host=params.dbh[0], db=params.dbh[1],
 179 			user=params.dbh[2], passwd=params.dbh[3]) 
 180         c=dbh.cursor() 
 181         c.execute(params.query) 
 182         dboutputs=c.fetchall() 
 183         dbh.close() 
 184     except MySQLdb.OperationalError, msg:
 185         output.append(msg[1])
 186     except MySQLdb.InterfaceError, msg:
 187         output.append(msg[1])
 188     except MySQLdb.DatabaseError, msg:
 189         output.append(msg[1])
 190     except MySQLdb.DataError, msg:
 191         output.append(msg[1])
 192     except MySQLdb.InternalError, msg:
 193         output.append(msg[1])
 194     except MySQLdb.ProgrammingError, msg:
 195         output.append(msg[1])
 196     except MySQLdb.NotSupportedError, msg:
 197         output.append(msg[1])
 198     except:
 199          output.append("Unknown Error while accessing database")
 200          output.append(params.linebreak)
 201 
 202 
 203     aRowNumber = 0
 204     aRowAttrs=[ params.evenattrs, params.oddattrs ]
 205     for aRow in dboutputs: 
 206         output.append(formatter.table_row(1)) 
 207         aColumnNumber=0 
 208         isHTML=0 
 209         for aCell in aRow: 
 210             aColumnNumber=aColumnNumber+1 
 211             element=unicode(str(aCell) , config.charset)
 212             if str(aColumnNumber) in params.hidden: 
 213                     conitnue
 214             if str(aColumnNumber) in params.html: 
 215                     isHTML=1 
 216                     element=_format_href % (str(aCell), str(aCell) )
 217             if str(aColumnNumber) in params.wiki: 
 218                     isHTML=1
 219                     element=_format_wikiref % (str(aCell), str(aCell) )
 220             if str(aColumnNumber) in params.mail: 
 221                     isHTML=1
 222                     element=_format_mailto % (str(aCell),str(aCell))
 223   
 224             if isHTML==1:  
 225                 output.append(formatter.table_cell(1,aRowAttrs[aRowNumber%2]))  
 226                 output.append(formatter.rawHTML(element))
 227                 output.append(formatter.table_cell(0))
 228                 isHTML=0
 229             else:
 230                 output.append(formatter.table_cell(1,aRowAttrs[aRowNumber%2]))
 231                 output.append(formatter.text(element))
 232                 output.append(formatter.table_cell(0))
 233         output.append(formatter.table_row(0)) 
 234         aRowNumber += 1
 235     output.append(formatter.table(0))
 236   
 237     outbuffer.extend(output)  
 238     return (''.join(outbuffer))