Inserting new records with one-to-many relationship in sqlalchemy

Question

I'm following the flask-sqlalchemy tutorial on declaring models regarding one-to-many relationship. The example code is as follows:

class Person(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))
    addresses = db.relationship('Address', backref='person',
                                lazy='dynamic')

class Address(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(50))
    person_id = db.Column(db.Integer, db.ForeignKey('person.id'))

Now I'm wondering how to insert new records into DB using such model. I assume I need a constructor init, but I have difficulties to understand how it should be implemented and used. The main problem for me here is that Person depends on Address and Address has ForeignKey to Person, so it should know about the Person in advance.

Plase help me to understand how it should be performed.

Thank you in advance.

score 100 · Accepted Answer · edited Jul 21 '21 at 11:39

100

You dont need to write a constructor, you can either treat the addresses property on a Person instance as a list:

a = Address(email='foo@bar.com')
p = Person(name='foo')
p.addresses.append(a)

Or you can pass a list of addresses to the Person constructor

a = Address(email='foo@bar.com')
p = Person(name='foo', addresses=[a])

In either case you can then access the addresses on your Person instance like so:

db.session.add(p)
db.session.add(a)
db.session.commit()
print(p.addresses.count()) # 1
print(p.addresses[0]) # <Address object at 0x10c098ed0>
print(p.addresses.filter_by(email='foo@bar.com').count()) # 1

edited Jul 21 '21 at 11:39

Star

131
3
18

answered May 08 '13 at 07:22

DazWorrall

13,682
5
43
37

12

Great! Now, if a Person already exists in the DB, and we've added a new address for them (`p.addresses.append(a)`), do we thence need to `db.session.add` the person, the address, both, or neither, before `db.session.commit`ting? – Michael Scheper Jan 12 '17 at 02:44
you have to query existing data from the db, like `p = db.session.query(Person).filter(Person.name == 'Name of existing Person').first()` and then do the same `p.addresses.append(a)`. You don't have to add it to the session then, because it comes straight from the session. But I would still `db.session.commit()` after all changes are done – Rich Steinmetz Feb 21 '18 at 15:54
3

This creates an new `Address` record. What if I want to assign the `id` (primary key ) of `Address` record with `email='foo@bar.com'`. I want to assign already created address to person, but I don't want to query the address first. – Jashwant Nov 12 '19 at 09:49

score 9 · Answer 2 · answered Mar 08 '21 at 13:33

I've gathered information here and elsewhere and found 3 ways to do so. In this model example (same as question):

class Person(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))
    addresses = db.relationship('Address', backref='person',
                                lazy='dynamic')

class Address(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(50))
    person_id = db.Column(db.Integer, db.ForeignKey('person.id'))

1.

a = Address(email='foo@bar.com')
p = Person(name='foo', addresses=[a])

2.

p = Person(name='foo')
a = Address(email='foo@bar.com', person_id=p.id)

3.

a = Address(email='foo@bar.com')
p = Person(name='foo')
p.addresses.append(a)

score 3 · Answer 3 · edited Jul 19 '21 at 13:39

The most important thing while looking into this model is to understand the fact that this model has a one to many relationship, i.e. one Person has more than one address and we will store those addresses in a list in our case.

So, the Person class with its init will look something like this.

class Person(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))
    addresses = db.relationship('Address', backref='person',
                            lazy='dynamic')

    def __init__(self,id,name,addresses = tuple()):
        self.id = id
        self.name = name
        self.addresses = addresses

So this Person class will be expecting an id, a name and a list that contains objects of type Address. I have kept that the default value to be an empty list.

Hope it helps. :)

Mutable default arguments is a [common gotcha](http://docs.python-guide.org/en/latest/writing/gotchas/#mutable-default-arguments), you might want to change your example to reflect this :) — DazWorrall, Aug 17 '15 at 08:29

score 1 · Answer 4 · answered Sep 10 '21 at 18:32

Additionally to all previous answers, in one-to-one relationships with uselist=False, like:

class Person(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))
    address = db.relationship('Address', backref='person',
                                lazy=True, uselist=False)

class Address(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(50))
    person_id = db.Column(db.Integer, db.ForeignKey('person.id'))

Only next approach helped to insert records:

p = Person(name='foo')
a = Address(email='foo@bar.com', person=p)  # Adding created person directly to Address's backref

If I use the above I get a type error in the lines of `TypeError: 'product' is an invalid keyword argument for Price` , any help on how to rectify — KevinCK, Feb 04 '23 at 21:52

Inserting new records with one-to-many relationship in sqlalchemy

4 Answers4

1.

2.

3.

Linked