Wrapper for pywin32's ActiveX ODBC handler

ActiveX ODBC Wrapper.py

# -*- coding: UTF-8 -*--

# *** ADO Connection ***
# -----------------------------------------------

"""
Code wrapping pywin32's code for ADO.Connection ActiveX object.
Used to make this god-awful excuse for ODBC support at least
partially bearable. At least until PyODBC or SQLAlchemy gets
its Python 3 port.
"""

import win32com.client

class ADODB:

	"""
	Base class for ODBC access. Can be invoked without parameters.
	If invoked without parameters, it sets up connection using default, hard-coded connection
	string. Otherwise, conn_string parameter is ODBC connection string.
	
	SAFE MODE:
	If only_safe parameter is set to True during instantiation, all queries will be wrapped in
	T-SQL transaction. When connection is in safe mode, each time a commit() method is used, it
	will, in addition to commiting transaction, open a new one. If the transaction is uncommited
	by the time connection is closed, it will be rollbacked. To manually rollback transaction, use
	rollback() method.
	
	CONTEXT MANAGER:
	Wrapper can be used as a context manager. When using ADODB as context manager, you might specify
	two additional attributes. supress_errors, if set to True, will make the context manager, as the name
	suggests, supress errors and exit quietly in case exception occurs. Second is context_functions.
	You can specify list of functions as this attribute. If so, when the context manager exits, it will
	execute this functions in order provided in the list. all of these functions need to follow pattern below:
	
	def function (self, exc_type, exc_value, traceback):
		... CODE ...
	"""
	
	# You can define your default connection string here
	# conn_string = ""
	
	def __init__ (self, conn_string = None, only_safe = False, supress_errors = False, context_functions = []):
	
		# Initiate internal variables
		self.__transaction__ = None
		self.__safemode__ = only_safe
		self.__supress_errors__ = supress_errors
		self.__context_functions__ = context_functions
		
		# Replace connection if any defined, use default otherwise
		if conn_string != None:
			self.conn_string = conn_string
		
		try:
			assert self.conn_string
		except AttributeError:
			raise AttributeError("ODBC Connection string is not defined")
		except AssertionError:
			raise AssertionError("ODBC Connection string is empty")
		
		self.__conn__ = win32com.client.Dispatch('ADODB.Connection')
		self.__conn__.Open(self.conn_string)
		
		# SAFE MODE
		if self.__safemode__:
			self.begin()
	
	def __del__ (self):
		
		try:
			assert self.__transaction__
			self.__conn__.RollbackTrans()
		except:
			pass
		
		self.__conn__.Close()
		self.__conn__ = None
	
	def __enter__ (self):
	
		# No additional code here
		return self
	
	def __exit__ (self, exc_type, exc_value, traceback):
	
		try:
			if len(self.__context_functions__) > 0:
				for function in self.__context_functions__:
					function(self, exc_type, exc_value, traceback)
		except:
			pass

		return self.__supress_errors__
	
	# Query Functions:
	# Shorthand functions for executing queries against SQL database.
		
	def begin (self):
	
		"""
		Initiates safe transaction. Each subsequent query to the database will be put
		into a batch, executed with commit() method or canceled with rollback() method.
		Useful for complex, multi-tier queries that we don't want to have done partially
		in case of error.
		Fails silently if in safe mode.
		"""
		
		if not self.__safemode__:
			self.__transaction__ = self.__conn__.BeginTrans()
	
	def commit (self):
	
		"""
		Commits transaction. Used either after transaction has been initiated by begin()
		method or in safe mode. If in safe mode, initiates another transaction immediately
		afterwards.
		Fails silently if there is no transaction to commit.
		"""
		
		try:
			assert self.__transaction__
			self.__conn__.CommitTrans()
			self.__transaction__ = None
		except AssertionError:
			pass
		
		if self.__safemode__:
			self.begin()
	
	def rollback (self):
	
		"""
		Rolls back transaction. Used to cancel any changes made since the transaction was initiated
		(begin() method or either since start, last commit() method or last rollback() method in safe mode).
		If in safe mode, initiates another transaction immediately.
		Fails silently if there is no transaction to commit.
		"""
		
		try:
			assert self.__transaction__
			self.__conn__.RollbackTrans()
			self.__transaction__ = None
		except AssertionError:
			pass
		
		if self.__safemode__:
			self.begin()
	
	def execute (self, sql_query):
	
		"""
		Executes given SQL query without returning any results.
		Handy for simple INSERTs or UPDATEs.
		"""
		
		self.__conn__.Execute(sql_query)
	
	def get (self, sql_query):
	
		"""
		Executes given SQL query and returns RecordSet object
		containing whatever result is returned from database.
		"""
		
		temp_rs_object = win32com.client.Dispatch('ADODB.RecordSet')
		temp_rs_object.Cursorlocation = 3
		temp_rs_object.Open(sql_query, self.__conn__, 1, 3)
		
		return temp_rs_object
	
	def select (self, sql_query, modifiers = None, sql_query_template = None):
	
		"""
		Executes SQL query (assumably some kind of SELECT), captures
		the result and returns list (rows of result) containing dictionaries
		(columns of result).
		
		Result can be modified before being returned. If second argument is
		a list of functions, result dictionary will be passed through these
		functions before being added to the result. If you plan on executing
		the same query with only a couple of parameters changed (for example,
		by format() method), you might consider passing unchanged SQL query as
		sql_query_template. Keep in mind that each function should follow similar
		template:
		
		def sample_function (result_dictionary, sql_query_used, adodb_instance):
			... CODE IF NO SQL TEMPLATE ...
		
		...or...
		
		def sample_function (result_dictionary, sql_query_used, sql_query_template, adodb_instance):
			... CODE IF SQL TEMPLATE AVAILABLE ...
		"""
		
		temp_rs_object = win32com.client.Dispatch('ADODB.RecordSet')
		temp_rs_object.Cursorlocation = 3
		temp_rs_object.Open(sql_query, self.__conn__, 1, 3)
		
		result_list = []
		
		if int(temp_rs_object.RecordCount) > 0:
			while not temp_rs_object.EOF:
			
				temp_dict = {}
				for x in range(temp_rs_object.Fields.Count):
					temp_dict[temp_rs_object.Fields.Item(x).Name] = temp_rs_object.Fields.Item(x).Value
				
				if isinstance(modifiers, list):
					for func in modifiers:
						try:
							assert sql_query_template
							func(temp_dict, sql_query, sql_query_template, self)
						except AssertionError:
							func(temp_dict, sql_query, self)
				
				result_list.append(temp_dict)
				
				temp_rs_object.MoveNext()
				
			temp_rs_object.Close()
		temp_rs_object = None
		
		return result_list