How to create a foreignkey reference with sqlalchemy

Question

Hi I am not able to understand how to make a foreignkey reference using sqlalchemy. I have 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 I am 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 shouts back saying :

(k, cls_.name)) TypeError: 'user' is an invalid keyword argument for Client

Now shouldn't this be obvious that user should be allowed as a keyword argument how can I make sure my table is able to take user keyword argument.

Answer 1

You need to define a relationship between 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 associate 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 assinging a 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's a third way - since we've configured a backref on Client.user, SQLAlchemy added a list-like clients attribute to our User model:

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