Object relational mappers for Pydantic

[IDrumsey]

It would be cool if you could convert returned rows directly into data models like Pydantic models. Something like

class MyRow(BaseModel):
  id: int
  name: str
  ...

rows = cursor.fetchall("SELECT * FROM ...").convert(AvailableORMs.Pydantic, MyRow)

[mkleehammer]

Well, I certainly couldn't bundle in something Pydantic specific since I try to keep pyodbc as low-level as possible so it can be used in any project. So anything like this would have to be pretty generic.

The syntax you have here couldn't be it since the return value from fetchall is a list and that's not something we want to change.

There is a ticket or discussion requesting returning rows as dictionaries. It might be possible to design a more generic solution that allows the results to be configurable and allow you to do what you want.

If you really wanted a convenient syntax for your projects using Pydantic, that works today, I
would suggest making a Connection and Cursor wrapper. Python's __getattr__ makes it a breeze.
Something like the following, which I have not tested and probably has obvious typos:

class ModelConnection:
    def __init__(self, cnxn):
        self.cnxn = cnxn

    def cursor(self):
        cursor = self.cnxn.cursor()
        return ModelCursor(cursor)

    def __getattr__(self, attr):
        # Instead of re-implementing all of the methods, just pass anything we
        # don't care about to the underlying connection.
        return getattr(self.cnxn, attr)


class ModelCursor:
    def __init__(self, cursor):
        # IMPORTANT: Don't pass in the cnxn and create circular references
        # unless you are sure of what you are doing.
        self.cursor = cursor

    def fetchrow(self, model):
        row = self.cursor.fetchrow()
        if row:
            return self. convert_to_model(row, model)
        return None

    def _convert_row(self, row, model):
        ...

    def __getattr__(self, attr):a
        # Instead of re-implementing all of the methods, just pass anything we
        # don't care about to the underlying cursor.
        return getattr(self.cursor, attr)

Using this is pretty transparent:

cnxn = connect()  # shared func that connects and wraps the cnxn
cursor = cnxn.cursor()
cursor.execute(...)
m = cursor.fetchrow(MyRow)

Add an override for fetchall and you are set.

One thing I would think about is not actually overriding but using slightly different names like fetchmodel and fetchmodels so you can still use rows. The rows are fantastic for lots of small jobs where the row is consumed in a single function or a tight group of functions. Defining new data types for every one of these uses is tedious and, in my opinion, work against you in the long run by adding conceptual complexity to the small areas where dynamic typing is useful.

[mkleehammer]

We will be looking at a new backwards compatible pyodbc design where the main classes are actually written in Python and use a C library for some of there methods. If that works out, subclassing Connection and Cursor will be another option.