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
