MoinMoin: attachment:MySQLOutput.py of MacroMarket/MySQLOutput

   1 #format python
   2 """
   3 MoinMoin macro to output table from MySql tables
   4 
   5 MySqlOutput.py
   6 sergio sergiorgiraldo at hotmail dot com
   7 
   8     I assume you have MySql for Python installed. If you haven't
   9     download from http://sourceforge.net/projects/mysql-python
  10 
  11     Usage:
  12         [[MySqlOutput(host|user|password|database|query)]]
  13 
  14     Note that this macro expects parameters to be separated by | (query can contain commas)
  15 
  16     Example : [[MySQLOutput(localhost|root|root|mysql|SELECT NAME, EXAMPLE FROM help_topic LIMIT 5)]]
  17 
  18     Demo : Try pasting the above line into a MoinMoin sandbox page.
  19 """
  20 
  21 import sys, MySQLdb
  22 
  23 def execute(macro, text):
  24 
  25     if text is None: # macro call without parameters
  26         return ''
  27     else:
  28         pOk, pHost, pUser, pPassword, pDatabase, pQuery = parseArgs(text)
  29         if not pOk:
  30             return ''
  31 
  32     db = MySQLdb.connect (host = pHost,user = pUser,passwd = pPassword,db = pDatabase)
  33 
  34     c = db.cursor()
  35 
  36     c.execute(pQuery)
  37 
  38     formatter = macro.request.formatter
  39 
  40     result = formatter.table(True)
  41 
  42     for l in c.fetchall():
  43         result += formatter.table_row(True)
  44         for i in range(0,len(l)):
  45             result += formatter.table_cell(True)
  46             result += unicode(str(l[i]), 'ISO-8859-1') #I speak portuguese so I use this encoding; adjust to your needs
  47             result += formatter.table_cell(False)
  48         result += formatter.table_row(False)
  49 
  50     result += formatter.table(False)
  51 
  52     db.close()
  53 
  54     return result
  55 
  56 def parseArgs(pArgs): #very simple parser
  57 
  58     Args = pArgs.split("|")
  59 
  60     if len(Args) <> 5:
  61         return False,"","","","",""
  62     else:
  63         if not Args[4].lower().startswith("select"):
  64             return False,"","","","",""
  65         else:
  66             return True,Args[0],Args[1],Args[2],Args[3],Args[4]