#format python
"""
MoinMoin macro to output table from MySql tables

MySqlOutput.py
sergio sergiorgiraldo at hotmail dot com

    I assume you have MySql for Python installed. If you haven't
    download from http://sourceforge.net/projects/mysql-python

    Usage:
        [[MySqlOutput(host|user|password|database|query)]]

    Note that this macro expects parameters to be separated by | (query can contain commas)

    Example : [[MySQLOutput(localhost|root|root|mysql|SELECT NAME, EXAMPLE FROM help_topic LIMIT 5)]]

    Demo : Try pasting the above line into a MoinMoin sandbox page.
"""

import sys, MySQLdb

def execute(macro, text):

    if text is None: # macro call without parameters
        return ''
    else:
        pOk, pHost, pUser, pPassword, pDatabase, pQuery = parseArgs(text)
        if not pOk:
            return ''

    db = MySQLdb.connect (host = pHost,user = pUser,passwd = pPassword,db = pDatabase)

    c = db.cursor()

    c.execute(pQuery)

    formatter = macro.request.formatter

    result = formatter.table(True)

    for l in c.fetchall():
        result += formatter.table_row(True)
        for i in range(0,len(l)):
            result += formatter.table_cell(True)
            result += unicode(str(l[i]), 'ISO-8859-1') #I speak portuguese so I use this encoding; adjust to your needs
            result += formatter.table_cell(False)
        result += formatter.table_row(False)

    result += formatter.table(False)

    db.close()

    return result

def parseArgs(pArgs): #very simple parser

    Args = pArgs.split("|")

    if len(Args) <> 5:
        return False,"","","","",""
    else:
        if not Args[4].lower().startswith("select"):
            return False,"","","","",""
        else:
            return True,Args[0],Args[1],Args[2],Args[3],Args[4]