how do simple SQLAlchemy relationships work?

Question

I'm no database expert -- I just know the basics, really. I've picked up SQLAlchemy for a small project, and I'm using the declarative base configuration rather than the "normal" way. This way seems a lot simpler.

However, while setting up my database schema, I realized I don't understand some database relationship concepts.

If I had a many-to-one relationship before, for example, articles by authors (where each article could be written by only a single author), I would put an author_id field in my articles column. But SQLAlchemy has this ForeignKey object, and a relationship function with a backref kwarg, and I have no idea what any of it MEANS.

I'm scared to find out what a many-to-many relationship with an intermediate table looks like (when I need additional data about each relationship).

Can someone demystify this for me? Right now I'm setting up to allow openID auth for my application. So I've got this:

from __init__ import Base from sqlalchemy.schema import Column from sqlalchemy.types import Integer, String  class Users(Base):     __tablename__ = 'users'     id = Column(Integer, primary_key=True)     username = Column(String, unique=True)     email = Column(String)     password = Column(String)     salt = Column(String)   class OpenID(Base):     __tablename__ = 'openid'     url = Column(String, primary_key=True)     user_id = #? 

I think the ? should be replaced by Column(Integer, ForeignKey('users.id')), but I'm not sure -- and do I need to put openids = relationship("OpenID", backref="users") in the Users class? Why? What does it do? What is a backref?

Answer 1

Yes, you need user_id = Column(Integer, ForeignKey('users.id')) or user_id = Column(Integer, ForeignKey('users.id'), nullable=False) if it's mandatory. This is directly translated to FOREIGN KEY in underlying database schema, no magic.

The simple way to declare relationship is user = relationship(Users) in OpenID class. You may also use users = relationship('OpenID') in Users class. backref parameter allows you to declare both relationships with single declaration: it means to automatically install backward relationship in related class. I personally prefer using backref-s for self-referring relationships only. The reason is that I like self-documented code: when you look through it's definition you see all defined properties, while with backref you need to look through other classes (probably defined in other modules).