SQLAlchemy: get Model from table name. This may imply appending some function to a metaclass constructor as far as I can see
Solution 1
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
Solution 2
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.
Solution 3
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
Solution 4
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
Solution 5
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)
Comments
-
Sheena almost 3 years
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.
-
Eevee about 11 yearsoh my goodness. this is brittle all around, but if nothing else: you don't need to stringify the class to test what it is! just check
issubclass(globals[k], Base)
! -
Sheena about 11 years@Eevee: hey, it works. sayap's comment makes sense... can you tell me how it is brittle? I know a try...except with no exception type is generally bad practice, and stringifying things isn't necessary (taken that out). What else?
-
Eevee about 11 years
type(...) == ...
is also unnecessary; useissubclass
with your base class orisinstance
with the metaclass. you could iterate overglobals.items()
instead of doing a key lookup multiple times. a function like this should raise an exception on failure. but most importantly, as soon as a model file like this gets bigger than a couple dozen tables, most developers will be inclined to split it up which will break this code in ways that may not be immediately obvious. -
Sheena about 11 yearsI didn't know about
items()
, thanks. I don't see the downside of usingtype
rather thanissubclass
orisinstance
... Also, the problem I solved with this is one of finding the correct model class in a predefined scope. In my case the scope is global and that's convenient. If anyone wants to adapt this for their purposes then they should adapt it for their scope. Scope is not the issue being explored here. And solving scope issues shouldn't be very hard anyway. Creative use of importlib would do the trick, or context could be included as a parameter. -
Eevee about 11 years
type
is brittle and can be fooled in a lot of ways. looking up things in a lexical scope should always be a last resort—in this case you already have the mapping you want, as the comment on your question indicates. -
Sheena about 11 years@Eevee: "the comment", what comment? If you are referring to my documentation string then I'm not sure what you mean. In what way do I already have a tablename to model class mapping? I know I have a model class to tablename mapping but that's not what I'm after
-
Eevee about 11 yearssayap's comment, above.
Base._decl_class_registry
is a dict of declarative class names to classes. you could iterate its.values()
and check the table names. or you could extend the declarative metaclass to create your own similar dict. -
pip over 9 yearsWorks great - for flask-sqlalchemy replace Base with db.Model as they are the same thing (more or less).
-
dequis over 7 yearsThis function takes the table object (the exact same one,
c.__table__ is table
), not a table name. -
Admin over 7 yearshad to change
__tablename__
to__table__
to get this to work -
RunOrVeith about 3 yearsThis should be a comment on the answer that you are referring to
-
Anand Tripathi over 2 yearscorrect maybe @Konsta you can change the comparision to str(c.__table__) in your implementation or use tablename cause most of the time we dont know the class only the table name then only we can use this function