Not sure if this is best pratice to use it but at least it works for me.

Model:

from odoo import models, fields, api
from odoo.addons.base_external_dbsource_odbc.models.base_external_dbsource import BaseExternalDbsource


class wws_dai_extension(models.TransientModel):
    _name = 'my_test_model'
    _description = 'my_test_model'

    name = fields.Char()
    description = fields.Char()

    def get_odbc_data(self):
        vals = {}
        self.dbsource = self.env['base.external.dbsource'].search([('name', '=', 'test')], limit=1)

        conn = BaseExternalDbsource.connection_open_pyodbc(self.dbsource)

        query = 'select myfield1, myfield2 from schema.mytable'

        cursor = conn.execute(query)
        
        self.env['my_test_model'].search([]).unlink()
        for row in cursor.fetchall() or []:
            vals['name'] = row[0]
            vals['description'] = row[1]
            self.create(vals)

        BaseExternalDbsource.connection_close_pyodbc(self, conn)
        action_vals =  {'name': 'Transient Model from external datasource',
            'type': 'ir.actions.act_window',
            'view_mode': 'tree',
            'res_model': 'my_test_model',
        }
        return action_vals
       

View:
<record model="ir.actions.server" id="my_test_model.action_server">
      <field name="name">my_test_model server</field>
      <field name="model_id" ref="model_my_test_model"/>
      <field name="type">ir.actions.server</field>
      <field name="state">code</field>
      <field name="code">action = model.get_odbc_data()</field>
    </record>

Been looking for the best example as well ...most of them seems like you're building a new model