Jump to content

WikiXRay db module

From Meta, a Wikimedia project coordination wiki

Save this file as dbaccess.py in the working directory of WikiXRay.

#############################################
#      WikiXRay: Quantitative Analysis of Wikipedia language versions                       
#############################################
#                  http://wikixray.berlios.de                                              
#############################################
# Copyright (c) 2006-7 Universidad Rey Juan Carlos (Madrid, Spain)     
#############################################
# This program is free software. You can redistribute it and/or modify    
# it under the terms of the GNU General Public License as published by 
# the Free Software Foundation; either version 2 or later of the GPL.     
#############################################
# Author: Jose Felipe Ortega Soto                                                             

"""
Generic methods providing access to the MySQL database.

@see: quantAnalay_main

@authors: Jose Felipe Ortega
@organization: Grupo de Sistemas y Comunicaciones, Universidad Rey Juan Carlos
@copyright:    Universidad Rey Juan Carlos (Madrid, Spain)
@license:      GNU GPL version 2 or any later version
@contact:      jfelipe@gsyc.escet.urjc.es
"""

import MySQLdb

def get_Connection(ahost, aport, auser, apasswd, adb="test"):
	""" Create a connection object and create a cursor
	"""
	if adb:
		Con = MySQLdb.Connect(host=ahost, port=aport, user=auser, passwd=apasswd, db=adb)
	else:
		Con = MySQLdb.Connect(host=ahost, port=aport, user=auser, passwd=apasswd)
	Cursor = Con.cursor()
	return (Con, Cursor)

def query_SQL(cursor, select, tables, where='', order='', group='',create='', insert=''):
	"""
	Singleton method to access the database
	Input is the query (given by several parameters)
	Output is a row (of rows)

	@type  select: string 
	@param select: Fields to select
	@type  tables: string
	@param tables: Database tables involved in this query
	@type  where: string
	@param where: Where clause (optional; default: not used)
	@type  order: string
	@param order: Order clause (optional; default: not used)
	@type  group: string
	@param group: Group clause (optional; default: not used)
	"""
	
	if create:
		if order and where and group:
			query = "CREATE TABLE " + create + " SELECT " + select + " FROM " + tables + " WHERE " + where  + " GROUP BY " + group + " ORDER BY " + order
		elif order and where:
			query = "CREATE TABLE " + create + " SELECT " + select + " FROM " + tables + " WHERE " + where + " ORDER BY " + order
		elif order and group:
			query = "CREATE TABLE " + create + " SELECT " + select + " FROM " + tables + " GROUP BY " + group + " ORDER BY " + order		
		elif group and where:
			query = "CREATE TABLE " + create + " SELECT " + select + " FROM " + tables + " WHERE " + where + " GROUP BY " + group
		elif group:
			query = "CREATE TABLE " + create + " SELECT " + select + " FROM " + tables + " GROUP BY " + group
		elif order:
			query = "CREATE TABLE " + create + " SELECT " + select + " FROM " + tables + " ORDER BY " + order
		elif where:
			query = "CREATE TABLE " + create + " SELECT " + select + " FROM " + tables + " WHERE " + where
		else:
			query = "CREATE TABLE " + create + " SELECT " + select + " FROM " + tables
	elif insert:
		if order and where and group:
			query = "INSERT INTO " + insert + " SELECT " + select + " FROM " + tables + " WHERE " + where  + " GROUP BY " + group + " ORDER BY " + order
		elif order and where:
			query = "INSERT INTO " + insert + " SELECT " + select + " FROM " + tables + " WHERE " + where + " ORDER BY " + order
		elif order and group:
			query = "INSERT INTO " + insert + " SELECT " + select + " FROM " + tables + " GROUP BY " + group + " ORDER BY " + order		
		elif group and where:
			query = "INSERT INTO " + insert + " SELECT " + select + " FROM " + tables + " WHERE " + where + " GROUP BY " + group
		elif group:
			query = "INSERT INTO " + insert + " SELECT " + select + " FROM " + tables + " GROUP BY " + group
		elif order:
			query = "INSERT INTO " + insert + " SELECT " + select + " FROM " + tables + " ORDER BY " + order
		elif where:
			query = "INSERT INTO " + insert + " SELECT " + select + " FROM " + tables + " WHERE " + where
		else:
			query = "INSERT INTO " + insert + " SELECT " + select + " FROM " + tables
	elif order and where and group:
		query = "SELECT " + select + " FROM " + tables + " WHERE " + where  + " GROUP BY " + group + " ORDER BY " + order
	elif order and where:
		query = "SELECT " + select + " FROM " + tables + " WHERE " + where + " ORDER BY " + order
	elif order and group:
		query = "SELECT " + select + " FROM " + tables + " GROUP BY " + group + " ORDER BY " + order		
	elif group and where:
		query = "SELECT " + select + " FROM " + tables + " WHERE " + where + " GROUP BY " + group
	elif order:
		query = "SELECT " + select + " FROM " + tables + " ORDER BY " + order
	elif group:
		query = "SELECT " + select + " FROM " + tables + " GROUP BY " + group
	elif where:
		query = "SELECT " + select + " FROM " + tables + " WHERE " + where
	else:
		query = "SELECT " + select + " FROM " + tables
	print "\n\n"+query+"\n\n"

	# Make SQL string and execute it
	cursor.execute(query)

	# Fetch all results from the cursor into a sequence and close the connection
	results = cursor.fetchall()
	
	return results
	
def raw_query_SQL(cursor, query):
	cursor.execute(query)
	results = cursor.fetchall()
	return results

def createDB_SQL(cursor,language):
	query = "CREATE DATABASE " + language
	cursor.execute(query)
	results = cursor.fetchall()
	
	return results

def dropTab_SQL(cursor,tab):
	query = "DROP TABLE IF EXISTS "+tab
	cursor.execute(query)

def createView(cursor, view="test_view", columns='', query=''):
    if columns:
        view_query="CREATE OR REPLACE VIEW "+view+" ("+columns+") AS "+query
        print "\n\n"+view_query+"\n\n"
        cursor.execute(view_query)
    else:
        view_query="CREATE OR REPLACE VIEW "+view+" AS "+query
        print "\n\n"+view_query+"\n\n"
        cursor.execute(view_query)

def dropView(cursor, view="test_view"):
    query = "DROP VIEW IF EXISTS "+view
    cursor.execute(query)

def close_Connection (con):
	#Cerramos conexion a BDatos	
	con.close()

Why is this not simply in subversion? Guaka 15:04, 5 November 2007 (UTC)[reply]