MySQL 5.0.38 with InnoDB tables / SQL Alchemy 0.4.4 / Python 2.5.1
Hi, I'm really impressed with SQL Alchemy in
general, but now that I'm trying to use it for a large-ish project,
I'm getting stuck more often than I'd like.
The problem is that when I try to create a record
that has foreign keys, I get: Exception: IntegrityError
'Cannot add or update a child row a foreign
key constraint fails. FOREIGN KEY(`tag`)
REFERENCES `tags`(`id`)
I _know_ that the tag with the particular ID is already
in the tags table.
The first time I had this problem, I had a one-to-
many between just two tables, and I would get this
error if I did a session.save(user) on the user side (
the one side) and then did a session.save(log_entry)
on the many side. I _really_ want to do the session
.saves like this sometimes. I got it to work by doing
the recommended user.logs.append(log_entry) with a
relation defined in the user mapper.
Now, I have a slightly more complex situation, and I
can't find a way to make it work. Every
time I try to tag the log entry, I get the
Integrity Errors.
My tables and mappings are:
self.user_table = Table('user', metadata,
Column('id', Integer, primary_key=True),
Column('username', Unicode(60), index=True),
Column('full_name', Unicode(80)),
Column('email', Unicode(80), index=True),
Column('pubkey', Unicode(120)),
Column('password_md5', Unicode(120)),
mysql_engine="innoDB"
)
user_mapper = mapper(model.User, self.user_table,
properties = {
'logs': relation(model.Log, backref='author'),
'tags': relation(model.Tagging, backref='tagger'),
}
)
self.logtype_table = Table('log_type', metadata,
Column('id', Integer, primary_key=True),
Column('mime_type', Unicode(20)),
Column('is_python', Boolean),
mysql_engine="innoDB"
)
mapper(model.LogType, self.logtype_table)
self.log_table = Table('log', metadata,
Column('id', Integer, primary_key=True),
Column('author_id', Integer, ForeignKey('user.id')),
Column('created', DateTime, nullable=False),
Column('type', Integer, ForeignKey('log_type.id')),
Column('title', Unicode(120), nullable=False),
Column('contents', UnicodeText, nullable=False),
Column('parent', Integer, ForeignKey('log.id')),
mysql_engine="innoDB"
)
log_mapper = mapper(model.Log, self.log_table,
properties = { 'tags': relation(model.Tagging) }
)
self.tags_table = Table('tags', metadata,
Column('id', Integer, primary_key=True),
Column('name', Unicode(120), nullable=False),
mysql_engine="innoDB"
)
mapper(model.Tag, self.tags_table)
self.taggings_table = Table('taggings', metadata,
Column('id', Integer, primary_key=True),
Column('tagger_id', Integer, ForeignKey('user.id'),
nullable=False),
Column('when', DateTime, nullable=False),
Column('tag', Integer, ForeignKey('tags.id')),
Column('tagged', Integer, ForeignKey('log.id')),
mysql_engine="innoDB"
)
mapper(model.Tagging, self.taggings_table)
You can see that the last table has three Foreign Keys.
I can add a user (self.jim) to the database, and also a log
entry (self.log), and then I try to add a tag, and a tagging:
# add a user and a log entry
.......
# add a tag
t = model.Tag('maplesong')
self.session.save(t)
self.session.flush() # successful
# and with that tag, we create a tagging
tg = model.Tagging(self.jim, self.log, t, datetime.today())
self.jim.tags.append(tg)
self.log.tags.append(tg)
self.session.flush() # Integrity Error
At which point I get the Exception: IntegrityError 'Cannot
add or update a child row a foreign key
constraint fails. FOREIGN KEY(`tag`) REFERENCES
`tags`(`id`)
I do not want to add the tagging to the tag... t.taggings.
add(tg) That doesn't make sense to me.
So my question is: How can I avoid these inappropriate
Integrity Errors? The foreign key that it's complaining
about really does already exist.
Thanks,
-Jim
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
