Automatically use value from a lookup table

[Yuval Dinari]

Hi,

I have a lookup/reference table which associate several fixed names to unique ids (like an enum type).

Those ids are used elsewhere as foreign keys.

It would be nice to somehow state that relationship, then in my python code create a column in the child table, put in there a value which will be a string or enum, an have sqlalchemy automatically replace that with the id associated to the name in the reference table.

Is there a way to get sqlalchemy to automatically get an id (integer) value for a string or enum type

Here's an example:

ref_status table

--------------------

id: int4

name: varchar

another table

------------------

status_id: foreign key references ref_status.id

Note: I have looked into sqlalchemy enum type, and that seems to work by specifying the values for the enum in python, while what I want is to have those value retrieved by name from the database.

Thanks

[Mike Bayer]

Setting an object value from a string is straightforward, this is done
using association proxy:

https://docs.sqlalchemy.org/en/latest/orm/extensions/associationproxy.html

Getting this object value from an existing set of objects in the
database is not as easy, because this requires a database lookup at
some point, either up front in the application, or when you are
building the object, or when you attach the object to the Session, or
right before the object is flushed.

There's a recipe on the Wiki that a lot of people use for this, while
I wrote it, I still find it kind of complicated, it's the UniqueObject
recipe: https://github.com/sqlalchemy/sqlalchemy/wiki/UniqueObject

The reason these patterns are hard is because different applications
treat their Session objects differently. UniqueObject relies strongly
on the concept of there being a thread-local Session (e.g. the
scoped_session).

if you aren't relying upon a thread-local Session registry (which I
tend to prefer, that is to not rely on one being present), there's
another way to handle the linkage of your objects to your
database-loaded lookup object, and that is to look up the correct
object either when the holder of the lookup object is associated with
the session using the transient-to-pending event, or if the object is
already associated with a Session it can be done when the attribute is
first set up.

An example that combines the association proxy with the second set of
techniques is below. If you are a beginner to SQLAlchemy, this might
be a lot to take in. The automatic-lookup thing has resisted having
a simple feature we can use in all cases thus far so requires a little
bit of tinkering at the moment. I'm going to add this to the wiki in
complement to UniqueObject.

from sqlalchemy import Column
from sqlalchemy import create_engine
from sqlalchemy import event
from sqlalchemy import ForeignKey
from sqlalchemy import Integer
from sqlalchemy import String
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.declarative import declared_attr
from sqlalchemy.orm import object_session
from sqlalchemy.orm import relationship
from sqlalchemy.orm import Session
from sqlalchemy.orm import validates

Base = declarative_base()


class Type(Base):
"""our lookup object."""

__tablename__ = "type"
id = Column(Integer, primary_key=True)
name = Column(String, unique=True)


class HasType(object):
"""Define a class that links to a Type object."""

@declared_attr
def type_id(cls):
return Column(ForeignKey("type.id"), nullable=False)

@declared_attr
def _type(cls):
return relationship("Type")

type = association_proxy(
"_type", "name", creator=lambda name: Type(name=name)
)
"""Define <someobject>.type as the string name of its Type object.

When <someobject>.type is set to a string, a new, anonymous Type() object
is created with that name and assigned to <someobject>._type. However it
does not have a database id. This will have to be fixed later when the
object is associated with a Session where we will replace this
Type() object with the correct one.

"""

@validates("_type")
def _validate_type(self, key, value):
"""Receive the event that occurs when <someobject>._type is set.

If the object is present in a Session, then make sure it's the Type
object that we looked up from the database.

Otherwise, do nothing and we'll fix it later when the object is
put into a Session.

"""
sess = object_session(self)
if sess is not None:
return _setup_type(sess, value)
else:
return value


@event.listens_for(Session, "transient_to_pending")
def _validate_type(session, object_):
"""Receive the HasType object when it gets attached to a Session to correct
its Type object.

Note that this discards the existing Type object.

"""

if (
isinstance(object_, HasType) # it's a HasType
and object_._type is not None # something set object_._type = Type()
and object_._type.id is None # and it has no database id
):
# the id-less Type object that got created
old_type = object_._type
# make sure it's not going to be persisted.
if old_type in session:
session.expunge(old_type)

object_._type = _setup_type(session, object_._type)


def _setup_type(session, type_object):
"""Given a Session and a Type object, return
the correct Type object from the database.

"""

with session.no_autoflush:
return session.query(Type).filter_by(name=type_object.name).one()


# demonstrate the pattern.


class A(HasType, Base):
__tablename__ = "a"

id = Column(Integer, primary_key=True)


e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)

s = Session(e)
t1, t2, t3 = Type(name="typea"), Type(name="typeb"), Type(name="typec")
s.add_all([t1, t2, t3])
s.commit()


a1 = A(type="typeb")
a2 = A(type="typec")
s.add_all([a1, a2])
s.commit()

assert a1._type is t2
assert a1.type == "typeb"
assert a2._type is t3
assert a2.type == "typec"

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
> ---
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.