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

   1 # -*- coding: iso-8859-1 -*-
   2 #Author: Igor Támara igor@tamarapatino.org
   3 #Date: 10/03/2006
   4 #No warranties.
   5 """
   6      MoinMoin - QueryDb macro
   7 
   8    PURPOSE : Show the result of some queries on a postgresql db.
   9 
  10    REQUISITES : Install the psycopg 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 : "[[QueryDb(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 [[QueryDb(SELECT count(*) from queries)]] 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, 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 = ["pages","psycopg"]
  40 from psycopg import *
  41 
  42 #Change this variable to suit your needs
  43 conexion="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(conexion,serialize=0)
  50     c=conn.cursor()
  51     try :
  52         c.execute(query.encode("iso8859-1"))
  53     except :
  54         raise
  55         return """<p>Query contains errors:
  56         <pre>
  57         %s
  58         </pre>
  59         """ % query
  60     rows=c.fetchall()
  61     if len(rows)==0 :
  62         return "<p>No results on the query"
  63     if len(c.description)==1 and len(rows)==1 :
  64         return str(rows[0][0])
  65         
  66     a="""
  67     <table>
  68     <tr>
  69     """
  70     for i in c.description :
  71         a+="<th>%s</th>" % i[0].replace("_"," ")
  72     a+="""</tr>
  73     """
  74             
  75     for reg in rows :
  76         a+="""
  77         <tr>"""
  78         for j in reg :
  79             a+="<td>%s</td>" % j
  80         a+="</tr>"
  81     a+="""
  82     </table>"""
  83     conn.close()
  84     return a
  85     
  86 def execute(macro, args):
  87     if len(args)>10 :
  88         res=showquery(args)
  89     else :
  90         res=""
  91     return macro.formatter.rawHTML(res)