sorry. i obviously didn't explain too well. it DOES work with ORM. i
don't have a problem at all.
however, i can't work with the tables using engine.conect or straight
MySQLdb, for that matter.
in fact, closer examination suggests that not only are the onupdate,
ondelete = CASCADE not set
in the tables but trying to set them manually also results in an
error:
Can't create table 'test2.#sql-a6_fb' (errno: 121)
following below, a stylized version of the code (which works with
ORM). however, i need to be able to
use 'straight' sql on all tables created with ORM.
thx
import ....
engine = ....
...
main_table = Table('main_table', meta,
Column(u'id', Integer, primary_key=True),
Column(u'data', Unicode(255)),
mysql_engine='InnoDB'
)
relation_table = Table('relation_table', meta,
Column(u'id', Integer, primary_key=True),
Column(u'data', Unicode(255)),
mysql_engine='InnoDB'
)
main_relation_table = Table('main_relation_table', meta,
Column(u'id', Integer, primary_key=True),
Column(u'main_id', Integer, ForeignKey('main_table.id')),
Column(u'relation_id', Integer, ForeignKey('relation_table.id')),
mysql_engine='InnoDB'
)
class MainObj(object): pass
class RelObj(object): pass
mapper(MainObj, main_table, properties={
'relations': relation(RelObj, secondary=main_relation_table,
backref='main_obj',\
cascade="all, delete, delete-orphan")
})
mapper(RelObj, relation_table)
Session = sessionmaker(bind=engine)
meta.create_all()
session = Session()
mainobj = MainObj()
mainobj.data = u'test main'
relobj = RelObj()
relobj.data = u'relation object instance 1'
mainobj.relations.append(relobj)
session.add(mainobj)
session.commit()
session.close()
if __name__=='__main__':
session = Session()
obj = session.query(MainObj).all()
for o in obj:
#print o.id, o.relations
pass
obj = session.query(MainObj).filter(MainObj.id==1).all()
for m in obj:
print m.id, m.data, m.relations
obj = session.query(RelObj).filter(RelObj.id==1).all()
for r in obj:
print r.id, r.main_obj
session.close()
meta.drop_all()
sys.exit()
On Jan 29, 4:27 am, Alex K <klizhen...@gmail.com> wrote:
> Well, this error says that you have rows in other(or same) tables
> referring to this row you are going to delete,
> and you should delete referring rows first. If you want SQLA to do it
> automatically,
> you need to use sessions and mappers (not raw SQL expression engine),
> more info here:
>
> http://www.sqlalchemy.org/docs/05/ormtutorial.html#configuring-delete...
>
> Regards,
> Alex
>
> On Jan 29, 8:33 am, n00b <pyn...@gmail.com> wrote:
>
> > back again, sorry.
>
> > i specified a model with a few one-to-many and one many-to-many
> > relations using SA 0.51 in MySql 5.1.25 rc; tables are all INNODB. all
> > works well and as expected in the ORM realm. however, when i'm trying
> > to use SQL Expression for a delete (row) operation, i get the dreaded
>
> > IntegrityError: (IntegrityError) (1451, 'Cannot delete or update a
> > parent row: a foreign key constraint fails
>
> > specifically, i'm using:
>
> > engine = create_engine('mysql://root:@localhost:3306/test2')
> > connection = engine.connect()
> > metadata = MetaData(bind=engine)
> > main_table = Table('main_table', metadata, autoload=True)
> > target_id = 1 #for illustrative purposes, primary key to delete
> > connection.execute(main_table.delete().where(main_table.c.id
> > ==target_id))
>
> > where main_table is my main table and all other tables link to its id
> > (primary key int) via
> > foreign keys.
>
> > thx for you help
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
