how do I update a relationship that goes through mulitple tables?

[Abbey Reisle]

sqlalchemy version: 1.2.16

database: mysql 5.7

so I currently have a relationship between 2 tables (that has a join table) that works well for querying, but I need to be able to update as well

I've tried a few different things that I've listed below, but haven't been able to get anything working

note: we use the automapper so not all the columns referrenced are explicitly defined

so there's 4 tables involved in this relationship

`Funding`, `Funding_Permissions`, `App_Role`, and `App_User`

`Funding_Permissions` has a foreign key to the other 3 tables,

and I want a relationship from `Funding` to `App_User` with a specific `App_Role`

currently the Funding_Permissions and Funding tables look like this

```python

class Funding_Permissions(Base):
    FK_Funding__ID = Column(
        'FK_Funding__ID', Integer, ForeignKey('Funding.Funding_ID'), nullable=False
    )


    FK_App_User__ID = Column(
        'FK_App_User__ID', Integer, ForeignKey('App_User.app_user_id'), nullable=False
    )
    FK_App_Role__ID = Column(
        'FK_App_Role__ID', Integer, ForeignKey('App_Role.app_role_id'), nullable=False
    )


class Funding(Base):
    __tablename__ = 'Funding'


    dissemination_editors = relationship(
        'App_User',
        primaryjoin='Funding_Permissions.FK_Funding__ID == Funding.Funding_ID',
        secondary='join(App_Role, Funding_Permissions, and_(Funding_Permissions.FK_App_Role__ID == App_Role.app_role_id, App_Role.role_name == "Funding_Dissemination_Editor"))',
        secondaryjoin='Funding_Permissions.FK_App_User__ID == App_User.app_user_id',
        uselist=True,
        viewonly=True,
    )

```

and this works as I expect for doing this

```bash

>>> DBSession.query(Funding).options(joinedload(Funding.dissemination_editors)).get(1246)

```

## attempt 1 at making it updateable: removed viewonly from relationship

I tried just removing the viewonly keyword as that seemed simplest

```python

class Funding(Base):
    __tablename__ = 'Funding'


    dissemination_editors = relationship(
        'App_User',
        primaryjoin='Funding_Permissions.FK_Funding__ID == Funding.Funding_ID',
        secondary='join(App_Role, Funding_Permissions, and_(Funding_Permissions.FK_App_Role__ID == App_Role.app_role_id, App_Role.role_name == "Funding_Dissemination_Editor"))',
        secondaryjoin='Funding_Permissions.FK_App_User__ID == App_User.app_user_id',
        uselist=True,
    )

```

but that resulting in the following error

```bash

>>> funding = DBSession.query(Funding).get(1246)
>>> user = DBSession.query(App_User).get(5)
>>> funding.dissemination_editors.append(user)
>>> DBSession.flush()
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/sqlalchemy/orm/scoping.py", line 162, in do
    return getattr(self.registry(), name)(*args, **kwargs)
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 2424, in flush
    self._flush(objects)
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 2562, in _flush
    transaction.rollback(_capture_exception=True)
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/sqlalchemy/util/langhelpers.py", line 67, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 277, in reraise
    raise value
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 2522, in _flush
    flush_context.execute()
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/sqlalchemy/orm/unitofwork.py", line 416, in execute
    rec.execute(self)
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/sqlalchemy/orm/unitofwork.py", line 534, in execute
    self.dependency_processor.process_saves(uow, states)
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/sqlalchemy/orm/dependency.py", line 1153, in process_saves
    uowcommit, secondary_insert, secondary_update, secondary_delete
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/sqlalchemy/orm/dependency.py", line 1214, in _run_crud
    statement = self.secondary.insert()
AttributeError: 'Join' object has no attribute 'insert'

```

## attempt 2: inheritance

so next I decided that the role in the join condition was probably 

problematic, and I found the inheritance docs so that seemed like it might work

so I added the mapper_args and changed the relationship to use the new class

`Funding_Dissemination_Editor_Permissions` instead

```python

class Funding_Permissions(Base):
    FK_Funding__ID = Column(
        'FK_Funding__ID', Integer, ForeignKey('Funding.Funding_ID'), nullable=False
    )


    FK_App_User__ID = Column(
        'FK_App_User__ID', Integer, ForeignKey('App_User.app_user_id'), nullable=False
    )
    FK_App_Role__ID = Column(
        'FK_App_Role__ID', Integer, ForeignKey('App_Role.app_role_id'), nullable=False
    )


    __mapper_args__ = {
        'polymorphic_identity': 'Funding_Permissions',
        "polymorphic_on": case([
            (FK_App_Role__ID == 4, "Funding_Dissemination_Editor_Permissions"),
            # ...other cases
        ], else_='Funding_Permissions'),
    }




class Funding_Dissemination_Editor_Permissions(Funding_Permissions):
    __mapper_args__ = {
        'polymorphic_identity': 'Funding_Dissemination_Editor_Permissions',
    }


class Funding(Base):
    __tablename__ = 'Funding'


    dissemination_editors = relationship(
        'App_User',
        primaryjoin='Funding.Funding_ID==Funding_Dissemination_Editor_Permissions.FK_Funding__ID',
        secondary=Funding_Dissemination_Editor_Permissions.__table__,
        secondaryjoin='App_User.app_user_id==Funding_Dissemination_Editor_Permissions.FK_App_User__ID',
    )

```

but this didn't return what I expected.

the relationship didn't add the `WHERE` clause that the [docs](https://docs.sqlalchemy.org/en/13/orm/inheritance.html#single-table-inheritance) talked about:

>Querying for a particular subclass in the hierarchy will render as a SELECT against the base table, which will include a WHERE clause that limits rows to those with a particular value or values present in the discriminator column or expression.

so I thought maybe `secondary=Funding_Dissemination_Editor_Permissions.__table__,`

was the issue there and then read about `association_proxy`

## attempt 3: association_proxy

so I keep the inheritance stuff I had tried before,

but replaced `dissemination_editors` like below

```python

c

lass Funding(Base):
    __tablename__ = 'Funding'


    dissemination_editor_permissions = relationship(
        Funding_Dissemination_Editor_Permissions,
    )


    dissemination_editors = association_proxy(
        'dissemination_editor_permissions',
        'app_user',
        creator=lambda app_user: Funding_Dissemination_Editor_Permissions(
            app_user=app_user,
            FK_App_Role__ID=4,
        ),
    )

```

and now this works

```bash

>>> funding = DBSession.query(Funding).get(1246)
>>> user = DBSession.query(App_User).get(5)
>>> funding.dissemination_editors.append(user)

```

but this doesn't 

```bash

>>> funding = DBSession.query(Funding).get(1246)
>>> user = DBSession.query(App_User).get(5)
>>> funding.dissemination_editors = [user]
>>> DBSession.flush()
Traceback (most recent call last):
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1230, in _execute_context
    cursor, statement, parameters, context
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 536, in do_execute
    cursor.execute(statement, parameters)
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/pymysql/cursors.py", line 167, in execute
    result = self._query(query)
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/pymysql/cursors.py", line 323, in _query
    conn.query(q)
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/pymysql/connections.py", line 836, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/pymysql/connections.py", line 1020, in _read_query_result
    result.read()
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/pymysql/connections.py", line 1303, in read
    first_packet = self.connection._read_packet()
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/pymysql/connections.py", line 982, in _read_packet
    packet.check_error()
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/pymysql/connections.py", line 394, in check_error
    err.raise_mysql_exception(self._data)
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/pymysql/err.py", line 120, in raise_mysql_exception
    _check_mysql_exception(errinfo)
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/pymysql/err.py", line 112, in _check_mysql_exception
    raise errorclass(errno, errorvalue)
pymysql.err.IntegrityError: (1452, 'Cannot add or update a child row: a foreign key constraint fails (`seqdev`.`Funding_Permissions`, CONSTRAINT `Funding_Permissions_ibfk_1` FOREIGN KEY (`FK_Funding__ID`) REFERENCES `Funding` (`Funding_ID`))')


The above exception was the direct cause of the following exception:


Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/sqlalchemy/orm/scoping.py", line 162, in do
    return getattr(self.registry(), name)(*args, **kwargs)
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 2424, in flush
    self._flush(objects)
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 2562, in _flush
    transaction.rollback(_capture_exception=True)
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/sqlalchemy/util/langhelpers.py", line 67, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 277, in reraise
    raise value
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 2522, in _flush
    flush_context.execute()
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/sqlalchemy/orm/unitofwork.py", line 416, in execute
    rec.execute(self)
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/sqlalchemy/orm/unitofwork.py", line 583, in execute
    uow,
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/sqlalchemy/orm/persistence.py", line 236, in save_obj
    update,
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/sqlalchemy/orm/persistence.py", line 976, in _emit_update_statements
    statement, multiparams
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 974, in execute
    return meth(self, multiparams, params)
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 273, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1093, in _execute_clauseelement
    distilled_params,
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1234, in _execute_context
    e, statement, parameters, cursor, context
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1452, in _handle_dbapi_exception
    util.raise_from_cause(sqlalchemy_exception, exc_info)
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 296, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 276, in reraise
    raise value.with_traceback(tb)
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1230, in _execute_context
    cursor, statement, parameters, context
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 536, in do_execute
    cursor.execute(statement, parameters)
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/pymysql/cursors.py", line 167, in execute
    result = self._query(query)
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/pymysql/cursors.py", line 323, in _query
    conn.query(q)
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/pymysql/connections.py", line 836, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/pymysql/connections.py", line 1020, in _read_query_result
    result.read()
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/pymysql/connections.py", line 1303, in read
    first_packet = self.connection._read_packet()
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/pymysql/connections.py", line 982, in _read_packet
    packet.check_error()
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/pymysql/connections.py", line 394, in check_error
    err.raise_mysql_exception(self._data)
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/pymysql/err.py", line 120, in raise_mysql_exception
    _check_mysql_exception(errinfo)
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/pymysql/err.py", line 112, in _check_mysql_exception
    raise errorclass(errno, errorvalue)
sqlalchemy.exc.IntegrityError: (pymysql.err.IntegrityError) (1452, 'Cannot add or update a child row: a foreign key constraint fails (`seqdev`.`Funding_Permissions`, CONSTRAINT `Funding_Permissions_ibfk_1` FOREIGN KEY (`FK_Funding__ID`) REFERENCES `Funding` (`Funding_ID`))') [SQL: 'UPDATE `Funding_Permissions` SET `FK_Funding__ID`=%(FK_Funding__ID)s WHERE `Funding_Permissions`.`Funding_Permissions_ID` = %(Funding_Permissions_Funding_Permissions_ID)s'] [parameters: {'FK_Funding__ID': None, 'Funding_Permissions_Funding_Permissions_ID': 1733}] (Background on this error at: http://sqlalche.me/e/gkpj)

```

and neither does this

```bashEnter code here...

>>> DBSession.query(Funding).options(joinedload(Funding.dissemination_editors)).get(1246)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 1498, in options
    return self._options(False, *args)
  File "<string>", line 2, in _options
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/sqlalchemy/orm/base.py", line 212, in generate
    fn(self, *args[1:], **kw)
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 1517, in _options
    opt.process_query(self)
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/sqlalchemy/orm/strategy_options.py", line 168, in process_query
    self._process(query, True)
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/sqlalchemy/orm/strategy_options.py", line 524, in _process
    raiseerr,
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/sqlalchemy/orm/strategy_options.py", line 671, in _bind_loader
    "mapper option expects " "string key or list of attributes"
sqlalchemy.exc.ArgumentError: mapper option expects string key or list of attributes

```

I'm not sure what to try next as I think there's just something fundamental that I'm not understanding.

Any assistance would be greatly appreciated

[Mike Bayer]

a join condition like that can't be writeable, you would need to map all four tables separately and use relationships that are each between two classes only.

so kudos to learning the documentation really well it looks like you tried a whole lot of things.

i don't have any immediate insight into the best mapping here this is something I would need to see in totality and play with it myself.

if you could illustrate a succinct version of the MySQL schema you are targeting, that is, the tables and their major keys , I could have a better idea how it might be best expressed in terms of mappings.    But usually the best way to start is one class per table, then things like association proxies are used to narrow the gaps.

  

112, in _check_mysql_exception
    raise errorclass(errno, errorvalue)
sqlalchemy.exc.IntegrityError: (pymysql.err.IntegrityError) (1452, 'Cannot add or update a child row: a foreign key constraint fails (`seqdev`.`Funding_Permissions`, CONSTRAINT `Funding_Permissions_ibfk_1` FOREIGN KEY (`FK_Funding__ID`) REFERENCES `Funding` (`Funding_ID`))') [SQL: 'UPDATE `Funding_Permissions` SET `FK_Funding__ID`=%(FK_Funding__ID)s WHERE `Funding_Permissions`.`Funding_Permissions_ID` = %(Funding_Permissions_Funding_Permissions_ID)s'] [parameters: {'FK_Funding__ID': None, 'Funding_Permissions_Funding_Permissions_ID': 1733}] (Background on this error at: http://sqlalche.me/e/gkpj)

```

and neither does this

```bashEnter code here...

>>> DBSession.query(Funding).options(joinedload(Funding.dissemination_editors)).get(1246)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 1498, in options
    return self._options(False, *args)
  File "<string>", line 2, in _options
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/sqlalchemy/orm/base.py", line 212, in generate
    fn(self, *args[1:], **kw)
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 1517, in _options
    opt.process_query(self)
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/sqlalchemy/orm/strategy_options.py", line 168, in process_query
    self._process(query, True)
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/sqlalchemy/orm/strategy_options.py", line 524, in _process
    raiseerr,
  File "/home/areisle/bitbucket/pylims-api/server/venv3.7/lib/python3.7/site-packages/sqlalchemy/orm/strategy_options.py", line 671, in _bind_loader
    "mapper option expects " "string key or list of attributes"
sqlalchemy.exc.ArgumentError: mapper option expects string key or list of attributes

```

I'm not sure what to try next as I think there's just something fundamental that I'm not understanding.

Any assistance would be greatly appreciated

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/3051ca99-cd34-42e7-806b-b74f9a54dc0d%40googlegroups.com.