Shortcuts on this page
r m x toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1# engine/result.py
2# Copyright (C) 2005-2021 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: http://www.opensource.org/licenses/mit-license.php
8"""Define result set constructs including :class:`_engine.ResultProxy`
9and :class:`.RowProxy`."""
12import collections
13import operator
15from .. import exc
16from .. import util
17from ..sql import expression
18from ..sql import sqltypes
19from ..sql import util as sql_util
22# This reconstructor is necessary so that pickles with the C extension or
23# without use the same Binary format.
24try:
25 # We need a different reconstructor on the C extension so that we can
26 # add extra checks that fields have correctly been initialized by
27 # __setstate__.
28 from sqlalchemy.cresultproxy import safe_rowproxy_reconstructor
30 # The extra function embedding is needed so that the
31 # reconstructor function has the same signature whether or not
32 # the extension is present.
33 def rowproxy_reconstructor(cls, state):
34 return safe_rowproxy_reconstructor(cls, state)
37except ImportError:
39 def rowproxy_reconstructor(cls, state):
40 obj = cls.__new__(cls)
41 obj.__setstate__(state)
42 return obj
45try:
46 from sqlalchemy.cresultproxy import BaseRowProxy
48 _baserowproxy_usecext = True
49except ImportError:
50 _baserowproxy_usecext = False
52 class BaseRowProxy(object):
53 __slots__ = ("_parent", "_row", "_processors", "_keymap")
55 def __init__(self, parent, row, processors, keymap):
56 """RowProxy objects are constructed by ResultProxy objects."""
58 self._parent = parent
59 self._row = row
60 self._processors = processors
61 self._keymap = keymap
63 def __reduce__(self):
64 return (
65 rowproxy_reconstructor,
66 (self.__class__, self.__getstate__()),
67 )
69 def values(self):
70 """Return the values represented by this RowProxy as a list."""
71 return list(self)
73 def __iter__(self):
74 for processor, value in zip(self._processors, self._row):
75 if processor is None:
76 yield value
77 else:
78 yield processor(value)
80 def __len__(self):
81 return len(self._row)
83 def __getitem__(self, key):
84 try:
85 processor, obj, index = self._keymap[key]
86 except KeyError as err:
87 processor, obj, index = self._parent._key_fallback(key, err)
88 except TypeError:
89 if isinstance(key, slice):
90 l = []
91 for processor, value in zip(
92 self._processors[key], self._row[key]
93 ):
94 if processor is None:
95 l.append(value)
96 else:
97 l.append(processor(value))
98 return tuple(l)
99 else:
100 raise
101 if index is None:
102 raise exc.InvalidRequestError(
103 "Ambiguous column name '%s' in "
104 "result set column descriptions" % obj
105 )
106 if processor is not None:
107 return processor(self._row[index])
108 else:
109 return self._row[index]
111 def __getattr__(self, name):
112 try:
113 return self[name]
114 except KeyError as e:
115 util.raise_(AttributeError(e.args[0]), replace_context=e)
118class RowProxy(BaseRowProxy):
119 """Represent a single result row.
121 The :class:`.RowProxy` object is retrieved from a database result, from the
122 :class:`_engine.ResultProxy` object using methods like
123 :meth:`_engine.ResultProxy.fetchall`.
125 The :class:`.RowProxy` object seeks to act mostly like a Python named
126 tuple, but also provides some Python dictionary behaviors at the same time.
128 .. seealso::
130 :ref:`coretutorial_selecting` - includes examples of selecting
131 rows from SELECT statements.
133 """
135 __slots__ = ()
137 def __contains__(self, key):
138 return self._parent._has_key(key)
140 def __getstate__(self):
141 return {"_parent": self._parent, "_row": tuple(self)}
143 def __setstate__(self, state):
144 self._parent = parent = state["_parent"]
145 self._row = state["_row"]
146 self._processors = parent._processors
147 self._keymap = parent._keymap
149 __hash__ = None
151 def _op(self, other, op):
152 return (
153 op(tuple(self), tuple(other))
154 if isinstance(other, RowProxy)
155 else op(tuple(self), other)
156 )
158 def __lt__(self, other):
159 return self._op(other, operator.lt)
161 def __le__(self, other):
162 return self._op(other, operator.le)
164 def __ge__(self, other):
165 return self._op(other, operator.ge)
167 def __gt__(self, other):
168 return self._op(other, operator.gt)
170 def __eq__(self, other):
171 return self._op(other, operator.eq)
173 def __ne__(self, other):
174 return self._op(other, operator.ne)
176 def __repr__(self):
177 return repr(sql_util._repr_row(self))
179 def has_key(self, key):
180 """Return True if this :class:`.RowProxy` contains the given key.
182 Through the SQLAlchemy 1.x series, the ``__contains__()`` method
183 of :class:`.RowProxy` also links to :meth:`.RowProxy.has_key`, in that
184 an expression such as ::
186 "some_col" in row
188 Will return True if the row contains a column named ``"some_col"``,
189 in the way that a Python mapping works.
191 However, it is planned that the 2.0 series of SQLAlchemy will reverse
192 this behavior so that ``__contains__()`` will refer to a value being
193 present in the row, in the way that a Python tuple works.
195 """
197 return self._parent._has_key(key)
199 def items(self):
200 """Return a list of tuples, each tuple containing a key/value pair.
202 This method is analogous to the Python dictionary ``.items()`` method,
203 except that it returns a list, not an iterator.
205 """
207 return [(key, self[key]) for key in self.keys()]
209 def keys(self):
210 """Return the list of keys as strings represented by this
211 :class:`.RowProxy`.
213 This method is analogous to the Python dictionary ``.keys()`` method,
214 except that it returns a list, not an iterator.
216 """
218 return self._parent.keys
220 def iterkeys(self):
221 """Return a an iterator against the :meth:`.RowProxy.keys` method.
223 This method is analogous to the Python-2-only dictionary
224 ``.iterkeys()`` method.
226 """
227 return iter(self._parent.keys)
229 def itervalues(self):
230 """Return a an iterator against the :meth:`.RowProxy.values` method.
232 This method is analogous to the Python-2-only dictionary
233 ``.itervalues()`` method.
235 """
236 return iter(self)
238 def values(self):
239 """Return the values represented by this :class:`.RowProxy` as a list.
241 This method is analogous to the Python dictionary ``.values()`` method,
242 except that it returns a list, not an iterator.
244 """
245 return super(RowProxy, self).values()
248try:
249 # Register RowProxy with Sequence,
250 # so sequence protocol is implemented
251 util.collections_abc.Sequence.register(RowProxy)
252except ImportError:
253 pass
256class ResultMetaData(object):
257 """Handle cursor.description, applying additional info from an execution
258 context."""
260 __slots__ = (
261 "_keymap",
262 "case_sensitive",
263 "matched_on_name",
264 "_processors",
265 "keys",
266 "_orig_processors",
267 )
269 def __init__(self, parent, cursor_description):
270 context = parent.context
271 dialect = context.dialect
272 self.case_sensitive = dialect.case_sensitive
273 self.matched_on_name = False
274 self._orig_processors = None
276 if context.result_column_struct:
277 (
278 result_columns,
279 cols_are_ordered,
280 textual_ordered,
281 ) = context.result_column_struct
282 num_ctx_cols = len(result_columns)
283 else:
284 result_columns = (
285 cols_are_ordered
286 ) = num_ctx_cols = textual_ordered = False
288 # merge cursor.description with the column info
289 # present in the compiled structure, if any
290 raw = self._merge_cursor_description(
291 context,
292 cursor_description,
293 result_columns,
294 num_ctx_cols,
295 cols_are_ordered,
296 textual_ordered,
297 )
299 self._keymap = {}
300 if not _baserowproxy_usecext:
301 # keymap indexes by integer index: this is only used
302 # in the pure Python BaseRowProxy.__getitem__
303 # implementation to avoid an expensive
304 # isinstance(key, util.int_types) in the most common
305 # case path
307 len_raw = len(raw)
309 self._keymap.update(
310 [(elem[0], (elem[3], elem[4], elem[0])) for elem in raw]
311 + [
312 (elem[0] - len_raw, (elem[3], elem[4], elem[0]))
313 for elem in raw
314 ]
315 )
317 # processors in key order for certain per-row
318 # views like __iter__ and slices
319 self._processors = [elem[3] for elem in raw]
321 # keymap by primary string...
322 by_key = dict([(elem[2], (elem[3], elem[4], elem[0])) for elem in raw])
324 # for compiled SQL constructs, copy additional lookup keys into
325 # the key lookup map, such as Column objects, labels,
326 # column keys and other names
327 if num_ctx_cols:
329 # if by-primary-string dictionary smaller (or bigger?!) than
330 # number of columns, assume we have dupes, rewrite
331 # dupe records with "None" for index which results in
332 # ambiguous column exception when accessed.
333 if len(by_key) != num_ctx_cols:
334 seen = set()
335 for rec in raw:
336 key = rec[1]
337 if key in seen:
338 # this is an "ambiguous" element, replacing
339 # the full record in the map
340 key = key.lower() if not self.case_sensitive else key
341 by_key[key] = (None, key, None)
342 seen.add(key)
344 # copy secondary elements from compiled columns
345 # into self._keymap, write in the potentially "ambiguous"
346 # element
347 self._keymap.update(
348 [
349 (obj_elem, by_key[elem[2]])
350 for elem in raw
351 if elem[4]
352 for obj_elem in elem[4]
353 ]
354 )
356 # if we did a pure positional match, then reset the
357 # original "expression element" back to the "unambiguous"
358 # entry. This is a new behavior in 1.1 which impacts
359 # TextAsFrom but also straight compiled SQL constructs.
360 if not self.matched_on_name:
361 self._keymap.update(
362 [
363 (elem[4][0], (elem[3], elem[4], elem[0]))
364 for elem in raw
365 if elem[4]
366 ]
367 )
368 else:
369 # no dupes - copy secondary elements from compiled
370 # columns into self._keymap
371 self._keymap.update(
372 [
373 (obj_elem, (elem[3], elem[4], elem[0]))
374 for elem in raw
375 if elem[4]
376 for obj_elem in elem[4]
377 ]
378 )
380 # update keymap with primary string names taking
381 # precedence
382 self._keymap.update(by_key)
384 # update keymap with "translated" names (sqlite-only thing)
385 if not num_ctx_cols and context._translate_colname:
386 self._keymap.update(
387 [(elem[5], self._keymap[elem[2]]) for elem in raw if elem[5]]
388 )
390 def _merge_cursor_description(
391 self,
392 context,
393 cursor_description,
394 result_columns,
395 num_ctx_cols,
396 cols_are_ordered,
397 textual_ordered,
398 ):
399 """Merge a cursor.description with compiled result column information.
401 There are at least four separate strategies used here, selected
402 depending on the type of SQL construct used to start with.
404 The most common case is that of the compiled SQL expression construct,
405 which generated the column names present in the raw SQL string and
406 which has the identical number of columns as were reported by
407 cursor.description. In this case, we assume a 1-1 positional mapping
408 between the entries in cursor.description and the compiled object.
409 This is also the most performant case as we disregard extracting /
410 decoding the column names present in cursor.description since we
411 already have the desired name we generated in the compiled SQL
412 construct.
414 The next common case is that of the completely raw string SQL,
415 such as passed to connection.execute(). In this case we have no
416 compiled construct to work with, so we extract and decode the
417 names from cursor.description and index those as the primary
418 result row target keys.
420 The remaining fairly common case is that of the textual SQL
421 that includes at least partial column information; this is when
422 we use a :class:`.TextAsFrom` construct. This construct may have
423 unordered or ordered column information. In the ordered case, we
424 merge the cursor.description and the compiled construct's information
425 positionally, and warn if there are additional description names
426 present, however we still decode the names in cursor.description
427 as we don't have a guarantee that the names in the columns match
428 on these. In the unordered case, we match names in cursor.description
429 to that of the compiled construct based on name matching.
430 In both of these cases, the cursor.description names and the column
431 expression objects and names are indexed as result row target keys.
433 The final case is much less common, where we have a compiled
434 non-textual SQL expression construct, but the number of columns
435 in cursor.description doesn't match what's in the compiled
436 construct. We make the guess here that there might be textual
437 column expressions in the compiled construct that themselves include
438 a comma in them causing them to split. We do the same name-matching
439 as with textual non-ordered columns.
441 The name-matched system of merging is the same as that used by
442 SQLAlchemy for all cases up through te 0.9 series. Positional
443 matching for compiled SQL expressions was introduced in 1.0 as a
444 major performance feature, and positional matching for textual
445 :class:`.TextAsFrom` objects in 1.1. As name matching is no longer
446 a common case, it was acceptable to factor it into smaller generator-
447 oriented methods that are easier to understand, but incur slightly
448 more performance overhead.
450 """
452 case_sensitive = context.dialect.case_sensitive
454 if (
455 num_ctx_cols
456 and cols_are_ordered
457 and not textual_ordered
458 and num_ctx_cols == len(cursor_description)
459 ):
460 self.keys = [elem[0] for elem in result_columns]
461 # pure positional 1-1 case; doesn't need to read
462 # the names from cursor.description
463 return [
464 (
465 idx,
466 key,
467 name.lower() if not case_sensitive else name,
468 context.get_result_processor(
469 type_, key, cursor_description[idx][1]
470 ),
471 obj,
472 None,
473 )
474 for idx, (key, name, obj, type_) in enumerate(result_columns)
475 ]
476 else:
477 # name-based or text-positional cases, where we need
478 # to read cursor.description names
479 if textual_ordered:
480 # textual positional case
481 raw_iterator = self._merge_textual_cols_by_position(
482 context, cursor_description, result_columns
483 )
484 elif num_ctx_cols:
485 # compiled SQL with a mismatch of description cols
486 # vs. compiled cols, or textual w/ unordered columns
487 raw_iterator = self._merge_cols_by_name(
488 context, cursor_description, result_columns
489 )
490 else:
491 # no compiled SQL, just a raw string
492 raw_iterator = self._merge_cols_by_none(
493 context, cursor_description
494 )
496 return [
497 (
498 idx,
499 colname,
500 colname,
501 context.get_result_processor(
502 mapped_type, colname, coltype
503 ),
504 obj,
505 untranslated,
506 )
507 for (
508 idx,
509 colname,
510 mapped_type,
511 coltype,
512 obj,
513 untranslated,
514 ) in raw_iterator
515 ]
517 def _colnames_from_description(self, context, cursor_description):
518 """Extract column names and data types from a cursor.description.
520 Applies unicode decoding, column translation, "normalization",
521 and case sensitivity rules to the names based on the dialect.
523 """
525 dialect = context.dialect
526 case_sensitive = dialect.case_sensitive
527 translate_colname = context._translate_colname
528 description_decoder = (
529 dialect._description_decoder
530 if dialect.description_encoding
531 else None
532 )
533 normalize_name = (
534 dialect.normalize_name if dialect.requires_name_normalize else None
535 )
536 untranslated = None
538 self.keys = []
540 for idx, rec in enumerate(cursor_description):
541 colname = rec[0]
542 coltype = rec[1]
544 if description_decoder:
545 colname = description_decoder(colname)
547 if translate_colname:
548 colname, untranslated = translate_colname(colname)
550 if normalize_name:
551 colname = normalize_name(colname)
553 self.keys.append(colname)
554 if not case_sensitive:
555 colname = colname.lower()
557 yield idx, colname, untranslated, coltype
559 def _merge_textual_cols_by_position(
560 self, context, cursor_description, result_columns
561 ):
562 num_ctx_cols = len(result_columns) if result_columns else None
564 if num_ctx_cols > len(cursor_description):
565 util.warn(
566 "Number of columns in textual SQL (%d) is "
567 "smaller than number of columns requested (%d)"
568 % (num_ctx_cols, len(cursor_description))
569 )
570 seen = set()
571 for (
572 idx,
573 colname,
574 untranslated,
575 coltype,
576 ) in self._colnames_from_description(context, cursor_description):
577 if idx < num_ctx_cols:
578 ctx_rec = result_columns[idx]
579 obj = ctx_rec[2]
580 mapped_type = ctx_rec[3]
581 if obj[0] in seen:
582 raise exc.InvalidRequestError(
583 "Duplicate column expression requested "
584 "in textual SQL: %r" % obj[0]
585 )
586 seen.add(obj[0])
587 else:
588 mapped_type = sqltypes.NULLTYPE
589 obj = None
591 yield idx, colname, mapped_type, coltype, obj, untranslated
593 def _merge_cols_by_name(self, context, cursor_description, result_columns):
594 dialect = context.dialect
595 case_sensitive = dialect.case_sensitive
596 result_map = self._create_result_map(result_columns, case_sensitive)
598 self.matched_on_name = True
599 for (
600 idx,
601 colname,
602 untranslated,
603 coltype,
604 ) in self._colnames_from_description(context, cursor_description):
605 try:
606 ctx_rec = result_map[colname]
607 except KeyError:
608 mapped_type = sqltypes.NULLTYPE
609 obj = None
610 else:
611 obj = ctx_rec[1]
612 mapped_type = ctx_rec[2]
613 yield idx, colname, mapped_type, coltype, obj, untranslated
615 def _merge_cols_by_none(self, context, cursor_description):
616 for (
617 idx,
618 colname,
619 untranslated,
620 coltype,
621 ) in self._colnames_from_description(context, cursor_description):
622 yield idx, colname, sqltypes.NULLTYPE, coltype, None, untranslated
624 @classmethod
625 def _create_result_map(cls, result_columns, case_sensitive=True):
626 d = {}
627 for elem in result_columns:
628 key, rec = elem[0], elem[1:]
629 if not case_sensitive:
630 key = key.lower()
631 if key in d:
632 # conflicting keyname, just double up the list
633 # of objects. this will cause an "ambiguous name"
634 # error if an attempt is made by the result set to
635 # access.
636 e_name, e_obj, e_type = d[key]
637 d[key] = e_name, e_obj + rec[1], e_type
638 else:
639 d[key] = rec
640 return d
642 def _key_fallback(self, key, err, raiseerr=True):
643 map_ = self._keymap
644 result = None
645 if isinstance(key, util.string_types):
646 result = map_.get(key if self.case_sensitive else key.lower())
647 # fallback for targeting a ColumnElement to a textual expression
648 # this is a rare use case which only occurs when matching text()
649 # or colummn('name') constructs to ColumnElements, or after a
650 # pickle/unpickle roundtrip
651 elif isinstance(key, expression.ColumnElement):
652 if (
653 key._label
654 and (key._label if self.case_sensitive else key._label.lower())
655 in map_
656 ):
657 result = map_[
658 key._label if self.case_sensitive else key._label.lower()
659 ]
660 elif (
661 hasattr(key, "name")
662 and (key.name if self.case_sensitive else key.name.lower())
663 in map_
664 ):
665 # match is only on name.
666 result = map_[
667 key.name if self.case_sensitive else key.name.lower()
668 ]
669 # search extra hard to make sure this
670 # isn't a column/label name overlap.
671 # this check isn't currently available if the row
672 # was unpickled.
673 if result is not None and result[1] is not None:
674 for obj in result[1]:
675 if key._compare_name_for_result(obj):
676 break
677 else:
678 result = None
679 if result is None:
680 if raiseerr:
681 util.raise_(
682 exc.NoSuchColumnError(
683 "Could not locate column in row for column '%s'"
684 % expression._string_or_unprintable(key)
685 ),
686 replace_context=err,
687 )
688 else:
689 return None
690 else:
691 map_[key] = result
692 return result
694 def _has_key(self, key):
695 if key in self._keymap:
696 return True
697 else:
698 return self._key_fallback(key, None, False) is not None
700 def _getter(self, key, raiseerr=True):
701 if key in self._keymap:
702 processor, obj, index = self._keymap[key]
703 else:
704 ret = self._key_fallback(key, None, raiseerr)
705 if ret is None:
706 return None
707 processor, obj, index = ret
709 if index is None:
710 util.raise_(
711 exc.InvalidRequestError(
712 "Ambiguous column name '%s' in "
713 "result set column descriptions" % obj
714 ),
715 from_=None,
716 )
718 return operator.itemgetter(index)
720 def __getstate__(self):
721 return {
722 "_pickled_keymap": dict(
723 (key, index)
724 for key, (processor, obj, index) in self._keymap.items()
725 if isinstance(key, util.string_types + util.int_types)
726 ),
727 "keys": self.keys,
728 "case_sensitive": self.case_sensitive,
729 "matched_on_name": self.matched_on_name,
730 }
732 def __setstate__(self, state):
733 # the row has been processed at pickling time so we don't need any
734 # processor anymore
735 self._processors = [None for _ in range(len(state["keys"]))]
736 self._keymap = keymap = {}
737 for key, index in state["_pickled_keymap"].items():
738 # not preserving "obj" here, unfortunately our
739 # proxy comparison fails with the unpickle
740 keymap[key] = (None, None, index)
741 self.keys = state["keys"]
742 self.case_sensitive = state["case_sensitive"]
743 self.matched_on_name = state["matched_on_name"]
746class ResultProxy(object):
747 """A facade around a DBAPI cursor object.
749 Returns database rows via the :class:`.RowProxy` class, which provides
750 additional API features and behaviors on top of the raw data returned
751 by the DBAPI.
753 .. seealso::
755 :ref:`coretutorial_selecting` - introductory material for accessing
756 :class:`_engine.ResultProxy` and :class:`.RowProxy` objects.
758 """
760 _process_row = RowProxy
761 out_parameters = None
762 _autoclose_connection = False
763 _metadata = None
764 _soft_closed = False
765 closed = False
767 def __init__(self, context):
768 self.context = context
769 self.dialect = context.dialect
770 self.cursor = self._saved_cursor = context.cursor
771 self.connection = context.root_connection
772 self._echo = (
773 self.connection._echo and context.engine._should_log_debug()
774 )
775 self._init_metadata()
777 def _getter(self, key, raiseerr=True):
778 try:
779 getter = self._metadata._getter
780 except AttributeError as err:
781 return self._non_result(None, err)
782 else:
783 return getter(key, raiseerr)
785 def _has_key(self, key):
786 try:
787 has_key = self._metadata._has_key
788 except AttributeError as err:
789 return self._non_result(None, err)
790 else:
791 return has_key(key)
793 def _init_metadata(self):
794 cursor_description = self._cursor_description()
795 if cursor_description is not None:
796 if (
797 self.context.compiled
798 and "compiled_cache" in self.context.execution_options
799 ):
800 if self.context.compiled._cached_metadata:
801 self._metadata = self.context.compiled._cached_metadata
802 else:
803 self._metadata = (
804 self.context.compiled._cached_metadata
805 ) = ResultMetaData(self, cursor_description)
806 else:
807 self._metadata = ResultMetaData(self, cursor_description)
808 if self._echo:
809 self.context.engine.logger.debug(
810 "Col %r", tuple(x[0] for x in cursor_description)
811 )
813 def keys(self):
814 """Return the list of string keys that would represented by each
815 :class:`.RowProxy`."""
817 if self._metadata:
818 return self._metadata.keys
819 else:
820 return []
822 @util.memoized_property
823 def rowcount(self):
824 """Return the 'rowcount' for this result.
826 The 'rowcount' reports the number of rows *matched*
827 by the WHERE criterion of an UPDATE or DELETE statement.
829 .. note::
831 Notes regarding :attr:`_engine.ResultProxy.rowcount`:
834 * This attribute returns the number of rows *matched*,
835 which is not necessarily the same as the number of rows
836 that were actually *modified* - an UPDATE statement, for example,
837 may have no net change on a given row if the SET values
838 given are the same as those present in the row already.
839 Such a row would be matched but not modified.
840 On backends that feature both styles, such as MySQL,
841 rowcount is configured by default to return the match
842 count in all cases.
844 * :attr:`_engine.ResultProxy.rowcount`
845 is *only* useful in conjunction
846 with an UPDATE or DELETE statement. Contrary to what the Python
847 DBAPI says, it does *not* return the
848 number of rows available from the results of a SELECT statement
849 as DBAPIs cannot support this functionality when rows are
850 unbuffered.
852 * :attr:`_engine.ResultProxy.rowcount`
853 may not be fully implemented by
854 all dialects. In particular, most DBAPIs do not support an
855 aggregate rowcount result from an executemany call.
856 The :meth:`_engine.ResultProxy.supports_sane_rowcount` and
857 :meth:`_engine.ResultProxy.supports_sane_multi_rowcount` methods
858 will report from the dialect if each usage is known to be
859 supported.
861 * Statements that use RETURNING may not return a correct
862 rowcount.
864 """
865 try:
866 return self.context.rowcount
867 except BaseException as e:
868 self.connection._handle_dbapi_exception(
869 e, None, None, self.cursor, self.context
870 )
872 @property
873 def lastrowid(self):
874 """Return the 'lastrowid' accessor on the DBAPI cursor.
876 This is a DBAPI specific method and is only functional
877 for those backends which support it, for statements
878 where it is appropriate. It's behavior is not
879 consistent across backends.
881 Usage of this method is normally unnecessary when
882 using insert() expression constructs; the
883 :attr:`~ResultProxy.inserted_primary_key` attribute provides a
884 tuple of primary key values for a newly inserted row,
885 regardless of database backend.
887 """
888 try:
889 return self._saved_cursor.lastrowid
890 except BaseException as e:
891 self.connection._handle_dbapi_exception(
892 e, None, None, self._saved_cursor, self.context
893 )
895 @property
896 def returns_rows(self):
897 """True if this :class:`_engine.ResultProxy` returns rows.
899 I.e. if it is legal to call the methods
900 :meth:`_engine.ResultProxy.fetchone`,
901 :meth:`_engine.ResultProxy.fetchmany`
902 :meth:`_engine.ResultProxy.fetchall`.
904 """
905 return self._metadata is not None
907 @property
908 def is_insert(self):
909 """True if this :class:`_engine.ResultProxy` is the result
910 of a executing an expression language compiled
911 :func:`_expression.insert` construct.
913 When True, this implies that the
914 :attr:`inserted_primary_key` attribute is accessible,
915 assuming the statement did not include
916 a user defined "returning" construct.
918 """
919 return self.context.isinsert
921 def _cursor_description(self):
922 """May be overridden by subclasses."""
924 return self._saved_cursor.description
926 def _soft_close(self):
927 """Soft close this :class:`_engine.ResultProxy`.
929 This releases all DBAPI cursor resources, but leaves the
930 ResultProxy "open" from a semantic perspective, meaning the
931 fetchXXX() methods will continue to return empty results.
933 This method is called automatically when:
935 * all result rows are exhausted using the fetchXXX() methods.
936 * cursor.description is None.
938 This method is **not public**, but is documented in order to clarify
939 the "autoclose" process used.
941 .. versionadded:: 1.0.0
943 .. seealso::
945 :meth:`_engine.ResultProxy.close`
948 """
949 if self._soft_closed:
950 return
951 self._soft_closed = True
952 cursor = self.cursor
953 self.connection._safe_close_cursor(cursor)
954 if self._autoclose_connection:
955 self.connection.close()
956 self.cursor = None
958 def close(self):
959 """Close this ResultProxy.
961 This closes out the underlying DBAPI cursor corresponding
962 to the statement execution, if one is still present. Note that the
963 DBAPI cursor is automatically released when the
964 :class:`_engine.ResultProxy`
965 exhausts all available rows. :meth:`_engine.ResultProxy.close`
966 is generally
967 an optional method except in the case when discarding a
968 :class:`_engine.ResultProxy`
969 that still has additional rows pending for fetch.
971 In the case of a result that is the product of
972 :ref:`connectionless execution <dbengine_implicit>`,
973 the underlying :class:`_engine.Connection` object is also closed,
974 which
975 :term:`releases` DBAPI connection resources.
977 After this method is called, it is no longer valid to call upon
978 the fetch methods, which will raise a :class:`.ResourceClosedError`
979 on subsequent use.
981 .. versionchanged:: 1.0.0 - the :meth:`_engine.ResultProxy.close`
982 method
983 has been separated out from the process that releases the underlying
984 DBAPI cursor resource. The "auto close" feature of the
985 :class:`_engine.Connection` now performs a so-called "soft close",
986 which
987 releases the underlying DBAPI cursor, but allows the
988 :class:`_engine.ResultProxy`
989 to still behave as an open-but-exhausted
990 result set; the actual :meth:`_engine.ResultProxy.close`
991 method is never
992 called. It is still safe to discard a
993 :class:`_engine.ResultProxy`
994 that has been fully exhausted without calling this method.
996 .. seealso::
998 :ref:`connections_toplevel`
1000 """
1002 if not self.closed:
1003 self._soft_close()
1004 self.closed = True
1006 def __iter__(self):
1007 """Implement iteration protocol."""
1009 while True:
1010 row = self.fetchone()
1011 if row is None:
1012 return
1013 else:
1014 yield row
1016 def __next__(self):
1017 """Implement the Python next() protocol.
1019 This method, mirrored as both ``.next()`` and ``.__next__()``, is part
1020 of Python's API for producing iterator-like behavior.
1022 .. versionadded:: 1.2
1024 """
1025 row = self.fetchone()
1026 if row is None:
1027 raise StopIteration()
1028 else:
1029 return row
1031 next = __next__
1033 @util.memoized_property
1034 def inserted_primary_key(self):
1035 """Return the primary key for the row just inserted.
1037 The return value is a list of scalar values
1038 corresponding to the list of primary key columns
1039 in the target table.
1041 This only applies to single row :func:`_expression.insert`
1042 constructs which did not explicitly specify
1043 :meth:`_expression.Insert.returning`.
1045 Note that primary key columns which specify a
1046 server_default clause,
1047 or otherwise do not qualify as "autoincrement"
1048 columns (see the notes at :class:`_schema.Column`), and were
1049 generated using the database-side default, will
1050 appear in this list as ``None`` unless the backend
1051 supports "returning" and the insert statement executed
1052 with the "implicit returning" enabled.
1054 Raises :class:`~sqlalchemy.exc.InvalidRequestError` if the executed
1055 statement is not a compiled expression construct
1056 or is not an insert() construct.
1058 """
1060 if not self.context.compiled:
1061 raise exc.InvalidRequestError(
1062 "Statement is not a compiled " "expression construct."
1063 )
1064 elif not self.context.isinsert:
1065 raise exc.InvalidRequestError(
1066 "Statement is not an insert() " "expression construct."
1067 )
1068 elif self.context._is_explicit_returning:
1069 raise exc.InvalidRequestError(
1070 "Can't call inserted_primary_key "
1071 "when returning() "
1072 "is used."
1073 )
1075 return self.context.inserted_primary_key
1077 def last_updated_params(self):
1078 """Return the collection of updated parameters from this
1079 execution.
1081 Raises :class:`~sqlalchemy.exc.InvalidRequestError` if the executed
1082 statement is not a compiled expression construct
1083 or is not an update() construct.
1085 """
1086 if not self.context.compiled:
1087 raise exc.InvalidRequestError(
1088 "Statement is not a compiled " "expression construct."
1089 )
1090 elif not self.context.isupdate:
1091 raise exc.InvalidRequestError(
1092 "Statement is not an update() " "expression construct."
1093 )
1094 elif self.context.executemany:
1095 return self.context.compiled_parameters
1096 else:
1097 return self.context.compiled_parameters[0]
1099 def last_inserted_params(self):
1100 """Return the collection of inserted parameters from this
1101 execution.
1103 Raises :class:`~sqlalchemy.exc.InvalidRequestError` if the executed
1104 statement is not a compiled expression construct
1105 or is not an insert() construct.
1107 """
1108 if not self.context.compiled:
1109 raise exc.InvalidRequestError(
1110 "Statement is not a compiled " "expression construct."
1111 )
1112 elif not self.context.isinsert:
1113 raise exc.InvalidRequestError(
1114 "Statement is not an insert() " "expression construct."
1115 )
1116 elif self.context.executemany:
1117 return self.context.compiled_parameters
1118 else:
1119 return self.context.compiled_parameters[0]
1121 @property
1122 def returned_defaults(self):
1123 """Return the values of default columns that were fetched using
1124 the :meth:`.ValuesBase.return_defaults` feature.
1126 The value is an instance of :class:`.RowProxy`, or ``None``
1127 if :meth:`.ValuesBase.return_defaults` was not used or if the
1128 backend does not support RETURNING.
1130 .. versionadded:: 0.9.0
1132 .. seealso::
1134 :meth:`.ValuesBase.return_defaults`
1136 """
1137 return self.context.returned_defaults
1139 def lastrow_has_defaults(self):
1140 """Return ``lastrow_has_defaults()`` from the underlying
1141 :class:`.ExecutionContext`.
1143 See :class:`.ExecutionContext` for details.
1145 """
1147 return self.context.lastrow_has_defaults()
1149 def postfetch_cols(self):
1150 """Return ``postfetch_cols()`` from the underlying
1151 :class:`.ExecutionContext`.
1153 See :class:`.ExecutionContext` for details.
1155 Raises :class:`~sqlalchemy.exc.InvalidRequestError` if the executed
1156 statement is not a compiled expression construct
1157 or is not an insert() or update() construct.
1159 """
1161 if not self.context.compiled:
1162 raise exc.InvalidRequestError(
1163 "Statement is not a compiled " "expression construct."
1164 )
1165 elif not self.context.isinsert and not self.context.isupdate:
1166 raise exc.InvalidRequestError(
1167 "Statement is not an insert() or update() "
1168 "expression construct."
1169 )
1170 return self.context.postfetch_cols
1172 def prefetch_cols(self):
1173 """Return ``prefetch_cols()`` from the underlying
1174 :class:`.ExecutionContext`.
1176 See :class:`.ExecutionContext` for details.
1178 Raises :class:`~sqlalchemy.exc.InvalidRequestError` if the executed
1179 statement is not a compiled expression construct
1180 or is not an insert() or update() construct.
1182 """
1184 if not self.context.compiled:
1185 raise exc.InvalidRequestError(
1186 "Statement is not a compiled " "expression construct."
1187 )
1188 elif not self.context.isinsert and not self.context.isupdate:
1189 raise exc.InvalidRequestError(
1190 "Statement is not an insert() or update() "
1191 "expression construct."
1192 )
1193 return self.context.prefetch_cols
1195 def supports_sane_rowcount(self):
1196 """Return ``supports_sane_rowcount`` from the dialect.
1198 See :attr:`_engine.ResultProxy.rowcount` for background.
1200 """
1202 return self.dialect.supports_sane_rowcount
1204 def supports_sane_multi_rowcount(self):
1205 """Return ``supports_sane_multi_rowcount`` from the dialect.
1207 See :attr:`_engine.ResultProxy.rowcount` for background.
1209 """
1211 return self.dialect.supports_sane_multi_rowcount
1213 def _fetchone_impl(self):
1214 try:
1215 return self.cursor.fetchone()
1216 except AttributeError as err:
1217 return self._non_result(None, err)
1219 def _fetchmany_impl(self, size=None):
1220 try:
1221 if size is None:
1222 return self.cursor.fetchmany()
1223 else:
1224 return self.cursor.fetchmany(size)
1225 except AttributeError as err:
1226 return self._non_result([], err)
1228 def _fetchall_impl(self):
1229 try:
1230 return self.cursor.fetchall()
1231 except AttributeError as err:
1232 return self._non_result([], err)
1234 def _non_result(self, default, err=None):
1235 if self._metadata is None:
1236 util.raise_(
1237 exc.ResourceClosedError(
1238 "This result object does not return rows. "
1239 "It has been closed automatically."
1240 ),
1241 replace_context=err,
1242 )
1243 elif self.closed:
1244 util.raise_(
1245 exc.ResourceClosedError("This result object is closed."),
1246 replace_context=err,
1247 )
1248 else:
1249 return default
1251 def process_rows(self, rows):
1252 process_row = self._process_row
1253 metadata = self._metadata
1254 keymap = metadata._keymap
1255 processors = metadata._processors
1256 if self._echo:
1257 log = self.context.engine.logger.debug
1258 l = []
1259 for row in rows:
1260 log("Row %r", sql_util._repr_row(row))
1261 l.append(process_row(metadata, row, processors, keymap))
1262 return l
1263 else:
1264 return [
1265 process_row(metadata, row, processors, keymap) for row in rows
1266 ]
1268 def fetchall(self):
1269 """Fetch all rows, just like DB-API ``cursor.fetchall()``.
1271 After all rows have been exhausted, the underlying DBAPI
1272 cursor resource is released, and the object may be safely
1273 discarded.
1275 Subsequent calls to :meth:`_engine.ResultProxy.fetchall` will return
1276 an empty list. After the :meth:`_engine.ResultProxy.close` method is
1277 called, the method will raise :class:`.ResourceClosedError`.
1279 :return: a list of :class:`.RowProxy` objects
1281 """
1283 try:
1284 l = self.process_rows(self._fetchall_impl())
1285 self._soft_close()
1286 return l
1287 except BaseException as e:
1288 self.connection._handle_dbapi_exception(
1289 e, None, None, self.cursor, self.context
1290 )
1292 def fetchmany(self, size=None):
1293 """Fetch many rows, just like DB-API
1294 ``cursor.fetchmany(size=cursor.arraysize)``.
1296 After all rows have been exhausted, the underlying DBAPI
1297 cursor resource is released, and the object may be safely
1298 discarded.
1300 Calls to :meth:`_engine.ResultProxy.fetchmany`
1301 after all rows have been
1302 exhausted will return
1303 an empty list. After the :meth:`_engine.ResultProxy.close` method is
1304 called, the method will raise :class:`.ResourceClosedError`.
1306 :return: a list of :class:`.RowProxy` objects
1308 """
1310 try:
1311 l = self.process_rows(self._fetchmany_impl(size))
1312 if len(l) == 0:
1313 self._soft_close()
1314 return l
1315 except BaseException as e:
1316 self.connection._handle_dbapi_exception(
1317 e, None, None, self.cursor, self.context
1318 )
1320 def fetchone(self):
1321 """Fetch one row, just like DB-API ``cursor.fetchone()``.
1323 After all rows have been exhausted, the underlying DBAPI
1324 cursor resource is released, and the object may be safely
1325 discarded.
1327 Calls to :meth:`_engine.ResultProxy.fetchone` after all rows have
1328 been exhausted will return ``None``.
1329 After the :meth:`_engine.ResultProxy.close` method is
1330 called, the method will raise :class:`.ResourceClosedError`.
1332 :return: a :class:`.RowProxy` object, or None if no rows remain
1334 """
1335 try:
1336 row = self._fetchone_impl()
1337 if row is not None:
1338 return self.process_rows([row])[0]
1339 else:
1340 self._soft_close()
1341 return None
1342 except BaseException as e:
1343 self.connection._handle_dbapi_exception(
1344 e, None, None, self.cursor, self.context
1345 )
1347 def first(self):
1348 """Fetch the first row and then close the result set unconditionally.
1350 After calling this method, the object is fully closed,
1351 e.g. the :meth:`_engine.ResultProxy.close`
1352 method will have been called.
1354 :return: a :class:`.RowProxy` object, or None if no rows remain
1356 """
1357 if self._metadata is None:
1358 return self._non_result(None)
1360 try:
1361 row = self._fetchone_impl()
1362 except BaseException as e:
1363 self.connection._handle_dbapi_exception(
1364 e, None, None, self.cursor, self.context
1365 )
1367 try:
1368 if row is not None:
1369 return self.process_rows([row])[0]
1370 else:
1371 return None
1372 finally:
1373 self.close()
1375 def scalar(self):
1376 """Fetch the first column of the first row, and close the result set.
1378 After calling this method, the object is fully closed,
1379 e.g. the :meth:`_engine.ResultProxy.close`
1380 method will have been called.
1382 :return: a Python scalar value , or None if no rows remain
1384 """
1385 row = self.first()
1386 if row is not None:
1387 return row[0]
1388 else:
1389 return None
1392class BufferedRowResultProxy(ResultProxy):
1393 """A ResultProxy with row buffering behavior.
1395 ``ResultProxy`` that buffers the contents of a selection of rows
1396 before ``fetchone()`` is called. This is to allow the results of
1397 ``cursor.description`` to be available immediately, when
1398 interfacing with a DB-API that requires rows to be consumed before
1399 this information is available (currently psycopg2, when used with
1400 server-side cursors).
1402 The pre-fetching behavior fetches only one row initially, and then
1403 grows its buffer size by a fixed amount with each successive need
1404 for additional rows up to a size of 1000.
1406 The size argument is configurable using the ``max_row_buffer``
1407 execution option::
1409 with psycopg2_engine.connect() as conn:
1411 result = conn.execution_options(
1412 stream_results=True, max_row_buffer=50
1413 ).execute("select * from table")
1415 .. versionadded:: 1.0.6 Added the ``max_row_buffer`` option.
1417 .. seealso::
1419 :ref:`psycopg2_execution_options`
1420 """
1422 def _init_metadata(self):
1423 self._max_row_buffer = self.context.execution_options.get(
1424 "max_row_buffer", None
1425 )
1426 self.__buffer_rows()
1427 super(BufferedRowResultProxy, self)._init_metadata()
1429 # this is a "growth chart" for the buffering of rows.
1430 # each successive __buffer_rows call will use the next
1431 # value in the list for the buffer size until the max
1432 # is reached
1433 size_growth = {
1434 1: 5,
1435 5: 10,
1436 10: 20,
1437 20: 50,
1438 50: 100,
1439 100: 250,
1440 250: 500,
1441 500: 1000,
1442 }
1444 def __buffer_rows(self):
1445 if self.cursor is None:
1446 return
1447 size = getattr(self, "_bufsize", 1)
1448 self.__rowbuffer = collections.deque(self.cursor.fetchmany(size))
1449 self._bufsize = self.size_growth.get(size, size)
1450 if self._max_row_buffer is not None:
1451 self._bufsize = min(self._max_row_buffer, self._bufsize)
1453 def _soft_close(self, **kw):
1454 self.__rowbuffer.clear()
1455 super(BufferedRowResultProxy, self)._soft_close(**kw)
1457 def _fetchone_impl(self):
1458 if self.cursor is None:
1459 return self._non_result(None)
1460 if not self.__rowbuffer:
1461 self.__buffer_rows()
1462 if not self.__rowbuffer:
1463 return None
1464 return self.__rowbuffer.popleft()
1466 def _fetchmany_impl(self, size=None):
1467 if size is None:
1468 return self._fetchall_impl()
1469 result = []
1470 for x in range(0, size):
1471 row = self._fetchone_impl()
1472 if row is None:
1473 break
1474 result.append(row)
1475 return result
1477 def _fetchall_impl(self):
1478 if self.cursor is None:
1479 return self._non_result([])
1480 self.__rowbuffer.extend(self.cursor.fetchall())
1481 ret = self.__rowbuffer
1482 self.__rowbuffer = collections.deque()
1483 return ret
1486class FullyBufferedResultProxy(ResultProxy):
1487 """A result proxy that buffers rows fully upon creation.
1489 Used for operations where a result is to be delivered
1490 after the database conversation can not be continued,
1491 such as MSSQL INSERT...OUTPUT after an autocommit.
1493 """
1495 def _init_metadata(self):
1496 super(FullyBufferedResultProxy, self)._init_metadata()
1497 self.__rowbuffer = self._buffer_rows()
1499 def _buffer_rows(self):
1500 return collections.deque(self.cursor.fetchall())
1502 def _soft_close(self, **kw):
1503 self.__rowbuffer.clear()
1504 super(FullyBufferedResultProxy, self)._soft_close(**kw)
1506 def _fetchone_impl(self):
1507 if self.__rowbuffer:
1508 return self.__rowbuffer.popleft()
1509 else:
1510 return self._non_result(None)
1512 def _fetchmany_impl(self, size=None):
1513 if size is None:
1514 return self._fetchall_impl()
1515 result = []
1516 for x in range(0, size):
1517 row = self._fetchone_impl()
1518 if row is None:
1519 break
1520 result.append(row)
1521 return result
1523 def _fetchall_impl(self):
1524 if not self.cursor:
1525 return self._non_result([])
1526 ret = self.__rowbuffer
1527 self.__rowbuffer = collections.deque()
1528 return ret
1531class BufferedColumnRow(RowProxy):
1532 def __init__(self, parent, row, processors, keymap):
1533 # preprocess row
1534 row = list(row)
1535 # this is a tad faster than using enumerate
1536 index = 0
1537 for processor in parent._orig_processors:
1538 if processor is not None:
1539 row[index] = processor(row[index])
1540 index += 1
1541 row = tuple(row)
1542 super(BufferedColumnRow, self).__init__(
1543 parent, row, processors, keymap
1544 )
1547class BufferedColumnResultProxy(ResultProxy):
1548 """A ResultProxy with column buffering behavior.
1550 ``ResultProxy`` that loads all columns into memory each time
1551 fetchone() is called. If fetchmany() or fetchall() are called,
1552 the full grid of results is fetched. This is to operate with
1553 databases where result rows contain "live" results that fall out
1554 of scope unless explicitly fetched.
1556 .. versionchanged:: 1.2 This :class:`_engine.ResultProxy` is not used by
1557 any SQLAlchemy-included dialects.
1559 """
1561 _process_row = BufferedColumnRow
1563 def _init_metadata(self):
1564 super(BufferedColumnResultProxy, self)._init_metadata()
1566 metadata = self._metadata
1568 # don't double-replace the processors, in the case
1569 # of a cached ResultMetaData
1570 if metadata._orig_processors is None:
1571 # orig_processors will be used to preprocess each row when
1572 # they are constructed.
1573 metadata._orig_processors = metadata._processors
1574 # replace the all type processors by None processors.
1575 metadata._processors = [None for _ in range(len(metadata.keys))]
1576 keymap = {}
1577 for k, (func, obj, index) in metadata._keymap.items():
1578 keymap[k] = (None, obj, index)
1579 metadata._keymap = keymap
1581 def fetchall(self):
1582 # can't call cursor.fetchall(), since rows must be
1583 # fully processed before requesting more from the DBAPI.
1584 l = []
1585 while True:
1586 row = self.fetchone()
1587 if row is None:
1588 break
1589 l.append(row)
1590 return l
1592 def fetchmany(self, size=None):
1593 # can't call cursor.fetchmany(), since rows must be
1594 # fully processed before requesting more from the DBAPI.
1595 if size is None:
1596 return self.fetchall()
1597 l = []
1598 for i in range(size):
1599 row = self.fetchone()
1600 if row is None:
1601 break
1602 l.append(row)
1603 return l