AttributeError: 'unicode' object has no attribute '_sa_instance_state'

Question

I'm just learning how to use SQLAlchemy. I'm trying to do the following, but storing title and link in two separate tables:

temp = Submissions(title=u'Facebook Homepage', link=u'http://facebook.com')
session.add(temp)
session.flush()
transaction.commit()

via:

class Links(Base):
    __tablename__ = 'links'
    id = Column(Integer, primary_key=True)
    link = Column(Text)
    created = Column(TIMESTAMP(), default=datetime.now())

    def __init__(self, link):
        self.link = link

class Submissions(Base):
    __tablename__ = 'submissions'
    id = Column(Integer, primary_key=True)
    created = Column(TIMESTAMP(), default=datetime.now())
    title = Column(Text)
    link_id = Column(Integer, ForeignKey('links.id'))
    link = relation(Links)

    def __init__(self, title, link):  
        self.title = title
        self.link = link

However, I always get this error:

AttributeError: 'unicode' object has no attribute '_sa_instance_state'

What is going on? Is there a better way to code this?

Answer 1

You can't quite do that with relationship.

You need to arrange for the Link to be looked up in some way.

The most obvious is to just look it up directly.

submission_link = session.query(Links) \
                         .filter(Links.link == u'http://facebook.com') \
                         .first()
if submission_link is None:
    submission_link = Links(link=u'http://facebook.com')
    session.add(submission_link)

submission = Submissions(title=u'Facebook Homepage', link=submission_link)
session.add(submission)
session.commit()

You can also use hybrid attributes to get something that looks a bit more like your example, but its substantially more convoluted.

also, it's relationship, relation is deprecated.

Answer 2

I would configure the relation as one-to-one (uselist=False) and add a property which would wrap the link relationship. The SA configuration would then look like below and your code should work just fine creating, updating and deleting the link. You might need to configure the relation for have delete-orphan option in a cascade.

...
class Submissions(Base):
    __tablename__ = 'submissions'
    id = Column(Integer, primary_key=True)
    created = Column(DateTime(), default=datetime.now())
    title = Column(Text)
    link_id = Column(Integer, ForeignKey('links.id'))
    link_rel = relation(Links, backref=backref("_submission", uselist=False))

    def __init__(self, title, link=None):
        self.title = title
        self.link = link

    @property
    def link(self):
        return self.link_rel and self.link_rel.link

    @link.setter
    def link(self, value):
        if value is None:
            self.link_rel = None
        elif self.link_rel is None:
            self.link_rel = Links(value)
        else:
            self.link_rel.link = value
...