### ### DAL DBAPI 2.0 Wrapper Module ### ### --PLB 2007-11-03 ### import types import dtuple import string ## ## Version Information ## version_info = (1, 0, 0) version = string.join(['%d' % (v) for v in version_info], '.') ## ## Return a wrapped DBAPI 2.0 module ## ## The driver parameter may be an existing module object or the name of a module to be imported ## def wrap(driver): return Module(driver) # # DBAPI Exceptions # # Thanks to Kevin Jacob's 'Virtual Exceptions' # http://mail.python.org/pipermail/db-sig/2003-April/003345.html # # Warning # # Exception raised for important warnings like data # truncations while inserting, etc. It must be a subclass of # the Python StandardError (defined in the module # exceptions). # class Warning(StandardError): pass # # Error # # Exception that is the base class of all other error # exceptions. You can use this to catch all errors with one # single 'except' statement. Warnings are not considered # errors and thus should not use this class as base. It must # be a subclass of the Python StandardError (defined in the # module exceptions). # class Error(StandardError): pass # # InterfaceError # # Exception raised for errors that are related to the # database interface rather than the database itself. It # must be a subclass of Error. # class InterfaceError(Error): pass # # DatabaseError # # Exception raised for errors that are related to the # database. It must be a subclass of Error. # class DatabaseError(Error): pass # # DataError # # Exception raised for errors that are due to problems with # the processed data like division by zero, numeric value # out of range, etc. It must be a subclass of DatabaseError. # class DataError(DatabaseError): pass # # OperationalError # # Exception raised for errors that are related to the # database's operation and not necessarily under the control # of the programmer, e.g. an unexpected disconnect occurs, # the data source name is not found, a transaction could not # be processed, a memory allocation error occurred during # processing, etc. It must be a subclass of DatabaseError. # class OperationalError(DatabaseError): pass # # IntegrityError # # Exception raised when the relational integrity of the # database is affected, e.g. a foreign key check fails. It # must be a subclass of DatabaseError. # class IntegrityError(DatabaseError): pass # # InternalError # # Exception raised when the database encounters an internal # error, e.g. the cursor is not valid anymore, the # transaction is out of sync, etc. It must be a subclass of # DatabaseError. # class InternalError(DatabaseError): pass # # ProgrammingError # # Exception raised for programming errors, e.g. table not # found or already exists, syntax error in the SQL # statement, wrong number of parameters specified, etc. It # must be a subclass of DatabaseError. # class ProgrammingError(DatabaseError): pass # # NotSupportedError # # Exception raised in case a method or database API was used # which is not supported by the database, e.g. requesting a # .rollback() on a connection that does not support # transaction or has transactions turned off. It must be a # subclass of DatabaseError. # class NotSupportedError(DatabaseError): pass ## ## DBAPI 2.0 Wrapper ## class Module(object): # # BEGIN INIT # def __init__(self, driver): """DBAPI 2.0 Module Wrapper""" object.__init__(self) # # Set version # self.version_info = version_info self.version = version # # Act appropriately if driver is a module or a string (the name of a module) # if type(driver) is types.ModuleType: self.driver = driver elif type(driver) is str: self.driver = __import__(driver) else: raise ImportError, 'Could not import DBAPI module %s' % (driver) # # These module globals must be defined: # # # apilevel # # String constant stating the supported DB API level. # Currently only the strings '1.0' and '2.0' are allowed. # # If not given, a DB-API 1.0 level interface should be # assumed. # self.apilevel = '2.0' # # threadsafety # # Integer constant stating the level of thread safety the # interface supports. Possible values are: # # 0 Threads may not share the module. # 1 Threads may share the module, but not connections. # 2 Threads may share the module and connections. # 3 Threads may share the module, connections and # cursors. # self.threadsafety = self.driver.threadsafety # # paramstyle # # String constant stating the type of parameter marker # formatting expected by the interface. Possible values are # [2]: # # 'qmark' Question mark style, # e.g. '...WHERE name=?' # 'numeric' Numeric, positional style, # e.g. '...WHERE name=:1' # 'named' Named style, # e.g. '...WHERE name=:name' # 'format' ANSI C printf format codes, # e.g. '...WHERE name=%s' # 'pyformat' Python extended format codes, # e.g. '...WHERE name=%(name)s' # self.paramstyle = self.driver.paramstyle # # This is the exception inheritance layout: # # StandardError # |__Warning # |__Error # |__InterfaceError # |__DatabaseError # |__DataError # |__OperationalError # |__IntegrityError # |__InternalError # |__ProgrammingError # |__NotSupportedError # # Note: The values of these exceptions are not defined. They should # give the user a fairly good idea of what went wrong, though. # self.exceptions = [ 'Warning', 'Error', 'InterfaceError', 'DatabaseError', 'DataError', 'OperationalError', 'IntegrityError', 'InternalError', 'ProgrammingError', 'NotSupportedError', ] # # Wrap captive driver exceptions # for exception in self.exceptions: driver_exception = getattr(self.driver, exception) if driver_exception: setattr(self, exception, driver_exception) # # STRING # # This type object is used to describe columns in a database # that are string-based (e.g. CHAR). # if hasattr(self.driver, 'STRING'): self.STRING = self.driver.STRING # # BINARY # # This type object is used to describe (long) binary columns # in a database (e.g. LONG, RAW, BLOBs). # if hasattr(self.driver, 'BINARY'): self.BINARY = self.driver.BINARY # # NUMBER # # This type object is used to describe numeric columns in a # database. # if hasattr(self.driver, 'NUMBER'): self.NUMBER = self.driver.NUMBER # # DATETIME # # This type object is used to describe date/time columns in # a database. # if hasattr(self.driver, 'DATETIME'): self.DATETIME = self.driver.DATETIME # # ROWID # # This type object is used to describe the "Row ID" column # in a database. # if hasattr(self.driver, 'ROWID'): self.ROWID = self.driver.ROWID # # END INIT # # # Access to the database is made available through connection # objects. The module must provide the following constructor for # these: # # connect(parameters...) # # Constructor for creating a connection to the database. # Returns a Connection Object. It takes a number of # parameters which are database dependent. [1] # def connect(self, *args, **kwargs): conn = self.driver.connect(*args, **kwargs) conn = Connection(conn, module=self) # # Optional DBAPI Extension # # Connection Attributes .Error, .ProgrammingError, etc. # # All exception classes defined by the DB API standard should be # exposed on the Connection objects are attributes (in addition # to being available at module scope). # # These attributes simplify error handling in multi-connection # environments. # # Warning Message: "DB-API extension connection. used" # for exception in self.exceptions: driver_exception = getattr(self, exception) if driver_exception: setattr(conn, exception, driver_exception) return conn # # The module exports the following constructors and singletons: # # # Date(year,month,day) # # This function constructs an object holding a date value. # def Date(self, year, month, day): return self.driver.Date(year, month, day) # # Time(hour,minute,second) # # This function constructs an object holding a time value. # def Time(self, hour, minute, second): return self.driver.Time(hour, minute, second) # Timestamp(year,month,day,hour,minute,second) # # This function constructs an object holding a time stamp # value. # def Timestamp(self, year, month, day, hour, minute, second): return self.driver.Timestamp(year, month, day, hour, minute, second) # # DateFromTicks(ticks) # # This function constructs an object holding a date value # from the given ticks value (number of seconds since the # epoch; see the documentation of the standard Python time # module for details). # def DateFromTicks(self, ticks): return self.driver.DateFromTicks(ticks) # # TimeFromTicks(ticks) # # This function constructs an object holding a time value # from the given ticks value (number of seconds since the # epoch; see the documentation of the standard Python time # module for details). # def TimeFromTicks(self, ticks): return self.driver.TimeFromTicks(ticks) # # TimestampFromTicks(ticks) # # This function constructs an object holding a time stamp # value from the given ticks value (number of seconds since # the epoch; see the documentation of the standard Python # time module for details). # def TimestampFromTicks(self, ticks): return self.driver.TimestampFromTicks(ticks) # # Binary(string) # # This function constructs an object capable of holding a # binary (long) string value. # def Binary(self, string): return self.driver.Binary(string) # # SQL NULL values are represented by the Python None singleton on # input and output. # # # Note: Usage of Unix ticks for database interfacing can cause # troubles because of the limited date range they cover. # ## ## Connection Objects ## class Connection(object): """DBAPI 2.0 Connection Wrapper""" def __init__(self, conn, module=None, paramstyle=None): object.__init__(self) self.conn = conn # # Set module and driver # if module is not None: self.module = module if hasattr(module, 'driver'): self.driver = module.driver else: self.driver = None else: self.module = None self.driver = None # # Set paramstyle # if paramstyle is not None: self.paramstyle = paramstyle elif self.driver is not None: self.paramstyle = self.driver.paramstyle # # Connection Objects should respond to the following methods: # # # .close() # # Close the connection now (rather than whenever __del__ is # called). The connection will be unusable from this point # forward; an Error (or subclass) exception will be raised # if any operation is attempted with the connection. The # same applies to all cursor objects trying to use the # connection. Note that closing a connection without # committing the changes first will cause an implicit # rollback to be performed. # def close(self): return self.conn.close() # # .commit() # # Commit any pending transaction to the database. Note that # if the database supports an auto-commit feature, this must # be initially off. An interface method may be provided to # turn it back on. # # Database modules that do not support transactions should # implement this method with void functionality. # def commit(self): return self.conn.commit() # # .rollback() # # This method is optional since not all databases provide # transaction support. [3] # # In case a database does provide transactions this method # causes the the database to roll back to the start of any # pending transaction. Closing a connection without # committing the changes first will cause an implicit # rollback to be performed. def rollback(self): return self.conn.rollback() # # .cursor() # # Return a new Cursor Object using the connection. If the # database does not provide a direct cursor concept, the # module will have to emulate cursors using other means to # the extent needed by this specification. [4] # def cursor(self): curs = self.conn.cursor() return Cursor(curs, conn=self, paramstyle=self.paramstyle) # include self so the optional .connection attribute can be set properly # # Optional DBAPI Extension # # Connection Attribute .messages # # Same as cursor.messages except that the messages in the list # are connection oriented. # # The list is cleared automatically by all standard connection # methods calls (prior to executing the call) to avoid excessive # memory usage and can also be cleared by executing "del # connection.messages[:]". # # Warning Message: "DB-API extension connection.messages used" # # NOT CURRENTLY IMPLEMENTED # ## ## Cursor Objects ## ## These objects represent a database cursor, which is used to ## manage the context of a fetch operation. Cursors created from ## the same connection are not isolated, i.e., any changes ## done to the database by a cursor are immediately visible by the ## other cursors. Cursors created from different connections can ## or can not be isolated, depending on how the transaction support ## is implemented (see also the connection's rollback() and commit() ## methods.) ## class Cursor(object): def __init__(self, curs, conn, paramstyle=None): object.__init__(self) self.cursor = curs self.curs = curs # # Set module and driver # if conn.module is not None: self.module = conn.module if hasattr(conn.module, 'driver'): self.driver = conn.module.driver else: self.driver = None else: self.module = None self.driver = None # # Set paramstyle # if paramstyle is not None: self.paramstyle = paramstyle elif self.driver is not None: self.paramstyle = self.driver.paramstyle # # Optional DBAPI Extension # # Cursor Attributes .connection # # This read-only attribute return a reference to the Connection # object on which the cursor was created. # # The attribute simplifies writing polymorph code in # multi-connection environments. # # Warning Message: "DB-API extension cursor.connection used" # self.connection = conn self.conn = conn # # Add convenience commit method # self.commit = self.conn.commit # # Cursor Objects should respond to the following methods and attributes: # # # .description # # This read-only attribute is a sequence of 7-item # sequences. Each of these sequences contains information # describing one result column: (name, type_code, # display_size, internal_size, precision, scale, # null_ok). The first two items (name and type_code) are # mandatory, the other five are optional and must be set to # None if meaningfull values are not provided. # # This attribute will be None for operations that # do not return rows or if the cursor has not had an # operation invoked via the executeXXX() method yet. # # The type_code can be interpreted by comparing it to the # Type Objects specified in the section below. # description = property(lambda self: self.curs.description) # # Update column descriptions for dtuple (not part of DBAPI) # def update_columns(self): if self.curs.description: columns = [] for d in self.curs.description: column = d[0] columns.append(d[0]) self.columns = dtuple.TupleDescriptor([[column] for column in columns]) # # .rowcount # # This read-only attribute specifies the number of rows that # the last executeXXX() produced (for DQL statements like # 'select') or affected (for DML statements like 'update' or # 'insert'). # # The attribute is -1 in case no executeXXX() has been # performed on the cursor or the rowcount of the last # operation is not determinable by the interface. [7] # # Note: Future versions of the DB API specification could # redefine the latter case to have the object return None # instead of -1. # rowcount = property(lambda self: self.curs.rowcount) # # .callproc(procname[,parameters]) # # (This method is optional since not all databases provide # stored procedures. [3]) # # Call a stored database procedure with the given name. The # sequence of parameters must contain one entry for each # argument that the procedure expects. The result of the # call is returned as modified copy of the input # sequence. Input parameters are left untouched, output and # input/output parameters replaced with possibly new values. # # The procedure may also provide a result set as # output. This must then be made available through the # standard fetchXXX() methods. # def callproc(self, procname, parameters=[], *args, **kwargs): # # Call the wrapped method and save the results # results = self.curs.callproc(procname, parameters, *args, **kwargs) # # Update the column descriptions for dtuple # self.update_columns() # # Return the saved results # return results # # .close() # # Close the cursor now (rather than whenever __del__ is # called). The cursor will be unusable from this point # forward; an Error (or subclass) exception will be raised # if any operation is attempted with the cursor. # def close(self, *args, **kwargs): return self.curs.close(*args, **kwargs) # # .execute(operation[,parameters]) # # Prepare and execute a database operation (query or # command). Parameters may be provided as sequence or # mapping and will be bound to variables in the operation. # Variables are specified in a database-specific notation # (see the module's paramstyle attribute for details). [5] # # A reference to the operation will be retained by the # cursor. If the same operation object is passed in again, # then the cursor can optimize its behavior. This is most # effective for algorithms where the same operation is used, # but different parameters are bound to it (many times). # # For maximum efficiency when reusing an operation, it is # best to use the setinputsizes() method to specify the # parameter types and sizes ahead of time. It is legal for # a parameter to not match the predefined information; the # implementation should compensate, possibly with a loss of # efficiency. # # The parameters may also be specified as list of tuples to # e.g. insert multiple rows in a single operation, but this # kind of usage is depreciated: executemany() should be used # instead. # # Return values are not defined. # def execute(self, operation, parameters=None, *args, **kwargs): # # Set appropriate default parameters depending on paramstyle type # if not parameters: if self.paramstyle in ['qmark', 'numeric', 'format']: # # sequence # parameters = [] else: # # mapping # parameters = {} # # Call the wrapped method and save the results # self.curs.execute(operation, parameters, *args, **kwargs) # # Update the column descriptions for dtuple # self.update_columns() # # Return self so we can iterate over the results # return self # # .executemany(operation,seq_of_parameters) # # Prepare a database operation (query or command) and then # execute it against all parameter sequences or mappings # found in the sequence seq_of_parameters. # # Modules are free to implement this method using multiple # calls to the execute() method or by using array operations # to have the database process the sequence as a whole in # one call. # # Use of this method for an operation which produces one or # more result sets constitutes undefined behavior, and the # implementation is permitted (but not required) to raise # an exception when it detects that a result set has been # created by an invocation of the operation. # # The same comments as for execute() also apply accordingly # to this method. # # Return values are not defined. # def executemany(self, operation, seq_of_parameters, *args, **kwargs): # # Call the wrapped method and save the results # self.curs.executemany(operation, seq_of_parameters, *args, **kwargs) # # Update the column descriptions for dtuple # self.update_columns() # # Return self so we can iterate over the results # return self # # .fetchone() # # Fetch the next row of a query result set, returning a # single sequence, or None when no more data is # available. [6] # # An Error (or subclass) exception is raised if the previous # call to executeXXX() did not produce any result set or no # call was issued yet. # def fetchone(self, *args, **kwargs): row = self.curs.fetchone(*args, **kwargs) # # Return results using dtuple # if row: return dtuple.DatabaseTuple(self.columns, row) else: return None # # .fetchmany([size=cursor.arraysize]) # # Fetch the next set of rows of a query result, returning a # sequence of sequences (e.g. a list of tuples). An empty # sequence is returned when no more rows are available. # # The number of rows to fetch per call is specified by the # parameter. If it is not given, the cursor's arraysize # determines the number of rows to be fetched. The method # should try to fetch as many rows as indicated by the size # parameter. If this is not possible due to the specified # number of rows not being available, fewer rows may be # returned. # # An Error (or subclass) exception is raised if the previous # call to executeXXX() did not produce any result set or no # call was issued yet. # # Note there are performance considerations involved with # the size parameter. For optimal performance, it is # usually best to use the arraysize attribute. If the size # parameter is used, then it is best for it to retain the # same value from one fetchmany() call to the next. # def fetchmany(self, size=None, *args, **kwargs): size = size or self.arraysize return [dtuple.DatabaseTuple(self.columns, row) for row in self.curs.fetchmany(size, *args, **kwargs)] # # .fetchall() # # Fetch all (remaining) rows of a query result, returning # them as a sequence of sequences (e.g. a list of tuples). # Note that the cursor's arraysize attribute can affect the # performance of this operation. # # An Error (or subclass) exception is raised if the previous # call to executeXXX() did not produce any result set or no # call was issued yet. # def fetchall(self, *args, **kwargs): return [dtuple.DatabaseTuple(self.columns, row) for row in self.curs.fetchall(*args, **kwargs)] # # .nextset() # # (This method is optional since not all databases support # multiple result sets. [3]) # # This method will make the cursor skip to the next # available set, discarding any remaining rows from the # current set. # # If there are no more sets, the method returns # None. Otherwise, it returns a true value and subsequent # calls to the fetch methods will return rows from the next # result set. # # An Error (or subclass) exception is raised if the previous # call to executeXXX() did not produce any result set or no # call was issued yet. # def nextset(self, *args, **kwargs): if hasattr(self.curs, 'nextset'): return self.curs.nextset(*args, **kwargs) else: return None # # .arraysize # # This read/write attribute specifies the number of rows to # fetch at a time with fetchmany(). It defaults to 1 meaning # to fetch a single row at a time. # # Implementations must observe this value with respect to # the fetchmany() method, but are free to interact with the # database a single row at a time. It may also be used in # the implementation of executemany(). # arraysize = property(lambda self: self.curs.arraysize) # # .setinputsizes(sizes) # # This can be used before a call to executeXXX() to # predefine memory areas for the operation's parameters. # # sizes is specified as a sequence -- one item for each # input parameter. The item should be a Type Object that # corresponds to the input that will be used, or it should # be an integer specifying the maximum length of a string # parameter. If the item is None, then no predefined memory # area will be reserved for that column (this is useful to # avoid predefined areas for large inputs). # # This method would be used before the executeXXX() method # is invoked. # # Implementations are free to have this method do nothing # and users are free to not use it. # def setinputsizes(self, *args, **kwargs): return self.curs.setinputsizes(*args, **kwargs) # # .setoutputsize(size[,column]) # # Set a column buffer size for fetches of large columns # (e.g. LONGs, BLOBs, etc.). The column is specified as an # index into the result sequence. Not specifying the column # will set the default size for all large columns in the # cursor. # # This method would be used before the executeXXX() method # is invoked. # # Implementations are free to have this method do nothing # and users are free to not use it. # def setoutputsize(self, *args, **kwargs): return self.curs.setoutputsize(*args, **kwargs) # # Optional DBAPI Extension # # Cursor Attribute .rownumber # # This read-only attribute should provide the current 0-based # index of the cursor in the result set or None if the index cannot # be determined. # # The index can be seen as index of the cursor in a sequence (the # result set). The next fetch operation will fetch the row # indexed by .rownumber in that sequence. # # Warning Message: "DB-API extension cursor.rownumber used" # # NOT CURRENTLY IMPLEMENTED # # # Optional DBAPI Extension # # Cursor Method .scroll(value[,mode='relative']) # # Scroll the cursor in the result set to a new position according # to mode. # # If mode is 'relative' (default), value is taken as offset to # the current position in the result set, if set to 'absolute', # value states an absolute target position. # # An IndexError should be raised in case a scroll operation would # leave the result set. In this case, the cursor position is left # undefined (ideal would be to not move the cursor at all). # # Note: This method should use native scrollable cursors, if # available , or revert to an emulation for forward-only # scrollable cursors. The method may raise NotSupportedErrors to # signal that a specific operation is not supported by the # database (e.g. backward scrolling). # # Warning Message: "DB-API extension cursor.scroll() used" # # NOT CURRENTLY IMPLEMENTED # # # Optional DBAPI Extension # # Cursor Attribute .messages # # This is a Python list object to which the interface appends # tuples (exception class, exception value) for all messages # which the interfaces receives from the underlying database for # this cursor. # # The list is cleared by all standard cursor methods calls (prior # to executing the call) except for the .fetchXXX() calls # automatically to avoid excessive memory usage and can also be # cleared by executing "del cursor.messages[:]". # # All error and warning messages generated by the database are # placed into this list, so checking the list allows the user to # verify correct operation of the method calls. # # The aim of this attribute is to eliminate the need for a # Warning exception which often causes problems (some warnings # really only have informational character). # # Warning Message: "DB-API extension cursor.messages used" # # NOT CURRENTLY IMPLEMENTED # # # Optional DBAPI Extension # # Cursor Method .next() # # Return the next row from the currently executing SQL statement # using the same semantics as .fetchone(). A StopIteration # exception is raised when the result set is exhausted for Python # versions 2.2 and later. Previous versions don't have the # StopIteration exception and so the method should raise an # IndexError instead. # # Warning Message: "DB-API extension cursor.next() used" # def next(self): row = self.curs.fetchone() if row: return dtuple.DatabaseTuple(self.columns, row) else: raise StopIteration # # Optional DBAPI Extension # # Cursor Method .__iter__() # # Return self to make cursors compatible to the iteration protocol. # # Warning Message: "DB-API extension cursor.__iter__() used" # def __iter__(self): return self # # Optional DBAPI Extension # # Cursor Attribute .lastrowid # # This read-only attribute provides the rowid of the last # modified row (most databases return a rowid only when a single # INSERT operation is performed). If the operation does not set # a rowid or if the database does not support rowids, this # attribute should be set to None. # # The semantics of .lastrowid are undefined in case the last # executed statement modified more than one row, e.g. when # using INSERT with .executemany(). # # Warning Message: "DB-API extension cursor.lastrowid used" # # NOT CURRENTLY IMPLEMENTED #