3

To anyone with experience of SQLAlchemy, this will be basic I am sure; But I don't find the docs that helpful and I am sick of scratching my head.

Given two classes:

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

class UserPost(Base):
    __tablename__='posts'
    id = Column(Integer, primary_key=True)
    poster = Column(Integer, ForeignKey('users.id'))
    subject = Column(String(32))

What I am after is a method for:

post = session.query(UserPost).filter_by(subject="foo").one()
print post.poster.name
>>> "John Doe"

I was attempting this with a relation() attribute, but I just kept going round in circles with errors regarding relationship of joins and so on :S

My Relation looks like:

class UserPost(Base):
    __tablename__='posts'
    id = Column(Integer, primary_key=True)
    poster = Column(Integer, ForeignKey('users.id'))
    subject = Column(String(32))
    poster_user = relation(User, primaryjoin=poster==User.id)

I am new to the voodoo of SQLAlchemy so be gentle! :)

Thanks in advance guys, and apologies in advance if this turns into a RTFM or wrong-end-of-stick

Aiden Bell
  • 28,212
  • 4
  • 75
  • 119

3 Answers3

2

I think you just have the relation definition backwards.

Try:

class User(Base):
    __tablename__='users'
    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    posts = relation("UserPost", backref="poster")

class UserPost(Base):
    __tablename__='posts'
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id'))
    subject = Column(String(32))
rhettg
  • 2,453
  • 18
  • 17
  • forgive me, in your example, where does 'backref="poster"' come from/relate to? – Aiden Bell Feb 13 '10 at 18:21
  • 1
    backref means a 'poster' attribute will be added to UserPost. Actually I'd probably call that 'user'. But the point is that 'relation()' knows how to join between those two tables because of the previously defined ForeignKey. – rhettg Feb 16 '10 at 21:50
1

Maybe you should start with elixir which provides a simpler interface to sqlalchemy:

from elixir import *

metadata.bind = 'sqlite:///:memory:'

class User(Entity):
    name = Field(String(32))
    posts = OneToMany('Post')

class Post(Entity):
    subject = Field(String(32))
    user = ManyToOne('User')

setup_all(True)

u1 = User(name='John Doe')
p1 = Post(subject='foo', user=u1)

session.commit()

print Post.query.filter_by(subject='foo').one().user.name
>>> John Doe
Mathias Loesch
  • 373
  • 1
  • 5
  • 15
0

Why not show how you have configured relation() ? That's probably the part that's not working.

zeemonkee
  • 685
  • 5
  • 4