How To Insert Relationship Object Data Into DB With SQLAlchemy?

Question

I am using SQLAlchemy 0.9 with Python 2.7.6 and Flask.

I receive form data, place it into object and add the objects to the session. When I try to do a db.commit() I get a rollback which seems to due to the relationship object in the INSERT statement. The SQL error is as follows:

INFO sqlalchemy.engine.base.Engine INSERT INTO site (name, address1, address2, postcode, city, fk_country_id, fk_school_id) VALUES (%(name)s, %(address1)s, %(address2)s, %(postcode)s, %(city)s, %(fk_country_id)s, %(fk_school_id)s) RETURNING site.id

INFO sqlalchemy.engine.base.Engine {'city': u'New York', 'fk_school_id': <sqlalchemy.orm.attributes.InstrumentedAttribute object at 0x8c274ac>, 'name': u'Site1', 'address1': u'2 York Way', 'address2': u'', 'postcode': u'12345', 'fk_country_id': u'2'}

INFO sqlalchemy.engine.base.Engine ROLLBACK (ProgrammingError) can't adapt type 'InstrumentedAttribute'

The relationship code in the model is as follows:

class Site(db.Model):
id = db.Column(db.Integer, primary_key = True)
school = db.relationship('School', backref = 'site', uselist = False)

What is it about the relationship object that causes the rollback? I am new to SQLAlchemy and so have followed what it says in the documentation regarding relationships.

Both of the data types in the models (Site, School) are ints.

kendlete · Accepted Answer · 2014-02-05 11:00:15Z

1

One of your fields is an object, not an ID integer or string. See:

'fk_school_id': <sqlalchemy.orm.attributes.InstrumentedAttribute object at 0x8c274ac>

Whats the code at the point you define the object and db.session.add(site)? And in your School class you have a site_id field as a ForeignKey? If so when you define the School you could just say

school = School(name='x', .. site_id = site.id)

Or you could say

site.school = school

edited Feb 5, 2014 at 11:00

answered Feb 5, 2014 at 10:44

kendlete

2244 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

VNorman Over a year ago

site.fk_school_id = school.id

VNorman Over a year ago

sorry - network disruption. What I meant to say what - site.fk_school_id = school.id is the code to assign the relationship to the object, but this is obviously incorrect. I do not have site_id as a ForeignKey in my School class, just an id and a name. If I don't assign something than None gets assigned, so what is the syntax I need to use to assign that as-yet-uncreated school Primary Key to my Site's ForeignKey field?

kendlete Over a year ago

So Site class has a field 'fk_school_id field? It isn't shown in your model above.

Jojodmo · Accepted Answer · 2014-11-19 06:48:56Z

1

You need a column and a relationship. Column is missing. Something like this.

id = db.Column(db.Integer, primary_key = True)
school_id = db.Column(db.Integer, ForeignKey('school.id'))

school = db.relationship(School, backref='site', uselist = False)

This Documentation should provide you with the details.

edited Nov 19, 2014 at 6:48

Jojodmo

23.6k13 gold badges69 silver badges112 bronze badges

answered Feb 12, 2014 at 13:09

navendu

2942 silver badges3 bronze badges

Collectives™ on Stack Overflow

How To Insert Relationship Object Data Into DB With SQLAlchemy?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related