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" On Sun, Apr 7, 2019 at 11:21 AM Yuval Dinari <[email protected]> wrote: > > 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 > > > > > > > -- > 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 [email protected]. > To post to this group, send email to [email protected]. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
