codekingpro/portable-devtools
114k
1"""adodbapi - A python DB API 2.0 (PEP 249) interface to Microsoft ADO2 3Copyright (C) 2002 Henrik Ekelund, versions 2.1 and later by Vernon Cole4* http://sourceforge.net/projects/pywin325* https://github.com/mhammond/pywin326* http://sourceforge.net/projects/adodbapi7 8 This library is free software; you can redistribute it and/or9 modify it under the terms of the GNU Lesser General Public10 License as published by the Free Software Foundation; either11 version 2.1 of the License, or (at your option) any later version.12 13 This library is distributed in the hope that it will be useful,14 but WITHOUT ANY WARRANTY; without even the implied warranty of15 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU16 Lesser General Public License for more details.17 18 You should have received a copy of the GNU Lesser General Public19 License along with this library; if not, write to the Free Software20 Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA21 22 django adaptations and refactoring by Adam Vandenberg23 24DB-API 2.0 specification: http://www.python.org/dev/peps/pep-0249/25 26This module source should run correctly in CPython versions 2.7 and later,27or IronPython version 2.7 and later,28or, after running through 2to3.py, CPython 3.4 or later.29"""30 31__version__ = "2.6.2.0"32version = "adodbapi v" + __version__33 34import copy35import decimal36import os37import sys38import weakref39 40from . import ado_consts as adc, apibase as api, process_connect_string41 42try:43 verbose = int(os.environ["ADODBAPI_VERBOSE"])44except:45 verbose = False46if verbose:47 print(version)48 49# --- define objects to smooth out IronPython <-> CPython differences50onWin32 = False # assume the worst51if api.onIronPython:52 from clr import Reference53 from System import (54 Activator,55 Array,56 Byte,57 DateTime,58 DBNull,59 Decimal as SystemDecimal,60 Type,61 )62 63 def Dispatch(dispatch):64 type = Type.GetTypeFromProgID(dispatch)65 return Activator.CreateInstance(type)66 67 def getIndexedValue(obj, index):68 return obj.Item[index]69 70else: # try pywin3271 try:72 import pythoncom73 import pywintypes74 import win32com.client75 76 onWin32 = True77 78 def Dispatch(dispatch):79 return win32com.client.Dispatch(dispatch)80 81 except ImportError:82 import warnings83 84 warnings.warn(85 "pywin32 package (or IronPython) required for adodbapi.", ImportWarning86 )87 88 def getIndexedValue(obj, index):89 return obj(index)90 91 92from collections.abc import Mapping93 94# --- define objects to smooth out Python3000 <-> Python 2.x differences95unicodeType = str96longType = int97StringTypes = str98maxint = sys.maxsize99 100 101# ----------------- The .connect method -----------------102def make_COM_connecter():103 try:104 if onWin32:105 pythoncom.CoInitialize() # v2.1 Paj106 c = Dispatch("ADODB.Connection") # connect _after_ CoIninialize v2.1.1 adamvan107 except:108 raise api.InterfaceError(109 "Windows COM Error: Dispatch('ADODB.Connection') failed."110 )111 return c112 113 114def connect(*args, **kwargs): # --> a db-api connection object115 """Connect to a database.116 117 call using:118 :connection_string -- An ADODB formatted connection string, see:119 * http://www.connectionstrings.com120 * http://www.asp101.com/articles/john/connstring/default.asp121 :timeout -- A command timeout value, in seconds (default 30 seconds)122 """123 co = Connection() # make an empty connection object124 125 kwargs = process_connect_string.process(args, kwargs, True)126 127 try: # connect to the database, using the connection information in kwargs128 co.connect(kwargs)129 return co130 except Exception as e:131 message = 'Error opening connection to "%s"' % co.connection_string132 raise api.OperationalError(e, message)133 134 135# so you could use something like:136# myConnection.paramstyle = 'named'137# The programmer may also change the default.138# For example, if I were using django, I would say:139# import adodbapi as Database140# Database.adodbapi.paramstyle = 'format'141 142# ------- other module level defaults --------143defaultIsolationLevel = adc.adXactReadCommitted144# Set defaultIsolationLevel on module level before creating the connection.145# For example:146# import adodbapi, ado_consts147# adodbapi.adodbapi.defaultIsolationLevel=ado_consts.adXactBrowse"148#149# Set defaultCursorLocation on module level before creating the connection.150# It may be one of the "adUse..." consts.151defaultCursorLocation = adc.adUseClient # changed from adUseServer as of v 2.3.0152 153dateconverter = api.pythonDateTimeConverter() # default154 155 156def format_parameters(ADOparameters, show_value=False):157 """Format a collection of ADO Command Parameters.158 159 Used by error reporting in _execute_command.160 """161 try:162 if show_value:163 desc = [164 'Name: %s, Dir.: %s, Type: %s, Size: %s, Value: "%s", Precision: %s, NumericScale: %s'165 % (166 p.Name,167 adc.directions[p.Direction],168 adc.adTypeNames.get(p.Type, str(p.Type) + " (unknown type)"),169 p.Size,170 p.Value,171 p.Precision,172 p.NumericScale,173 )174 for p in ADOparameters175 ]176 else:177 desc = [178 "Name: %s, Dir.: %s, Type: %s, Size: %s, Precision: %s, NumericScale: %s"179 % (180 p.Name,181 adc.directions[p.Direction],182 adc.adTypeNames.get(p.Type, str(p.Type) + " (unknown type)"),183 p.Size,184 p.Precision,185 p.NumericScale,186 )187 for p in ADOparameters188 ]189 return "[" + "\n".join(desc) + "]"190 except:191 return "[]"192 193 194def _configure_parameter(p, value, adotype, settings_known):195 """Configure the given ADO Parameter 'p' with the Python 'value'."""196 197 if adotype in api.adoBinaryTypes:198 p.Size = len(value)199 p.AppendChunk(value)200 201 elif isinstance(value, StringTypes): # v2.1 Jevon202 L = len(value)203 if adotype in api.adoStringTypes: # v2.2.1 Cole204 if settings_known:205 L = min(L, p.Size) # v2.1 Cole limit data to defined size206 p.Value = value[:L] # v2.1 Jevon & v2.1 Cole207 else:208 p.Value = value # dont limit if db column is numeric209 if L > 0: # v2.1 Cole something does not like p.Size as Zero210 p.Size = L # v2.1 Jevon211 212 elif isinstance(value, decimal.Decimal):213 if api.onIronPython:214 s = str(value)215 p.Value = s216 p.Size = len(s)217 else:218 p.Value = value219 exponent = value.as_tuple()[2]220 digit_count = len(value.as_tuple()[1])221 p.Precision = digit_count222 if exponent == 0:223 p.NumericScale = 0224 elif exponent < 0:225 p.NumericScale = -exponent226 if p.Precision < p.NumericScale:227 p.Precision = p.NumericScale228 else: # exponent > 0:229 p.NumericScale = 0230 p.Precision = digit_count + exponent231 232 elif type(value) in dateconverter.types:233 if settings_known and adotype in api.adoDateTimeTypes:234 p.Value = dateconverter.COMDate(value)235 else: # probably a string236 # provide the date as a string in the format 'YYYY-MM-dd'237 s = dateconverter.DateObjectToIsoFormatString(value)238 p.Value = s239 p.Size = len(s)240 241 elif api.onIronPython and isinstance(value, longType): # Iron Python Long242 s = str(value) # feature workaround for IPy 2.0243 p.Value = s244 245 elif adotype == adc.adEmpty: # ADO will not let you specify a null column246 p.Type = (247 adc.adInteger248 ) # so we will fake it to be an integer (just to have something)249 p.Value = None # and pass in a Null *value*250 251 # For any other type, set the value and let pythoncom do the right thing.252 else:253 p.Value = value254 255 256# # # # # ----- the Class that defines a connection ----- # # # # #257class Connection(object):258 # include connection attributes as class attributes required by api definition.259 Warning = api.Warning260 Error = api.Error261 InterfaceError = api.InterfaceError262 DataError = api.DataError263 DatabaseError = api.DatabaseError264 OperationalError = api.OperationalError265 IntegrityError = api.IntegrityError266 InternalError = api.InternalError267 NotSupportedError = api.NotSupportedError268 ProgrammingError = api.ProgrammingError269 FetchFailedError = api.FetchFailedError # (special for django)270 # ...class attributes... (can be overridden by instance attributes)271 verbose = api.verbose272 273 @property274 def dbapi(self): # a proposed db-api version 3 extension.275 "Return a reference to the DBAPI module for this Connection."276 return api277 278 def __init__(self): # now define the instance attributes279 self.connector = None280 self.paramstyle = api.paramstyle281 self.supportsTransactions = False282 self.connection_string = ""283 self.cursors = weakref.WeakValueDictionary()284 self.dbms_name = ""285 self.dbms_version = ""286 self.errorhandler = None # use the standard error handler for this instance287 self.transaction_level = 0 # 0 == Not in a transaction, at the top level288 self._autocommit = False289 290 def connect(self, kwargs, connection_maker=make_COM_connecter):291 if verbose > 9:292 print("kwargs=", repr(kwargs))293 try:294 self.connection_string = (295 kwargs["connection_string"] % kwargs296 ) # insert keyword arguments297 except Exception as e:298 self._raiseConnectionError(299 KeyError, "Python string format error in connection string->"300 )301 self.timeout = kwargs.get("timeout", 30)302 self.mode = kwargs.get("mode", adc.adModeUnknown)303 self.kwargs = kwargs304 if verbose:305 print('%s attempting: "%s"' % (version, self.connection_string))306 self.connector = connection_maker()307 self.connector.ConnectionTimeout = self.timeout308 self.connector.ConnectionString = self.connection_string309 self.connector.Mode = self.mode310 311 try:312 self.connector.Open() # Open the ADO connection313 except api.Error:314 self._raiseConnectionError(315 api.DatabaseError,316 "ADO error trying to Open=%s" % self.connection_string,317 )318 319 try: # Stefan Fuchs; support WINCCOLEDBProvider320 if getIndexedValue(self.connector.Properties, "Transaction DDL").Value != 0:321 self.supportsTransactions = True322 except pywintypes.com_error:323 pass # Stefan Fuchs324 self.dbms_name = getIndexedValue(self.connector.Properties, "DBMS Name").Value325 try: # Stefan Fuchs326 self.dbms_version = getIndexedValue(327 self.connector.Properties, "DBMS Version"328 ).Value329 except pywintypes.com_error:330 pass # Stefan Fuchs331 self.connector.CursorLocation = defaultCursorLocation # v2.1 Rose332 if self.supportsTransactions:333 self.connector.IsolationLevel = defaultIsolationLevel334 self._autocommit = bool(kwargs.get("autocommit", False))335 if not self._autocommit:336 self.transaction_level = (337 self.connector.BeginTrans()338 ) # Disables autocommit & inits transaction_level339 else:340 self._autocommit = True341 if "paramstyle" in kwargs:342 self.paramstyle = kwargs["paramstyle"] # let setattr do the error checking343 self.messages = []344 if verbose:345 print("adodbapi New connection at %X" % id(self))346 347 def _raiseConnectionError(self, errorclass, errorvalue):348 eh = self.errorhandler349 if eh is None:350 eh = api.standardErrorHandler351 eh(self, None, errorclass, errorvalue)352 353 def _closeAdoConnection(self): # all v2.1 Rose354 """close the underlying ADO Connection object,355 rolling it back first if it supports transactions."""356 if self.connector is None:357 return358 if not self._autocommit:359 if self.transaction_level:360 try:361 self.connector.RollbackTrans()362 except:363 pass364 self.connector.Close()365 if verbose:366 print("adodbapi Closed connection at %X" % id(self))367 368 def close(self):369 """Close the connection now (rather than whenever __del__ is called).370 371 The connection will be unusable from this point forward;372 an Error (or subclass) exception will be raised if any operation is attempted with the connection.373 The same applies to all cursor objects trying to use the connection.374 """375 for crsr in list(self.cursors.values())[376 :377 ]: # copy the list, then close each one378 crsr.close(dont_tell_me=True) # close without back-link clearing379 self.messages = []380 try:381 self._closeAdoConnection() # v2.1 Rose382 except Exception as e:383 self._raiseConnectionError(sys.exc_info()[0], sys.exc_info()[1])384 385 self.connector = None # v2.4.2.2 fix subtle timeout bug386 # per M.Hammond: "I expect the benefits of uninitializing are probably fairly small,387 # so never uninitializing will probably not cause any problems."388 389 def commit(self):390 """Commit any pending transaction to the database.391 392 Note that if the database supports an auto-commit feature,393 this must be initially off. An interface method may be provided to turn it back on.394 Database modules that do not support transactions should implement this method with void functionality.395 """396 self.messages = []397 if not self.supportsTransactions:398 return399 400 try:401 self.transaction_level = self.connector.CommitTrans()402 if verbose > 1:403 print("commit done on connection at %X" % id(self))404 if not (405 self._autocommit406 or (self.connector.Attributes & adc.adXactAbortRetaining)407 ):408 # If attributes has adXactCommitRetaining it performs retaining commits that is,409 # calling CommitTrans automatically starts a new transaction. Not all providers support this.410 # If not, we will have to start a new transaction by this command:411 self.transaction_level = self.connector.BeginTrans()412 except Exception as e:413 self._raiseConnectionError(api.ProgrammingError, e)414 415 def _rollback(self):416 """In case a database does provide transactions this method causes the the database to roll back to417 the start of any pending transaction. Closing a connection without committing the changes first will418 cause an implicit rollback to be performed.419 420 If the database does not support the functionality required by the method, the interface should421 throw an exception in case the method is used.422 The preferred approach is to not implement the method and thus have Python generate423 an AttributeError in case the method is requested. This allows the programmer to check for database424 capabilities using the standard hasattr() function.425 426 For some dynamically configured interfaces it may not be appropriate to require dynamically making427 the method available. These interfaces should then raise a NotSupportedError to indicate the428 non-ability to perform the roll back when the method is invoked.429 """430 self.messages = []431 if (432 self.transaction_level433 ): # trying to roll back with no open transaction causes an error434 try:435 self.transaction_level = self.connector.RollbackTrans()436 if verbose > 1:437 print("rollback done on connection at %X" % id(self))438 if not self._autocommit and not (439 self.connector.Attributes & adc.adXactAbortRetaining440 ):441 # If attributes has adXactAbortRetaining it performs retaining aborts that is,442 # calling RollbackTrans automatically starts a new transaction. Not all providers support this.443 # If not, we will have to start a new transaction by this command:444 if (445 not self.transaction_level446 ): # if self.transaction_level == 0 or self.transaction_level is None:447 self.transaction_level = self.connector.BeginTrans()448 except Exception as e:449 self._raiseConnectionError(api.ProgrammingError, e)450 451 def __setattr__(self, name, value):452 if name == "autocommit": # extension: allow user to turn autocommit on or off453 if self.supportsTransactions:454 object.__setattr__(self, "_autocommit", bool(value))455 try:456 self._rollback() # must clear any outstanding transactions457 except:458 pass459 return460 elif name == "paramstyle":461 if value not in api.accepted_paramstyles:462 self._raiseConnectionError(463 api.NotSupportedError,464 'paramstyle="%s" not in:%s'465 % (value, repr(api.accepted_paramstyles)),466 )467 elif name == "variantConversions":468 value = copy.copy(469 value470 ) # make a new copy -- no changes in the default, please471 object.__setattr__(self, name, value)472 473 def __getattr__(self, item):474 if (475 item == "rollback"476 ): # the rollback method only appears if the database supports transactions477 if self.supportsTransactions:478 return (479 self._rollback480 ) # return the rollback method so the caller can execute it.481 else:482 raise AttributeError("this data provider does not support Rollback")483 elif item == "autocommit":484 return self._autocommit485 else:486 raise AttributeError(487 'no such attribute in ADO connection object as="%s"' % item488 )489 490 def cursor(self):491 "Return a new Cursor Object using the connection."492 self.messages = []493 c = Cursor(self)494 return c495 496 def _i_am_here(self, crsr):497 "message from a new cursor proclaiming its existence"498 oid = id(crsr)499 self.cursors[oid] = crsr500 501 def _i_am_closing(self, crsr):502 "message from a cursor giving connection a chance to clean up"503 try:504 del self.cursors[id(crsr)]505 except:506 pass507 508 def printADOerrors(self):509 j = self.connector.Errors.Count510 if j:511 print("ADO Errors:(%i)" % j)512 for e in self.connector.Errors:513 print("Description: %s" % e.Description)514 print("Error: %s %s " % (e.Number, adc.adoErrors.get(e.Number, "unknown")))515 if e.Number == adc.ado_error_TIMEOUT:516 print(517 "Timeout Error: Try using adodbpi.connect(constr,timeout=Nseconds)"518 )519 print("Source: %s" % e.Source)520 print("NativeError: %s" % e.NativeError)521 print("SQL State: %s" % e.SQLState)522 523 def _suggest_error_class(self):524 """Introspect the current ADO Errors and determine an appropriate error class.525 526 Error.SQLState is a SQL-defined error condition, per the SQL specification:527 http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt528 529 The 23000 class of errors are integrity errors.530 Error 40002 is a transactional integrity error.531 """532 if self.connector is not None:533 for e in self.connector.Errors:534 state = str(e.SQLState)535 if state.startswith("23") or state == "40002":536 return api.IntegrityError537 return api.DatabaseError538 539 def __del__(self):540 try:541 self._closeAdoConnection() # v2.1 Rose542 except:543 pass544 self.connector = None545 546 def __enter__(self): # Connections are context managers547 return self548 549 def __exit__(self, exc_type, exc_val, exc_tb):550 if exc_type:551 self._rollback() # automatic rollback on errors552 else:553 self.commit()554 555 def get_table_names(self):556 schema = self.connector.OpenSchema(20) # constant = adSchemaTables557 558 tables = []559 while not schema.EOF:560 name = getIndexedValue(schema.Fields, "TABLE_NAME").Value561 tables.append(name)562 schema.MoveNext()563 del schema564 return tables565 566 567# # # # # ----- the Class that defines a cursor ----- # # # # #568class Cursor(object):569 ## ** api required attributes:570 ## description...571 ## This read-only attribute is a sequence of 7-item sequences.572 ## Each of these sequences contains information describing one result column:573 ## (name, type_code, display_size, internal_size, precision, scale, null_ok).574 ## This attribute will be None for operations that do not return rows or if the575 ## cursor has not had an operation invoked via the executeXXX() method yet.576 ## The type_code can be interpreted by comparing it to the Type Objects specified in the section below.577 ## rowcount...578 ## This read-only attribute specifies the number of rows that the last executeXXX() produced579 ## (for DQL statements like select) or affected (for DML statements like update or insert).580 ## The attribute is -1 in case no executeXXX() has been performed on the cursor or581 ## the rowcount of the last operation is not determinable by the interface.[7]582 ## arraysize...583 ## This read/write attribute specifies the number of rows to fetch at a time with fetchmany().584 ## It defaults to 1 meaning to fetch a single row at a time.585 ## Implementations must observe this value with respect to the fetchmany() method,586 ## but are free to interact with the database a single row at a time.587 ## It may also be used in the implementation of executemany().588 ## ** extension attributes:589 ## paramstyle...590 ## allows the programmer to override the connection's default paramstyle591 ## errorhandler...592 ## allows the programmer to override the connection's default error handler593 594 def __init__(self, connection):595 self.command = None596 self._ado_prepared = False597 self.messages = []598 self.connection = connection599 self.paramstyle = connection.paramstyle # used for overriding the paramstyle600 self._parameter_names = []601 self.recordset_is_remote = False602 self.rs = None # the ADO recordset for this cursor603 self.converters = [] # conversion function for each column604 self.columnNames = {} # names of columns {lowercase name : number,...}605 self.numberOfColumns = 0606 self._description = None607 self.rowcount = -1608 self.errorhandler = connection.errorhandler609 self.arraysize = 1610 connection._i_am_here(self)611 if verbose:612 print(613 "%s New cursor at %X on conn %X"614 % (version, id(self), id(self.connection))615 )616 617 def __iter__(self): # [2.1 Zamarev]618 return iter(self.fetchone, None) # [2.1 Zamarev]619 620 def prepare(self, operation):621 self.command = operation622 self._description = None623 self._ado_prepared = "setup"624 625 def __next__(self):626 r = self.fetchone()627 if r:628 return r629 raise StopIteration630 631 def __enter__(self):632 "Allow database cursors to be used with context managers."633 return self634 635 def __exit__(self, exc_type, exc_val, exc_tb):636 "Allow database cursors to be used with context managers."637 self.close()638 639 def _raiseCursorError(self, errorclass, errorvalue):640 eh = self.errorhandler641 if eh is None:642 eh = api.standardErrorHandler643 eh(self.connection, self, errorclass, errorvalue)644 645 def build_column_info(self, recordset):646 self.converters = [] # convertion function for each column647 self.columnNames = {} # names of columns {lowercase name : number,...}648 self._description = None649 650 # if EOF and BOF are true at the same time, there are no records in the recordset651 if (recordset is None) or (recordset.State == adc.adStateClosed):652 self.rs = None653 self.numberOfColumns = 0654 return655 self.rs = recordset # v2.1.1 bkline656 self.recordset_format = api.RS_ARRAY if api.onIronPython else api.RS_WIN_32657 self.numberOfColumns = recordset.Fields.Count658 try:659 varCon = self.connection.variantConversions660 except AttributeError:661 varCon = api.variantConversions662 for i in range(self.numberOfColumns):663 f = getIndexedValue(self.rs.Fields, i)664 try:665 self.converters.append(666 varCon[f.Type]667 ) # conversion function for this column668 except KeyError:669 self._raiseCursorError(670 api.InternalError, "Data column of Unknown ADO type=%s" % f.Type671 )672 self.columnNames[f.Name.lower()] = i # columnNames lookup673 674 def _makeDescriptionFromRS(self):675 # Abort if closed or no recordset.676 if self.rs is None:677 self._description = None678 return679 desc = []680 for i in range(self.numberOfColumns):681 f = getIndexedValue(self.rs.Fields, i)682 if self.rs.EOF or self.rs.BOF:683 display_size = None684 else:685 display_size = (686 f.ActualSize687 ) # TODO: Is this the correct defintion according to the DB API 2 Spec ?688 null_ok = bool(f.Attributes & adc.adFldMayBeNull) # v2.1 Cole689 desc.append(690 (691 f.Name,692 f.Type,693 display_size,694 f.DefinedSize,695 f.Precision,696 f.NumericScale,697 null_ok,698 )699 )700 self._description = desc701 702 def get_description(self):703 if not self._description:704 self._makeDescriptionFromRS()705 return self._description706 707 def __getattr__(self, item):708 if item == "description":709 return self.get_description()710 object.__getattribute__(711 self, item712 ) # may get here on Remote attribute calls for existing attributes713 714 def format_description(self, d):715 """Format db_api description tuple for printing."""716 if self.description is None:717 self._makeDescriptionFromRS()718 if isinstance(d, int):719 d = self.description[d]720 desc = (721 "Name= %s, Type= %s, DispSize= %s, IntSize= %s, Precision= %s, Scale= %s NullOK=%s"722 % (723 d[0],724 adc.adTypeNames.get(d[1], str(d[1]) + " (unknown type)"),725 d[2],726 d[3],727 d[4],728 d[5],729 d[6],730 )731 )732 return desc733 734 def close(self, dont_tell_me=False):735 """Close the cursor now (rather than whenever __del__ is called).736 The cursor will be unusable from this point forward; an Error (or subclass)737 exception will be raised if any operation is attempted with the cursor.738 """739 if self.connection is None:740 return741 self.messages = []742 if (743 self.rs and self.rs.State != adc.adStateClosed744 ): # rs exists and is open #v2.1 Rose745 self.rs.Close() # v2.1 Rose746 self.rs = None # let go of the recordset so ADO will let it be disposed #v2.1 Rose747 if not dont_tell_me:748 self.connection._i_am_closing(749 self750 ) # take me off the connection's cursors list751 self.connection = (752 None # this will make all future method calls on me throw an exception753 )754 if verbose:755 print("adodbapi Closed cursor at %X" % id(self))756 757 def __del__(self):758 try:759 self.close()760 except:761 pass762 763 def _new_command(self, command_type=adc.adCmdText):764 self.cmd = None765 self.messages = []766 767 if self.connection is None:768 self._raiseCursorError(api.InterfaceError, None)769 return770 try:771 self.cmd = Dispatch("ADODB.Command")772 self.cmd.ActiveConnection = self.connection.connector773 self.cmd.CommandTimeout = self.connection.timeout774 self.cmd.CommandType = command_type775 self.cmd.CommandText = self.commandText776 self.cmd.Prepared = bool(self._ado_prepared)777 except:778 self._raiseCursorError(779 api.DatabaseError,780 'Error creating new ADODB.Command object for "%s"'781 % repr(self.commandText),782 )783 784 def _execute_command(self):785 # Stored procedures may have an integer return value786 self.return_value = None787 recordset = None788 count = -1 # default value789 if verbose:790 print('Executing command="%s"' % self.commandText)791 try:792 # ----- the actual SQL is executed here ---793 if api.onIronPython:794 ra = Reference[int]()795 recordset = self.cmd.Execute(ra)796 count = ra.Value797 else: # pywin32798 recordset, count = self.cmd.Execute()799 # ----- ------------------------------- ---800 except Exception as e:801 _message = ""802 if hasattr(e, "args"):803 _message += str(e.args) + "\n"804 _message += "Command:\n%s\nParameters:\n%s" % (805 self.commandText,806 format_parameters(self.cmd.Parameters, True),807 )808 klass = self.connection._suggest_error_class()809 self._raiseCursorError(klass, _message)810 try:811 self.rowcount = recordset.RecordCount812 except:813 self.rowcount = count814 self.build_column_info(recordset)815 816 # The ADO documentation hints that obtaining the recordcount may be timeconsuming817 # "If the Recordset object does not support approximate positioning, this property818 # may be a significant drain on resources # [ekelund]819 # Therefore, COM will not return rowcount for server-side cursors. [Cole]820 # Client-side cursors (the default since v2.8) will force a static821 # cursor, and rowcount will then be set accurately [Cole]822 823 def get_rowcount(self):824 return self.rowcount825 826 def get_returned_parameters(self):827 """with some providers, returned parameters and the .return_value are not available until828 after the last recordset has been read. In that case, you must coll nextset() until it829 returns None, then call this method to get your returned information."""830 831 retLst = (832 []833 ) # store procedures may return altered parameters, including an added "return value" item834 for p in tuple(self.cmd.Parameters):835 if verbose > 2:836 print(837 'Returned=Name: %s, Dir.: %s, Type: %s, Size: %s, Value: "%s",'838 " Precision: %s, NumericScale: %s"839 % (840 p.Name,841 adc.directions[p.Direction],842 adc.adTypeNames.get(p.Type, str(p.Type) + " (unknown type)"),843 p.Size,844 p.Value,845 p.Precision,846 p.NumericScale,847 )848 )849 pyObject = api.convert_to_python(p.Value, api.variantConversions[p.Type])850 if p.Direction == adc.adParamReturnValue:851 self.returnValue = (852 pyObject # also load the undocumented attribute (Vernon's Error!)853 )854 self.return_value = pyObject855 else:856 retLst.append(pyObject)857 return retLst # return the parameter list to the caller858 859 def callproc(self, procname, parameters=None):860 """Call a stored database procedure with the given name.861 The sequence of parameters must contain one entry for each862 argument that the sproc expects. The result of the863 call is returned as modified copy of the input864 sequence. Input parameters are left untouched, output and865 input/output parameters replaced with possibly new values.866 867 The sproc may also provide a result set as output,868 which is available through the standard .fetch*() methods.869 Extension: A "return_value" property may be set on the870 cursor if the sproc defines an integer return value.871 """872 self._parameter_names = []873 self.commandText = procname874 self._new_command(command_type=adc.adCmdStoredProc)875 self._buildADOparameterList(parameters, sproc=True)876 if verbose > 2:877 print(878 "Calling Stored Proc with Params=",879 format_parameters(self.cmd.Parameters, True),880 )881 self._execute_command()882 return self.get_returned_parameters()883 884 def _reformat_operation(self, operation, parameters):885 if self.paramstyle in ("format", "pyformat"): # convert %s to ?886 operation, self._parameter_names = api.changeFormatToQmark(operation)887 elif self.paramstyle == "named" or (888 self.paramstyle == "dynamic" and isinstance(parameters, Mapping)889 ):890 operation, self._parameter_names = api.changeNamedToQmark(891 operation892 ) # convert :name to ?893 return operation894 895 def _buildADOparameterList(self, parameters, sproc=False):896 self.parameters = parameters897 if parameters is None:898 parameters = []899 900 # Note: ADO does not preserve the parameter list, even if "Prepared" is True, so we must build every time.901 parameters_known = False902 if sproc: # needed only if we are calling a stored procedure903 try: # attempt to use ADO's parameter list904 self.cmd.Parameters.Refresh()905 if verbose > 2:906 print(907 "ADO detected Params=",908 format_parameters(self.cmd.Parameters, True),909 )910 print("Program Parameters=", repr(parameters))911 parameters_known = True912 except api.Error:913 if verbose:914 print("ADO Parameter Refresh failed")915 pass916 else:917 if len(parameters) != self.cmd.Parameters.Count - 1:918 raise api.ProgrammingError(919 "You must supply %d parameters for this stored procedure"920 % (self.cmd.Parameters.Count - 1)921 )922 if sproc or parameters != []:923 i = 0924 if parameters_known: # use ado parameter list925 if self._parameter_names: # named parameters926 for i, pm_name in enumerate(self._parameter_names):927 p = getIndexedValue(self.cmd.Parameters, i)928 try:929 _configure_parameter(930 p, parameters[pm_name], p.Type, parameters_known931 )932 except Exception as e:933 _message = (934 "Error Converting Parameter %s: %s, %s <- %s\n"935 % (936 p.Name,937 adc.ado_type_name(p.Type),938 p.Value,939 repr(parameters[pm_name]),940 )941 )942 self._raiseCursorError(943 api.DataError, _message + "->" + repr(e.args)944 )945 else: # regular sequence of parameters946 for value in parameters:947 p = getIndexedValue(self.cmd.Parameters, i)948 if (949 p.Direction == adc.adParamReturnValue950 ): # this is an extra parameter added by ADO951 i += 1 # skip the extra952 p = getIndexedValue(self.cmd.Parameters, i)953 try:954 _configure_parameter(p, value, p.Type, parameters_known)955 except Exception as e:956 _message = (957 "Error Converting Parameter %s: %s, %s <- %s\n"958 % (959 p.Name,960 adc.ado_type_name(p.Type),961 p.Value,962 repr(value),963 )964 )965 self._raiseCursorError(966 api.DataError, _message + "->" + repr(e.args)967 )968 i += 1969 else: # -- build own parameter list970 if (971 self._parameter_names972 ): # we expect a dictionary of parameters, this is the list of expected names973 for parm_name in self._parameter_names:974 elem = parameters[parm_name]975 adotype = api.pyTypeToADOType(elem)976 p = self.cmd.CreateParameter(977 parm_name, adotype, adc.adParamInput978 )979 _configure_parameter(p, elem, adotype, parameters_known)980 try:981 self.cmd.Parameters.Append(p)982 except Exception as e:983 _message = "Error Building Parameter %s: %s, %s <- %s\n" % (984 p.Name,985 adc.ado_type_name(p.Type),986 p.Value,987 repr(elem),988 )989 self._raiseCursorError(990 api.DataError, _message + "->" + repr(e.args)991 )992 else: # expecting the usual sequence of parameters993 if sproc:994 p = self.cmd.CreateParameter(995 "@RETURN_VALUE", adc.adInteger, adc.adParamReturnValue996 )997 self.cmd.Parameters.Append(p)998 999 for elem in parameters:1000 name = "p%i" % i1001 adotype = api.pyTypeToADOType(elem)1002 p = self.cmd.CreateParameter(1003 name, adotype, adc.adParamInput1004 ) # Name, Type, Direction, Size, Value1005 _configure_parameter(p, elem, adotype, parameters_known)1006 try:1007 self.cmd.Parameters.Append(p)1008 except Exception as e:1009 _message = "Error Building Parameter %s: %s, %s <- %s\n" % (1010 p.Name,1011 adc.ado_type_name(p.Type),1012 p.Value,1013 repr(elem),1014 )1015 self._raiseCursorError(1016 api.DataError, _message + "->" + repr(e.args)1017 )1018 i += 11019 if self._ado_prepared == "setup":1020 self._ado_prepared = (1021 True # parameters will be "known" by ADO next loop1022 )1023 1024 def execute(self, operation, parameters=None):1025 """Prepare and execute a database operation (query or command).1026 1027 Parameters may be provided as sequence or mapping and will be bound to variables in the operation.1028 Variables are specified in a database-specific notation1029 (see the module's paramstyle attribute for details). [5]1030 A reference to the operation will be retained by the cursor.1031 If the same operation object is passed in again, then the cursor1032 can optimize its behavior. This is most effective for algorithms1033 where the same operation is used, but different parameters are bound to it (many times).1034 1035 For maximum efficiency when reusing an operation, it is best to use1036 the setinputsizes() method to specify the parameter types and sizes ahead of time.1037 It is legal for a parameter to not match the predefined information;1038 the implementation should compensate, possibly with a loss of efficiency.1039 1040 The parameters may also be specified as list of tuples to e.g. insert multiple rows in1041 a single operation, but this kind of usage is depreciated: executemany() should be used instead.1042 1043 Return value is not defined.1044 1045 [5] The module will use the __getitem__ method of the parameters object to map either positions1046 (integers) or names (strings) to parameter values. This allows for both sequences and mappings1047 to be used as input.1048 The term "bound" refers to the process of binding an input value to a database execution buffer.1049 In practical terms, this means that the input value is directly used as a value in the operation.1050 The client should not be required to "escape" the value so that it can be used -- the value1051 should be equal to the actual database value."""1052 if (1053 self.command is not operation1054 or self._ado_prepared == "setup"1055 or not hasattr(self, "commandText")1056 ):1057 if self.command is not operation:1058 self._ado_prepared = False1059 self.command = operation1060 self._parameter_names = []1061 self.commandText = (1062 operation1063 if (self.paramstyle == "qmark" or not parameters)1064 else self._reformat_operation(operation, parameters)1065 )1066 self._new_command()1067 self._buildADOparameterList(parameters)1068 if verbose > 3:1069 print("Params=", format_parameters(self.cmd.Parameters, True))1070 self._execute_command()1071 1072 def executemany(self, operation, seq_of_parameters):1073 """Prepare a database operation (query or command)1074 and then execute it against all parameter sequences or mappings found in the sequence seq_of_parameters.1075 1076 Return values are not defined.1077 """1078 self.messages = list()1079 total_recordcount = 01080 1081 self.prepare(operation)1082 for params in seq_of_parameters:1083 self.execute(self.command, params)1084 if self.rowcount == -1:1085 total_recordcount = -11086 if total_recordcount != -1:1087 total_recordcount += self.rowcount1088 self.rowcount = total_recordcount1089 1090 def _fetch(self, limit=None):1091 """Fetch rows from the current recordset.1092 1093 limit -- Number of rows to fetch, or None (default) to fetch all rows.1094 """1095 if self.connection is None or self.rs is None:1096 self._raiseCursorError(1097 api.FetchFailedError, "fetch() on closed connection or empty query set"1098 )1099 return1100 1101 if self.rs.State == adc.adStateClosed or self.rs.BOF or self.rs.EOF:1102 return list()1103 if limit: # limit number of rows retrieved1104 ado_results = self.rs.GetRows(limit)1105 else: # get all rows1106 ado_results = self.rs.GetRows()1107 if (1108 self.recordset_format == api.RS_ARRAY1109 ): # result of GetRows is a two-dimension array1110 length = (1111 len(ado_results) // self.numberOfColumns1112 ) # length of first dimension1113 else: # pywin321114 length = len(ado_results[0]) # result of GetRows is tuples in a tuple1115 fetchObject = api.SQLrows(1116 ado_results, length, self1117 ) # new object to hold the results of the fetch1118 return fetchObject1119 1120 def fetchone(self):1121 """Fetch the next row of a query result set, returning a single sequence,1122 or None when no more data is available.1123 1124 An Error (or subclass) exception is raised if the previous call to executeXXX()1125 did not produce any result set or no call was issued yet.1126 """1127 self.messages = []1128 result = self._fetch(1)1129 if result: # return record (not list of records)1130 return result[0]1131 return None1132 1133 def fetchmany(self, size=None):1134 """Fetch the next set of rows of a query result, returning a list of tuples. An empty sequence is returned when no more rows are available.1135 1136 The number of rows to fetch per call is specified by the parameter.1137 If it is not given, the cursor's arraysize determines the number of rows to be fetched.1138 The method should try to fetch as many rows as indicated by the size parameter.1139 If this is not possible due to the specified number of rows not being available,1140 fewer rows may be returned.1141 1142 An Error (or subclass) exception is raised if the previous call to executeXXX()1143 did not produce any result set or no call was issued yet.1144 1145 Note there are performance considerations involved with the size parameter.1146 For optimal performance, it is usually best to use the arraysize attribute.1147 If the size parameter is used, then it is best for it to retain the same value from1148 one fetchmany() call to the next.1149 """1150 self.messages = []1151 if size is None:1152 size = self.arraysize1153 return self._fetch(size)1154 1155 def fetchall(self):1156 """Fetch all (remaining) rows of a query result, returning them as a sequence of sequences (e.g. a list of tuples).1157 1158 Note that the cursor's arraysize attribute1159 can affect the performance of this operation.1160 An Error (or subclass) exception is raised if the previous call to executeXXX()1161 did not produce any result set or no call was issued yet.1162 """1163 self.messages = []1164 return self._fetch()1165 1166 def nextset(self):1167 """Skip to the next available recordset, discarding any remaining rows from the current recordset.1168 1169 If there are no more sets, the method returns None. Otherwise, it returns a true1170 value and subsequent calls to the fetch methods will return rows from the next result set.1171 1172 An Error (or subclass) exception is raised if the previous call to executeXXX()1173 did not produce any result set or no call was issued yet.1174 """1175 self.messages = []1176 if self.connection is None or self.rs is None:1177 self._raiseCursorError(1178 api.OperationalError,1179 ("nextset() on closed connection or empty query set"),1180 )1181 return None1182 1183 if api.onIronPython:1184 try:1185 recordset = self.rs.NextRecordset()1186 except TypeError:1187 recordset = None1188 except api.Error as exc:1189 self._raiseCursorError(api.NotSupportedError, exc.args)1190 else: # pywin321191 try: # [begin 2.1 ekelund]1192 rsTuple = self.rs.NextRecordset() #1193 except pywintypes.com_error as exc: # return appropriate error1194 self._raiseCursorError(1195 api.NotSupportedError, exc.args1196 ) # [end 2.1 ekelund]1197 recordset = rsTuple[0]1198 if recordset is None:1199 return None1200 self.build_column_info(recordset)