How To Insert Relationship-Object Data Into DB?

[Jude Lucien]

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.

[Michael Bayer]

there’s nothing wrong with the configuration there, the error has to do with the wrong kind of object being assigned to a database row, in this case the class-bound attribute.

So basically this:

class Site(Base):

    # …

    fk_school_id = Column(Integer, ForeignKey(…))

class School(Base):

    id = Column(Integer, primary_key=True)

site = Site()

site.fk_school_id = School.id   # <— incorrect, School is a class, not an instance of School

session.add(site)

session.commit()

a class-bound attribute like “School.id” is an InstrumentedAttribute.  your intent here is to assign an integer value to fk_school_id.

But also, when using relationship() you will have an easier time if you work with objects rather than foreign key attributes:

some_school = School()

site.school = some_school

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.

[Jude Lucien]

Thanks for your informative answer!

I have a secondary problem now having changed my model to use declarative base - as in db.create_all() does not create my tables in the database.

How can I do this using the declarative base method?

--
"None are more hopelessly enslaved than those who falsely believe they are free"  -- Johann Wolfgang von Goethe

[Michael Bayer]

On Feb 5, 2014, at 9:43 AM, Jude Lucien <jlu...@gmail.com> wrote:

Thanks for your informative answer!

I have a secondary problem now having changed my model to use declarative base - as in db.create_all() does not create my tables in the database.

How can I do this using the declarative base method?

Base.metadata is where you’d call create_all(my engine) from.

[Matthew Phipps]

On Wednesday, February 5, 2014 10:44:39 AM UTC-5, Michael Bayer wrote:

On Feb 5, 2014, at 9:43 AM, Jude Lucien <jlu...@gmail.com> wrote:

I have a secondary problem now having changed my model to use declarative base - as in db.create_all() does not create my tables in the database. 

How can I do this using the declarative base method?

Base.metadata is where you’d call create_all(my engine) from.

@Jude: db.create_all() is part of the Flask-SQLAlchemy API, not SQLAlchemy proper. AFAIK it's ok to mix the two together (the project I'm working on certainly does) but if you're not subclassing db.Model in your models Flask-SQLAlchemy may not be doing you much good. Which is fine, just be aware.

-Matt

[Jude Lucien]

This approach still doesn't seem to help me.  I get the same error, just about type School:

2014-02-08 11:33:59,109 INFO sqlalchemy.engine.base.Engine ROLLBACK
(ProgrammingError) can't adapt type 'School' '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' {'city': u'Carddif', 'fk_school_id': <warrior.models.location.School object at 0x9abcf8c>, 'name': u'Choo Site', 'address1': u'1 Choo Way', 'address2': u'', 'postcode': u'12345', 'fk_country_id': u'1'}

How can I use relationships to do this?  If I do:

school = School()

site = Site()

site.fk_school_id = school

I get the above error.  If I change school to school.id I get the InstrumentedAttribute error again.

How to do this without doing multiple commits?

--
You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/yl_5BnwzfhA/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.

To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.

[Michael Bayer]

you’re getting confused over attributes that refer to foreign key columns and attributes that refer to relationships.

if you have a class as:

class Site(Base):

    # …

    fk_school_id = Column(Integer, ForeignKey(‘school.id’))

    school = relationship(School)

then say you have a Site:

my_site = Site()

you can:

A. assign scalar, typically integer values to the foreign key:

my_site.fk_school_id = 17

or 

B. assign objects to the relationship attribute:

my_school = Session.query(School).get(17)

my_site.school  = my_school

what you’re doing is this:

my_site.fk_school_id = my_school   #  <— wrong

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.