Morgan Allen Morgan Allen - 6 months ago 25
Python Question

Adding in multiple foreign keys in SQLAlchemy

I'm following this guide - http://docs.sqlalchemy.org/en/rel_1_0/orm/tutorial.html

Here is my code

from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship

engine = create_engine('sqlite:///test.db', echo=True)
Base = declarative_base()
Session = sessionmaker(bind=engine)
session = Session()

class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String)
fullname = Column(String)
password = Column(String)
def __repr__(self):
return "<User(name='%s', fullname='%s', password='%s')>" % (self.name, self.fullname, self.password)

class Address(Base):
__tablename__ = 'addresses'
id = Column(Integer, primary_key = True)
email_address = Column(String, nullable=False)
user_id = Column(Integer, ForeignKey('users.id'))
user = relationship("User", back_populates = 'addresses')
def __repr__(self):
return "<Address(email_address='%s')>" %self.email_address

User.addresses = relationship ("Address", order_by=Address.id, back_populates="user")


Base.metadata.create_all(engine)


When I create a record for the
User
table, I do it like this:

candidate = User(name='Jack', fullname='Jack Bean', password='blah')


And to add in email addresses, I do it like this (following the guide):

candidate.addresses = [Address(email_address='jack_bean@gmail.com')]


I'm trying to figure out how to add multiple email address to Jack's record after the fact. When I try it again

candidate.addresses = [Address(email_address='jack@gmail.com')]


It erases the foreign key for
jack_bean@gmail.com
in the Address table (it initially was 1) and makes it
NULL
.

How do I add multiple foreign keys after creating the initial record?

Answer

Instead of doing:

candidate.addresses = [Address(email_address='jack_bean@gmail.com')]

if you need to add an address to a candidate after they have already been created, do this:

candidate.addresses.append(Address(email_address="new@new.com"))
db_session.commit()
Comments