Implement a Record object

[anthony-tuininga]

Discussed in #301

^{Originally posted by syniex February 27, 2024}
Currently, when retrieving results from a database, many of us construct dictionaries from lists of tuples to provide a more convenient API for working with the data. This approach is common but might not be the most efficient.

We could create a Record class in Cython that encapsulates the data returned from the database. This class would allow for easier access to column values and could potentially offer performance benefits over the current approach of using dictionaries.

asyncpg Record API Documentation

[syniex]

@anthony-tuininga what are your thoughts about this?

[anthony-tuininga]

I am still thinking about it. I need some time to determine how much of a performance hit this would incur. Assuming that is too much of a hit I have some other thoughts on how to allow for an easy opt-in for those who want it -- but I need some time to do some research on these matters. I haven't forgotten about it, though!

[doerwalter]

ll.orasql uses the following classes for that:

class Record(tuple, abc.Mapping):
	"""
	A :class:`Record` is a subclass of :class:`tuple` that is used for storing
	results of database fetches and procedure and function calls. Both item and
	attribute access (i.e. :meth:`__getitem__` and :meth:`__getattr__`) are
	available. Field names are case insensitive.
	"""

	def __new__(cls, index2name, name2index, values):
		record = tuple.__new__(cls, values)
		record._index2name = index2name
		record._name2index = name2index
		return record

	def __getitem__(self, arg):
		if isinstance(arg, str):
			arg = self._name2index[arg.lower()]
		return tuple.__getitem__(self, arg)

	def __getattr__(self, name):
		try:
			index = self._name2index[name.lower()]
		except KeyError:
			raise AttributeError(f"{self.__class__.__module__}.{self.__class__.__qualname__} object has no attribute {name!r}")
		return tuple.__getitem__(self, index)

	def get(self, name, default=None):
		"""
		Return the value for the field named ``name``. If this field doesn't
		exist in ``self``, return ``default`` instead.
		"""
		try:
			index = self._name2index[name.lower()]
		except KeyError:
			return default
		return tuple.__getitem__(self, index)

	def __contains__(self, name):
		return name.lower() in self._name2index

	def keys(self):
		"""
		Return an iterator over field names.
		"""
		return iter(self._index2name)

	def items(self):
		"""
		Return an iterator over (field name, field value) tuples.
		"""
		for (index, key) in enumerate(self._index2name):
			yield (key, tuple.__getitem__(self, index))

	def replace(self, **kwargs):
		"""
		Return a new :class:`Record` with the same fields as ``self``, except
		for those fields given new values by whichever keyword arguments are
		specified.
		"""
		values = list(self)
		for (key, value) in kwargs.items():
			values[self._name2index[key.lower()]] = value
		return self.__class__(self._index2name, self._name2index, values)

	def __repr__(self):
		items = ", ".join(f"{key}={value!r}" for (key, value) in self.items())
		return f"<{self.__class__.__module__}.{self.__class__.__qualname__} {items} at {id(self):#x}>"

class RecordMaker:
	def __init__(self, cursor):
		self._index2name = tuple(d[0].lower() for d in cursor.description)
		self._name2index = dict(zip(self._index2name, itertools.count()))
		self._index2conv = tuple(getattr(self, d[1].name, self.DEFAULT) for d in cursor.description)

	def __call__(self, *row):
		row = tuple(conv(value) for (conv, value) in zip(self._index2conv, row))
		return Record(self._index2name, self._name2index, row)

	def DB_TYPE_CLOB(self, value):
		return value.read() if value is not None else None

	def DB_TYPE_NCLOB(self, value):
		return value.read() if value is not None else None

	def DB_TYPE_BLOB(self, value):
		return value.read() if value is not None else None

	def DEFAULT(self, value):
		return value

and RecordMaker is used as the row_factory.

It would be great if oracledbs future Record class would support some/most/all of that functionality.