MoinMoin: attachment:PgSQLQuery.py of MacroMarket/PgSQLQuery

   1 """
   2 PgSQLConnect.py -- Query a PostgreSQL database
   3 
   4 by Jörg Haustein <jchaustein -[AT]- gmx.de>, 2006
   5 Heavy modification of William Waites macros:
   6 SQLConnect, SQLQuery and SQLClose
   7 
   8 This program is Free Software and is released
   9 under the terms of the GNU General Public License.
  10 Please see http://www.gnu.org/licenses/gpl for
  11 the full text of the terms and conditions.
  12 
  13 In order to use this macro you need
  14 to have pgdb installed. (www.pygresql.org)
  15 
  16 You must create a file called 'moin_sql.py'
  17 in your python path (i.e. the root directory of your
  18 wiki). This file should contain definitions for 
  19 your database connections in the following format:
  20 
  21 mydb = {'host': 'fill in address:port (i.e. localhost:5432)',
  22         'database': 'name of the database',
  23 	'user' : 'username',
  24 	'password' : 'password for username'}
  25 
  26 The connection parameters all default to None
  27 if left out.
  28 
  29 This protects your database access information.
  30 NOTE: If you don't want other users to use your database
  31 connections, protect your page with ACLs!
  32 
  33 You can use this macro in your wiki 
  34 pages like this:
  35 
  36 [[PgSQLQuery(mydb, SELECT * FROM table)]]
  37 
  38 PgSQLQuery  will connect to the database and
  39 output the query results in simple text if it is
  40 one row and one column, otherwise in a table.
  41 
  42 NOTE: For security reasons, PgSQLQuery only allows
  43 "SELECT" statements as queries.
  44 """
  45 from MoinMoin import config
  46 from MoinMoin.parser import wiki
  47 from MoinMoin.parser.wiki import Parser
  48 import re,string, pgdb, cStringIO
  49 
  50 
  51 _arg_query = r',\s*(?P<query>[Ss][Ee][Ll][Ee][Cc][Tt].+)\s*$'
  52 _arg_pattern =r'^\s*(?P<db_conn>[^,]+)%s' % _arg_query
  53 
  54 def execute(macro, args):
  55     # check arguments
  56     args_re = re.compile(_arg_pattern)
  57     if args:
  58 	args=args_re.match(args.encode('utf-8'))
  59     if not args:
  60 	msg = 'Not enough arguments or query not a SELECT statement. Example: PgSQLQuery (mydb, SELECT * FROM table)'
  61 	return "%s%s%s" % (macro.formatter.sysmsg(1),
  62 			   macro.formatter.text(msg),
  63 			   macro.formatter.sysmsg(0))
  64 
  65     # get the connection string
  66     try:
  67         moin_sql = __import__("moin_sql", {}, {}, None)
  68     except ImportError, message:
  69 	msg = 'Connection definition file not found.'
  70 	return "%s%s%s" % (macro.formatter.sysmsg(1),
  71 			   macro.formatter.text(msg),
  72 			   macro.formatter.sysmsg(0))
  73     try:
  74         conn_def = getattr(moin_sql, args.group('db_conn'))
  75     except AttributeError, message:
  76 	msg = 'Connection name not found in connection definition file.'
  77 	return "%s%s%s" % (macro.formatter.sysmsg(1),
  78 			   macro.formatter.text(msg),
  79 			   macro.formatter.sysmsg(0))
  80 
  81     # connect to database
  82     try:
  83         db = pgdb.connect(host=conn_def.get('host'),
  84 			  database=conn_def.get('database'),
  85 			  user=conn_def.get('user'),
  86 			  password=conn_def.get('password'))
  87 	cursor = db.cursor()
  88     except Exception, message:
  89 	msg = 'Database connection failed: %s.' % message
  90 	return "%s%s%s" % (macro.formatter.sysmsg(1),
  91 			   macro.formatter.text(msg),
  92 			   macro.formatter.sysmsg(0))
  93     
  94     # set client encoding to unicode, so we can always assume returned values to be utf-8
  95     # this will only fail if the database encoding is 'MULE_INTERNAL'
  96     try:
  97         cursor. execute("SET client_encoding TO 'UNICODE'")
  98     except Exception, message:
  99 	msg = 'Set encoding to UNICODE failed: %s' % message
 100 	return "%s%s%s" % (macro.formatter.sysmsg(1),
 101 			   macro.formatter.text(msg),
 102 			   macro.formatter.sysmsg(0))
 103     
 104     # execute query
 105     try:
 106 	cursor.execute(args.group('query'))
 107         rows = cursor.fetchall()
 108     except Exception, message:
 109 	msg = 'Query failed: %s' % message
 110 	return "%s%s%s" % (macro.formatter.sysmsg(1),
 111 			   macro.formatter.text(msg),
 112 			   macro.formatter.sysmsg(0))
 113 
 114     # simply leave quietly if there was no return
 115     if cursor.rowcount==0:
 116         cursor.close()
 117 	db.close()
 118 	return ""
 119 
 120     # just output result if only one value was queried
 121     if len(rows)==1 and len(rows[0])==1:
 122         cursor.close()
 123 	db.close()
 124 	text=rows[0][0] or ''
 125 	return macro.request.redirectedOutput(Parser(text.decode('utf-8'),macro.request).format,macro.formatter)
 126 
 127     # output a whole table otherwise
 128     table=[]
 129     table.append(macro.formatter.table(1))
 130     table.append(macro.formatter.table_row(1))
 131 
 132     for header in cursor.description:
 133         table.append(macro.formatter.table_cell(1))
 134         table.append(macro.formatter.strong(1))
 135 	text=header[0] or ''
 136         text = macro.request.redirectedOutput(Parser(text.decode('utf-8'),macro.request).format,macro.formatter)
 137         table.append(text)
 138         table.append(macro.formatter.strong(0))
 139         table.append(macro.formatter.table_cell(0))
 140         
 141     table.append(macro.formatter.table_row(0))
 142     for row in rows:
 143         table.append(macro.formatter.table_row(1))
 144 	for col in row:
 145             table.append(macro.formatter.table_cell(1))
 146             if col:
 147                 text = macro.request.redirectedOutput(Parser(col.decode('utf-8'),macro.request).format,macro.formatter)
 148                 table.append(text)
 149             table.append(macro.formatter.table_cell(0))
 150         table.append(macro.formatter.table_row(0))
 151 
 152     table.append(macro.formatter.table(0))
 153     cursor.close()
 154     db.close()
 155     return ''.join(table)