SQLAlchemy: get Model from table name. This may imply appending some function to a metaclass constructor as far as I can see

Question

I want to make a function that, given the name of a table, returns the model with that tablename. Eg:

class Model(Base):
    __tablename__ = 'table'
    ...a bunch of Columns

def getModelFromTableName(tablename):
   ...something magical

so getModelFromTableName('table') should return the Model class.

My aim is to use the function in a simple form generator I'm making since FormAlchemy does not work with python3.2 and I want it to handle foreign keys nicely.

Can anyone give me any pointers on how to get getModelFromTableName to work?

Here's one idea I have (it might be totally wrong, I haven't worked with meta classes before...)

What if I were to make my Model classes inherit from Base as well as some other class (TableReg) and have the class meta of TableReg store Model.tablename in some global dictionary or Singleton.

I realise this could be totally off because Base's metaclass does some very important and totally nifty stuff that I don't want to break, but I assume there has to be a way for me to append a little bit of constructor code to the meta class of my models. Or I don't understand.

Answer 1

elaborating all previous comments, my working solution using:

flask-alchemy 3.1.1 SQLAlchemy 2.0.29 Python 3.8

is:

def get_tableclass_by_name(db, tablename):
  for tbl in db.Model.metadata.tables.values():
    if hasattr(tbl, 'name') and tbl.name == tablename:
      return tbl

db = SQLAlchemy()

table = get_tableclass_by_name(db, "table_name")

Answer 2

I don't know if things are different now or something, but in relatively simple circumstances you can create a lookup table after configuring your mappers like so:

table_name_to_class = {m.tables[0].name: m.class_ for m in Base.registry.mappers}

Here's a full code sample for anyone who cares (sorry for mixing in the pytest junk, I'm in the middle of something and this was the easiest way to demo the code...)

import pytest
import sqlalchemy as sa
from sqlalchemy.orm import declarative_base, Session

engine = sa.create_engine("postgresql+psycopg2://postgres@localhost/test")
Base = declarative_base(bind=engine)


class A(Base):
    __tablename__ = "table_a"
    a_id = sa.Column(sa.Integer, primary_key=True)


class B(Base):
    __tablename__ = "table_b"
    b_id = sa.Column(sa.Integer, primary_key=True)
    a_id = sa.Column(sa.Integer, sa.ForeignKey("table_a.a_id"))


@pytest.fixture
def table_name_to_class():
    try:
        Base.metadata.create_all()
        Base.registry.configure()
        yield {m.tables[0].name: m.class_ for m in Base.registry.mappers}
    finally:
        Base.metadata.drop_all()


def test_table_lookup(table_name_to_class):
    assert table_name_to_class["table_a"] is A
    assert table_name_to_class["table_b"] is B

    with Session(bind=Base.metadata.bind) as session:
        session.add(table_name_to_class["table_a"](a_id=0))
        session.add(table_name_to_class["table_b"](b_id=0, a_id=0))
        session.add(table_name_to_class["table_b"](b_id=1, a_id=0))
        session.commit()

    assert engine.execute(""" SELECT COUNT(a_id) FROM table_a """).scalar() == 1
    assert engine.execute(""" SELECT COUNT(b_id) FROM table_b """).scalar() == 2

Answer 3

Old thread, but none of the answers here worked for me working with Flask-SQLAlchemy 2.5.1. and SQLAlchemy 1.4.32

As db.Model object you use in Flask-SQLAlchemy is practically identical to the Base model object in SQLAlchemy ORM, I think my answer below applies in the modern context.

This comment from @Supra621 seems to be the cleanest solution where you can simply do:

def get_class_from_tablename(tablename):
  for c in Base.__subclasses__():
    if c.__tablename__ == tablename:
      return c

Additionally, if you'd rather access this table object using the registry like the majority of the old answers here:

def get_class_from_tablename(tablename):
  for c in Base.registry._class_registry.data.values():
    if hasattr(c, '__tablename__') and c.__tablename__ == tablename:
      return c

Not sure from which version the attribute names change, but at least this works in both SQLAlchemy ORM and Flask SQLAlchemy (latest versions at the time of writing).

Answer 4

For sqlalchemy 1.4.x (and probably 2.0.x for the future readers too) You can nicely extend the Erotemic answer to be more convenient when models are distributed across many files (such case is a primary reason for looking up ORM classes when doing proper OOP).

Take such class and make a Base from it:

from sqlalchemy.orm import declarative_base

class BaseModel:

    @classmethod
    def model_lookup_by_table_name(cls, table_name):
        registry_instance = getattr(cls, "registry")
        for mapper_ in registry_instance.mappers:
            model = mapper_.class_
            model_class_name = model.__tablename__
            if model_class_name == table_name:
                return model


Base = declarative_base(cls=BaseModel)

Then declaring your models, even in separate modules, enables You to use cls.model_lookup_by_table_name(...) method without importing anything, as long as You are deriving from a Base:

user_models.py

from sqlalchemy import Column, Integer

class User(Base):
    __tablename__ = "user"

    id = Column(Integer, primary_key=True)

    # ... and other columns

    def some_method(self):
        # successfully use lookup like this
        balance_model = self.model_lookup_by_table_name("balance")
        # ...
        return balance_model

balance_models.py

from sqlalchemy import Column, Integer

class Balance(Base):
    __tablename__ = "balance"

    id = Column(Integer, primary_key=True)

    # ... other columns

    def some_method(self):
        # lookup works on every model
        user_model = self.model_lookup_by_table_name("user")
        # ...
        return user_model

And it works as expected:

>>> User().some_method()
<class 'balance_models.Balance'>
>>> Balance().some_method()
<class 'user_models.User'>
>>> Base.model_lookup_by_table_name("user")
<class 'user_models.User'>
>>> Base.model_lookup_by_table_name("balance")
<class 'balance_models.Balance'>

You can safely cache the output of this method using functools.lru_cache to improve performance (avoiding python for loop when it is not needed). Also, You can add more lookups the same way, e.g. by a class name (not only by a table name like in this example)

Answer 5

So in SQLAlchemy version 1.4.x (which I updated to around 2020-03-16) it seems _decl_class_registry no longer exists.

I was able to work around using the new registry class attribute (which is not protected, so hopefully it wont be removed suddenly!).

Base.TBLNAME_TO_CLASS = {}

for mapper in Base.registry.mappers:
    cls = mapper.class_
    classname = cls.__name__
    if not classname.startswith('_'):
        tblname = cls.__tablename__
        Base.TBLNAME_TO_CLASS[tblname] = cls

Not sure if this is the best way to do it, but its how I did it.

Answer 6

One way would be to define a __table_cls__ classmethod in Base or as a mixin. Note that I haven't tested this with any sort of inheritance. An example if augmenting Base:

# Python 3.7.2, SQLAlchemy 1.3.13
###
# app/models.py
from sqlalchemy import Table
from sqlalchemy.ext.declarative import declarative_base

class Base(object):
    @classmethod
    def __table_cls__(cls, *args, **kwargs):
        t = Table(*args, **kwargs)
        t.decl_class = cls
        return t

Base = declarative_base(cls=Base)

Contriving a table example:

# app/models.py
class Account(Base):  # Note this is the customized Base class
    __tablename__ = 'account'
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    account_number = Column(String)

Testing in console:

# Python 3.7.2 Console
>>> from app import models as m
>>> account_table = m.Base.metadata.tables['account']
>>> account_table.__class__
<class 'sqlalchemy.sql.schema.Table'>

>>> account_table.decl_class
<class 'app.models.Account'>

As a function:

def get_class_by_table_name(table_name):
    tbl = Base.metadata.tables[table_name]
    return tbl.decl_class

Side note: since a lot of answers have referenced Base._decl_class_registry, the declarative_base() function also accepts a dictionary for the class_registry named parameter.

>>> registered_classes = {}
>>> Base = declarative_base(cls=Base, class_registry=registered_classes)
>>> registered_classes.keys()
dict_keys(['Account', '_sa_module_registry', 'AccountType', ...])

Answer 7

Beware the OrangeTux answer does not take schemas in account. If you have table homonyms in different schemas use:

def get_class_by_tablename(table_fullname):
  """Return class reference mapped to table.

  :param table_fullname: String with fullname of table.
  :return: Class reference or None.
  """
  for c in Base._decl_class_registry.values():
    if hasattr(c, '__table__') and c.__table__.fullname == table_fullname:
      return c

fullname is a Table attribute see:

github.com/sqlalchemy/sqlalchemy/blob/master/lib/sqlalchemy/sql/schema.py#L530-L532

Answer 8

if you are working with sqlalchemy automap, you need a slight modification. Took me couple minutes to tune it out:

def get_class_by_tablename(tablename, Base):
  for c in Base._decl_class_registry.values():
    if c.__table__.name == tablename:
      return c

Answer 9

Inspired by Eevee's comment:

def get_class_by_tablename(tablename):
  """Return class reference mapped to table.

  :param tablename: String with name of table.
  :return: Class reference or None.
  """
  for c in Base._decl_class_registry.values():
    if hasattr(c, '__tablename__') and c.__tablename__ == tablename:
      return c

Answer 10

Utility function for this has been added to SQLAlchemy-Utils. See get_class_by_table docs for more information. The solution in SQLAlchemy-Utils is able to cover single table inheritance scenarios as well.

import sqlalchemy as sa


def get_class_by_table(base, table, data=None):
    """
    Return declarative class associated with given table. If no class is found
    this function returns `None`. If multiple classes were found (polymorphic
    cases) additional `data` parameter can be given to hint which class
    to return.

    ::

        class User(Base):
            __tablename__ = 'entity'
            id = sa.Column(sa.Integer, primary_key=True)
            name = sa.Column(sa.String)


        get_class_by_table(Base, User.__table__)  # User class


    This function also supports models using single table inheritance.
    Additional data paratemer should be provided in these case.

    ::

        class Entity(Base):
            __tablename__ = 'entity'
            id = sa.Column(sa.Integer, primary_key=True)
            name = sa.Column(sa.String)
            type = sa.Column(sa.String)
            __mapper_args__ = {
                'polymorphic_on': type,
                'polymorphic_identity': 'entity'
            }

        class User(Entity):
            __mapper_args__ = {
                'polymorphic_identity': 'user'
            }


        # Entity class
        get_class_by_table(Base, Entity.__table__, {'type': 'entity'})

        # User class
        get_class_by_table(Base, Entity.__table__, {'type': 'user'})


    :param base: Declarative model base
    :param table: SQLAlchemy Table object
    :param data: Data row to determine the class in polymorphic scenarios
    :return: Declarative class or None.
    """
    found_classes = set(
        c for c in base._decl_class_registry.values()
        if hasattr(c, '__table__') and c.__table__ is table
    )
    if len(found_classes) > 1:
        if not data:
            raise ValueError(
                "Multiple declarative classes found for table '{0}'. "
                "Please provide data parameter for this function to be able "
                "to determine polymorphic scenarios.".format(
                    table.name
                )
            )
        else:
            for cls in found_classes:
                mapper = sa.inspect(cls)
                polymorphic_on = mapper.polymorphic_on.name
                if polymorphic_on in data:
                    if data[polymorphic_on] == mapper.polymorphic_identity:
                        return cls
            raise ValueError(
                "Multiple declarative classes found for table '{0}'. Given "
                "data row does not match any polymorphic identity of the "
                "found classes.".format(
                    table.name
                )
            )
    elif found_classes:
        return found_classes.pop()
    return None

Answer 11

I was going to delete this but I figure that the discussion in the comments might be useful for people who want to know about some good practices. Take this answer with a pinch of salt.

---

something like this does the trick:

def getModelFromTableName(sTable):
    """
    return the Model class with the given __tablename__
    """
    globals = globals()
    for k in globals:
        if type(globals[k]) == sqlalchemy.ext.declarative.DeclarativeMeta:
            try:
                if globals[k].__tablename__ == sTable:
                    return globals[k]
            except:
                pass
    return None