On Dec 4, 2011, at 1:44 AM, rivka wrote:
> 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'
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.
>
> 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
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> 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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
