Re: [sqlalchemy] Re: session.query().get() is unsupported during flush for getting an object that was just added?
On Feb 6, 2012, at 4:05 PM, Kent wrote: > On Feb 1, 3:17 pm, Kent wrote: >>> If the value is based on what's already been INSERTed for previous rows, >>> I'd emit a SQL statement to get at the value.If it's based on some kind >>> of natural consideration that isn't dependent on the outcome of an INSERT >>> statement, then you can do the looping above within the before_flush() >>> event and assign everything at once.Basically you need to "batch" the >>> same way the UOW itself does. >> >> is IdentitySet an OrderedSet? if I loop through session.new, for >> example, am I guaranteed to hit these in the same order they were >> added to the session? > > Is there a way to work out the order in which session.new items were > added... I take it session.new is not ordered. InstanceState has an attribute "insert_order" that's assigned as objects are added, so the sort at flush time is based on that attribute. It is unique across all of session.new. > Did you mention you were in the works of an instance level event for > 'before_flush()', similar to 'before_insert/before_update'? there's room to support a series of extensions to the before_flush() listener such that it would provide filtered collections or individual instances to the listener callable.Some notion of this was always in mind, though actually implementing it is way down on my list of priorities.It would be implemented within a _listen method on SessionEvents. _listen() is called by the event system when a user-defined listener function is first established against an event.In this case the given listener function would be wrapped inside a filtering callable that calls it as many times as needed passing in the appropriate objects.For examples of listeners being wrapped on listen, see AttributeEvents._listen, InstanceEvents._listen, MapperEvents._listen. -- 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.
[sqlalchemy] Re: session.query().get() is unsupported during flush for getting an object that was just added?
On Feb 1, 3:17 pm, Kent wrote: > > If the value is based on what's already been INSERTed for previous rows, > > I'd emit a SQL statement to get at the value.If it's based on some kind > > of natural consideration that isn't dependent on the outcome of an INSERT > > statement, then you can do the looping above within the before_flush() > > event and assign everything at once.Basically you need to "batch" the > > same way the UOW itself does. > > is IdentitySet an OrderedSet? if I loop through session.new, for > example, am I guaranteed to hit these in the same order they were > added to the session? Is there a way to work out the order in which session.new items were added... I take it session.new is not ordered. Did you mention you were in the works of an instance level event for 'before_flush()', similar to 'before_insert/before_update'? -- 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.
Re: [sqlalchemy] Question about mutable primary keys and foreign keys
On Feb 6, 2012, at 12:39 PM, Michael Naber wrote:
> I am trying to efficiently update all things that foreign key to a particular
> record so that they instead foreign key to a different record. I provided an
> example that illustrates the problem I am trying to solve. Please see my
> question at the bottom of the code.
>
> Thanks for your help,
> Michael
>
>
> from sqlalchemy import Column, Integer, String, ForeignKey, create_engine
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.orm import scoped_session, sessionmaker, relationship, backref
>
> Base = declarative_base()
> Session = scoped_session(sessionmaker())
>
> def init_model(dsn):
> engine = create_engine(dsn)
> Session.configure(bind=engine)
> Base.metadata.bind = engine
> Base.metadata.create_all(engine)
>
> class Person(Base):
> __tablename__ = 'person'
> id = Column(Integer, primary_key=True)
> name = Column(String(50))
> addresses = relationship("Address", backref="person",
> cascade="all", passive_updates=False)
> vehicles = relationship("Vehicle", backref="person",
> cascade="all", passive_updates=False)
>
> class Address(Base):
> __tablename__ = 'address'
> id = Column(Integer, primary_key=True)
> email = Column(String(50))
> person_id = Column(Integer, ForeignKey('person.id'))
> #backref: person
> def __repr__(self):
> return 'id: %s, person_id: %s, email: %s' % \
> (self.id, self.person_id, self.email)
>
> class Vehicle(Base):
> __tablename__ = 'vehicle'
> id = Column(Integer, primary_key=True)
> color = Column(String)
> kind = Column(String)
> person_id = Column(Integer, ForeignKey('person.id'))
> #backref: person
> def __repr__(self):
> return 'id: %s, person_id: %s, kind: %s, color: %s' % \
> (self.id, self.person_id, self.kind, self.color)
>
> init_model('sqlite:///:memory:')
> s = Session()
> s.add_all([Person(name='Mike',
> addresses=[Address(email='mi...@mike.com'),
> Address(email='mi...@mike.com')],
> vehicles=[Vehicle(color='red', kind='truck'),
> Vehicle(color='white', kind='van')]),
>Person(name='Steve',
> addresses=[Address(email='ste...@steve.com')],
> vehicles=[Vehicle(color='orange', kind='car')])])
>
> mike = s.query(Person).filter_by(name='Mike').one()
>
> '''
> I can easily change the person_id value of everything that had a FK to mike
> by changing mike.id:
> '''
>
> mike.id = 50
>
> for vehicle in s.query(Vehicle).all():
> print vehicle.person_id
> for address in s.query(Address).all():
> print address.person_id
>
> '''
> Question:
> What would I do if I wanted to take all the things that FK to Mike, and then
> FK them to Steve instead?
> Not possible to do mike.id = steve.id because primary key must be unique.
>
> Of course in this example I could issue separate updates to Address and
> Vehicle which would not be too bad,
> but in my real project there are dozens of other classes besides Address and
> Vehicle. Is there a more efficient way besides
> separate updates?
the ORM-centric way to move the FK on these items, though not necessarily the
"all in one UPDATE statement" way, is to just move them at the Python level:
steve.addresses = mike.addresses
steve.vehicles = mike.vehicles
Session.commit()
if you want to emit an UPDATE for all FKs at once and skip the unit of work,
the SQL UPDATE statement supports this, you can get at it in terms of ORM
objects using query.update():
session.query(Address).with_parent(mike).update({"person_id":50})
session.query(Vehicle).with_parent(mike).update({"person_id":50})
The above two statements will try to search through the session to find all
objects already present which are affected, in order to refresh them. If you
just need to emit the two UPDATE statements and don't need to worry about any
Address or Vehicle objects that may have been affected, you can pass the flag
"synchronize_session=False" to the update() call - saves some time.
http://docs.sqlalchemy.org/en/latest/orm/query.html?highlight=query.update#sqlalchemy.orm.query.Query.update
--
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.
[sqlalchemy] Question about mutable primary keys and foreign keys
I am trying to efficiently update all things that foreign key to a
particular record so that they instead foreign key to a different record. I
provided an example that illustrates the problem I am trying to solve.
Please see my question at the bottom of the code.
Thanks for your help,
Michael
from sqlalchemy import Column, Integer, String, ForeignKey, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker, relationship,
backref
Base = declarative_base()
Session = scoped_session(sessionmaker())
def init_model(dsn):
engine = create_engine(dsn)
Session.configure(bind=engine)
Base.metadata.bind = engine
Base.metadata.create_all(engine)
class Person(Base):
__tablename__ = 'person'
id = Column(Integer, primary_key=True)
name = Column(String(50))
addresses = relationship("Address", backref="person",
cascade="all", passive_updates=False)
vehicles = relationship("Vehicle", backref="person",
cascade="all", passive_updates=False)
class Address(Base):
__tablename__ = 'address'
id = Column(Integer, primary_key=True)
email = Column(String(50))
person_id = Column(Integer, ForeignKey('person.id'))
#backref: person
def __repr__(self):
return 'id: %s, person_id: %s, email: %s' % \
(self.id, self.person_id, self.email)
class Vehicle(Base):
__tablename__ = 'vehicle'
id = Column(Integer, primary_key=True)
color = Column(String)
kind = Column(String)
person_id = Column(Integer, ForeignKey('person.id'))
#backref: person
def __repr__(self):
return 'id: %s, person_id: %s, kind: %s, color: %s' % \
(self.id, self.person_id, self.kind, self.color)
init_model('sqlite:///:memory:')
s = Session()
s.add_all([Person(name='Mike',
addresses=[Address(email='mi...@mike.com'),
Address(email='mi...@mike.com')],
vehicles=[Vehicle(color='red', kind='truck'),
Vehicle(color='white', kind='van')]),
Person(name='Steve',
addresses=[Address(email='ste...@steve.com')],
vehicles=[Vehicle(color='orange', kind='car')])])
mike = s.query(Person).filter_by(name='Mike').one()
'''
I can easily change the person_id value of everything that had a FK to mike
by changing mike.id:
'''
mike.id = 50
for vehicle in s.query(Vehicle).all():
print vehicle.person_id
for address in s.query(Address).all():
print address.person_id
'''
Question:
What would I do if I wanted to take all the things that FK to Mike, and
then FK them to Steve instead?
Not possible to do mike.id = steve.id because primary key must be unique.
Of course in this example I could issue separate updates to Address and
Vehicle which would not be too bad,
but in my real project there are dozens of other classes besides Address
and Vehicle. Is there a more efficient way besides
separate updates?
Thanks,
Michael
'''
--
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.
[sqlalchemy] Re: Having trouble getting a subquery to return entities
Michael:
I created a full reproducing test (http://pastebin.com/vutfUgpk) and
the test ... WORKS!
So, there's obviously something else creeping in here somewhere. It
was useful for me to create the test. Thanks for the suggestion.
I will close the loop when I find the cause of my original issue.
THANKS
-C
On Feb 3, 9:45 pm, Michael Bayer wrote:
> On Feb 3, 2012, at 11:28 AM, cbc wrote:
>
>
>
>
>
>
>
>
>
> > Hi:
>
> > I'm having trouble getting a subquery to return entities.
>
> > class Question(Base):
> > __tablename__ = 'question'
> > question_id = Column(INTEGER(unsigned=True), primary_key=True)
> > ...etc
> > q_answers = relationship("Answer", backref="question")
>
> > class Answer(Base):
> > __tablename__ = 'answer'
> > answer_id = Column(INTEGER(unsigned=True), primary_key=True)
> > user_id = Column(INTEGER(unsigned=True), ForeignKey('user.user_id'),
> > nullable=False)
> > question_id = Column(INTEGER(unsigned=True),
> > ForeignKey('question.question_id'), nullable=False)
> > ...etc
>
> > stmt = session.query(Answer).filter(Answer.user_id ==
> > user_id).subquery()
> > answers = aliased(Answer, stmt)
> > query = session.query(Question, answers)\
> > .outerjoin(answers, Question.q_answers)\
> > .filter(Question.question_group_id == question_group_id)
> > questions = query.all()
>
> > This generates MySQL that returns all desired columns and returns NULL
> > if question has not yet been answered by the specified user. Groovy so
> > far.
>
> > I was expecting tuples in the list of questions (Answer, Question),
> > but the first element is always None. e.g.
>
> dir(questions[0])
> > [None, 'Question', ... etc
>
> > So while I'm expecting the subquery results to be understood as
> > entities (Answer), this isn't happening. But I cannot figure out why.
>
> > Where have I failed?
>
> The outerjoin here is from Question to Answer so you should never have None
> in the first element of the tuple. I'd need a full reproducing test to see
> exactly what would cause Question to be retuned as None.
--
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.
Re: [sqlalchemy] Disabling auto-conversion to Decimal in Oracle connections
On Feb 5, 2012, at 11:10 PM, Anthony Foglia wrote: > > If I do shut off the outputtypehandler on the connection, will that > cause any other problems as I start adding Table objects with the > appropriate Columns? Will the connection outputtypehandler be reset > when it goes back into the pool? Or should I just keep one connection > from returning to the pool solely for these old style queries? The outputtypehandler should only be set on the Connection by SQLAlchemy using the same event I gave you, the "connect" event, and it should occur before your handler would get to it. So whatever state you set on it should remain. Without the handler, you get back datatypes like str instead of unicode, and float instead of Decimal. The only area that there would be some dependency on the type returned is when a TypeEngine, like String, Unicode, or Numeric are in use. The String type should be fine. The cx_oracle-specific implementation of numeric here, which is sqlalchemy.dialects.oracle.cx_oracle._OracleNumeric, looks like it might assume it's getting a Decimal or string back if that supports_native_decimal flag is turned on, so if you're using that type you might need to flip that flag off, or do an outputtypehandler similar to the one that's there which just returns decimals as strings. Attached is a patch that allows "coerce_to_decimal" to be passed to create_engine() and will disable the output handling if passed as False. Do you want to give it a try, I can commit it for 0.7.6 if it does the trick. > > -- > --Anthony > > -- > 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. > 2399.patch Description: Binary data -- 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.
[sqlalchemy] @validates & clean-up
I am having some trouble cleaning up after my @validates method throws an exception. The validation is of a relationship attribute and the foreign record should be removed. Are there some general tips? Otherwise I will post a code example. -- 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.
[sqlalchemy] Understanding sqlalchemy memory usage and releasing unused memory
I wanted to get idea about how much memory my sqlalchemy objects are taking. I used pympler library and used its class tracking function. I am using declarative base for defining the model and hence tables. I am pretty new to web development and python in general. So some questions may not be entirely due to sqlalchemy but due to my less knowledge about python. My User model which contain 13 column attributes, 1 association proxy for many to many connections and 6 one to many attributes set using lazy='dynamic'. When I tracked its memory usage only 6 attributes were loaded as others were set deffered and memory used was 1.6 mb. I tried to do rough calculations and thought if one object was taking this much then loading 100 will take 160mb. Then I created 50 user objects and then tracked memory and to my surprise total memory consumed was only 2 mb! What is the reason of this?? Are this objects sharing some base which is is around 1.5 mb and actual object is just in kbs? I tracked another type of object defined using 5 fields took only about 30kb and another object containing only 2 fields was 90 kb. Why is this different behavior in sizes? Reading about memory usage and sqlalchemy in this group and other places that once python process accumulates memory then they release only once closed I am little confused. Does it mean when I have loaded 100 objects and their usage is over, after that its memory will not be released back to system?? Final question expiring an object creates a weak reference and can be collected by gc if not used. Does expunging an object does the same? mainly how can i make memory taken by object free after usage is over. Thas quite a lot of questions. Thank you for reading. Regards, Manav Goel -- 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.
