SQLAlchemy Basic Question

Question

Hello everyone,

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

Answer 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

Answer 2

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

Answer 3

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