Basic SQLAlchemy Question

Question

For anyone with SQLAlchemy experience, this will be fundamental, I am sure; But I do not find documents that are useful to me, and I'm tired of scratching my head.

For 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 need is a method for:

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

I tried with this attribute relation(), but I just kept spinning around with errors regarding union relationships, etc .: S

My relationship is like this:

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'm new to SQLAlchemy voodoo, so be gentle! :)

We thank the guys in advance and apologize in advance if this turns into RTFM or the wrong end of the stick

Answer 1

I think you just have a definition of a relationship back.

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))

Answer 2

, elixir, 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

Answer 3

Why not show how you set up the relation ()? This part probably doesn't work.