1 """adodbapi - A python DB API 2.0 (PEP 249) interface to Microsoft ADO 2 3 Copyright (C) 2002 Henrik Ekelund, versions 2.1 and later by Vernon Cole 4 * https://sourceforge.net/projects/pywin32 5 * https://github.com/mhammond/pywin32 6 * https://sourceforge.net/projects/adodbapi 7 8 This library is free software; you can redistribute it and/or 9 modify it under the terms of the GNU Lesser General Public 10 License as published by the Free Software Foundation; either 11 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 of 15 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU 16 Lesser General Public License for more details. 17 18 You should have received a copy of the GNU Lesser General Public 19 License along with this library; if not, write to the Free Software 20 Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA 21 22 django adaptations and refactoring by Adam Vandenberg 23 24 DB-API 2.0 specification: https://peps.python.org/pep-0249/ 25 26 This module source should run correctly in CPython versions 2.7 and later, 27 or CPython 3.4 or later. 28 """ 29 30 __version__ = "2.6.2.0" 31 version = "adodbapi v" + __version__ 32 33 import copy 34 import decimal 35 import os 36 import sys 37 import weakref 38 39 from . import ado_consts as adc, apibase as api, process_connect_string 40 41 try: 42 verbose = int(os.environ["ADODBAPI_VERBOSE"]) 43 except: 44 verbose = False 45 if verbose: 46 print(version) 47 48 try: 49 import pythoncom 50 import pywintypes 51 from win32com.client import Dispatch 52 except ImportError: 53 import warnings 54 55 warnings.warn("pywin32 package required for adodbapi.", ImportWarning) 56 57 58 def getIndexedValue(obj, index): 59 return obj(index) 60 61 62 from collections.abc import Mapping 63 64 65 # ----------------- The .connect method ----------------- 66 def make_COM_connecter(): 67 try: 68 pythoncom.CoInitialize() # v2.1 Paj 69 c = Dispatch("ADODB.Connection") # connect _after_ CoInitialize v2.1.1 adamvan 70 except: 71 raise api.InterfaceError( 72 "Windows COM Error: Dispatch('ADODB.Connection') failed." 73 ) 74 return c 75 76 77 def connect(*args, **kwargs): # --> a db-api connection object 78 """Connect to a database. 79 80 call using: 81 :connection_string -- An ADODB formatted connection string, see: 82 * https://www.connectionstrings.com 83 * https://www.codeguru.com/dotnet/whats-in-an-ado-connection-string/ 84 * https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/connection-strings 85 :timeout -- A command timeout value, in seconds (default 30 seconds) 86 """ 87 co = Connection() # make an empty connection object 88 89 kwargs = process_connect_string.process(args, kwargs, True) 90 91 try: # connect to the database, using the connection information in kwargs 92 co.connect(kwargs) 93 return co 94 except Exception as e: 95 message = 'Error opening connection to "%s"' % co.connection_string 96 raise api.OperationalError(e, message) 97 98 99 # so you could use something like: 100 # myConnection.paramstyle = 'named' 101 # The programmer may also change the default. 102 # For example, if I were using django, I would say: 103 # import adodbapi as Database 104 # Database.adodbapi.paramstyle = 'format' 105 106 # ------- other module level defaults -------- 107 defaultIsolationLevel = adc.adXactReadCommitted 108 # Set defaultIsolationLevel on module level before creating the connection. 109 # For example: 110 # import adodbapi, ado_consts 111 # adodbapi.adodbapi.defaultIsolationLevel=ado_consts.adXactBrowse" 112 # 113 # Set defaultCursorLocation on module level before creating the connection. 114 # It may be one of the "adUse..." consts. 115 defaultCursorLocation = adc.adUseClient # changed from adUseServer as of v 2.3.0 116 117 dateconverter = api.pythonDateTimeConverter() # default 118 119 120 def format_parameters(ADOparameters, show_value=False): 121 """Format a collection of ADO Command Parameters. 122 123 Used by error reporting in _execute_command. 124 """ 125 try: 126 if show_value: 127 desc = [ 128 'Name: %s, Dir.: %s, Type: %s, Size: %s, Value: "%s", Precision: %s, NumericScale: %s' 129 % ( 130 p.Name, 131 adc.directions[p.Direction], 132 adc.adTypeNames.get(p.Type, str(p.Type) + " (unknown type)"), 133 p.Size, 134 p.Value, 135 p.Precision, 136 p.NumericScale, 137 ) 138 for p in ADOparameters 139 ] 140 else: 141 desc = [ 142 "Name: %s, Dir.: %s, Type: %s, Size: %s, Precision: %s, NumericScale: %s" 143 % ( 144 p.Name, 145 adc.directions[p.Direction], 146 adc.adTypeNames.get(p.Type, str(p.Type) + " (unknown type)"), 147 p.Size, 148 p.Precision, 149 p.NumericScale, 150 ) 151 for p in ADOparameters 152 ] 153 return "[" + "\n".join(desc) + "]" 154 except: 155 return "[]" 156 157 158 def _configure_parameter(p, value, adotype, settings_known): 159 """Configure the given ADO Parameter 'p' with the Python 'value'.""" 160 161 if adotype in api.adoBinaryTypes: 162 p.Size = len(value) 163 p.AppendChunk(value) 164 165 elif isinstance(value, str): # v2.1 Jevon 166 length = len(value) 167 if adotype in api.adoStringTypes: # v2.2.1 Cole 168 if settings_known: 169 length = min(length, p.Size) # v2.1 Cole limit data to defined size 170 p.Value = value[:length] # v2.1 Jevon & v2.1 Cole 171 else: 172 p.Value = value # don't limit if db column is numeric 173 if length > 0: # v2.1 Cole something does not like p.Size as Zero 174 p.Size = length # v2.1 Jevon 175 176 elif isinstance(value, decimal.Decimal): 177 p.Value = value 178 exponent = value.as_tuple()[2] 179 digit_count = len(value.as_tuple()[1]) 180 p.Precision = digit_count 181 if exponent == 0: 182 p.NumericScale = 0 183 elif exponent < 0: 184 p.NumericScale = -exponent 185 if p.Precision < p.NumericScale: 186 p.Precision = p.NumericScale 187 else: # exponent > 0: 188 p.NumericScale = 0 189 p.Precision = digit_count + exponent 190 191 elif type(value) in dateconverter.types: 192 if settings_known and adotype in api.adoDateTimeTypes: 193 p.Value = dateconverter.COMDate(value) 194 else: # probably a string 195 # provide the date as a string in the format 'YYYY-MM-dd' 196 s = dateconverter.DateObjectToIsoFormatString(value) 197 p.Value = s 198 p.Size = len(s) 199 200 elif adotype == adc.adEmpty: # ADO will not let you specify a null column 201 p.Type = ( 202 adc.adInteger 203 ) # so we will fake it to be an integer (just to have something) 204 p.Value = None # and pass in a Null *value* 205 206 # For any other type, set the value and let pythoncom do the right thing. 207 else: 208 p.Value = value 209 210 211 # # # # # ----- the Class that defines a connection ----- # # # # # 212 class Connection: 213 # include connection attributes as class attributes required by api definition. 214 Warning = api.Warning 215 Error = api.Error 216 InterfaceError = api.InterfaceError 217 DataError = api.DataError 218 DatabaseError = api.DatabaseError 219 OperationalError = api.OperationalError 220 IntegrityError = api.IntegrityError 221 InternalError = api.InternalError 222 NotSupportedError = api.NotSupportedError 223 ProgrammingError = api.ProgrammingError 224 FetchFailedError = api.FetchFailedError # (special for django) 225 # ...class attributes... (can be overridden by instance attributes) 226 verbose = api.verbose 227 228 @property 229 def dbapi(self): # a proposed db-api version 3 extension. 230 "Return a reference to the DBAPI module for this Connection." 231 return api 232 233 def __init__(self): # now define the instance attributes 234 self.connector = None 235 self.paramstyle = api.paramstyle 236 self.supportsTransactions = False 237 self.connection_string = "" 238 self.cursors = weakref.WeakValueDictionary[int, Cursor]() 239 self.dbms_name = "" 240 self.dbms_version = "" 241 self.errorhandler = None # use the standard error handler for this instance 242 self.transaction_level = 0 # 0 == Not in a transaction, at the top level 243 self._autocommit = False 244 245 def connect(self, kwargs, connection_maker=make_COM_connecter): 246 if verbose > 9: 247 print(f"kwargs={kwargs!r}") 248 try: 249 self.connection_string = ( 250 kwargs["connection_string"] % kwargs 251 ) # insert keyword arguments 252 except Exception as e: 253 self._raiseConnectionError( 254 KeyError, "Python string format error in connection string->" 255 ) 256 self.timeout = kwargs.get("timeout", 30) 257 self.mode = kwargs.get("mode", adc.adModeUnknown) 258 self.kwargs = kwargs 259 if verbose: 260 print('%s attempting: "%s"' % (version, self.connection_string)) 261 self.connector = connection_maker() 262 self.connector.ConnectionTimeout = self.timeout 263 self.connector.ConnectionString = self.connection_string 264 self.connector.Mode = self.mode 265 266 try: 267 self.connector.Open() # Open the ADO connection 268 except api.Error: 269 self._raiseConnectionError( 270 api.DatabaseError, 271 "ADO error trying to Open=%s" % self.connection_string, 272 ) 273 274 try: # Stefan Fuchs; support WINCCOLEDBProvider 275 if getIndexedValue(self.connector.Properties, "Transaction DDL").Value != 0: 276 self.supportsTransactions = True 277 except pywintypes.com_error: 278 pass # Stefan Fuchs 279 self.dbms_name = getIndexedValue(self.connector.Properties, "DBMS Name").Value 280 try: # Stefan Fuchs 281 self.dbms_version = getIndexedValue( 282 self.connector.Properties, "DBMS Version" 283 ).Value 284 except pywintypes.com_error: 285 pass # Stefan Fuchs 286 self.connector.CursorLocation = defaultCursorLocation # v2.1 Rose 287 if self.supportsTransactions: 288 self.connector.IsolationLevel = defaultIsolationLevel 289 self._autocommit = bool(kwargs.get("autocommit", False)) 290 if not self._autocommit: 291 self.transaction_level = ( 292 self.connector.BeginTrans() 293 ) # Disables autocommit & inits transaction_level 294 else: 295 self._autocommit = True 296 if "paramstyle" in kwargs: 297 self.paramstyle = kwargs["paramstyle"] # let setattr do the error checking 298 self.messages = [] 299 if verbose: 300 print("adodbapi New connection at %X" % id(self)) 301 302 def _raiseConnectionError(self, errorclass, errorvalue): 303 eh = self.errorhandler 304 if eh is None: 305 eh = api.standardErrorHandler 306 eh(self, None, errorclass, errorvalue) 307 308 def _closeAdoConnection(self): # all v2.1 Rose 309 """close the underlying ADO Connection object, 310 rolling it back first if it supports transactions.""" 311 if self.connector is None: 312 return 313 if not self._autocommit: 314 if self.transaction_level: 315 try: 316 self.connector.RollbackTrans() 317 except: 318 pass 319 self.connector.Close() 320 if verbose: 321 print("adodbapi Closed connection at %X" % id(self)) 322 323 def close(self): 324 """Close the connection now (rather than whenever __del__ is called). 325 326 The connection will be unusable from this point forward; 327 an Error (or subclass) exception will be raised if any operation is attempted with the connection. 328 The same applies to all cursor objects trying to use the connection. 329 """ 330 for crsr in list(self.cursors.values())[ 331 : 332 ]: # copy the list, then close each one 333 crsr.close(dont_tell_me=True) # close without back-link clearing 334 self.messages = [] 335 try: 336 self._closeAdoConnection() # v2.1 Rose 337 except Exception as e: 338 self._raiseConnectionError(sys.exc_info()[0], sys.exc_info()[1]) 339 340 self.connector = None # v2.4.2.2 fix subtle timeout bug 341 # per M.Hammond: "I expect the benefits of uninitializing are probably fairly small, 342 # so never uninitializing will probably not cause any problems." 343 344 def commit(self): 345 """Commit any pending transaction to the database. 346 347 Note that if the database supports an auto-commit feature, 348 this must be initially off. An interface method may be provided to turn it back on. 349 Database modules that do not support transactions should implement this method with void functionality. 350 """ 351 self.messages = [] 352 if not self.supportsTransactions: 353 return 354 355 try: 356 self.transaction_level = self.connector.CommitTrans() 357 if verbose > 1: 358 print("commit done on connection at %X" % id(self)) 359 if not ( 360 self._autocommit 361 or (self.connector.Attributes & adc.adXactAbortRetaining) 362 ): 363 # If attributes has adXactCommitRetaining it performs retaining commits that is, 364 # calling CommitTrans automatically starts a new transaction. Not all providers support this. 365 # If not, we will have to start a new transaction by this command: 366 self.transaction_level = self.connector.BeginTrans() 367 except Exception as e: 368 self._raiseConnectionError(api.ProgrammingError, e) 369 370 def _rollback(self): 371 """In case a database does provide transactions this method causes the the database to roll back to 372 the start of any pending transaction. Closing a connection without committing the changes first will 373 cause an implicit rollback to be performed. 374 375 If the database does not support the functionality required by the method, the interface should 376 throw an exception in case the method is used. 377 The preferred approach is to not implement the method and thus have Python generate 378 an AttributeError in case the method is requested. This allows the programmer to check for database 379 capabilities using the standard hasattr() function. 380 381 For some dynamically configured interfaces it may not be appropriate to require dynamically making 382 the method available. These interfaces should then raise a NotSupportedError to indicate the 383 non-ability to perform the roll back when the method is invoked. 384 """ 385 self.messages = [] 386 if ( 387 self.transaction_level 388 ): # trying to roll back with no open transaction causes an error 389 try: 390 self.transaction_level = self.connector.RollbackTrans() 391 if verbose > 1: 392 print("rollback done on connection at %X" % id(self)) 393 if not self._autocommit and not ( 394 self.connector.Attributes & adc.adXactAbortRetaining 395 ): 396 # If attributes has adXactAbortRetaining it performs retaining aborts that is, 397 # calling RollbackTrans automatically starts a new transaction. Not all providers support this. 398 # If not, we will have to start a new transaction by this command: 399 if not self.transaction_level: 400 self.transaction_level = self.connector.BeginTrans() 401 except Exception as e: 402 self._raiseConnectionError(api.ProgrammingError, e) 403 404 def __setattr__(self, name, value): 405 if name == "autocommit": # extension: allow user to turn autocommit on or off 406 if self.supportsTransactions: 407 object.__setattr__(self, "_autocommit", bool(value)) 408 try: 409 self._rollback() # must clear any outstanding transactions 410 except: 411 pass 412 return 413 elif name == "paramstyle": 414 if value not in api.accepted_paramstyles: 415 self._raiseConnectionError( 416 api.NotSupportedError, 417 f"paramstyle={value!r} not in:{api.accepted_paramstyles!r}", 418 ) 419 elif name == "variantConversions": 420 # make a new copy -- no changes in the default, please 421 value = copy.copy(value) 422 object.__setattr__(self, name, value) 423 424 def __getattr__(self, item): 425 if ( 426 item == "rollback" 427 ): # the rollback method only appears if the database supports transactions 428 if self.supportsTransactions: 429 return ( 430 self._rollback 431 ) # return the rollback method so the caller can execute it. 432 else: 433 raise AttributeError("this data provider does not support Rollback") 434 elif item == "autocommit": 435 return self._autocommit 436 else: 437 raise AttributeError( 438 'no such attribute in ADO connection object as="%s"' % item 439 ) 440 441 def cursor(self): 442 "Return a new Cursor Object using the connection." 443 self.messages = [] 444 c = Cursor(self) 445 return c 446 447 def _i_am_here(self, crsr): 448 "message from a new cursor proclaiming its existence" 449 oid = id(crsr) 450 self.cursors[oid] = crsr 451 452 def _i_am_closing(self, crsr): 453 "message from a cursor giving connection a chance to clean up" 454 try: 455 del self.cursors[id(crsr)] 456 except: 457 pass 458 459 def printADOerrors(self): 460 j = self.connector.Errors.Count 461 if j: 462 print("ADO Errors:(%i)" % j) 463 for e in self.connector.Errors: 464 print("Description: %s" % e.Description) 465 print("Error: %s %s " % (e.Number, adc.adoErrors.get(e.Number, "unknown"))) 466 if e.Number == adc.ado_error_TIMEOUT: 467 print( 468 "Timeout Error: Try using adodbpi.connect(constr,timeout=Nseconds)" 469 ) 470 print("Source: %s" % e.Source) 471 print("NativeError: %s" % e.NativeError) 472 print("SQL State: %s" % e.SQLState) 473 474 def _suggest_error_class(self): 475 """Introspect the current ADO Errors and determine an appropriate error class. 476 477 Error.SQLState is a SQL-defined error condition, per the SQL specification: 478 https://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt 479 480 The 23000 class of errors are integrity errors. 481 Error 40002 is a transactional integrity error. 482 """ 483 if self.connector is not None: 484 for e in self.connector.Errors: 485 state = str(e.SQLState) 486 if state.startswith("23") or state == "40002": 487 return api.IntegrityError 488 return api.DatabaseError 489 490 def __del__(self): 491 try: 492 self._closeAdoConnection() # v2.1 Rose 493 except: 494 pass 495 self.connector = None 496 497 def __enter__(self): # Connections are context managers 498 return self 499 500 def __exit__(self, exc_type, exc_val, exc_tb): 501 if exc_type: 502 self._rollback() # automatic rollback on errors 503 else: 504 self.commit() 505 506 def get_table_names(self): 507 schema = self.connector.OpenSchema(20) # constant = adSchemaTables 508 509 tables = [] 510 while not schema.EOF: 511 name = getIndexedValue(schema.Fields, "TABLE_NAME").Value 512 tables.append(name) 513 schema.MoveNext() 514 del schema 515 return tables 516 517 518 # # # # # ----- the Class that defines a cursor ----- # # # # # 519 class Cursor: 520 ## ** api required attributes: 521 ## description... 522 ## This read-only attribute is a sequence of 7-item sequences. 523 ## Each of these sequences contains information describing one result column: 524 ## (name, type_code, display_size, internal_size, precision, scale, null_ok). 525 ## This attribute will be None for operations that do not return rows or if the 526 ## cursor has not had an operation invoked via the executeXXX() method yet. 527 ## The type_code can be interpreted by comparing it to the Type Objects specified in the section below. 528 ## rowcount... 529 ## This read-only attribute specifies the number of rows that the last executeXXX() produced 530 ## (for DQL statements like select) or affected (for DML statements like update or insert). 531 ## The attribute is -1 in case no executeXXX() has been performed on the cursor or 532 ## the rowcount of the last operation is not determinable by the interface.[7] 533 ## arraysize... 534 ## This read/write attribute specifies the number of rows to fetch at a time with fetchmany(). 535 ## It defaults to 1 meaning to fetch a single row at a time. 536 ## Implementations must observe this value with respect to the fetchmany() method, 537 ## but are free to interact with the database a single row at a time. 538 ## It may also be used in the implementation of executemany(). 539 ## ** extension attributes: 540 ## paramstyle... 541 ## allows the programmer to override the connection's default paramstyle 542 ## errorhandler... 543 ## allows the programmer to override the connection's default error handler 544 545 def __init__(self, connection): 546 self.command = None 547 self._ado_prepared = False 548 self.messages = [] 549 self.connection = connection 550 self.paramstyle = connection.paramstyle # used for overriding the paramstyle 551 self._parameter_names = [] 552 self.recordset_is_remote = False 553 self.rs = None # the ADO recordset for this cursor 554 self.converters = [] # conversion function for each column 555 self.columnNames = {} # names of columns {lowercase name : number,...} 556 self.numberOfColumns = 0 557 self._description = None 558 self.rowcount = -1 559 self.errorhandler = connection.errorhandler 560 self.arraysize = 1 561 connection._i_am_here(self) 562 if verbose: 563 print( 564 "%s New cursor at %X on conn %X" 565 % (version, id(self), id(self.connection)) 566 ) 567 568 def __iter__(self): # [2.1 Zamarev] 569 return iter(self.fetchone, None) # [2.1 Zamarev] 570 571 def prepare(self, operation): 572 self.command = operation 573 self._description = None 574 self._ado_prepared = "setup" 575 576 def __next__(self): 577 r = self.fetchone() 578 if r: 579 return r 580 raise StopIteration 581 582 def __enter__(self): 583 "Allow database cursors to be used with context managers." 584 return self 585 586 def __exit__(self, exc_type, exc_val, exc_tb): 587 "Allow database cursors to be used with context managers." 588 self.close() 589 590 def _raiseCursorError(self, errorclass, errorvalue): 591 eh = self.errorhandler 592 if eh is None: 593 eh = api.standardErrorHandler 594 eh(self.connection, self, errorclass, errorvalue) 595 596 def build_column_info(self, recordset): 597 self.converters = [] # conversion function for each column 598 self.columnNames = {} # names of columns {lowercase name : number,...} 599 self._description = None 600 601 # if EOF and BOF are true at the same time, there are no records in the recordset 602 if (recordset is None) or (recordset.State == adc.adStateClosed): 603 self.rs = None 604 self.numberOfColumns = 0 605 return 606 self.rs = recordset # v2.1.1 bkline 607 self.recordset_format = api.RS_WIN_32 608 self.numberOfColumns = recordset.Fields.Count 609 try: 610 varCon = self.connection.variantConversions 611 except AttributeError: 612 varCon = api.variantConversions 613 for i in range(self.numberOfColumns): 614 f = getIndexedValue(self.rs.Fields, i) 615 try: 616 self.converters.append( 617 varCon[f.Type] 618 ) # conversion function for this column 619 except KeyError: 620 self._raiseCursorError( 621 api.InternalError, "Data column of Unknown ADO type=%s" % f.Type 622 ) 623 self.columnNames[f.Name.lower()] = i # columnNames lookup 624 625 def _makeDescriptionFromRS(self): 626 # Abort if closed or no recordset. 627 if self.rs is None: 628 self._description = None 629 return 630 desc = [] 631 for i in range(self.numberOfColumns): 632 f = getIndexedValue(self.rs.Fields, i) 633 if self.rs.EOF or self.rs.BOF: 634 display_size = None 635 else: 636 # TODO: Is this the correct defintion according to the DB API 2 Spec ? 637 display_size = f.ActualSize 638 null_ok = bool(f.Attributes & adc.adFldMayBeNull) # v2.1 Cole 639 desc.append( 640 ( 641 f.Name, 642 f.Type, 643 display_size, 644 f.DefinedSize, 645 f.Precision, 646 f.NumericScale, 647 null_ok, 648 ) 649 ) 650 self._description = desc 651 652 def get_description(self): 653 if not self._description: 654 self._makeDescriptionFromRS() 655 return self._description 656 657 def __getattr__(self, item): 658 if item == "description": 659 return self.get_description() 660 object.__getattribute__( 661 self, item 662 ) # may get here on Remote attribute calls for existing attributes 663 664 def format_description(self, d): 665 """Format db_api description tuple for printing.""" 666 if self.description is None: 667 self._makeDescriptionFromRS() 668 if isinstance(d, int): 669 d = self.description[d] 670 desc = ( 671 "Name= %s, Type= %s, DispSize= %s, IntSize= %s, Precision= %s, Scale= %s NullOK=%s" 672 % ( 673 d[0], 674 adc.adTypeNames.get(d[1], str(d[1]) + " (unknown type)"), 675 d[2], 676 d[3], 677 d[4], 678 d[5], 679 d[6], 680 ) 681 ) 682 return desc 683 684 def close(self, dont_tell_me=False): 685 """Close the cursor now (rather than whenever __del__ is called). 686 The cursor will be unusable from this point forward; an Error (or subclass) 687 exception will be raised if any operation is attempted with the cursor. 688 """ 689 if self.connection is None: 690 return 691 self.messages = [] 692 if ( 693 self.rs and self.rs.State != adc.adStateClosed 694 ): # rs exists and is open #v2.1 Rose 695 self.rs.Close() # v2.1 Rose 696 self.rs = None # let go of the recordset so ADO will let it be disposed #v2.1 Rose 697 if not dont_tell_me: 698 self.connection._i_am_closing( 699 self 700 ) # take me off the connection's cursors list 701 self.connection = ( 702 None # this will make all future method calls on me throw an exception 703 ) 704 if verbose: 705 print("adodbapi Closed cursor at %X" % id(self)) 706 707 def __del__(self): 708 try: 709 self.close() 710 except: 711 pass 712 713 def _new_command(self, command_type=adc.adCmdText): 714 self.cmd = None 715 self.messages = [] 716 717 if self.connection is None: 718 self._raiseCursorError(api.InterfaceError, None) 719 return 720 try: 721 self.cmd = Dispatch("ADODB.Command") 722 self.cmd.ActiveConnection = self.connection.connector 723 self.cmd.CommandTimeout = self.connection.timeout 724 self.cmd.CommandType = command_type 725 self.cmd.CommandText = self.commandText 726 self.cmd.Prepared = bool(self._ado_prepared) 727 except: 728 self._raiseCursorError( 729 api.DatabaseError, 730 f"Error creating new ADODB.Command object for {self.commandText!r}", 731 ) 732 733 def _execute_command(self): 734 # Stored procedures may have an integer return value 735 self.return_value = None 736 recordset = None 737 count = -1 # default value 738 if verbose: 739 print('Executing command="%s"' % self.commandText) 740 try: 741 # ----- the actual SQL is executed here --- 742 recordset, count = self.cmd.Execute() 743 # ----- ------------------------------- --- 744 except Exception as e: 745 _message = "" 746 if hasattr(e, "args"): 747 _message += str(e.args) + "\n" 748 _message += "Command:\n%s\nParameters:\n%s" % ( 749 self.commandText, 750 format_parameters(self.cmd.Parameters, True), 751 ) 752 klass = self.connection._suggest_error_class() 753 self._raiseCursorError(klass, _message) 754 try: 755 self.rowcount = recordset.RecordCount 756 except: 757 self.rowcount = count 758 self.build_column_info(recordset) 759 760 # The ADO documentation hints that obtaining the recordcount may be timeconsuming 761 # "If the Recordset object does not support approximate positioning, this property 762 # may be a significant drain on resources # [ekelund] 763 # Therefore, COM will not return rowcount for server-side cursors. [Cole] 764 # Client-side cursors (the default since v2.8) will force a static 765 # cursor, and rowcount will then be set accurately [Cole] 766 767 def get_rowcount(self): 768 return self.rowcount 769 770 def get_returned_parameters(self): 771 """with some providers, returned parameters and the .return_value are not available until 772 after the last recordset has been read. In that case, you must coll nextset() until it 773 returns None, then call this method to get your returned information.""" 774 775 # store procedures may return altered parameters, including an added "return value" item 776 retLst = [] 777 for p in tuple(self.cmd.Parameters): 778 if verbose > 2: 779 print( 780 'Returned=Name: %s, Dir.: %s, Type: %s, Size: %s, Value: "%s",' 781 " Precision: %s, NumericScale: %s" 782 % ( 783 p.Name, 784 adc.directions[p.Direction], 785 adc.adTypeNames.get(p.Type, str(p.Type) + " (unknown type)"), 786 p.Size, 787 p.Value, 788 p.Precision, 789 p.NumericScale, 790 ) 791 ) 792 pyObject = api.convert_to_python(p.Value, api.variantConversions[p.Type]) 793 if p.Direction == adc.adParamReturnValue: 794 self.returnValue = ( 795 pyObject # also load the undocumented attribute (Vernon's Error!) 796 ) 797 self.return_value = pyObject 798 else: 799 retLst.append(pyObject) 800 return retLst # return the parameter list to the caller 801 802 def callproc(self, procname, parameters=None): 803 """Call a stored database procedure with the given name. 804 The sequence of parameters must contain one entry for each 805 argument that the sproc expects. The result of the 806 call is returned as modified copy of the input 807 sequence. Input parameters are left untouched, output and 808 input/output parameters replaced with possibly new values. 809 810 The sproc may also provide a result set as output, 811 which is available through the standard .fetch*() methods. 812 Extension: A "return_value" property may be set on the 813 cursor if the sproc defines an integer return value. 814 """ 815 self._parameter_names = [] 816 self.commandText = procname 817 self._new_command(command_type=adc.adCmdStoredProc) 818 self._buildADOparameterList(parameters, sproc=True) 819 if verbose > 2: 820 print( 821 "Calling Stored Proc with Params=", 822 format_parameters(self.cmd.Parameters, True), 823 ) 824 self._execute_command() 825 return self.get_returned_parameters() 826 827 def _reformat_operation(self, operation, parameters): 828 if self.paramstyle in ("format", "pyformat"): # convert %s to ? 829 operation, self._parameter_names = api.changeFormatToQmark(operation) 830 elif self.paramstyle == "named" or ( 831 self.paramstyle == "dynamic" and isinstance(parameters, Mapping) 832 ): 833 operation, self._parameter_names = api.changeNamedToQmark( 834 operation 835 ) # convert :name to ? 836 return operation 837 838 def _buildADOparameterList(self, parameters, sproc=False): 839 self.parameters = parameters 840 if parameters is None: 841 parameters = [] 842 843 # Note: ADO does not preserve the parameter list, even if "Prepared" is True, so we must build every time. 844 parameters_known = False 845 if sproc: # needed only if we are calling a stored procedure 846 try: # attempt to use ADO's parameter list 847 self.cmd.Parameters.Refresh() 848 if verbose > 2: 849 print( 850 "ADO detected Params=", 851 format_parameters(self.cmd.Parameters, True), 852 ) 853 print(f"Program Parameters={parameters!r}") 854 parameters_known = True 855 except api.Error: 856 if verbose: 857 print("ADO Parameter Refresh failed") 858 pass 859 else: 860 if len(parameters) != self.cmd.Parameters.Count - 1: 861 raise api.ProgrammingError( 862 "You must supply %d parameters for this stored procedure" 863 % (self.cmd.Parameters.Count - 1) 864 ) 865 if sproc or parameters != []: 866 i = 0 867 if parameters_known: # use ado parameter list 868 if self._parameter_names: # named parameters 869 for i, pm_name in enumerate(self._parameter_names): 870 p = getIndexedValue(self.cmd.Parameters, i) 871 try: 872 _configure_parameter( 873 p, parameters[pm_name], p.Type, parameters_known 874 ) 875 except Exception as e: 876 _message = "Error Converting Parameter {}: {}, {} <- {!r}\n".format( 877 p.Name, 878 adc.ado_type_name(p.Type), 879 p.Value, 880 parameters[pm_name], 881 ) 882 self._raiseCursorError( 883 api.DataError, f"{_message}->{e.args!r}" 884 ) 885 else: # regular sequence of parameters 886 for value in parameters: 887 p = getIndexedValue(self.cmd.Parameters, i) 888 if ( 889 p.Direction == adc.adParamReturnValue 890 ): # this is an extra parameter added by ADO 891 i += 1 # skip the extra 892 p = getIndexedValue(self.cmd.Parameters, i) 893 try: 894 _configure_parameter(p, value, p.Type, parameters_known) 895 except Exception as e: 896 _message = "Error Converting Parameter {}: {}, {} <- {!r}\n".format( 897 p.Name, 898 adc.ado_type_name(p.Type), 899 p.Value, 900 value, 901 ) 902 self._raiseCursorError( 903 api.DataError, f"{_message}->{e.args!r}" 904 ) 905 i += 1 906 else: # -- build own parameter list 907 # we expect a dictionary of parameters, this is the list of expected names 908 if self._parameter_names: 909 for parm_name in self._parameter_names: 910 elem = parameters[parm_name] 911 adotype = api.pyTypeToADOType(elem) 912 p = self.cmd.CreateParameter( 913 parm_name, adotype, adc.adParamInput 914 ) 915 _configure_parameter(p, elem, adotype, parameters_known) 916 try: 917 self.cmd.Parameters.Append(p) 918 except Exception as e: 919 _message = ( 920 "Error Building Parameter {}: {}, {} <- {!r}\n".format( 921 p.Name, 922 adc.ado_type_name(p.Type), 923 p.Value, 924 elem, 925 ) 926 ) 927 self._raiseCursorError( 928 api.DataError, f"{_message}->{e.args!r}" 929 ) 930 else: # expecting the usual sequence of parameters 931 if sproc: 932 p = self.cmd.CreateParameter( 933 "@RETURN_VALUE", adc.adInteger, adc.adParamReturnValue 934 ) 935 self.cmd.Parameters.Append(p) 936 937 for elem in parameters: 938 name = "p%i" % i 939 adotype = api.pyTypeToADOType(elem) 940 p = self.cmd.CreateParameter( 941 name, adotype, adc.adParamInput 942 ) # Name, Type, Direction, Size, Value 943 _configure_parameter(p, elem, adotype, parameters_known) 944 try: 945 self.cmd.Parameters.Append(p) 946 except Exception as e: 947 _message = ( 948 "Error Building Parameter {}: {}, {} <- {!r}\n".format( 949 p.Name, 950 adc.ado_type_name(p.Type), 951 p.Value, 952 elem, 953 ) 954 ) 955 self._raiseCursorError( 956 api.DataError, f"{_message}->{e.args!r}" 957 ) 958 i += 1 959 if self._ado_prepared == "setup": 960 self._ado_prepared = ( 961 True # parameters will be "known" by ADO next loop 962 ) 963 964 def execute(self, operation, parameters=None): 965 """Prepare and execute a database operation (query or command). 966 967 Parameters may be provided as sequence or mapping and will be bound to variables in the operation. 968 Variables are specified in a database-specific notation 969 (see the module's paramstyle attribute for details). [5] 970 A reference to the operation will be retained by the cursor. 971 If the same operation object is passed in again, then the cursor 972 can optimize its behavior. This is most effective for algorithms 973 where the same operation is used, but different parameters are bound to it (many times). 974 975 For maximum efficiency when reusing an operation, it is best to use 976 the setinputsizes() method to specify the parameter types and sizes ahead of time. 977 It is legal for a parameter to not match the predefined information; 978 the implementation should compensate, possibly with a loss of efficiency. 979 980 The parameters may also be specified as list of tuples to e.g. insert multiple rows in 981 a single operation, but this kind of usage is depreciated: executemany() should be used instead. 982 983 Return value is not defined. 984 985 [5] The module will use the __getitem__ method of the parameters object to map either positions 986 (integers) or names (strings) to parameter values. This allows for both sequences and mappings 987 to be used as input. 988 The term "bound" refers to the process of binding an input value to a database execution buffer. 989 In practical terms, this means that the input value is directly used as a value in the operation. 990 The client should not be required to "escape" the value so that it can be used -- the value 991 should be equal to the actual database value.""" 992 if ( 993 self.command is not operation 994 or self._ado_prepared == "setup" 995 or not hasattr(self, "commandText") 996 ): 997 if self.command is not operation: 998 self._ado_prepared = False 999 self.command = operation 1000 self._parameter_names = [] 1001 self.commandText = ( 1002 operation 1003 if (self.paramstyle == "qmark" or not parameters) 1004 else self._reformat_operation(operation, parameters) 1005 ) 1006 self._new_command() 1007 self._buildADOparameterList(parameters) 1008 if verbose > 3: 1009 print("Params=", format_parameters(self.cmd.Parameters, True)) 1010 self._execute_command() 1011 1012 def executemany(self, operation, seq_of_parameters): 1013 """Prepare a database operation (query or command) 1014 and then execute it against all parameter sequences or mappings found in the sequence seq_of_parameters. 1015 1016 Return values are not defined. 1017 """ 1018 self.messages = list() 1019 total_recordcount = 0 1020 1021 self.prepare(operation) 1022 for params in seq_of_parameters: 1023 self.execute(self.command, params) 1024 if self.rowcount == -1: 1025 total_recordcount = -1 1026 if total_recordcount != -1: 1027 total_recordcount += self.rowcount 1028 self.rowcount = total_recordcount 1029 1030 def _fetch(self, limit=None): 1031 """Fetch rows from the current recordset. 1032 1033 limit -- Number of rows to fetch, or None (default) to fetch all rows. 1034 """ 1035 if self.connection is None or self.rs is None: 1036 self._raiseCursorError( 1037 api.FetchFailedError, "fetch() on closed connection or empty query set" 1038 ) 1039 return 1040 1041 if self.rs.State == adc.adStateClosed or self.rs.BOF or self.rs.EOF: 1042 return list() 1043 if limit: # limit number of rows retrieved 1044 ado_results = self.rs.GetRows(limit) 1045 else: # get all rows 1046 ado_results = self.rs.GetRows() 1047 if ( 1048 self.recordset_format == api.RS_ARRAY 1049 ): # result of GetRows is a two-dimension array 1050 length = ( 1051 len(ado_results) // self.numberOfColumns 1052 ) # length of first dimension 1053 else: # pywin32 1054 length = len(ado_results[0]) # result of GetRows is tuples in a tuple 1055 fetchObject = api.SQLrows( 1056 ado_results, length, self 1057 ) # new object to hold the results of the fetch 1058 return fetchObject 1059 1060 def fetchone(self): 1061 """Fetch the next row of a query result set, returning a single sequence, 1062 or None when no more data is available. 1063 1064 An Error (or subclass) exception is raised if the previous call to executeXXX() 1065 did not produce any result set or no call was issued yet. 1066 """ 1067 self.messages = [] 1068 result = self._fetch(1) 1069 if result: # return record (not list of records) 1070 return result[0] 1071 return None 1072 1073 def fetchmany(self, size=None): 1074 """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. 1075 1076 The number of rows to fetch per call is specified by the parameter. 1077 If it is not given, the cursor's arraysize determines the number of rows to be fetched. 1078 The method should try to fetch as many rows as indicated by the size parameter. 1079 If this is not possible due to the specified number of rows not being available, 1080 fewer rows may be returned. 1081 1082 An Error (or subclass) exception is raised if the previous call to executeXXX() 1083 did not produce any result set or no call was issued yet. 1084 1085 Note there are performance considerations involved with the size parameter. 1086 For optimal performance, it is usually best to use the arraysize attribute. 1087 If the size parameter is used, then it is best for it to retain the same value from 1088 one fetchmany() call to the next. 1089 """ 1090 self.messages = [] 1091 if size is None: 1092 size = self.arraysize 1093 return self._fetch(size) 1094 1095 def fetchall(self): 1096 """Fetch all (remaining) rows of a query result, returning them as a sequence of sequences (e.g. a list of tuples). 1097 1098 Note that the cursor's arraysize attribute 1099 can affect the performance of this operation. 1100 An Error (or subclass) exception is raised if the previous call to executeXXX() 1101 did not produce any result set or no call was issued yet. 1102 """ 1103 self.messages = [] 1104 return self._fetch() 1105 1106 def nextset(self): 1107 """Skip to the next available recordset, discarding any remaining rows from the current recordset. 1108 1109 If there are no more sets, the method returns None. Otherwise, it returns a true 1110 value and subsequent calls to the fetch methods will return rows from the next result set. 1111 1112 An Error (or subclass) exception is raised if the previous call to executeXXX() 1113 did not produce any result set or no call was issued yet. 1114 """ 1115 self.messages = [] 1116 if self.connection is None or self.rs is None: 1117 self._raiseCursorError( 1118 api.OperationalError, 1119 ("nextset() on closed connection or empty query set"), 1120 ) 1121 return None 1122 1123 try: # [begin 2.1 ekelund] 1124 rsTuple = self.rs.NextRecordset() # 1125 except pywintypes.com_error as exc: # return appropriate error 1126 self._raiseCursorError(api.NotSupportedError, exc.args) # [end 2.1 ekelund] 1127 recordset = rsTuple[0] 1128 if recordset is None: 1129 return None 1130 self.build_column_info(recordset) 1131 return True 1132 1133 def setinputsizes(self, sizes): 1134 pass 1135 1136 def setoutputsize(self, size, column=None): 1137 pass 1138 1139 def _last_query(self): # let the programmer see what query we actually used 1140 try: 1141 if self.parameters is None: 1142 ret = self.commandText 1143 else: 1144 ret = f"{self.commandText},parameters={self.parameters!r}" 1145 except: 1146 ret = None 1147 return ret 1148 1149 query = property(_last_query, None, None, "returns the last query executed") 1150 1151 1152 if __name__ == "__main__": 1153 raise api.ProgrammingError(version + " cannot be run as a main program.") 1154