python-dhm 0.6-3build1 / usr / share / pyshared / dhm / sql / utils.py

# dbutils.py
#
# Copyright 2001,2002,2003,2005 Wichert Akkerman <wichert@deephackmode.org>
#
# This file is free software; you can redistribute it and/or modify it
# under the terms of version 2 of the GNU General Public License as
# published by the Free Software Foundation.
#
# This program is distributed in the hope that it will be useful, but
# WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
# General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
# Calculate shared library dependencies

"""Utility functions to make using SQL databases easier.
"""

__docformat__	= "epytext en"

import mx.DateTime
import dhm.strtools

_type_dtd	= type(mx.DateTime.DateTimeDelta(0))
_type_dt	= type(mx.DateTime.DateTime(0))

def encode(buf):
	"""Encode a string.

	Escapes all characters in a string that have a special meaning in SQL
	so a string is safe to use in SQL expressions.

	@param buf: string to encode
	@type buf:  string
	@return:    encoded version of str
	@rtype:     string
	"""

	global _type_dt, _type_dtd
	from pyPgSQL import libpq

	if buf==None:
		return "NULL"
	elif type(buf)==_type_dt:
		return buf.strftime("'%Y-%m-%d %H:%M:%S'")
	elif type(buf)==_type_dtd:
		return buf.strftime("'%d %H:%M:%S'")
	else:
		if not isinstance(buf, str):
			buf=str(buf)
		return libpq.PgQuoteBytea(buf)


def decode(str):
	"""Decode a string.

	Removes all escaping from a string.

	@param str: string to decode
	@type str:  string
	@return:    decoded version of str
	@rtype:     string
	"""
	from pyPgSQL import libpq
	if str=="NULL":
		return None
	else:
		return libpq.PgUnQuoteBytea(str)


def QmarkToPyformat(query, params):
	"""Convert from qmark to pyformat parameter style.

	The python DB-API 2.0 specifies four different possible parameter
	styles that can be used by drivers. This function convers from the
	qmark style to pyformat style.

	@param  query: SQL query to transform
	@type   query: string
	@param params: arguments to query
	@type  params: sequence of strings
	@return: converted query and parameters
	@rtype:  tuple with the new command and a dictionary of arguments
	"""
	tokens=dhm.strtools.Tokenize(query, quotes="'")
	output=[]
	count=1
	for token in tokens:
		if token.endswith("?"):
			output.append(token[:-1] + "%%(param%d)s" % count)
			count+=1
		else:
			output.append(token)

	dict={}
	count=1
	for param in params:
		dict["param%d" % count]=param
		count+=1
	
	return (" ".join(output), dict)


def FormatToPyformat(query, params):
	"""Convert from format to pyformat parameter style.

	The python DB-API 2.0 specifies four different possible parameter
	styles that can be used by drivers. This function convers from the
	format style to pyformat style.

	@param  query: SQL query to transform
	@type   query: string
	@param params: arguments to query
	@type  params: sequence of strings
	@return: converted query and parameters
	@rtype:  tuple with the new command and a dictionary of arguments
	"""

	strborder=None
	newquery=""
	locquery=query
	locparams=params
	count=1
	dict={}

	while locquery:
		(char,dchar,locquery)=(locquery[0], locquery[:2], locquery[1:])

		if strborder:
			if char==strborder:
				strborder=None
				newquery+=char
			elif dchar=='\\'+strborder:
				newquery+=dchar
				locquery=locquery[1:]
			else:
				newquery+=char
		else:
			if char in [ '"', "'"]:
				strborder=char
				newquery+=char
			elif dchar=='%s':
				newquery+='%%(param%d)s' % count
				dict["param%d" % count]=locparams[0]
				locquery=locquery[1:]
				locparams=locparams[1:]
				count+=1
			else:
				newquery+=char

	return (newquery, dict)


def FormatToNamed(query, params):
	"""Convert from format to named parameter style.

	The python DB-API 2.0 specifies four different possible parameter
	styles that can be used by drivers. This function convers from the
	format style to pyformat style.

	@param  query: SQL query to transform
	@type   query: string
	@param params: arguments to query
	@type  params: sequence of strings
	@return: converted query and parameters
	@rtype:  tuple with the new command and a dictionary of arguments
	"""

	strborder=None
	newquery=""
	locquery=query
	locparams=params
	count=1
	dict={}

	while locquery:
		(char,dchar,locquery)=(locquery[0], locquery[:2], locquery[1:])

		if strborder:
			if char==strborder:
				strborder=None
				newquery+=char
			elif dchar=='\\'+strborder:
				newquery+=dchar
				locquery=locquery[1:]
			else:
				newquery+=char
		else:
			if char in [ '"', "'"]:
				strborder=char
				newquery+=char
			elif dchar=='%s':
				newquery+=':param%d:' % count
				dict["param%d" % count]=locparams[0]
				locquery=locquery[1:]
				locparams=locparams[1:]
				count+=1
			else:
				newquery+=char

	return (newquery, dict)
	
def PyformatToFormat(query, params):
	"""Convert from the pyformat to the format parameter style.

	The python DB-API 2.0 specifies four different possible parameter
	styles that can be used by drivers. This function convers from the
	format style to pyformat style.

	@param  query: SQL query to transform
	@type   query: string
	@param params: arguments to query
	@type  params: dictionary of arguments
	@return: converted query and parameters
	@rtype:  tuple with the new command and a string sequence of arguments
	"""
	import re

	matcher=re.compile("%\(([^)]+)\)s")
	keys=matcher.findall(query)

	return (matcher.sub("%s", query),
		map(lambda key,p=params: p[key], keys))


def __notimplemented(a, b):
	raise NotImplementedError, "Paramstyle conversion not implemented"


__FormatConvertors = {
	"qmark"	: { 
		"qmark"		: lambda a,b: (a,b),
		"numeric"	: __notimplemented,
		"named"		: __notimplemented,
		"format"	: __notimplemented,
		"pyformat"	: QmarkToPyformat,
		},
	"numeric"	: {
		"qmark"		: __notimplemented,
		"numeric"	: lambda a,b: (a,b),
		"named"		: __notimplemented,
		"format"	: __notimplemented,
		"pyformat"	: __notimplemented
		},
	"named"		: {
		"qmark"		: __notimplemented,
		"numeric"	: __notimplemented,
		"named"		: lambda a,b: (a,b),
		"format"	: __notimplemented,
		"pyformat"	: __notimplemented
		},
	"format"	: {
		"qmark"		: __notimplemented,
		"numeric"	: __notimplemented,
		"named"		: FormatToNamed,
		"format"	: lambda a,b: (a,b),
		"pyformat"	: FormatToPyformat,
		},
	"pyformat"	: {
		"qmark"		: __notimplemented,
		"numeric"	: __notimplemented,
		"named"		: __notimplemented,
		"format"	: PyformatToFormat,
		"pyformat"	: lambda a,b: (a,b),
		},
	}


def ParamStyleConvert(frm, to, query, params):
	"""Convert parameter style from one style to another.

	@param frm: style to convert from
	@type  frm: string
	@param  to: style to convert to
	@type   to: string
	@param  query: SQL query to transform
	@type   query: string
	@param params: arguments to query
	@type  params: dictionary of arguments
	@return: converted query and parameters
	@rtype:  tuple with the new command and a string sequence of arguments
	"""

	return __FormatConvertors[frm][to](query, params)