how do simple SQLAlchemy relationships work?

Posted by Carson Myers on Stack Overflow See other posts from Stack Overflow or by Carson Myers
Published on 2010-05-27T01:00:13Z Indexed on 2010/05/27 1:11 UTC
Read the original article Hit count: 308

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?

© Stack Overflow or respective owner

Related posts about python

Related posts about database-design