On 02/29/2016 06:15 PM, Alex Hewson wrote:
Hi Mike,
Thanks for the quick response. If that's the intended behaviour I'll go
back to non-bulk inserts for my inherited types. Doubtless I could work
around it by inserting N new Entities, fetching their autoincrement ID's
then using them to make Child1 and Child2's but I don't trust myself
with the added complexity.
Well it's the fetching of these autoincrement identifiers that takes all
the time since you need to do it immediately on a per-row basis,
otherwise, at least on most databases besides SQLite, you can never know
if other transactions have also inserted rows at the same time. Once
you're doing a SELECT or RETURNING for every row you no longer can do an
efficient bulk insert.
Cheers,
Alex.
On Monday, February 29, 2016 at 10:38:22 PM UTC, Alex Hewson wrote:
Hello All,
I'm trying to use the new bulk_save_objects() to improve performance
on bulk inserts, and have run into a problem. If
bulk_save_objects() is used to save objects of a polymorphic class..
1. They are created correctly in the DB, with polymorphic type
column populated correctly
2. BUT queries for the new objects will return one of incorrect
type. In my case I'm getting instances of Child1 back when I
would expect to get a Child2.
The following code demonstrates the problem:
|
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
fromsqlalchemy importcreate_engine
fromsqlalchemy importColumn,Integer,SmallInteger,String,ForeignKey
fromsqlalchemy.orm importsessionmaker
fromsqlalchemy.ext.declarative importdeclarative_base
Base=declarative_base()
classEntity(Base):
__tablename__ ='Entity'
Id=Column(Integer,primary_key=True,nullable=False)
Content=Column(String)
_polytype =Column(SmallInteger,nullable=False)
__mapper_args__ ={
'polymorphic_identity':1,
'polymorphic_on':_polytype
}
classChild1(Entity):
__tablename__ ='Child1'
MyId=Column(ForeignKey("Entity.Id"),primary_key=True)
__mapper_args__ ={'polymorphic_identity':11}
classChild2(Entity):
__tablename__ ='Child2'
MyId=Column(ForeignKey("Entity.Id"),primary_key=True)
__mapper_args__ ={'polymorphic_identity':12}
if__name__ =='__main__':
# engine = create_engine('sqlite:///:memory:', echo=False)
engine =create_engine('sqlite:///test.db',echo=False)
Session=sessionmaker(bind=engine)
sess =Session()
Base.metadata.create_all(engine)
c1_many =[Child1(Content="c1inst_%d"%i)fori inrange(0,1000)]
c2_many =[Child2(Content="c2inst_%d"%i)fori inrange(0,1000)]
sess.bulk_save_objects(c1_many)
sess.bulk_save_objects(c2_many)
# sess.add_all(c1_many)
# sess.add_all(c2_many)
sess.flush()
sess.commit()
forc insess.query(Child1):
assertisinstance(c,Child1)
forc insess.query(Child2):
assertisinstance(c,Child2)
|
All the calls to assert isinstance(c, Child1) complete
successfully. But once we start checking for Child2 - boom, we are
still getting back Child1 instances.
At first I wondered if I was misunderstanding SA's implementation of
polymorphism, so tried inserting rows the traditional way with
sess.add_all(). But that works fine so I think I've exposed a bug
in the new bulk_save_objects() code.
My environment is Python 3.5.1, SQLAlchemy==1.0.12, SQLite 3.8.10.2
on OSX.
--
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to [email protected]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
