Re: [sqlalchemy] Polyphormic class insertion by SA .vs. by database triggers
What would it do , INSERT into the parent table, then the child row is created implicitly and then.do an UPDATE to the child table ? where does the stored procedure get the values to be set up for the child row ? The trigger is activated on INSERTS only, and creates the child row using the same id (pk) as the parent row. So, the only mandatory value comes from the parent row (all other are set with NULL or DEFAULT). So yeah I think you'd have to be using a mapper here that doesn't know about the second table, or isn't expecting to INSERT into it. Some pattern like this: # when we do #1895, this will be possible mapper(Base, base_table, polymorphic_on=base_table.c.type) mapper(Child, child_table, inherits=Base, polymorphic_identity='child') session.add_all([Base(type='child'), Base(type='child'), Base(type='child')) # until then, probably like this: mapper(InsertChild, base_table) mapper(Base, base_table, polymorphic_on=base_table.c.type) mapper(Child, child_table, inherits=Base, polymorphic_identity='child') session.add_all([InsertBase(type='child'), InsertBase(type='child'), InsertBase(type='child')) c1, c2, c3 = Session.query(Child).all() You could use MapperExtension to issue an UPDATE to the child table, using the connection given, as the InsertBase objects are inserted. It would all be very inconvenient.One way to change your stored procedure or trigger in a minimal way, depending on the database in use, is to either disable the trigger for the connection using a pool listener, or have your stored procedure check some connection-wide variable, again which you'd set up on each connection using a pool listener. Thanks for all the precious tips! -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
[sqlalchemy] Polyphormic class insertion by SA .vs. by database triggers
Hi list,
I've a sample application that makes use of polymorphism both with and
without SQLAlchemy.
On the non-SA side, I've implemented record insertion via stored procedures.
Now, integrating new code based on SA, the ORM behaviour is to create both
the base and the inherited record. SA fails at this points because the
inherited records has already been created on the database side.
Sample error:
---
sqlalchemy.exc.IntegrityError: (IntegrityError) duplicate key value violates
unique constraint compound_item_pkey
'INSERT INTO compound_item (id) VALUES (%(id)s)' {'id': 7}
---
I dont't have the option to touch the legacy (but current) code, so I expect
to be able to make SA adapt to this situation. I guess I'd need a mapper
option that does not insert inherited records or maybe ignores if they're
already there.
Do you guys think I'm in the right direction? Does SA have this already? Any
pointers to where to start to code this?
Thanks!
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] Polyphormic class insertion by SA .vs. by database triggers
On Oct 14, 2010, at 4:25 PM, Cleber Rodrigues wrote:
Hi list,
I've a sample application that makes use of polymorphism both with and
without SQLAlchemy.
On the non-SA side, I've implemented record insertion via stored procedures.
Now, integrating new code based on SA, the ORM behaviour is to create both
the base and the inherited record. SA fails at this points because the
inherited records has already been created on the database side.
Sample error:
---
sqlalchemy.exc.IntegrityError: (IntegrityError) duplicate key value violates
unique constraint compound_item_pkey
'INSERT INTO compound_item (id) VALUES (%(id)s)' {'id': 7}
---
I dont't have the option to touch the legacy (but current) code, so I expect
to be able to make SA adapt to this situation. I guess I'd need a mapper
option that does not insert inherited records or maybe ignores if they're
already there.
Do you guys think I'm in the right direction? Does SA have this already? Any
pointers to where to start to code this?
What would it do , INSERT into the parent table, then the child row is created
implicitly and then.do an UPDATE to the child table ? where does the
stored procedure get the values to be set up for the child row ?
So yeah I think you'd have to be using a mapper here that doesn't know about
the second table, or isn't expecting to INSERT into it.
Some pattern like this:
# when we do #1895, this will be possible
mapper(Base, base_table, polymorphic_on=base_table.c.type)
mapper(Child, child_table, inherits=Base, polymorphic_identity='child')
session.add_all([Base(type='child'), Base(type='child'), Base(type='child'))
# until then, probably like this:
mapper(InsertChild, base_table)
mapper(Base, base_table, polymorphic_on=base_table.c.type)
mapper(Child, child_table, inherits=Base, polymorphic_identity='child')
session.add_all([InsertBase(type='child'), InsertBase(type='child'),
InsertBase(type='child'))
c1, c2, c3 = Session.query(Child).all()
You could use MapperExtension to issue an UPDATE to the child table, using the
connection given, as the InsertBase objects are inserted.
It would all be very inconvenient.One way to change your stored procedure
or trigger in a minimal way, depending on the database in use, is to either
disable the trigger for the connection using a pool listener, or have your
stored procedure check some connection-wide variable, again which you'd set up
on each connection using a pool listener.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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.
