On Wed, Jun 3, 2020, at 3:42 PM, Abbey Reisle wrote:
> 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,
> )
>
> ```
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.
## 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 13
>
>
>
> 03, 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
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+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/3051ca99-cd34-42e7-806b-b74f9a54dc0d%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/3051ca99-cd34-42e7-806b-b74f9a54dc0d%40googlegroups.com?utm_medium=email&utm_source=footer>.
--
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/017a531b-2b61-48d0-803f-dbef971c0774%40www.fastmail.com.
