MoinMoin: attachment:QueryDb-1.9.py of MacroMarket/QueryPg

   1 # -*- coding: utf-8 -*-
   2 #Author: Igor Támara igor@tamarapatino.org
   3 #Date: 10/03/2006
   4 #No warranties.
   5 """
   6      MoinMoin - QueryPg macro
   7 
   8    PURPOSE : Show the result of some queries on a postgresql db.
   9 
  10    REQUISITES : Install the psycopg2 module.
  11 
  12    INPUTS : receives as a single argument the query.
  13 
  14    OUTPUT : If the query returns one column with one row, just outputs
  15    the result, if it returns many columns or many rows, it prints a table
  16    the headers of the tables will show spaces replacing underscores.
  17 
  18    EXAMPLES : "<<QueryPg(SELECT name FROM countries)>>" would fetch every
  19    name of the table country ;) and if there are results it will present
  20    the results in a table.
  21    "There are <<QueryPg(SELECT count(*) from countries)>> query records" will
  22    inline the result on your html code
  23 
  24    IMPROVEMENTS : If you plan to use multiple wikis or multiple databases,
  25    you could create a separate file with dictionaries of identifiers and
  26    string connections, as a first input you would receive the key of the
  27    dictionary that will let you connect to the desired database,
  28    the second argument would be the query.
  29 
  30    RECOMMENDATIONS :   Create a reading user on your database, people
  31    looking with ?action=raw could deduce your database schema, if you are not
  32    concerned about security don't bother.   Remember that anyone with write
  33    access to your wiki could do a nasty sql query to slow down you or D.O.S.
  34    attack your system.
  35 
  36    If you find any bugs, please let me know.
  37 """
  38 
  39 Dependencies = ['psycopg2']
  40 from psycopg2 import *
  41 
  42 #Change this variable to suit your needs
  43 connvars = 'dbname=flisol host=localhost user=igor password=superflisol'
  44 
  45 
  46 def showquery(query):
  47     """Pre: Gets a query
  48     Post: Shows a table on that query replacing on titles "_" by " " """
  49     conn = connect(connvars)
  50     conn.set_client_encoding('UTF-8')
  51     c = conn.cursor()
  52     try:
  53         c.execute(query)
  54     except:
  55         raise
  56         return """<p>Query contains errors:
  57         <pre>
  58         {0} 
  59         </pre>
  60         """.format(query)
  61     rows = c.fetchall()
  62     if len(rows) == 0:
  63         return u"<p>No results on the query"
  64     if len(c.description) == 1 and len(rows) == 1:
  65         return str(rows[0][0])
  66 
  67     res = u"""
  68     <table>
  69     <tr>
  70     """
  71     for title in c.description:
  72         res += u"<th>{0}</th>".format(title[0].replace(u"_", u" "))
  73     res += u"""</tr>
  74     """
  75 
  76     for reg in rows:
  77         res += u"""
  78         <tr>"""
  79         for row in reg:
  80             res += u"<td>{0}</td>".format(row.decode('utf-8'))
  81         res += u"</tr>"
  82     res += u"""
  83     </table>"""
  84     conn.close()
  85     return res
  86 
  87 
  88 def execute(macro, args):
  89     if len(args) > 10:
  90         res = showquery(args)
  91     else:
  92         res = u''
  93     return macro.formatter.rawHTML(res)