Python Sqlalchemy mysql "Cannot add or update a child row: a foreign key constraint fails"

Question

我正在尝试使用 sqlalchemy 和 mysql 创建一个简单的真实数据库。我不确定我做错了什么，尽管我认为这可能与我将行添加到表中的方式有​​关。下面是我的模型以及创建下面所有条目的代码。

这是我遇到的错误:

2014-03-12 14:53:52,109 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)
2014-03-12 14:53:52,139 INFO sqlalchemy.engine.base.Engine INSERT INTO accession_numbers (accession_number, gene) VALUES (%s, %s)
INFO:sqlalchemy.engine.base.Engine:INSERT INTO accession_numbers (accession_number, gene) VALUES (%s, %s)
2014-03-12 14:53:52,140 INFO sqlalchemy.engine.base.Engine (('AB009589', <__main__.Gene object at 0xe8ba090>), ('AB014887', <__main__.Gene object at 0xe220890>), ('AB019534', <__main__.Gene object at 0xe8baf10>), ('AB038490', <__main__.Gene object at 0xe8b8150>), ('AB046409', <__main__.Gene object at 0xe8b86d0>), ('AB051625', <__main__.Gene object at 0xe8b8c50>), ('AB051627', <__main__.Gene object at 0xe8b8ed0>), ('AB060808', <__main__.Gene object at 0xe8dd110>)  ... displaying 10 of 1317 total bound parameter sets ...  ('EU266531', <__main__.Gene object at 0x15093b50>), ('EU286279', <__main__.Gene object at 0x15093cd0>))
INFO:sqlalchemy.engine.base.Engine:(('AB009589', <__main__.Gene object at 0xe8ba090>), ('AB014887', <__main__.Gene object at 0xe220890>), ('AB019534', <__main__.Gene object at 0xe8baf10>), ('AB038490', <__main__.Gene object at 0xe8b8150>), ('AB046409', <__main__.Gene object at 0xe8b86d0>), ('AB051625', <__main__.Gene object at 0xe8b8c50>), ('AB051627', <__main__.Gene object at 0xe8b8ed0>), ('AB060808', <__main__.Gene object at 0xe8dd110>)  ... displaying 10 of 1317 total bound parameter sets ...  ('EU266531', <__main__.Gene object at 0x15093b50>), ('EU286279', <__main__.Gene object at 0x15093cd0>))
2014-03-12 14:53:52,143 INFO sqlalchemy.engine.base.Engine ROLLBACK
INFO:sqlalchemy.engine.base.Engine:ROLLBACK
---------------------------------------------------------------------------
IntegrityError                            Traceback (most recent call last)
<ipython-input-17-96f4786cad50> in <module>()
     29         session.add(acc)
     30 
---> 31 session.commit()

/usr/lib64/python2.7/site-packages/sqlalchemy/orm/session.pyc in commit(self)
    719                 raise sa_exc.InvalidRequestError("No transaction is begun.")
    720 
--> 721         self.transaction.commit()
    722 
    723     def prepare(self):

/usr/lib64/python2.7/site-packages/sqlalchemy/orm/session.pyc in commit(self)
    352         self._assert_active(prepared_ok=True)
    353         if self._state is not PREPARED:
--> 354             self._prepare_impl()
    355 
    356         if self._parent is None or self.nested:

/usr/lib64/python2.7/site-packages/sqlalchemy/orm/session.pyc in _prepare_impl(self)
    332                 if self.session._is_clean():
    333                     break
--> 334                 self.session.flush()
    335             else:
    336                 raise exc.FlushError(

/usr/lib64/python2.7/site-packages/sqlalchemy/orm/session.pyc in flush(self, objects)
   1822         try:
   1823             self._flushing = True
-> 1824             self._flush(objects)
   1825         finally:
   1826             self._flushing = False

/usr/lib64/python2.7/site-packages/sqlalchemy/orm/session.pyc in _flush(self, objects)
   1940         except:
   1941             with util.safe_reraise():
-> 1942                 transaction.rollback(_capture_exception=True)
   1943 
   1944     def is_modified(self, instance, include_collections=True,

/usr/lib64/python2.7/site-packages/sqlalchemy/util/langhelpers.pyc in __exit__(self, type_, value, traceback)
     56             exc_type, exc_value, exc_tb = self._exc_info
     57             self._exc_info = None   # remove potential circular references
---> 58             compat.reraise(exc_type, exc_value, exc_tb)
     59         else:
     60             self._exc_info = None   # remove potential circular references

/usr/lib64/python2.7/site-packages/sqlalchemy/orm/session.pyc in _flush(self, objects)
   1904             self._warn_on_events = True
   1905             try:
-> 1906                 flush_context.execute()
   1907             finally:
   1908                 self._warn_on_events = False

/usr/lib64/python2.7/site-packages/sqlalchemy/orm/unitofwork.pyc in execute(self)
    370                                     self.dependencies,
    371                                     postsort_actions):
--> 372                 rec.execute(self)
    373 
    374     def finalize_flush_changes(self):

/usr/lib64/python2.7/site-packages/sqlalchemy/orm/unitofwork.pyc in execute(self, uow)
    523         persistence.save_obj(self.mapper,
    524             uow.states_for_mapper_hierarchy(self.mapper, False, False),
--> 525             uow
    526         )
    527 

/usr/lib64/python2.7/site-packages/sqlalchemy/orm/persistence.pyc in save_obj(base_mapper, states, uowtransaction, single)
     62             _emit_insert_statements(base_mapper, uowtransaction,
     63                                     cached_connections,
---> 64                                     table, insert)
     65 
     66     _finalize_insert_update_commands(base_mapper, uowtransaction,

/usr/lib64/python2.7/site-packages/sqlalchemy/orm/persistence.pyc in _emit_insert_statements(base_mapper, uowtransaction, cached_connections, table, insert)
    539             multiparams = [rec[2] for rec in records]
    540             c = cached_connections[connection].\
--> 541                                 execute(statement, multiparams)
    542 
    543             for (state, state_dict, params, mapper,

/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.pyc in execute(self, object, *multiparams, **params)
    660                                                 object,
    661                                                 multiparams,
--> 662                                                 params)
    663         else:
    664             raise exc.InvalidRequestError(

/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.pyc in _execute_clauseelement(self, elem, multiparams, params)
    759             compiled_sql,
    760             distilled_params,
--> 761             compiled_sql, distilled_params
    762         )
    763         if self._has_events:

/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.pyc in _execute_context(self, dialect, constructor, statement, parameters, *args)
    872                                 parameters,
    873                                 cursor,
--> 874                                 context)
    875 
    876         if self._has_events:

/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.pyc in _handle_dbapi_exception(self, e, statement, parameters, cursor, context)
   1022                                         self.dialect.dbapi.Error,
   1023                                         connection_invalidated=self._is_disconnect),
-> 1024                                     exc_info
   1025                                 )
   1026 

/usr/lib64/python2.7/site-packages/sqlalchemy/util/compat.pyc in raise_from_cause(exception, exc_info)
    194         # the code line where the issue occurred
    195         exc_type, exc_value, exc_tb = exc_info
--> 196         reraise(type(exception), exception, tb=exc_tb)
    197 
    198 

/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.pyc in _execute_context(self, dialect, constructor, statement, parameters, *args)
    854                                     statement,
    855                                     parameters,
--> 856                                     context)
    857             elif not parameters and context.no_parameters:
    858                 self.dialect.do_execute_no_params(

/usr/lib64/python2.7/site-packages/sqlalchemy/connectors/mysqldb.pyc in do_executemany(self, cursor, statement, parameters, context)
     58 
     59     def do_executemany(self, cursor, statement, parameters, context=None):
---> 60         rowcount = cursor.executemany(statement, parameters)
     61         if context is not None:
     62             context._rowcount = rowcount

/usr/lib64/python2.7/site-packages/MySQLdb/cursors.pyc in executemany(self, query, args)
    204             r = 0
    205             for a in args:
--> 206                 r = r + self.execute(query, a)
    207             return r
    208         p = m.start(1)

/usr/lib64/python2.7/site-packages/MySQLdb/cursors.pyc in execute(self, query, args)
    172             del tb
    173             self.messages.append((exc, value))
--> 174             self.errorhandler(self, exc, value)
    175         self._executed = query
    176         if not self._defer_warnings: self._warning_check()

/usr/lib64/python2.7/site-packages/MySQLdb/connections.pyc in defaulterrorhandler(***failed resolving arguments***)
     34     del cursor
     35     del connection
---> 36     raise errorclass, errorvalue
     37 
     38 re_numeric_part = re.compile(r"^(\d+)")

IntegrityError: (IntegrityError) (1452, 'Cannot add or update a child row: a foreign key constraint fails (`chromo9`.`accession_numbers`, CONSTRAINT `accession_numbers_ibfk_1` FOREIGN KEY (`gene`) REFERENCES `genes` (`gene_identifier`))') 'INSERT INTO accession_numbers (accession_number, gene) VALUES (%s, %s)' (('AB009589', <__main__.Gene object at 0xe8ba090>), ('AB014887', <__main__.Gene object at 0xe220890>), ('AB019534', <__main__.Gene object at 0xe8baf10>), ('AB038490', <__main__.Gene object at 0xe8b8150>), ('AB046409', <__main__.Gene object at 0xe8b86d0>), ('AB051625', <__main__.Gene object at 0xe8b8c50>), ('AB051627', <__main__.Gene object at 0xe8b8ed0>), ('AB060808', <__main__.Gene object at 0xe8dd110>)  ... displaying 10 of 1317 total bound parameter sets ...  ('EU266531', <__main__.Gene object at 0x15093b50>), ('EU286279', <__main__.Gene object at 0x15093cd0>))

我的模型:

from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, String, Text
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import ForeignKey
from sqlalchemy.orm import sessionmaker

SETTINGS = {
            'DB':{
                  'USER':'root',
                  'LOCATION':'localhost',
                  'PORT':'3306',
                  'DATABASE_NAME':'chromo9',
                  },
            }

class Settings:
    def __init__(self, **attrs):
        for key, value in attrs.iteritems():
            if type(value) is dict:
                attrs[key] = Settings(**value)
        self.__dict__.update(attrs)

SETTINGS = Settings(**SETTINGS)

if not hasattr(SETTINGS.DB, 'PASSWD'):
    SETTINGS.DB.PASSWD = raw_input('Enter passwd for mysql user {}'.format(SETTINGS.DB.USER))

conn_string = 'mysql+mysqldb://{user}:{passwd}@{location}:{port}'.format(
                                            user = SETTINGS.DB.USER,
                                            passwd = SETTINGS.DB.PASSWD,
                                            location = SETTINGS.DB.LOCATION,
                                            port = SETTINGS.DB.PORT,
                                                                         )
#engine = create_engine('sqlite:///:memory:', )#echo=True)
engine = create_engine(conn_string, echo=True)
engine.execute('CREATE DATABASE IF NOT EXISTS {}'.format(SETTINGS.DB.DATABASE_NAME))
engine.execute('USE {}'.format(SETTINGS.DB.DATABASE_NAME))

Base = declarative_base()

class Gene(Base):
    __tablename__ = 'genes'

    gene_identifier= Column(Integer, primary_key=True)
    nucleotide_sequence = Column(Text)
    chromosome_location = Column(String(8))    


class Accession(Base):
    __tablename__ = 'accession_numbers'

    accession_number = Column(String(8), primary_key=True)
    gene = Column(Integer, ForeignKey('genes.gene_identifier'))


class Exon(Base):
    __tablename__ = 'exons'

    id = Column(Integer, primary_key=True)
    start = Column(Integer)
    end = Column(Integer)
    gene = Column(Integer, ForeignKey('genes.gene_identifier'))


class Protein(Base):
    __tablename__ = 'proteins'

    id = Column(Integer, primary_key=True)    
    name = Column(String(128))
    sequence = Column(Text)
    gene = Column(Integer, ForeignKey('genes.gene_identifier'))


Base.metadata.create_all(engine) # create the tables

创建所有对象:

Session = sessionmaker(bind=engine)
session = Session()

for record in records:
    gene = Gene(gene_identifier=record.gi ,nucleotide_sequence=record.sequence ,chromosome_location=record.locus)
    session.add(gene)

    for feature in record.features:

        if feature.key == 'CDS':
            translation, name  = None, None
            for qualifier in feature.qualifiers:
                if qualifier.key == '/translation=':
                    translation = qualifier.value
                if qualifier.key == '/product=':
                    name = qualifier.value
            # create protein object
            protein = Protein(name=name, sequence=translation, gene=gene)
            session.add(protein)

        if feature.key == 'exon':
            start, end = feature.location.split('..')
            start, end = int(''.join([d for d in start if d.isdigit()])), int(''.join([d for d in end if d.isdigit()]))
            exon = Exon(start=start,end=end,gene=gene)
            session.add(exon)

    for accession in record.accession:
        acc = Accession(accession_number=accession, gene=gene)
        session.add(acc)

session.commit()

Answer 1

有很多东西要看，所以我肯定我错过了一些东西，但由于我不确定你对你正在使用的数据库了解多少，我将从这里开始，希望它能有所帮助:

看起来您正在尝试插入引用不存在的基因的种质。那就是它提示的外键。看起来您试图在同一 session 中创建基因和加入。您可能需要在创建 Gene(以及以后更新所依赖的任何其他对象)之后调用 session.commit()。这会将 Gene 放入数据库中，然后供 Accession 引用。

这有点像瞎猜，因为我不熟悉 SQLAlchemy(也不知道什么是 Accession)。我在这里只是依靠我对 SQL 的了解。但问题肯定是在插入依赖于基因表的表中的行之前，将任何行放入基因表。