[sqlalchemy] Re: Allowing orphaned children

[Michael Bayer]

cascade=all includes delete cascade.   any Hat objects attached to  
User will be deleted when the User is deleted.To resolve, leave  
the cascade argument out.  it defaults to save-update, merge which  
is enough for most use cases.


On Feb 6, 2009, at 11:05 PM, James wrote:


 Hi, I'm trying to set up a model where child objects are allowed to
 not have parents. At present, I can't get SA to leave the children
 intact, despite having ondelete=SET NULL and no delete-orphans.

 This is with SA 0.4.3.

 To demonstrate my confusion, can someone explain why this code deletes
 all my hats:

 import sys, time
 from datetime import datetime
 from sqlalchemy import Table, Column, ForeignKey, MetaData,
 create_engine
 from sqlalchemy.orm import relation, sessionmaker, mapper, backref
 from sqlalchemy import String, Unicode, Integer, DateTime

 metadata=MetaData()
 engine = create_engine(sqlite:///:memory:)

 users_table = Table('tg_user', metadata,
Column('user_id', Integer, primary_key=True),
 )

 hat_table = Table('hat', metadata,
Column('id', Integer, primary_key=True),
Column('user_id', Integer, ForeignKey('tg_user.user_id',
 ondelete='SET NULL')),
 )

 metadata.create_all(engine)

 class User(object):
pass

 class Hat(object):
pass

 mapper(User, users_table)

 mapper(Hat, hat_table,
properties = {
'user': relation(User, backref=backref(hats,
 cascade=all)),
}
 )

 Session = sessionmaker(bind=engine, autoflush=False,
 transactional=True)
 session = Session()

 me = User()
 me.hats.extend([Hat(), Hat(), Hat()])
 session.save(me)
 session.flush()

 print session.query(Hat).count(), hats
 session.delete(me)
 session.flush()
 print session.query(Hat).count(), hats

 Thank you!
 James
 


--~--~-~--~~~---~--~~
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: Allowing orphaned children

[James]

Oh, of course - thanks Michael!

On Feb 7, 1:51 pm, Michael Bayer mike...@zzzcomputing.com wrote:
 cascade=all includes delete cascade.   any Hat objects attached to  
 User will be deleted when the User is deleted.    To resolve, leave  
 the cascade argument out.  it defaults to save-update, merge which  
 is enough for most use cases.

 On Feb 6, 2009, at 11:05 PM, James wrote:



  Hi, I'm trying to set up a model where child objects are allowed to
  not have parents. At present, I can't get SA to leave the children
  intact, despite having ondelete=SET NULL and no delete-orphans.

  This is with SA 0.4.3.

  To demonstrate my confusion, can someone explain why this code deletes
  all my hats:

  import sys, time
  from datetime import datetime
  from sqlalchemy import Table, Column, ForeignKey, MetaData,
  create_engine
  from sqlalchemy.orm import relation, sessionmaker, mapper, backref
  from sqlalchemy import String, Unicode, Integer, DateTime

  metadata=MetaData()
  engine = create_engine(sqlite:///:memory:)

  users_table = Table('tg_user', metadata,
     Column('user_id', Integer, primary_key=True),
  )

  hat_table = Table('hat', metadata,
     Column('id', Integer, primary_key=True),
     Column('user_id', Integer, ForeignKey('tg_user.user_id',
  ondelete='SET NULL')),
  )

  metadata.create_all(engine)

  class User(object):
     pass

  class Hat(object):
     pass

  mapper(User, users_table)

  mapper(Hat, hat_table,
     properties = {
         'user': relation(User, backref=backref(hats,
  cascade=all)),
     }
  )

  Session = sessionmaker(bind=engine, autoflush=False,
  transactional=True)
  session = Session()

  me = User()
  me.hats.extend([Hat(), Hat(), Hat()])
  session.save(me)
  session.flush()

  print session.query(Hat).count(), hats
  session.delete(me)
  session.flush()
  print session.query(Hat).count(), hats

  Thank you!
  James
--~--~-~--~~~---~--~~
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] Multi tread operation and max query in sqlalchemy

[reetesh nigam]

Hi All,
i am not able to use max query in sqlalchemy?
currently i am using this:
r= model.session.query(model.RadReport).max
(model.t_reports.c.reportid)



2009-02-06 20:52:07,453  INFO *Main  Thread*
[sqlalchemy.engine.base.Engine.0x..b0:_cursor_execute] SELECT max
(t_reports.reportid) AS max_1

FROM t_reports

2009-02-06 20:52:07,453  INFO *MainThread*
[sqlalchemy.engine.base.Engine.0x..b0:_cursor_execute] []

2009-02-06 20:52:07,453  DEBUG*MainThread*
[sqlalchemy.engine.base.Engine.0x..b0:_init_metadata] Col ('max_1',)

2009-02-06 20:52:07,467  DEBUG*MainThread*
[sqlalchemy.engine.base.Engine.0x..b0:__init__] Row (None,)





 I executed the query in Mysql and got the correct value

SELECT max(t_reports.reportid) AS max_1 FROM t_reports
.

please let me know where i have done work!
--~--~-~--~~~---~--~~
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] Declarative with Enthoughts Traits framework.

[cputter]

Hi guys and girls,

I've recently discovered the joys of using sqlalchemy and would love
to using it together with Traits.  A few months back there was an
attempt to integrate sqlalchemy into traits, though it wasn't really
comprehensive in exploiting all of sqlalchemy's potential.

So I'm trying to work on that and combine ext.Declarative with traits.
The basic idea is to use the DeclarativeMeta type to generate Columns
from Traits and pass those on for the Declarative extension to do its
magic.  This would allow mixing of sqlalchemy attributes and trait
attributes in a single class so that we could still make use of all
the relational setup sqlalchemy does in any case.

Reading through several threads and looking at Elixir's SA integration
helped me a bit though I couldn't find any documentation on how to
implement the InstrumentationManager interface.  I'm assuming this
would be essential for letting Traits and SQLAlchemy play well
together.

There's still a lot of work to do, and I'm not really sure what needs
to be done for everything to work properly.  Would really appreciate
it if someone could help me out.


Here's an example of how it's working at the moment, I'll add the
actual implementation at the end.

#
class User(HasTraitsORM):
__tablename__ = 'users'

id = Column('id', Integer, primary_key=True)
name = Str(sqldb=True)

def _name_changed(self, old, new):
print 'Changed name from %s to %s.' % (old, new)

def __repr__(self):
return 'User(%s, %s)' % (self.id, self.name)

people = ['John', 'Charls','Steve','Smith','Jane']

for per in people:
obj = User(name=per)
sess = sqlservice.Session()
sess.add(obj)
sess.commit()
sess.close()
print obj

session = sqlservice.Session()
print '\nQuery all users\n'
for user in session.query(User).order_by(User.name).all():
print user

session.close()




Which spits out:
###
Changed name from  to John.
User(users.id, )
Changed name from  to Charls.
User(users.id, )
Changed name from  to Steve.
User(users.id, Steve)
Changed name from  to Smith.
User(users.id, Smith)
Changed name from  to Jane.
User(users.id, Jane)

Query all users

User(2, Charls)
User(1, John)

##

Which is really strange behaviour.  There's obviously something wrong
in my implementation of HasTraitsORM but why the different results
within the same loop???  Why add only two instances?

Totally baffles me.

Here's the rest of my code, hope somehow can help me out.  It's very
messy, I've been hacking at it like crazy with no success :-)

Hope you're all having a great weekend.
-Chris


##

# Standard library imports.
import logging

# Enthought library imports
from enthought.preferences.api import Preferences
from enthought.traits.api import \
HasTraits, MetaHasTraits, Int, Str, Bool, Float, Any,\
String, Enum, Python, \
on_trait_change, TraitListObject

# Package imports
import sqlalchemy
from sqlalchemy import Column, Integer
from sqlalchemy.schema import MetaData
from sqlalchemy.orm.interfaces import MapperProperty,
InstrumentationManager
from sqlalchemy.orm.attributes import get_attribute, set_attribute,
is_instrumented
from sqlalchemy.orm.collections import InstrumentedList,
collection_adapter
from sqlalchemy.ext.declarative import _as_declarative


# Setup a logger for this module.
logger = logging.getLogger(__name__)


TRAIT_MAPPING = {
 Int : 'sqlalchemy.Integer',
 Str : 'sqlalchemy.Text',
 Enum : 'sqlalchemy.Text',
 String : 'sqlalchemy.Text',
 Float : 'sqlalchemy.Float',
 Bool : 'sqlalchemy.Boolean',
 }


class HasTraitsORMState(InstrumentationManager):
def __init__(self, cls):
self.states = {}

def instrument_attribute(self, class_, key, attr):
pass

def install_descriptor(self, class_, key, attr):
pass

def uninstall_descriptor(self, class_, key, attr):
pass

def instrument_collection_class(self, class_, key,
collection_class):
return ObjectCollection

def get_instance_dict(self, class_, instance):
return instance.__dict__

def initialize_instance_dict(self, class_, instance):
instance.reset_traits()

def initialize_collection(self, key, state, factory):
data = factory()
return ObjectCollectionAdapter(key, state, data), data

def install_state(self, class_, instance, state):
self.states[id(instance)] = state

def state_getter(self, class_):
def find(instance):
return self.states[id(instance)]
return find


class ObjectCollectionAdapter(object):

An adapter for SQLAlchemy for TraitsListObject which is the
collection
we use for storing instances of classes within attributes of
other
classes.

TODO:  Think of a way to get this to 

[sqlalchemy] Re: Declarative with Enthoughts Traits framework.

[Christiaan Putter]

Hi Michael,

Thanks for your swift reply.

I wasn't really sure which way to go with combining Traits and SA.
From reading through the source it seemed that I had to use
InstrumentationManager, I think it said somewhere it was the stable
public interface.  I had a look at the Trellis source as well, they
subclass the ClassManager though.  I'll give that a try next.  Either
way it's not quite clear from the source what I really need to
implement in my own interface and how SA expects that to behave.


At the moment I think the problem lies with how attributes are get and
set.  From what I understand from going through the SA source is that
the mapper places decoraters on the attributes once Declarative has
created the appropriate tables.  And Traits does something similar
though I'm not quite sure how.  The entire process seems rather
complex, complicated by the fact that Traits sometimes writes directly
to the __dict__ of an instance without going through setattr first
(which I'm guessing is what's needed by SA for its bookkeeping).

I'm quite sure that the SQL SA produces is correct, it's just the
object's state that isn't being handled properly.  Is there some
documentation somewhere that explains what the various methods in the
interface (either InstrumentationManager or ClassManager) are supposed
to do?  For instance is install_state(self, class_, instance, state)
supposed to copy the data in the state parameter to the instance or
merely store it for future use by state_getter ?


I'll give the ClassManager a try now.  Hope you can make some sense of my code.

Enjoy your evening,
Chris

--~--~-~--~~~---~--~~
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: Declarative with Enthoughts Traits framework.

[Michael Bayer]

On Feb 7, 2009, at 8:27 PM, Christiaan Putter wrote:


 Hi Michael,

 Thanks for your swift reply.

 I wasn't really sure which way to go with combining Traits and SA.
 From reading through the source it seemed that I had to use
 InstrumentationManager, I think it said somewhere it was the stable
 public interface.  I had a look at the Trellis source as well, they
 subclass the ClassManager though.  I'll give that a try next.  Either
 way it's not quite clear from the source what I really need to
 implement in my own interface and how SA expects that to behave.

uh thats a little weird since the InstrumentationManager is designed  
to be the thing you subclass.  you're not supposed to subclass  
ClassManager.   There should be no difference in behavior subclassing  
one or the other.


 At the moment I think the problem lies with how attributes are get and
 set.  From what I understand from going through the SA source is that
 the mapper places decoraters on the attributes once Declarative has
 created the appropriate tables.  And Traits does something similar
 though I'm not quite sure how.  The entire process seems rather
 complex, complicated by the fact that Traits sometimes writes directly
 to the __dict__ of an instance without going through setattr first
 (which I'm guessing is what's needed by SA for its bookkeeping).

yeah thats all true but the point of InstrumentationManager is that  
its all OK - you send SQLA the events it needs.

 I'm quite sure that the SQL SA produces is correct, it's just the
 object's state that isn't being handled properly.  Is there some
 documentation somewhere that explains what the various methods in the
 interface (either InstrumentationManager or ClassManager) are supposed
 to do?  For instance is install_state(self, class_, instance, state)
 supposed to copy the data in the state parameter to the instance or
 merely store it for future use by state_getter ?

well thats all internal-ish API for which you'd have to just trace out  
the flow of data.install_state() for example just sticks an  
InstanceState attributre on a given instance.


--~--~-~--~~~---~--~~
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: Declarative with Enthoughts Traits framework.

[Christiaan Putter]

Hi,

 uh thats a little weird since the InstrumentationManager is designed
 to be the thing you subclass.  you're not supposed to subclass
 ClassManager.   There should be no difference in behavior subclassing
 one or the other.


It behaves a little weird to when I tried it myself.  I'll stick to
the InstrumentationManager then if that's the way to do it.



 At the moment I think the problem lies with how attributes are get and
 set.  From what I understand from going through the SA source is that
 the mapper places decoraters on the attributes once Declarative has
 created the appropriate tables.  And Traits does something similar
 though I'm not quite sure how.  The entire process seems rather
 complex, complicated by the fact that Traits sometimes writes directly
 to the __dict__ of an instance without going through setattr first
 (which I'm guessing is what's needed by SA for its bookkeeping).

 yeah thats all true but the point of InstrumentationManager is that
 its all OK - you send SQLA the events it needs.


The question is what events does it need?


 I'm quite sure that the SQL SA produces is correct, it's just the
 object's state that isn't being handled properly.  Is there some
 documentation somewhere that explains what the various methods in the
 interface (either InstrumentationManager or ClassManager) are supposed
 to do?  For instance is install_state(self, class_, instance, state)
 supposed to copy the data in the state parameter to the instance or
 merely store it for future use by state_getter ?

 well thats all internal-ish API for which you'd have to just trace out
 the flow of data.install_state() for example just sticks an
 InstanceState attributre on a given instance.


I've been stepping through the code for several days now and I'm
slowly starting to understand what SA needs and how it operates, not
there yet though.  Do you perhaps know of another project that uses
the InstrumentationManager interface?  Seeing some implemented code
would certainly help out my understanding.


I'm just glad that debugging python is much more fun than debugging c++.


Let me know if you see some flaws in my code.

Thanks for your help 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: Declarative with Enthoughts Traits framework.

[Michael Bayer]

On Feb 7, 2009, at 8:57 PM, Christiaan Putter wrote:

 yeah thats all true but the point of InstrumentationManager is that
 its all OK - you send SQLA the events it needs.


 The question is what events does it need?

there's a demo here:

http://www.sqlalchemy.org/trac/browser/sqlalchemy/trunk/examples/custom_attributes/custom_management.py

--~--~-~--~~~---~--~~
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: Declarative with Enthoughts Traits framework.

[Christiaan Putter]

Thanks for the link.  That's where I figured out most of what I'm trying to do.

Now that I've stepped through the code I noticed that 'install_state'
is only being called on the first 2 iterations of the loop.  Haven't
found out yet why that is.

What exactly is supposed to be in the state?  Is an instance.__dict__
supposed to be identical to that of state.dict?   It seems that after
a commit that state.dict is cleared so I'm guessing it's only used for
dirty attributes.  For some reason after the commit instance.__dict__
gets modified and the attributes that have been updated get cleared,
only for the first two iterations though.

I changed the loop to look like:

sess = sqlservice.Session()
for per in people:
obj = User(name=per)
sess.add(obj)
print obj
sess.commit()
sess.close()


which behaves the way one would expect...   So I'm guessing it must be
in the way I'm storing the state right? At the moment the
InstrumentationManager stores the states in a dict self.states.  So I
tried to rewrite it to store it directly on the instance, say
instance.__sa_state__, but that doesn't work.  When add gets called,
install_state hasn't been called yet.  Is that supposed to be that
way?  Maybe my instances aren't getting instantiated correctly.

This is becoming really confusing.  I'm taking a quick smoke break.

--~--~-~--~~~---~--~~
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: Declarative with Enthoughts Traits framework.

[Michael Bayer]

install_state is normally called during __init__ of your object.  the  
attributes package decorates __init__ for this purpose.   so you have  
to get __init__, or __new__, or whatever, to ensure that  
attributes.instance_state(obj) will return an InstanceState at all  
times.

On Feb 7, 2009, at 10:02 PM, Christiaan Putter wrote:


 Thanks for the link.  That's where I figured out most of what I'm  
 trying to do.

 Now that I've stepped through the code I noticed that 'install_state'
 is only being called on the first 2 iterations of the loop.  Haven't
 found out yet why that is.

 What exactly is supposed to be in the state?  Is an instance.__dict__
 supposed to be identical to that of state.dict?   It seems that after
 a commit that state.dict is cleared so I'm guessing it's only used for
 dirty attributes.  For some reason after the commit instance.__dict__
 gets modified and the attributes that have been updated get cleared,
 only for the first two iterations though.

 I changed the loop to look like:

 sess = sqlservice.Session()
 for per in people:
obj = User(name=per)
sess.add(obj)
print obj
 sess.commit()
 sess.close()


 which behaves the way one would expect...   So I'm guessing it must be
 in the way I'm storing the state right? At the moment the
 InstrumentationManager stores the states in a dict self.states.  So I
 tried to rewrite it to store it directly on the instance, say
 instance.__sa_state__, but that doesn't work.  When add gets called,
 install_state hasn't been called yet.  Is that supposed to be that
 way?  Maybe my instances aren't getting instantiated correctly.

 This is becoming really confusing.  I'm taking a quick smoke break.

 


--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---