On Fri, Oct 26, 2018 at 2:29 PM Sven <[email protected]> wrote:
>
> Hello!
>
> I am working with the Composite Association Proxies example available under
> the following link:
>
> https://docs.sqlalchemy.org/en/latest/orm/extensions/associationproxy.html#composite-association-proxies
>
> My problem: when I delete the keys of the dictionary, SQLAlchemy let the
> values of the dictionary in the database. The keys are deleted, but not the
> values.
>
> I didn't modify the functioning of the Composite Association Proxies example.
> I just created and stored an "User" object before trying to delete the keys
> from the dictionary.
>
> Here is the code:
> from sqlalchemy import Column, Integer, String, ForeignKey, create_engine
> from sqlalchemy.orm import relationship, backref, Session
> from sqlalchemy.ext.associationproxy import association_proxy
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.orm.collections import attribute_mapped_collection
>
>
> engine = create_engine('sqlite:///foo.db')
> Base = declarative_base()
>
>
> class User(Base):
> __tablename__ = 'user'
> id = Column(Integer, primary_key=True)
> name = Column(String(64))
>
>
> # the same 'user_keywords'->'keyword' proxy as in
> # the basic dictionary example
> keywords = association_proxy(
> 'user_keywords',
> 'keyword',
> creator=lambda k, v:
> UserKeyword(special_key=k, keyword=v)
> )
>
>
> def __init__(self, name):
> self.name = name
>
>
> class UserKeyword(Base):
> __tablename__ = 'user_keyword'
> user_id = Column(Integer, ForeignKey('user.id'), primary_key=True)
> keyword_id = Column(Integer, ForeignKey('keyword.id'),
> primary_key=True)
> special_key = Column(String)
> user = relationship(User, backref=backref(
> "user_keywords",
> collection_class=attribute_mapped_collection("special_key"),
> cascade="all, delete-orphan"
> )
> )
>
>
> # the relationship to Keyword is now called
> # 'kw'
> kw = relationship("Keyword")
>
>
> # 'keyword' is changed to be a proxy to the
> # 'keyword' attribute of 'Keyword'
> keyword = association_proxy('kw', 'keyword')
>
>
> class Keyword(Base):
> __tablename__ = 'keyword'
> id = Column(Integer, primary_key=True)
> keyword = Column('keyword', String(64))
>
>
> def __init__(self, keyword):
> self.keyword = keyword
>
>
> Base.metadata.create_all(engine)
> session = Session(engine)
>
> john = User("john")
>
> session.add(john)
>
> john.keywords["k1"] = "v1"
> john.keywords["k2"] = "v2"
>
> session.commit()
>
> del john.keywords["k2"]
> del john.keywords["k1"]
>
> session.commit()
>
> Here are the values stored in the database after the two commits:
>
> Table USER:
>
> ID - NAME
> 1 - "john"
>
> Table USER_KEYWORD:
>
> USER_ID - KEYWORD_ID - SPECIAL_KEY
> The table is empty.
>
> Table KEYWORD:
>
> ID - KEYWORD
> 1 - "v1"
> 2 - "v2"
>
> Is it possible to configure SQLAlchemy in order to also delete the values of
> the dictionary from the database when the keys are deleted?
>
> I don't know if it can explains something but I am using PostgreSQL.
as given, you would configure UserKeyword.kw as:
# the relationship to Keyword is now called
# 'kw'
kw = relationship("Keyword", cascade="all, delete-orphan",
single_parent=True)
However, this is not a very useful setup, as it means even if you had
many of the same data for the "value" across many users, they'd all
use independent Keyword rows, and there's no real reason to have a
"keyword" table at all in that case, you'd just put the values in your
UserKeyword table.
Usually, the desired setup is that there are many User rows, many
UserKeyword rows, but the Keyword.keyword rows are unique. In this
case, you would also not want to delete Keyword rows when the
UserKeyword is deleted because lots of other UserKeyword rows would
also be referring to it.
>
> Thank you for your help!
>
> Sven
>
> --
> 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 [email protected].
> To post to this group, send email to [email protected].
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
