IntegrityError with many to many self referential
rivka
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
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.
> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>