How to create a link to a foreign key with sqlalchemy

Question

Hi, I can’t figure out how to link to someone else’s key using sqlalchemy. I created a new table client in my database:

class Client(DeclarativeBase): __tablename__ = 'client' id = Column(Integer, primary_key=True) user_id = Column( Integer, ForeignKey('user.id', ondelete='CASCADE'), nullable=False, index=True, ) orgname = Column(Unicode, nullable=False) def __init__(self, **kwargs): super(Client, self).__init__(**kwargs) 

Not trying to do something like this

  u = User(user_name=u'dusual') session.add(u) c = Client(user=u, orgname="dummy_org") session.add(c) 

But sqlalchemy yells back saying:

(k, cls _. name )) TypeError: "user" is an invalid keyword argument for the client

Now it should not be obvious that the user should be allowed as a keyword argument, how can I make sure that my table can accept a user keyword argument.

Answer 1

You need to define the relationship between the User and Client models:

from sqlalchemy.orm import relationship

 class Client(DeclarativeBase): __tablename__ = 'client' id = Column(Integer, primary_key=True) user_id = Column( Integer, ForeignKey('user.id', ondelete='CASCADE'), nullable=False, # no need to add index=True, all FKs have indexes ) user = relationship('User', backref='clients') orgname = Column(Unicode, nullable=False) # no need to add a constructor 

Then you can link instances of User and Client models in two ways: either by assigning an integer to Client.user_id :

 u = User(user_name=u'dusual') session.add(u) session.flush() # to make sure the id is fetched from the database c = Client(user_id=u.id, orgname="dummy_org") session.add(c) 

or by counting the User instance to Client.user .

 u = User(user_name=u'dusual') # no need to flush, no need to add `u` to the session because sqlalchemy becomes aware of the object once we assign it to c.user c = Client(user=u, orgname="dummy_org") session.add(c) 

Actually there is a third way - since we configured backref on Client.user , SQLAlchemy added a clients attribute similar to the list to our User model:

 u = User(user_name=u'dusual') u.clients.append(Client(orgname="dummy_org")) session.add(u)