Michael, Thanks so much for your prompt answer. Instead of going into the extensive details of my program - I decided to abstract from it and just try to get down the basic operations of the construct. So - I wrote a very simple tutorial that pretty much implements what the documentation recommends (Pg. 81 together with the stuff from pg. 71)
SO I have: from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import backref, relationship from sqlalchemy import Column, MetaData, Table from sqlalchemy import Integer, String, ForeignKey engine = create_engine('sqlite:///:memory:', echo=False) Base = declarative_base() metadata = MetaData(engine) Session = sessionmaker(bind=engine) session = Session() class Ref_Association(Base): __table__= Table('article_xrefs', Base.metadata, Column('referencing_id', Integer, ForeignKey("articles.article_id"), primary_key=True), Column('referenced_id', Integer, ForeignKey("articles.article_id"), primary_key=True), Column('info', String) ) class Article(Base): __tablename__='articles' article_id = Column(Integer, primary_key=True) headline = Column(String(150)) body = Column(String) references = relationship("Article", secondary=Ref_Association.__table__, primaryjoin= article_id==Ref_Association.__table__.c.referencing_id, secondaryjoin= article_id==Ref_Association.__table__.c.referenced_id, backref="referencing") def __init__(self, headline=None, body=None): self.headline = headline self.body = body def __repr__(self): return 'Article %d: "%s keywords: %s"' % \ (self.article_id, self.headline, [assoc.keyword for assoc in self.keywords]) Base.metadata.create_all(engine) a1 = Article(headline="Python is cool!", body="(to be written)") a2 = Article(headline="SQLAlchemy Tutorial", body="You're reading it") a3 = Article(headline="What is the ORM", body="Try it - if you don't like it - skip it") a4 = Article(headline="The wonders of mapping", body="Once you wrapped your brain around it") r_assoc1 = Ref_Association() a1.references.append(r_assoc1) session.add_all([a1,a2,a3,a4,r_assoc1]) session.commit() arts = session.query(Article).all() for art in arts: print art +++++++++++++++++ and when I run it I get: +++++++++++++++++ pydev debugger: starting Traceback (most recent call last): File "/Applications/eclipse/plugins/org.python.pydev.debug_2.2.4.2011110216/pysrc/pydevd.py", line 1307, in <module> debugger.run(setup['file'], None, None) File "/Applications/eclipse/plugins/org.python.pydev.debug_2.2.4.2011110216/pysrc/pydevd.py", line 1060, in run pydev_imports.execfile(file, globals, locals) #execute the script File "/Users/RIvka/python/Tutorial/tutorial_2.py", line 59, in <module> a1.references.append(r_assoc1) File "/Library/Python/2.6/site-packages/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/orm/collections.py", line 939, in append item = __set(self, item, _sa_initiator) File "/Library/Python/2.6/site-packages/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/orm/collections.py", line 914, in __set item = getattr(executor, 'fire_append_event')(item, _sa_initiator) File "/Library/Python/2.6/site-packages/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/orm/collections.py", line 605, in fire_append_event item, initiator) File "/Library/Python/2.6/site-packages/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/orm/attributes.py", line 680, in fire_append_event value = ext.append(state, value, initiator or self) File "/Library/Python/2.6/site-packages/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/orm/attributes.py", line 893, in append child_state.get_impl(self.key).append( File "/Library/Python/2.6/site-packages/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/orm/state.py", line 121, in get_impl return self.manager.get_impl(key) File "/Library/Python/2.6/site-packages/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/orm/attributes.py", line 1137, in get_impl return self[key].impl KeyError: 'referencing' +++++++++++++++++ AND : I ran all kind of experimentations and found that 1. I have to use the hybrid approach or classical mapping one (yes - I watched your tutorial last night) - so that the table is declared explicitly - or else the mapper fails to understand the connectivity - and yes - all the details in the relationship (i.e. secondary, primaryjoin and secondaryjoin) are absolutely demanded by the mapper - or else it barfs. So - the question is : 1. What is the issue with the 'referencing' backref? 2. If the articles are the nodes and the Association table describes the edge - how do I enter the information into the Ref_Association table so as to populate the values for the 'referencing_id' and the 'referenced_id' columns? I built another prototype for the many:many without self referential (It is attached - you are welcomed to use it as an example) following the example on page 71 of the doc (I am attaching the file) - and there it was fairly simple to define the edge. However - here I have a single backref value for the edge - so I am not clear how to initialize it - or otherwise define the edge end nodes. Thanks, Rivka On Dec 4, 2011, at 4:25 PM, sqlalchemy@googlegroups.com wrote: > Today's Topic Summary > Group: http://groups.google.com/group/sqlalchemy/topics > > IntegrityError with many to many self referential [2 Updates] > Passing additional arguments to event listeners ? [1 Update] > IntegrityError with many to many self referential > rivka <rivka.shen...@gmail.com> Dec 03 10:44PM -0800 > > Hi, > > I am trying to implement the above (M:M self referential). I have > patents which cite others - so I have the parent as the PatentInfo and > a child class for the citation - since each citation - apart from the > patent number that it cites - has a few other attributes that I want > to preserve. > > To verify that the problem is local - I went ahead and disabled the > ForeignKey on the citNum in CitedPatInfo and downgraded the > relationship in the parent class (PatentInfo) to a simple relationship > (like the one with the other tables) and I managed to write the data > successfully into the DB and read it back. > > The code: > > class CitedPatInfo(Base): > > __tablename__ = 'citings' > pNum = Column(Integer, ForeignKey('pat_info_main.pNum'), > primary_key=True) > citNum = Column(Integer, ForeignKey('pat_info_main.pNum'), > primary_key=True) > citBy = Column(CHAR(1)) > citPhase = Column(String) > > cit_by = {'A':'Applicant', 'E':'Examiner'} > > def __init__(self, cit={'p_num':0, 'phase':'', > 'info':CitationInfo()}) : > > if isinstance(cit, CitedPatInfo): > self.pNum = cit.pNum > self.citBy = cit.citBy > self.citPhase = cit.citPhase > self.citNum = cit.citNum > else: > self.pNum = cit['p_num'] > self.citPhase = self.TranslatePhaseKey(cit['phase']) > self.citBy = cit['info'].cited_by > self.citNum = cit['info'].num > > if self.pNum and not(self.citBy in self.cit_by): > logging.error('CitedPatInfo Pat#: %d: Unrecognized citeBy > type: %s', > self.pNum, self.citBy) > > ... > > and the parent: > > > class PatentInfo(Base): > __tablename__ = "pat_info_main" > pNum = Column(Integer, primary_key=True) > pStatus = Column(String) > pTitle = Column(String) > pLang = Column(String) > > ## backref indicates the attribute established during the append > on the child class > ## and refers to the parent object > > pProcessInfo = relationship(ProcessDocInfo, > backref="pat_info_main", > primaryjoin = > pNum==ProcessDocInfo.pNum) > pParties = relationship(Party, backref="pat_info_main", > primaryjoin = pNum==Party.pNum) > pEuClass = relationship(EuClass, backref="pat_info_main", > primaryjoin = pNum==EuClass.pNum) > pUSClass = relationship(USClass, backref="pat_info_main", > primaryjoin = pNum==USClass.pNum) > pCitedUSPats = relationship(CitedPatInfo, > secondary=CitedPatInfo, > primaryjoin = pNum==CitedPatInfo.pNum, > secondaryjoin = > pNum==CitedPatInfo.citNum, > backref = "pat_info_main") > pCitedNonUSPats = relationship(CitedIntPatInfo, > backref="pat_info_main", > primaryjoin = > pNum==CitedIntPatInfo.pNum) > pCitedLits = relationship(CitedLitInfo, backref="pat_info_main", > primaryjoin = pNum==CitedLitInfo.pNum) > > def __init__(self, pDat): > > if isinstance(pDat, PatentInfo): > self.pNum = pDat.pNum > self.pStatus = pDat.pStatus > self.pTitle = pDat.pTitle > self.pLang = pDat.pLang > > elif isinstance(pDat, PatentDatItem): > self.pNum = pDat.pat_num > self.pStatus = pDat.status > self.pTitle = pDat.bib_dat.title > self.pLang = pDat.bib_dat.lang > ## Process documents > > if self.pStatus=='N' and self.pTitle: > self.pStatus = 'Y' > > After I add the data for a single patent and try to execute > session.commit() I get: > Traceback (most recent call last): > File "/Applications/eclipse/plugins/ > org.python.pydev.debug_2.2.4.2011110216/pysrc/pydevd.py", line 1307, > in <module> > debugger.run(setup['file'], None, None) > File "/Applications/eclipse/plugins/ > org.python.pydev.debug_2.2.4.2011110216/pysrc/pydevd.py", line 1060, > in run > pydev_imports.execfile(file, globals, locals) #execute the script > File "/Users/RIvka/python/OPS/src/OPSXface.py", line 338, in > <module> > ops_xface.Acquire() > File "/Users/RIvka/python/OPS/src/OPSXface.py", line 174, in Acquire > self.parser.ParseXML(doc, num_entries) > File "/Users/RIvka/python/OPS/src/ParseResponse.py", line 162, in > ParseXML > self.session.commit() > File "/opt/local/Library/Frameworks/Python.framework/Versions/2.6/ > lib/python2.6/site-packages/SQLAlchemy-0.7b3-py2.6.egg/sqlalchemy/orm/ > session.py", line 617, in commit > self.transaction.commit() > File "/opt/local/Library/Frameworks/Python.framework/Versions/2.6/ > lib/python2.6/site-packages/SQLAlchemy-0.7b3-py2.6.egg/sqlalchemy/orm/ > session.py", line 293, in commit > self._prepare_impl() > File "/opt/local/Library/Frameworks/Python.framework/Versions/2.6/ > lib/python2.6/site-packages/SQLAlchemy-0.7b3-py2.6.egg/sqlalchemy/orm/ > session.py", line 277, in _prepare_impl > self.session.flush() > File "/opt/local/Library/Frameworks/Python.framework/Versions/2.6/ > lib/python2.6/site-packages/SQLAlchemy-0.7b3-py2.6.egg/sqlalchemy/orm/ > session.py", line 1465, in flush > self._flush(objects) > File "/opt/local/Library/Frameworks/Python.framework/Versions/2.6/ > lib/python2.6/site-packages/SQLAlchemy-0.7b3-py2.6.egg/sqlalchemy/orm/ > session.py", line 1534, in _flush > flush_context.execute() > File "/opt/local/Library/Frameworks/Python.framework/Versions/2.6/ > lib/python2.6/site-packages/SQLAlchemy-0.7b3-py2.6.egg/sqlalchemy/orm/ > unitofwork.py", line 327, in execute > rec.execute(self) > File "/opt/local/Library/Frameworks/Python.framework/Versions/2.6/ > lib/python2.6/site-packages/SQLAlchemy-0.7b3-py2.6.egg/sqlalchemy/orm/ > unitofwork.py", line 471, in execute > uow > File "/opt/local/Library/Frameworks/Python.framework/Versions/2.6/ > lib/python2.6/site-packages/SQLAlchemy-0.7b3-py2.6.egg/sqlalchemy/orm/ > mapper.py", line 2092, in _save_obj > execute(statement, params) > File "/opt/local/Library/Frameworks/Python.framework/Versions/2.6/ > lib/python2.6/site-packages/SQLAlchemy-0.7b3-py2.6.egg/sqlalchemy/ > engine/base.py", line 1259, in execute > params) > File "/opt/local/Library/Frameworks/Python.framework/Versions/2.6/ > lib/python2.6/site-packages/SQLAlchemy-0.7b3-py2.6.egg/sqlalchemy/ > engine/base.py", line 1392, in _execute_clauseelement > compiled_sql, distilled_params > File "/opt/local/Library/Frameworks/Python.framework/Versions/2.6/ > lib/python2.6/site-packages/SQLAlchemy-0.7b3-py2.6.egg/sqlalchemy/ > engine/base.py", line 1500, in _execute_context > context) > File "/opt/local/Library/Frameworks/Python.framework/Versions/2.6/ > lib/python2.6/site-packages/SQLAlchemy-0.7b3-py2.6.egg/sqlalchemy/ > engine/base.py", line 1493, in _execute_context > context) > File "/opt/local/Library/Frameworks/Python.framework/Versions/2.6/ > lib/python2.6/site-packages/SQLAlchemy-0.7b3-py2.6.egg/sqlalchemy/ > engine/default.py", line 325, in do_execute > cursor.execute(statement, parameters) > sqlalchemy.exc.IntegrityError: (IntegrityError) citings.pNum may not > be NULL u'INSERT INTO citings ("citNum", "citBy", "citPhase") VALUES > (?, ?, ?)' (5290642, 'E', 'SEA') > > > Thanks, > > RIvka > > Michael Bayer <mike...@zzzcomputing.com> Dec 04 10:36AM -0500 > > On Dec 4, 2011, at 1:44 AM, rivka wrote: > > > ## Process documents > > > if self.pStatus=='N' and self.pTitle: > > self.pStatus = 'Y' > > I notice you are manipulating the foreign key attributes of CitedPatInfo > directly in the constructor. This is fine but may conflict with mutation > activities that you perform on the pCitedUSPats / pat_info_main > relationships, which would take precedence, assuming activity occurred on > them before flush. > > Usually it should be easy enough as : > > class CitedPatInfo(Base): > # mapping > > def __init__(self, patent_info): > self.pat_info_main = patent_info > > that is, don't manipulate foreign key values directly, just use the > relationships as intended. > > The configuration of the relationships seem to be fine, though you shouldn't > need those primaryjoin/secondaryjoin conditions as they are automatically > determined among simple foreign key relationships, and including them when > not needed only introduces more potential for mistakes, and also makes > configuration more complicated. > > There's no actual usage example here, that is how it is the CitedPatInfo and > PatientInfo objects are being constructed, so it's not possible to say > exactly why CitedPatInfo has NULL for one of its keys, but it would appear > that the value was never set and/or the CitedPatInfo.pat_info_main > relationship (or the other direction, pCitedUSPats) weren't set up before the > commit. > > > > Passing additional arguments to event listeners ? > "Łukasz Czuja" <luk...@czuja.pl> Dec 04 01:31AM -0800 > > Thank you all for tips. I'll probably stick to the functools method > though. > > I recommend adding a paragraph about this to docs as more people will > stumble upon this as this was possible with the extension system (one > could pass additional vars to extension constructor and use them in > callbacks) and no longer (directly) with events. > > cheers. > > > You received this message because you are subscribed to the Google Group > sqlalchemy. > You can post via email. > To unsubscribe from this group, send an empty message. > For more options, visit this group. > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalchemy@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en.
-- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
On Dec 4, 2011, at 4:25 PM, sqlalchemy@googlegroups.com wrote:
|
''' Created on Dec 4, 2011 @author: RIvka ''' from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import backref, relationship from sqlalchemy import Column, MetaData, Table from sqlalchemy import Integer, String, ForeignKey engine = create_engine('sqlite:///:memory:', echo=False) Base = declarative_base() metadata = MetaData(engine) Session = sessionmaker(bind=engine) session = Session() class KW_Association(Base): __tablename__= 'articles_keywords' article_id = Column(Integer, ForeignKey("articles.article_id"), primary_key=True) keyword_id = Column(Integer, ForeignKey("keywords.keyword_id"), primary_key=True) keyword = relationship("Keyword", backref="articles") def __init__(self, keyword=None, article=None): self.keyword = keyword self.article = article class Article(Base): __tablename__='articles' article_id = Column(Integer, primary_key=True) headline = Column(String(150)) body = Column(String) keywords = relationship(KW_Association, backref="article" ) def __init__(self, headline=None, body=None): self.headline = headline self.body = body def __repr__(self): return 'Article %d: "%s keywords: %s"' % \ (self.article_id, self.headline, [assoc.keyword for assoc in self.keywords]) class Keyword(Base): __tablename__='keywords' keyword_id = Column(Integer, primary_key=True) keyword_name = Column(String(50)) body = Column(String) def __init__(self, name=None): self.keyword_name = name def __repr__(self): return self.keyword_name Base.metadata.create_all(engine) a1 = Article(headline="Python is cool!", body="(to be written)") a2 = Article(headline="SQLAlchemy Tutorial", body="You're reading it") k_tutorial = Keyword('tutorial') k_cool = Keyword('cool') k_unfinished = Keyword('unfinished') assoc1=KW_Association() assoc1.keyword=k_unfinished a1.keywords.append(assoc1) ## A second way of doing the same assoc2=KW_Association(k_cool,a1) assoc3=KW_Association(k_cool,a2) session.add_all([a1,a2,k_tutorial,k_cool,k_unfinished, assoc1, assoc2, assoc3]) session.commit() arts = session.query(Article).all() for art in arts: print art