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

# sqlwrap.py
#
# Copyright 2003 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

"""DB-API wrappers

The python DB-API 2.0 provides a standard API for accessing SQL databases.
Unfortunately it has a few problems that make it hard to write portable
SQL using code:

  1. there is no portable way to access the exception classes; since
  they are put in the driver module you always have to know the module
  name throughout your code
 
  2. it allows for multiple different parameter styles, and each driver
  module only implements one of them.

This module implements some classes that make it easier to write
portable SQL using code. It does this by adding some wrappers around
DB-API which hide the incompatibilties:

  - it introduces database classes which always copy the exception class types
  into a connection object

  - it adds connection objects which can execute a SQL command and return
  a cursor with the results, with support for converting between different
  parameter styles
"""
__docformat__	= "epytext en"

import dhm.sql.utils

class Server:
	"""SQL server.

	This is the general SQL server wrapper; it has connect and login
	information for a database and can setup database connections
	when asked to.

	This is an abstract base class; please use a derived class which
	overloads the _connect() method with real database connection
	code.

	@ivar     user: username to use when connecting to the database
	@type     user: string
	@ivar password: password to use when connecting to the database
	@type password: string
	@ivar     host: hostname of database server
	@type     host: string
	"""
	def __init__(self, user=None, password=None, host=None):
		self.user=user
		self.password=password
		self.host=host
	

	def _connect(self, db):
		"""Connect to a database.

		This function connects to a database on a SQL server
		and returns the connection and the driver module
		used for the connection.

		@param db: database to connect to
		@type  db: string
		@return: connection object and driver module
		@rtype:  tuple with Connection instance and driver module
		"""
		raise NotImplementedError, "Abstract base class used"


	def __getitem__(self, key):
		return Connection(*self._connect(key))



class PsycoServer(Server):
	"""Postgres SQL server using psycopg.
	"""
	def _connect(self, db):
		import psycopg

		dbc=psycopg.connect("host=%s dbname=%s user=%s password=%s" %
				(self.host, db, self.user, self.password))

		return (dbc, psycopg)
		

class PostgresServer(Server):
	"""Postgres SQL server using psycopg.
	"""
	def _connect(self, db):
		import pyPgSQL.PgSQL

		dbc=pyPgSQL.PgSQL.connect(user=self.user,
				password=self.password,
				host=self.host, database=db,
				client_encoding="utf-8",
				unicode_results=True)

		return (dbc, pyPgSQL.PgSQL)
		

class MysqlServer(Server):
	"""MySQL SQL server.
	"""
	def _connect(self, db):
		import MySQLdb

		dbc=MySQLdb.connect(user=self.user, passwd=self.password,
			host=self.host, db=db)

		return (dbc, MySQLdb)

class MssqlServer(Server):
	"""MS-SQL server.
	"""
	def _connect(self, db):
		import MSSQL

		dbc=MSSQL.connect(self.host, self.user, self.password, db)

		return (dbc, MSSQL)


class SybaseServer(Server):
	"""Sybase server.
	"""
	def _connect(self, db):
		import Sybase

		dbc=Sybase.connect(self.host, self.user, self.password, db)

		return (dbc, Sybase)


class Connection:
	"""A database connection.

	This class can do automatic paramstyle conversion if desired; to
	do this simply change the paramstyle instance variable to the
	desires paramstyle. Please note that not all conversion types are
	implemented.

	@ivar       paramstyle: DB-API 2.0 paramerstyle expected
	@type       paramstyle: string
	@ivar driverparamstyle: DB-API 2.0 paramerstyle used by the db driver
	@type driverparamstyle: string
	@ivar              dbc: low-level connection object used internally
	@type              dbc: DB-API 2.0 connection object
	"""
	def __init__(self, dbc, module):
		"""Constructor

		@param    dbc: low-level connection object used internally
		@type     dbc: DB-API 2.0 connection object
		@param module: SQL driver module
		@type  module: python module
		"""
		self.dbc=dbc
		self.paramstyle=module.paramstyle
		self.driverparamstyle=module.paramstyle

		for error in [ "Warning", "Error", "InterfaceError",
				"DatabaseError", "DataError",
				"OperationalError", "IntegrityError",
				"InternalError", "ProgrammingError",
				"NotSupportedError" ]:
			setattr(self, error, getattr(module, error))


	def cursor(self):
		"""Return a DB-API cursor.
		"""

		return self.dbc.cursor()


	def execute(self, operation, params=(), paramstyle=None):
		"""Execute a SQL command.

		@param  operation: SQL command to execute
		@type   operation: string
		@param     params: parameters for SQL command
		@type      params: depends on paramstyle used
		@param paramstyle: DB-API parameter style to used if not using the
		current connection paramstyle
		@type  paramstyle: string
		@return: cursor with results
		@rtype: DB-API cursor instance
		"""
		if paramstyle==None:
			paramstyle=self.paramstyle
		cursor=self.dbc.cursor()
		if params:
			cursor.execute(*dhm.sql.utils.ParamStyleConvert(
					paramstyle, self.driverparamstyle,
					operation, params))
		else:
			cursor.execute(operation)

		return cursor


	def query(self, operation, params=(), paramstyle=None):
		"""Execute a SQL query and return the results.

		This is a convenience wrapper around the execute method
		which returns all results directly.

		@param  operation: SQL command to execute
		@type   operation: string
		@param     params: parameters for SQL command
		@type      params: depends on paramstyle used
		@param paramstyle: DB-API parameter style to used if not using the
		current connection paramstyle
		@type  paramstyle: string
		@return: list of query results
		@rtype: list
		"""

		c=self.execute(operation, params, paramstyle)
		try:
			res=c.fetchall()
		finally:
			c.close()

		return res


	def close(self):
		"""Close the connection to the database.
		"""
		self.dbc.close()
	

	def commit(self):
		"""Commit an open transaction."
		"""
		self.dbc.commit()
	

	def rollback(self):
		"""Roll back an open transaction."
		"""
		self.dbc.rollback()


class Transaction:
	"""Transaction object.

	This class makes using transactions a bit simpler: when you
	instantiate it it will open a transaction (which is a nop in
	python since the DB-API uses implicit transaction). When the
	class is garbage collected the transaction is rolled back,
	unless it is manually commited by calling commit(). Note that
	this relies on the reference counting garbage collection
	approach python uses, which will automatically delete the class
	instance when it goes out of scope. 

	@ivar _connection: database connection
	@type _connection: Connection instance or DB-API 2.0 complient
	connection object
	@ivar     _active: flag indicating if we are in an active transaction
	@type     _active: boolean

	"""
	def __init__(self, connection):
		"""Constructor.

		@param connection: database connection 
		@type  connection: Connection instance
		"""
		self._connection=connection
		self._active=1
	

	def __del__(self):
		assert(not self._active)
	

	def commit(self):
		"""Commit the transaction.
		"""
		self._connection.commit()
		self._active=0


	def rollback(self):
		"""Rollback the transaction.
		"""
		self._connection.rollback()
		self._active=0


def GetServer(driver, **kwargs):
	"""Server factory function.

	This is a convenience function that returns a Server class
	instance for a specific server. The supported types are:

	 - mysql: return a MysqlServer using the MySQLdb module
	 - postgres: return a PostgresServer using the psycopg module

	@param driver: SQL driver to use
	@type  driver: string
	@return: a server instance
	@type:   Server class instance
	"""
	if driver=="mysql":
		return MysqlServer(**kwargs)
	elif driver=="postgres":
		return PostgresServer(**kwargs)
	elif driver=="psyco":
		return PsycoServer(**kwargs)
	elif driver=="mssql":
		return MssqlServer(**kwargs)
	elif driver=="sybase":
		return SybaseServer(**kwargs)

	raise KeyError, "Unknown server type"