Re: [sqlalchemy] Custom column + AttributeExtension -- Value parameter empty in set() method of the Extension

[Hector Blanco]

Yep, you're right... That's exactly what's happening. The set event
is, indeed self._values = []

The issue is that I can't upgrade to SqlAlchemy 0.7...

Anyway... thank you for the help... I'll have to look for a workaround...

2012/7/9 Michael Bayer mike...@zzzcomputing.com:

 On Jul 9, 2012, at 12:38 PM, Hector Blanco wrote:

 Hello everyone.

 I have a class that uses a custom column to store a list of strings.
 The column is saved in the database using a comma sepparated string.
 When it's loaded into an instance, it becomes a list:

 class Keyword(declarativeBase):
   __tablename__ = keywords
   _id = Column(id, Integer, primary_key=True)
   _values = column_property(Column(values,
 CharSeparatedStrings.CharSeparatedStrings()),
 extension=ValuesAttributeExtension.ValuesAttributeExtension(),
 active_history=True)

 I want to use an extension to check the items that have been
 deleted/modified in that list. That's why I'm wrapping the Column in a
 column_property.

 you've already got a custom type there, so unless you want an immediate 
 exception raise or something, this would be easier just to keep it in terms 
 of the column type, like a TypeDecorator around String that deals with sets 
 on the Python side.


 When I put a bunch of checkpoints in the ValueAttributeExtension,
 the value that is going to be set is always an empty list. The
 oldvalue works fine, though:

 class ValuesAttributeExtension(AttributeExtension):
   def append(self, state, value, initiator):
   print(%s::append  Checkpoint!. Value: %s, state.obj()::%s %
 (self.__class__.__name__, value, state.obj()))
   return value
   def remove(self, state, value, initiator):
   print(%s::append  Checkpoint!. Value: %s, state.obj()::%s %
 (self.__class__.__name__, value, state.obj()))
   return value
   def set(self, state, value, oldValue, initiator):
   print(%s::set  Checkpoint!. Value: %s, oldValue: %s,
 state.obj().values: %s % (self.__class__.__name__, value, oldValue,
 state.obj().values))
   return value

 Let's say the initial (old) value was [yes] and I add a no. I
 would expect the value parameter in the set method to be [yes,
 no] and the oldValue to be [yes] but this is what I get:

 what is adding here ?

 like keyword.values.append(x) ?

 assuming values is a descriptor that just says, self._values = []; return 
 self._values, that self._values = [] is your set event.   What you do with 
 the list subsequent to that is outside of SQLAlchemy's purview, that list 
 isn't instrumented.

 you'd get the event if you said :  keyword._values = [yes].  that's a set 
 event.   the append and remove events aren't relevant here since this is not 
 an instrumented collection (its a column, not a relationship).

 if you're really looking for the list here, set as a scalar from a mapping 
 perspective, to emit events as things happen to it, that's the use case for 
 the mutable extension: 
 http://docs.sqlalchemy.org/en/rel_0_7/orm/extensions/mutable.html .

 if you are in fact on 0.7, I'd look to upgrade from AttributeExtension to the 
 event package as well.



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


-- 
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] Custom column + AttributeExtension -- Value parameter empty in set() method of the Extension

[Hector Blanco]

Hello everyone.

I have a class that uses a custom column to store a list of strings.
The column is saved in the database using a comma sepparated string.
When it's loaded into an instance, it becomes a list:

class Keyword(declarativeBase):
__tablename__ = keywords
_id = Column(id, Integer, primary_key=True)   
_values = column_property(Column(values,
CharSeparatedStrings.CharSeparatedStrings()),
extension=ValuesAttributeExtension.ValuesAttributeExtension(),
active_history=True)

I want to use an extension to check the items that have been
deleted/modified in that list. That's why I'm wrapping the Column in a
column_property.

When I put a bunch of checkpoints in the ValueAttributeExtension,
the value that is going to be set is always an empty list. The
oldvalue works fine, though:

class ValuesAttributeExtension(AttributeExtension):
def append(self, state, value, initiator):
print(%s::append  Checkpoint!. Value: %s, state.obj()::%s %
(self.__class__.__name__, value, state.obj()))
return value
def remove(self, state, value, initiator):
print(%s::append  Checkpoint!. Value: %s, state.obj()::%s %
(self.__class__.__name__, value, state.obj()))
return value
def set(self, state, value, oldValue, initiator):
print(%s::set  Checkpoint!. Value: %s, oldValue: %s,
state.obj().values: %s % (self.__class__.__name__, value, oldValue,
state.obj().values))
return value

Let's say the initial (old) value was [yes] and I add a no. I
would expect the value parameter in the set method to be [yes,
no] and the oldValue to be [yes] but this is what I get:

ValuesAttributeExtension::set  Checkpoint!. Value: [], oldValue:
[u'yes'], state.obj().values: [u'yes']

The append or remove methods are never run... I guess that's
because it's a weird type (not really a relationship) so that's ok,
but I'd like to know if there's a way of getting the new value that is
going to be set, to check the differences.

Everything else seems to be working fine, though. Even though value is
an empty list in the set method, it gets properly updated in the
database.

Thank you in advance.

-- 
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] Get all the instances of class A that are using an specific instance of class B (SqlAlchemy 0.6.9)

[Hector Blanco]

Hello everybody!

I would like to know if it's possible to create an optimized query
that gives all the elements of a class A that have a relationship to
an specific class B

Here's the thing:

I have a pretty simple class Region that defines an area in the screen:


class Region(declarativeBase):
__tablename__ = regions
id = Column(id, Integer, primary_key=True, key=id)
x = Column(x, Integer)
y = Column(y, Integer)
width = Column(width, Integer(unsigned=True))
height = Column(height, Integer(unsigned=True))

And then a class Layout that can contain (or point to?) a number of
these regions depending on what they're used for:


class Layout(declarativeBase):
__tablename__ = layouts

id = Column(id, Integer, primary_key=True, key=id)
width = Column(width, Integer)
height = Column(height, Integer)


logoRegionId = Column(logo_region_id, Integer,
ForeignKey(regions.id), key=logoRegionId)
logoRegion = relationship(Region.Region,
uselist=False,
primaryjoin=lambda: 
Region.Region.id == Layout.logoRegionId
  )
backgroundRegionId = Column(background_region_id, Integer,
ForeignKey(regions.id), 
key=backgroundRegionId)
backgroundRegion = relationship(Region.Region,
uselist=False,
primaryjoin=lambda: 
Region.Region.id == Layout.backgroundRegionId
)
mainImageRegionId = Column(main_image_region_id, Integer,
ForeignKey(regions.id), 
key=mainImageRegionId)
mainImageRegion = relationship(Region.Region,
uselist=False,
primaryjoin=lambda: 
Region.Region.id == Layout.mainImageRegionId
)

As you can see, the Regions don't have a backref to Layout (mainly
because they may be used in other classes) but one specific region
will only be in one layout at the same time (regions are never shared
by layouts) and one Region will never be used in the same layout more
than once (there won't be a Layout.logoRegion and a
Layout.mainImageRegion pointing to the same Region instance)

What I would like to do is knowing what layouts (or, better said,
layout) are using an specific given region in any of its fields.

Let's say, given this:

Layouts:

++---+++--+--+
| id | width | height | logo_region_id | background_region_id |
main_image_region_id |
++---+++--+--+
| 38 |  1280 |720 |  8 |7 |
9 |
| 45 |  1280 |720 | 15 |   16 |
   17 |
| 52 |  1280 |720 | 23 |   24 |
   25 |
++---+++--+--+


I would like to have a method to say What layouts are using region
'16'? and the answer would be a [45], for instance (if it were [45,
52], I'd have to send a nasty exception, but that's besides the point).
Of course, I could just go checking field by field in the Layouts, but
if I could do it with an SQL expression, that would probably be
faster.

I even have a method that can give me the names of the relationships
towards another class (in this case,
Layout.relationshipsTo(Region.Region), which would return
[logoRegion, backgroundRegion, mainImageRegion]. I've tried
playing with getattr(Layout, name_of_relationship), put that in
joins, in onclauses for the join... Nothing.

Any hint will be appreciated!

Thank you in advance

-- 
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] Get all the instances of class A that are using an specific instance of class B (SqlAlchemy 0.6.9)

[Hector Blanco]

2 days trying by myself... 20 minutes with your answer... I still
don't know why I didn't try what you said...

Thank you, Michael

2012/5/16 Michael Bayer mike...@zzzcomputing.com:

 On May 16, 2012, at 5:50 PM, Hector Blanco wrote:

 Hello everybody!

 I would like to know if it's possible to create an optimized query
 that gives all the elements of a class A that have a relationship to
 an specific class B

 from the below it seems like you mean a specific *instance* B...

 As you can see, the Regions don't have a backref to Layout (mainly
 because they may be used in other classes) but one specific region
 will only be in one layout at the same time (regions are never shared
 by layouts) and one Region will never be used in the same layout more
 than once (there won't be a Layout.logoRegion and a
 Layout.mainImageRegion pointing to the same Region instance)

 What I would like to do is knowing what layouts (or, better said,
 layout) are using an specific given region in any of its fields.

 Let's say, given this:

 Layouts:

 ++---+++--+--+
 | id | width | height | logo_region_id | background_region_id |
 main_image_region_id |
 ++---+++--+--+
 | 38 |  1280 |    720 |              8 |                    7 |
            9 |
 | 45 |  1280 |    720 |             15 |                   16 |
           17 |
 | 52 |  1280 |    720 |             23 |                   24 |
           25 |
 ++---+++--+--+


 I would like to have a method to say What layouts are using region
 '16'?

 so say you have region 16 - what layouts?  query for layouts:

 region = s.query(Region).get(16)
 s.query(Layout).filter(or_(
    Layout.logoRegion==region,
    Layout.mainImageRegion==region,
    Layout.backgroundRegion==region
 ))

 basically SomeClass.some_m2o_relationship==some_instance will pull out the PK 
 of some_instance and compare it to the FK column on SomeClass.

 If you had the numerical id of some_instance instead, you'd adjust this to 
 compare on the column, not the relationship.

 this would be perfect for a class method also:

 class Layout(...):
    

   @classmethod
   def has_region(cls, some_region):
        return or_(
        Layout.logoRegion==region,
        Layout.mainImageRegion==region,
        Layout.backgroundRegion==region
        )

 s.query(Layout).filter(Layout.has_region(some_region))



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


-- 
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] SqlAlchemy 0.7.4 Make a hybrid_property a declared_attr

[Hector Blanco]

Hello everyone!

I'm trying to migrate from SqlAlchemy 0.6.8 to 0.7.4.

I have a class that is the base for all the bases in my system. In
that class is where I define the id (numeric primary key) for the rest
of my classes. That class is not mapped to any table.

I want to have getter/setter for said id, making it a
hybrid_property, something like:

class BaseClass(object):
_id = Column(id, Integer, primary_key=True, key=id)

@hybrid_property
def id(self):
return self._id
@id.setter
def setId(self, id):
try:
self._id = int(id)
except TypeError:
self._id = None

but if then i try to use the id in filters (such as
Product.manufacturer.any(id=parameterId), where Product is a really
mapped class, with a relationship towards Manufacturer, another
really mapped class, ) I get a key error id, so it looks like the
id is not property set.

In 0.6.8, what I did was

class BaseClass(object):
 _id =  Column(id, Integer, primary_key=True, key=id)

@declared_attr
def id(cls):
return synonym('_id', descriptor=property(cls.getId, cls.setId))


I've tried as many dirty tricks as I could come up with, and I didn't
get any success... Any ideas will be welcome!

-- 
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] SqlAlchemy 0.7.4 Make a hybrid_property a declared_attr

[Hector Blanco]

 The workaround is very simple, just say A.id==5 instead of id=5.   The
 bug itself is fixed in r99564ce1414c.


Ah, yeah! That works!  I find using the double == for comparison
clearer (just a personal opinion)



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



-- 
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] SqlAclhemy 0.6.8 Delete orphan with objects that contain new objects (momentarily orphan)?

[Hector Blanco]

Oh, it wasn't exactly an error with the Synonyms itself. They are
working fine for what I've been doing with them (just using them for
getters/setters... nothing fancy) It was more that when I migrated the
Synonyms I have in the 0.6.8 to hybrid_properties in 0.7.4, I was
getting trouble (which I'm not anymore:
http://groups.google.com/group/sqlalchemy/browse_thread/thread/23c0cfa1d4789ee5)



2011/12/11, Michael Bayer mike...@zzzcomputing.com:
 synonyms had a lot of regressions in 0.7.2, and I worked onsite with a very
 prominent user of SQLAlchemy to fix all known synonym regressions in 0.7.3 -
 they use synonyms enormously, in all kinds of ways I never envisioned or
 tested.  If there are *any* incompatibilities whatsoever, I need full
 reproducing test cases.  There are no plans to ever remove synonym() or to
 have any backwards incompatibilities of any kind.

 Here's one particular order_by(), the one on Query which is most common.
 Works fine:

 from sqlalchemy import *
 from sqlalchemy.orm import *
 from sqlalchemy.ext.declarative import declarative_base

 Base= declarative_base()

 class A(Base):
 __tablename__ = a

 id = Column(Integer, primary_key=True)
 b = Column(Integer)
 c = synonym(b)

 s = Session()
 print s.query(A).order_by(A.c)


 You might be using a different order_by (like on relationship, etc), or you
 might have a different flavor of synonym.So need a full reproducing test
 case as a trac ticket please thanks !




 On Dec 11, 2011, at 12:52 AM, Hector Blanco wrote:

 I would like to. I've been testing 0.7.4, and it seems to work really
 fast, but my code is heavily dependent on Synonyms, which seem to be
 incompatible with certain new features (I'm getting errors when I use
 order_by, for instance) so, until I have time to migrate my code to
 Hibrids, I'm afraid I have to stick with SqlAlchemy  0.7

 One of these days, though...

 Thanks again!

 2011/12/11 Michael Bayer mike...@zzzcomputing.com:
 if you have a relationship() with delete-orphan, SQLAlchemy will not let
 you save the child without the parent being attached.  It is more or less
 a bug in that this particular check is unnecessary, and you should
 upgrade to 0.7.


 On Dec 11, 2011, at 12:00 AM, Hector Blanco wrote:

 Thank you for your reply.

 I'm not exactly sure of what is blocking the insert. I would say
 SqlAlchemy, because my Foreign Keys are nullable (which raises the
 question of whether it's a good design or not... but that's a
 different story)

 I read in the documentation:
 http://www.sqlalchemy.org/docs/orm/relationships.html

  if an item of the child’s type is detached from its parent, mark it
 for deletion

 Is there a way of put (or add) the parent manually in a child? I
 believe that might work, because the parent is going to be merged,
 flushed, commited and... written in the database when I start loading
 the child

 Again, thank you

 2011/12/10 Michael Bayer mike...@zzzcomputing.com:

 On Dec 10, 2011, at 7:07 PM, Hector Blanco wrote:



 That data (is JSON) is sent to the Category handler. That handler
 does the following
 1) Creates a new Category() instance,
 2) Fill the non-relationship fields (_name in this case)
 3) Adds the category to the session, so it gets an _id
 4) Go through the relationships fields (_products)
 5) If there's a dictionary inside, call recursively to the same method
 with that dictionary.
 6) The recursive call will try to create a new product (with the
 proper _model) and TRIES to add it to the database
 7) The recursive call returns, so that newly created product can be
 added to the _products relationship of the category and the backref
 will properly set up the _category relationship in the product

 And in the 6th point is where my problem shows up: If I set up the
 delete-orphans, the database detects that I'm trying to insert a
 product that doesn't belong to a category, therefore, it's orphan...
 and blows up.

 Is there a way to delay the triggering of the delete-orphans or...
 or something similar, so I can have a product not belonging to a
 category... for a bit (until the category has finished loading?)

 when you say the database detects it's not entirely clear what you
 mean; SQLA 0.6 and earlier will prevent you from persisting a
 delete-orphan without a parent, before it ever goes to the database.
 So that's SQLA preventing the operation, not the DB.  OTOH if your
 foreign key is NOT NULL then the DB prevents the orphan row from being
 INSERTed no matter what SQLA allows or not.

 So it depends on specifically what is blocking the activity from
 happening - if you want to INSERT rows with a null foreign key and
 still have delete-orphan, you'd need to use SQLAlchemy 0.7 which
 removes the orphan detection at the INSERT level.If you don't
 want to actually have any orphaned rows in the DB ever and the FK
 will be NOT NULL, then you have to organize your steps such that the
 INSERTs don't occur until everything is ready to go

[sqlalchemy] SqlAlchemy 0.7.4 Hibrid-properties not showing in NamedTuples when values are queried (they are resolved)

[Hector Blanco]

Hello everyone!

I am continuing with the migration from SqlAlchemy 0.6.8 to 0.7.4, and
I've seen a little difference in behavior.

I have a class Product (more or less) like:

class Product(declarativeBase):
_id = Column(id, Integer, primary_key=True)
_model = Column(model, Unicode(128))

@hybrid_property
def model(self):
return self._model

@model.setter
def setModel(self, model):
if model:
self._model = unicode(model)
else:
self._model = None

 #id hybrid property as well

Both id and model are hybrid_properties now (they were Synonyms in 0.6.8)

When I query only some values:

query = session.query(Product.Product).filter(Product.Product.id ==
25).values(Product.Product.model)
for element in query:
print %s\n % vars(element)

The query works perfectly, but I get this:
{'_model': u'myAifon', '_labels': ('_model',)}

The hidden (or masked... I don't know how to call it) attribute (the
private one, starting with a _) so if I try something like

for element in query:
print The 'model' is: %s\n % element.model

I get a Kaboom!! (I''ve heard it's the Swahili for AttributeError
exception). Is there a workaround this? I am trying to migrate all my
Synonyms to hybrid_properties, specially since I read synonym() is
superseded as of 0.7 by the hybrid extension.
(http://www.sqlalchemy.org/docs/orm/mapper_config.html). That was
enough to make me obsessed with getting rid of the synonyms :-)

Thank you in advance!

-- 
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] SqlAclhemy 0.6.8

[Hector Blanco]

Hello everyone!

In my application, I have a class Product that can belong to a
Category. (1 product, 1 category). The category knows which products
belong to it thanks to a backref. A product can not exist if
it doesn't belong to a category. If a category is deleted, all it's
products are deleted as well. That's why I would like to stablish a
delete-orphan in that relationship.Something like:

class Product(declarative_base):
__tablename__ = products
_id = Column(id, Integer, primary_key=True)

_model = Column(model, Unicode(128))
_categoryId = Column(category_id, Integer,
  ForeignKey(categories.id), key=categoryId)
_category = relationship(Category,
uselist=False,
backref=backref(
 _products,
 collection_class=set,
 #Set up delete
orphans here?
)   

)

class Category(declarative_base):
__tablename__ = categories
_id = Column(id, Integer, primary_key=True)
_name = Column(name, String(50))
#_products  Backref from Product class

The problem is that, in a certain spot I want to create a category and
a product in one shot. This is running in a web server, that may
receive a category looking more or less like this:

#This is a new category with a new product inside. They don't have id,
# therefore, both of them are considered new
{
_name: cellphones,
_products: [
{
_model: iphone
}
]
}

That data (is JSON) is sent to the Category handler. That handler
does the following
1) Creates a new Category() instance,
2) Fill the non-relationship fields (_name in this case)
3) Adds the category to the session, so it gets an _id
4) Go through the relationships fields (_products)
5) If there's a dictionary inside, call recursively to the same method
with that dictionary.
6) The recursive call will try to create a new product (with the
proper _model) and TRIES to add it to the database
7) The recursive call returns, so that newly created product can be
added to the _products relationship of the category and the backref
will properly set up the _category relationship in the product

And in the 6th point is where my problem shows up: If I set up the
delete-orphans, the database detects that I'm trying to insert a
product that doesn't belong to a category, therefore, it's orphan...
and blows up.

Is there a way to delay the triggering of the delete-orphans or...
or something similar, so I can have a product not belonging to a
category... for a bit (until the category has finished loading?)

I can start digging the information the _products relationship has
inside, checking the parent, the backref attribute, pass the
category to the recursive method, so it will put it in the
Product._category field,... yadda, yadda, yadda, but I was wondering
if there's a better way of achieving what I want (assuming I explained
properly what I want)

Thank you in advance!

-- 
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] SqlAclhemy 0.6.8 Delete orphan with objects that contain new objects (momentarily orphan)?

[Hector Blanco]

Sorry, I messed up the subject of the email


-- Forwarded message --
From: Hector Blanco white.li...@gmail.com
Date: 2011/12/10
Subject: SqlAclhemy 0.6.8
To: sqlalchemy@googlegroups.com


Hello everyone!

In my application, I have a class Product that can belong to a
Category. (1 product, 1 category). The category knows which products
belong to it thanks to a backref. A product can not exist if
it doesn't belong to a category. If a category is deleted, all it's
products are deleted as well. That's why I would like to stablish a
delete-orphan in that relationship.Something like:

class Product(declarative_base):
       __tablename__ = products
       _id = Column(id, Integer, primary_key=True)

       _model = Column(model, Unicode(128))
       _categoryId = Column(category_id, Integer,
                         ForeignKey(categories.id), key=categoryId)
       _category = relationship(Category,
                                       uselist=False,
                                       backref=backref(
                                                    _products,
                                                    collection_class=set,
                                                    #Set up delete
orphans here?
                                               )
                                       )

class Category(declarative_base):
       __tablename__ = categories
       _id = Column(id, Integer, primary_key=True)
       _name = Column(name, String(50))
       #_products  Backref from Product class

The problem is that, in a certain spot I want to create a category and
a product in one shot. This is running in a web server, that may
receive a category looking more or less like this:

#This is a new category with a new product inside. They don't have id,
# therefore, both of them are considered new
{
   _name: cellphones,
   _products: [
       {
           _model: iphone
       }
   ]
}

That data (is JSON) is sent to the Category handler. That handler
does the following
1) Creates a new Category() instance,
2) Fill the non-relationship fields (_name in this case)
3) Adds the category to the session, so it gets an _id
4) Go through the relationships fields (_products)
5) If there's a dictionary inside, call recursively to the same method
with that dictionary.
6) The recursive call will try to create a new product (with the
proper _model) and TRIES to add it to the database
7) The recursive call returns, so that newly created product can be
added to the _products relationship of the category and the backref
will properly set up the _category relationship in the product

And in the 6th point is where my problem shows up: If I set up the
delete-orphans, the database detects that I'm trying to insert a
product that doesn't belong to a category, therefore, it's orphan...
and blows up.

Is there a way to delay the triggering of the delete-orphans or...
or something similar, so I can have a product not belonging to a
category... for a bit (until the category has finished loading?)

I can start digging the information the _products relationship has
inside, checking the parent, the backref attribute, pass the
category to the recursive method, so it will put it in the
Product._category field,... yadda, yadda, yadda, but I was wondering
if there's a better way of achieving what I want (assuming I explained
properly what I want)

Thank you in advance!

-- 
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] SqlAclhemy 0.6.8 Delete orphan with objects that contain new objects (momentarily orphan)?

[Hector Blanco]

Thank you for your reply.

I'm not exactly sure of what is blocking the insert. I would say
SqlAlchemy, because my Foreign Keys are nullable (which raises the
question of whether it's a good design or not... but that's a
different story)

I read in the documentation:
http://www.sqlalchemy.org/docs/orm/relationships.html

 if an item of the child’s type is detached from its parent, mark it
for deletion

Is there a way of put (or add) the parent manually in a child? I
believe that might work, because the parent is going to be merged,
flushed, commited and... written in the database when I start loading
the child

Again, thank you

2011/12/10 Michael Bayer mike...@zzzcomputing.com:

 On Dec 10, 2011, at 7:07 PM, Hector Blanco wrote:



 That data (is JSON) is sent to the Category handler. That handler
 does the following
 1) Creates a new Category() instance,
 2) Fill the non-relationship fields (_name in this case)
 3) Adds the category to the session, so it gets an _id
 4) Go through the relationships fields (_products)
 5) If there's a dictionary inside, call recursively to the same method
 with that dictionary.
 6) The recursive call will try to create a new product (with the
 proper _model) and TRIES to add it to the database
 7) The recursive call returns, so that newly created product can be
 added to the _products relationship of the category and the backref
 will properly set up the _category relationship in the product

 And in the 6th point is where my problem shows up: If I set up the
 delete-orphans, the database detects that I'm trying to insert a
 product that doesn't belong to a category, therefore, it's orphan...
 and blows up.

 Is there a way to delay the triggering of the delete-orphans or...
 or something similar, so I can have a product not belonging to a
 category... for a bit (until the category has finished loading?)

 when you say the database detects it's not entirely clear what you mean; 
 SQLA 0.6 and earlier will prevent you from persisting a delete-orphan 
 without a parent, before it ever goes to the database.  So that's SQLA 
 preventing the operation, not the DB.  OTOH if your foreign key is NOT NULL 
 then the DB prevents the orphan row from being INSERTed no matter what SQLA 
 allows or not.

 So it depends on specifically what is blocking the activity from happening - 
 if you want to INSERT rows with a null foreign key and still have 
 delete-orphan, you'd need to use SQLAlchemy 0.7 which removes the orphan 
 detection at the INSERT level.    If you don't want to actually have any 
 orphaned rows in the DB ever and the FK will be NOT NULL, then you have to 
 organize your steps such that the INSERTs don't occur until everything is 
 ready to go in - that's regardless of SQLAlchemy version.  Nothing regarding 
 orphans happens in any case until a flush occurs.   So if its a simple matter 
 of delaying the flush, just turn off autoflush temporarily.



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


-- 
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] SqlAclhemy 0.6.8 Delete orphan with objects that contain new objects (momentarily orphan)?

[Hector Blanco]

I would like to. I've been testing 0.7.4, and it seems to work really
fast, but my code is heavily dependent on Synonyms, which seem to be
incompatible with certain new features (I'm getting errors when I use
order_by, for instance) so, until I have time to migrate my code to
Hibrids, I'm afraid I have to stick with SqlAlchemy  0.7

One of these days, though...

Thanks again!

2011/12/11 Michael Bayer mike...@zzzcomputing.com:
 if you have a relationship() with delete-orphan, SQLAlchemy will not let you 
 save the child without the parent being attached.  It is more or less a bug 
 in that this particular check is unnecessary, and you should upgrade to 0.7.


 On Dec 11, 2011, at 12:00 AM, Hector Blanco wrote:

 Thank you for your reply.

 I'm not exactly sure of what is blocking the insert. I would say
 SqlAlchemy, because my Foreign Keys are nullable (which raises the
 question of whether it's a good design or not... but that's a
 different story)

 I read in the documentation:
 http://www.sqlalchemy.org/docs/orm/relationships.html

  if an item of the child’s type is detached from its parent, mark it
 for deletion

 Is there a way of put (or add) the parent manually in a child? I
 believe that might work, because the parent is going to be merged,
 flushed, commited and... written in the database when I start loading
 the child

 Again, thank you

 2011/12/10 Michael Bayer mike...@zzzcomputing.com:

 On Dec 10, 2011, at 7:07 PM, Hector Blanco wrote:



 That data (is JSON) is sent to the Category handler. That handler
 does the following
 1) Creates a new Category() instance,
 2) Fill the non-relationship fields (_name in this case)
 3) Adds the category to the session, so it gets an _id
 4) Go through the relationships fields (_products)
 5) If there's a dictionary inside, call recursively to the same method
 with that dictionary.
 6) The recursive call will try to create a new product (with the
 proper _model) and TRIES to add it to the database
 7) The recursive call returns, so that newly created product can be
 added to the _products relationship of the category and the backref
 will properly set up the _category relationship in the product

 And in the 6th point is where my problem shows up: If I set up the
 delete-orphans, the database detects that I'm trying to insert a
 product that doesn't belong to a category, therefore, it's orphan...
 and blows up.

 Is there a way to delay the triggering of the delete-orphans or...
 or something similar, so I can have a product not belonging to a
 category... for a bit (until the category has finished loading?)

 when you say the database detects it's not entirely clear what you mean; 
 SQLA 0.6 and earlier will prevent you from persisting a delete-orphan 
 without a parent, before it ever goes to the database.  So that's SQLA 
 preventing the operation, not the DB.  OTOH if your foreign key is NOT NULL 
 then the DB prevents the orphan row from being INSERTed no matter what SQLA 
 allows or not.

 So it depends on specifically what is blocking the activity from happening 
 - if you want to INSERT rows with a null foreign key and still have 
 delete-orphan, you'd need to use SQLAlchemy 0.7 which removes the orphan 
 detection at the INSERT level.    If you don't want to actually have any 
 orphaned rows in the DB ever and the FK will be NOT NULL, then you have 
 to organize your steps such that the INSERTs don't occur until everything 
 is ready to go in - that's regardless of SQLAlchemy version.  Nothing 
 regarding orphans happens in any case until a flush occurs.   So if its a 
 simple matter of delaying the flush, just turn off autoflush temporarily.



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


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


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


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

Re: [sqlalchemy] SQLA 0.6.8 Given an AssociationProxy, how can I get the class it would give me?

[Hector Blanco]

Just a little detail...

To get the class of a regular relationship, I use a class_mapper:

def getClassOfRelationship(cls, name):
retval = None
mapper = sqlalchemy.orm.class_mapper(cls)
try:
prop = mapper.get_property(name)
if isinstance(prop, sqlalchemy.orm.properties.SynonymProperty):
retval = getClassOfRelationship(cls, prop.name)
elif isinstance(prop, 
sqlalchemy.orm.properties.RelationshipProperty):
retval = prop.mapper.class_
except ValueError:
retval = None
return retval

But the AssociationProxies don't (seem to) show in the mapper, correct?

I can always find a workaround, like ok, if the attribute is not in
the class_mapper, then go to the class and check if it's an
AssociationProxy, but I was wondering if there's a better (cleaner)
way. As AssociationProxies don't show in the class_mapper, I'll be
getting an exception when I try to get it from the mapper (here: prop
= mapper.get_property(name)) If I get an exception, I can go to the
class itself (cls parameter), check whether it's an AssociationProxy,
and then do what it is described in Michael's solution, but maybe
someone knows a cleaner way?

Thank you in advance!

Thanks in advance!



2011/12/7 Michael Bayer mike...@zzzcomputing.com:

 On Dec 7, 2011, at 7:53 PM, Hector Blanco wrote:

 Hello everyone:

 In one of my classes, I am using an associationproxy to provide a list
 in which the same item can be inserted twice. Basically, a product can
 contain images (several times the same image).  One product can have
 two different lists of images (images1 and images2... yep... not
 very creative with the naming here)

 Is there a way, given a class Product (or an instance, but preferably a
 class) knowing that Product._images1 (or Product._images2) is going to
 give me Images (er... things of class Image). I've been able to do
 that with regular relationships through [relationship].mapper.class_.



 associationproxy has an attribute target_class for the middle class:

 MyClass.my_association.target_class

 then for the target, 0.7 has remote_attr.  Not in 0.6 but it's just 
 shorthand for:

 getattr(MyClass.my_association.target_class, self.value_attr)

 but AP doesn't know what type that is.  Suppose it were a relationship, then 
 you'd say:

 getattr(MyClass.my_association.target_class, 
 MyClass.my_association.value_attr).property.class_

 if a column:

 getattr(MyClass.my_association.target_class, 
 MyClass.my_association.value_attr).property.columns[0]

 etc.


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


-- 
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] SQLA 0.6.8 Given an AssociationProxy, how can I get the class it would give me?

[Hector Blanco]

Yes! Thank you for your, as usual :-) , quick, wise and right reply!

I believe that's all I need. I already have in place all the machinery
to deal with Columns, relationships, and stuff like that! Awesome (or
as we would pronounce in my mother tongue, Spanish... osom!! )

2011/12/7 Michael Bayer mike...@zzzcomputing.com:
 getattr(MyClass.my_association.target_class, 
 MyClass.my_association.value_attr).property.class_

-- 
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] SqlAlchemy 0.6.8 Initiatior in AttributeExtension

[Hector Blanco]

@validates... Where have you been? Oh... In the documentation, all
along. The day I learn to read, I'll conquer the world

It works great. Thank you!

2011/12/1 Michael Bayer mike...@zzzcomputing.com:

 On Nov 30, 2011, at 7:48 PM, Hector Blanco wrote:

 Hello everyone!

 I am using (yeah, still) SqlAlchemy 0.6.8 and I'm using an
 AttributeExtension to build permissions of users.


 class UserGroupExtension(AttributeExtension):
       def set(self, state, value, oldvalue, initiator):
               userToUpdate = # !!! do things here to get the user
               value.rebuildPermissions(userToUpdate)
                return value

       def remove(self, state, value, initiator):
               removeAllThePermissionForUsersInGroup(value)
 

 So, in the UserGroupExtension, I need to get the user that fired the
 event, to apply the proper permissions to it.

 I've tried state.obj(), but that gives me an empty user.

 The state object's obj() is the parent User object receiving the events. It 
 should be the same identity as the User receiving the append.   The 
 @validates decorator will get you the same effect with less boilerplate.

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



-- 
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] SqlAlchemy 0.6.8 Initiatior in AttributeExtension

[Hector Blanco]

Hello everyone!

I am using (yeah, still) SqlAlchemy 0.6.8 and I'm using an
AttributeExtension to build permissions of users.

My users get the permissions depending on the UserGroup they're in.
The user has the typical relationship towards UserGroup.

My classes:

-- User.py
class User(declarative):
_id = Column(id, Integer, primary_key=True, key=id)
_userName = Column(user_name, String(50), unique=True, nullable=False)
_password = Column(password, String(64), nullable=False)

_userGroupId = Column(user_group_id, Integer,
ForeignKey(user_groups.id), 
key=userGroupId)
_userGroup = relationship(UserGroup,
  uselist=False,
  
extension=UserGroupExtension.UserGroupExtension(),
  
primaryjoin=user_groups.c.id == users.c.userGroupId,
  
backref=backref(_users,
  collection_class=set
  ))



-- UserGroup.py
class UserGroup(declarative):
_id = Column(id, Integer, primary_key=True, key=id)
_name = Column(name, String(50), nullable=False)
_permissions = Column(permissions,

CharSeparatedStrings.CharSeparatedStrings())

def rebuildPermissions(self, user):
   # do stuff to assign the
  # permissions of group 'self' to user 'user'



-- UserGroupExtension.py

class UserGroupExtension(AttributeExtension):
def set(self, state, value, oldvalue, initiator):
userToUpdate = # !!! do things here to get the user
value.rebuildPermissions(userToUpdate)
return value

def remove(self, state, value, initiator):
removeAllThePermissionForUsersInGroup(value)


So, in the UserGroupExtension, I need to get the user that fired the
event, to apply the proper permissions to it.

I've tried state.obj(), but that gives me an empty user.

Thank you in advance!

-- 
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] Customized order_by in tree of Mixins relationship

[Hector Blanco]

Hi everyone:

I have an object structure that uses (heavily) object orientation.

I have a BaseObject class that is the class for three kinds of
subclasses, Element1, Element2 and Element3. In my application I
have a tree of BaseObject elements:

class BaseObject(declarative):
__tablename__ = base_objects

_polymorphicIdentity = Column(polymorphic_identity, String(20),

key=polymorphicIdentity)
__mapper_args__ = {
'polymorphic_on': _polymorphicIdentity,
'polymorphic_identity': None
}
_id = Column(id, Integer, primary_key=True, key=id)
_parentId = Column(parent_id, Integer,
  ForeignKey(base_objects.id),
key=parentId)
_children = relationship(BaseObject,
 collection_class=set,
 
backref=backref(_parent, remote_side=lambda: BaseObject.id,

 uselist=False),
 
#order_by=??
 )

class Element1(BaseObject):
__tablename__ = elements_1
_id = Column(id, Integer, ForeignKey(BaseObject.BaseObject.id),
 primary_key=True)

__mapper_args__ = {
'polymorphic_identity': 'Element1',
'inherit_condition': _id == BaseObject.BaseObject._id,
}
_name = Column(name, String(50))

class Element2(BaseObject):
__tablename__ = elements_2
_id = Column(id, Integer, ForeignKey(BaseObject.BaseObject.id),
 primary_key=True)

__mapper_args__ = {
'polymorphic_identity': 'Element2',
'inherit_condition': _id == BaseObject.BaseObject._id,
}
_name = Column(name, String(50))

class Element3(BaseObject):
__tablename__ = elements_3
_id = Column(id, Integer, ForeignKey(BaseObject.BaseObject.id),
  primary_key=True)

__mapper_args__ = {
'polymorphic_identity': 'Element3',
'inherit_condition': _id == BaseObject.BaseObject._id,
}

As you can see, only two of those classes have a _name attribute.

What I'd like to know is if I can set an order_by in the
BaseObject._children relationship that does the following:
1) Sort by type, but not the default ordering. If I set up (in
_children) the order_by to order by _polymorphicIdentity (by the
discriminator), I'd get instances of type Element1, then instances
of Element2 and finally, from Element3. I don't want that. I need
to get Element1, Element3 and finally, Element2.
2) Sort by name if the child has a name. If it doesn't, leave it with
the 'special' _polymorphicIdentity described above if the child class
doesn't have a _name attribute.

I don't even know if that's possible.
Because of certain dependencies, I can't use SqlAlchemy 0.7.x yet. I
am using 0.6.8

Thank you very much in advance!

-- 
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] serialize list

[Hector Blanco]

I'm not sure if this will be helpful, but I found the types decorators
very useful :

http://www.sqlalchemy.org/docs/core/types.html#typedecorator-recipes

You can basically keep a list() in your instances and do something
when you want to save it in the database (such as saving them as a
comma separated string, or in json format...)

Evertime you rebuild an instance from the database you'll get a
list() and everytime you serialize in the datbase you can make that
list be a comma separated string (or json)

2011/9/2 Sebastian Elsner sebast...@risefx.com:
  Hello,

 I want to serialize a python list, which by convention can only contain
 strings and save it to mysql database in on column. I was thinking about an
 unicode column and an attribute event which just does a str(list) for saving
 and a eval(string) for access. I am not sure though if this is a good
 method, or if there even is a buildin method.

 Thanks for your suggestions!

 Sebastian

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



-- 
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] or_(MyClass.relationship1.contains(item), MyClass.relationship2.contains(item)) returns empty list

[Hector Blanco]

Thank you!

As I'm sure some of the people in the list already know, I also asked
this same question in StackOverflow, and I got a couple of interesting
answers.

Just in case:
http://stackoverflow.com/questions/6118783/sqlalchemy-check-if-one-object-is-in-any-relationship-or-object-relationship1

2011/5/26 Michael Bayer mike...@zzzcomputing.com:
 This seems to be an issue of poor documentation on our part.   Here are new 
 documentation elements, linked from the ORM tutorial which was previously the 
 only place contains() was mentioned, fully describing the behavior of 
 contains(), and how any() and outerjoin() are more appropriate if OR 
 conjunctions are used:

 http://www.sqlalchemy.org/docs/orm/internals.html#sqlalchemy.orm.properties.RelationshipProperty.Comparator.contains


 On May 24, 2011, at 7:51 PM, Hector Blanco wrote:

 Hello everybody...

 Let's say I have a class like this:

 class Foo(declarativeBase):
      bars1 = relationship(Bar.Bar, secondary=foos_to_bars1,
 collection_class=set())
      bars2 = relationship(Bar.Bar, secondary=foos_to_bars2,
 collection_class=list())

 At a certain point, I want to get instances of Foos that have a
 bar (instance of Bar.Bar) in any of the relationships.

 If I try to do:

 def inAnyBar(bar)
    query(Foo).filter(or_(Foo.bars1.contains(bar),
 Foo.bars2.contains(bar)).all()

 I get an empty result.

 It looks (to me) like I'm doing something like:

 query(Foo).join(Foo.bars1).filter(Foo.bars1.contains(bar)).join(Foo.bars2).filter(Foo.bars1.contains(bar))

 Since Foo.bars1 doesn't contain bar, the second filter gives empty results.

 I've been able to find a workaround with subqueries (each join+filter
 in a subquery, then or_ all the subqueries) but I'd like to know if
 there's a better way to do it...

 I'm still using SqlAlchemy 0.6.6, though.

 Thank you!

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


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



-- 
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] or_(MyClass.relationship1.contains(item), MyClass.relationship2.contains(item)) returns empty list

[Hector Blanco]

Hello everybody...

Let's say I have a class like this:

class Foo(declarativeBase):
  bars1 = relationship(Bar.Bar, secondary=foos_to_bars1,
collection_class=set())
  bars2 = relationship(Bar.Bar, secondary=foos_to_bars2,
collection_class=list())

At a certain point, I want to get instances of Foos that have a
bar (instance of Bar.Bar) in any of the relationships.

If I try to do:

def inAnyBar(bar)
query(Foo).filter(or_(Foo.bars1.contains(bar),
Foo.bars2.contains(bar)).all()

I get an empty result.

It looks (to me) like I'm doing something like:

query(Foo).join(Foo.bars1).filter(Foo.bars1.contains(bar)).join(Foo.bars2).filter(Foo.bars1.contains(bar))

Since Foo.bars1 doesn't contain bar, the second filter gives empty results.

I've been able to find a workaround with subqueries (each join+filter
in a subquery, then or_ all the subqueries) but I'd like to know if
there's a better way to do it...

I'm still using SqlAlchemy 0.6.6, though.

Thank you!

-- 
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] Execute a function on orphan

[Hector Blanco]

That is what I was looking for!
http://www.sqlalchemy.org/docs/orm/interfaces.html?highlight=attributeextension#sqlalchemy.orm.interfaces.AttributeExtension

Thank you very much!

2011/4/10 Michael Trier mtr...@gmail.com:
 On Sat, Apr 9, 2011 at 10:32 PM, Hector Blanco white.li...@gmail.com
 wrote:

 Unassigning a user from its userGroup, indeed, puts the UserGroup in
 that user to NULL. But it turns out that the entity that controls the
 permissions is a third thing. That thing takes some fields of the
 User class (id, name...) and generates an instance of a third object
 which is in charge of controlling the permissions. That third entity
 is what effectively has the permissions to access (or not) the
 application. Then, when a user is unassigned from a group, I have to
 take that third entity corresponding to that user and remove its
 permissions.


 I'm with Mike. It seems the architecture is wrong. That said if you're on
 0.7 you can use signals to handle this. On 0.6 you might want to look into
 AttributeExtension.
 http://www.sqlalchemy.org/docs/07/orm/events.html
 http://www.sqlalchemy.org/docs/orm/interfaces.html?highlight=attributeextension#sqlalchemy.orm.interfaces.AttributeExtension

 --
 Michael Trier
 http://michaeltrier.com/

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


-- 
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] Execute a function on orphan

[Hector Blanco]

Hello everyone!

I have an application with Users and UserGroups... The users have
certain permissions to do stuff in the application depending on the
UserGroup they belong to. The relationship is as follows:

--
class User(BaseClass.BaseClass, Database.Base):
__tablename__ = users

_firstName = Column(first_name, String(50), key=fistName)
_lastName = Column(last_name, String(50), key=lastName)
_userName = Column(user_name, String(50), unique=True, nullable=False)
_password = Column(password, String(64), nullable=False)

_userGroupId = Column(user_group_id, Integer,
ForeignKey(user_groups.id), key=userGroupId)

_userGroup = relationship(UserGroup, uselist=False,
backref=backref(_users,
collection_class=set
))


class UserGroup(BaseClass.BaseClass, Database.Base):
Represents a group of users with the same features
__tablename__ = user_groups

_name = Column(name, String(50))
_permissions = Column(attach_posts,
CharSeparatedStrings.CharSeparatedStrings(), key=attachPosts)
#_users: Backref from User

--

Let's say an administrator unassigns a user i.e.: john from its
UserGroup. I don't want to delete john from the system, but I want
to execute a method when it becomes orphan of user group (mainly to
remove all the permissions john has). Is that possible? Something
like:

_userGroup = relationship(UserGroup, uselist=False,
backref=backref(_users,
collection_class=set,
on_orphan=function_to_remove_permissions()
))

Thank you in advance!

-- 
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] Execute a function on orphan

[Hector Blanco]

Unassigning a user from its userGroup, indeed, puts the UserGroup in
that user to NULL. But it turns out that the entity that controls the
permissions is a third thing. That thing takes some fields of the
User class (id, name...) and generates an instance of a third object
which is in charge of controlling the permissions. That third entity
is what effectively has the permissions to access (or not) the
application. Then, when a user is unassigned from a group, I have to
take that third entity corresponding to that user and remove its
permissions.

If someone is curious, I'm using this:
http://grok.zope.org/documentation/how-to/authentication-with-grok

I can find workarounds, of course... is just that it'd be nice to have
a method executed when one of the users becomes orphan of usergroup


2011/4/9 Mike Conley mconl...@gmail.com:
 Not sure I understand the use case example.

 With these tables, doesn't the act of an administrator unassigning a user
 from its UserGroup set the group id column to NULL? If so, doesn't that
 effectively remove all the permissions because there is no longer a
 connection between the user and the group.

 --
 Mike Conley


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


-- 
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] Navigate through tree-like structure with sqlalchemy. Is it doable (now) ?

[Hector Blanco]

Hello everyone:

I have a tree-like structure (groups/nodes, basically) with Stores and
StoreGroups. An store can belong only to one storeGroup, but an
StoreGroup can contain stores or other storeGroups:

class StoreGroup(BaseClass.BaseClass, Database.Base):
Represents a storeGroup
__tablename__ = store_groups
_id = Column(id, Integer, primary_key=True)
_name = Column(name, String(50))

_storeGroups = relationship(StoreGroup,
secondary=store_group_groups, order_by=lambda:StoreGroup.name,
primaryjoin=lambda: StoreGroup.id == 
store_group_groups.c.store_groupA_id,
secondaryjoin=lambda: StoreGroup.id == 
store_group_groups.c.store_groupB_id,
backref=parentGroup,
collection_class=set)
#_stores  Backref from Store class

class Store(BaseClass.BaseClass, Database.Base):
Represents a store
__tablename__ = stores
_id = Column(id, Integer, primary_key=True)
_name = Column(name, String(50))
_number = Column(number, Integer)
_timeZone = Column(time_zone, String(20))

_storeGroupId = Column(store_group_id, Integer,
ForeignKey(store_groups.id))
_storeGroup = relationship(StoreGroup, uselist=False,
backref=backref(_stores,
order_by=lambda:Store.name,
collection_class=set)
)

So I would like to have a way to, given an StoreGroup id, to
recursively iterate through the hierarchy (StoreGroup.storeGroups) and
grab all the Stores found on said hierarchy.

I have seen the example:
http://www.sqlalchemy.org/trac/browser/examples/adjacency_list/adjacency_list.py

but that needs the depth to look in in advance.

I also saw:
http://groups.google.com/group/sqlalchemy/msg/80ea8e712380bff4

where apparently there's no sqlalchemistic way of doing it (it needs
sql tools and raw queries). I was hoping that mybe the information
I have is outdated, and it's doable now (using SqlAlchemy 0.6.6)

Thank you.

-- 
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] Navigate through tree-like structure with sqlalchemy. Is it doable (now) ?

[Hector Blanco]

Thanks for the quick reply.

I'll give it a try.

2011/4/8 Michael Bayer mike...@zzzcomputing.com:

 On Apr 8, 2011, at 12:12 PM, Hector Blanco wrote:

 Hello everyone:

 I have a tree-like structure (groups/nodes, basically) with Stores and
 StoreGroups. An store can belong only to one storeGroup, but an
 StoreGroup can contain stores or other storeGroups:

 class StoreGroup(BaseClass.BaseClass, Database.Base):
       Represents a storeGroup
       __tablename__ = store_groups
       _id = Column(id, Integer, primary_key=True)
       _name = Column(name, String(50))

       _storeGroups = relationship(StoreGroup,
               secondary=store_group_groups, order_by=lambda:StoreGroup.name,
               primaryjoin=lambda: StoreGroup.id == 
 store_group_groups.c.store_groupA_id,
               secondaryjoin=lambda: StoreGroup.id == 
 store_group_groups.c.store_groupB_id,
               backref=parentGroup,
               collection_class=set)
       #_stores  Backref from Store class

 class Store(BaseClass.BaseClass, Database.Base):
       Represents a store
       __tablename__ = stores
       _id = Column(id, Integer, primary_key=True)
       _name = Column(name, String(50))
       _number = Column(number, Integer)
       _timeZone = Column(time_zone, String(20))

       _storeGroupId = Column(store_group_id, Integer,
 ForeignKey(store_groups.id))
       _storeGroup = relationship(StoreGroup, uselist=False,
               backref=backref(_stores,
               order_by=lambda:Store.name,
               collection_class=set)
               )

 So I would like to have a way to, given an StoreGroup id, to
 recursively iterate through the hierarchy (StoreGroup.storeGroups) and
 grab all the Stores found on said hierarchy.

 I have seen the example:
 http://www.sqlalchemy.org/trac/browser/examples/adjacency_list/adjacency_list.py

 but that needs the depth to look in in advance.

 I also saw:
 http://groups.google.com/group/sqlalchemy/msg/80ea8e712380bff4

 where apparently there's no sqlalchemistic way of doing it (it needs
 sql tools and raw queries). I was hoping that mybe the information
 I have is outdated, and it's doable now (using SqlAlchemy 0.6.6)

 that link doesn't appear to be relevant to what you are asking here.    To 
 iterate as you asked, that would be:

 group = session.query(StoreGroup).filter(StoreGroup._id==id).one()

 stack = [group]
 while stack:
    g = stack.pop()
    for store in g._stores:
        do_something_with(store)
    stack.extend(g._storeGroups)



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



-- 
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] I'm missing something with the session...

[Hector Blanco]

Oh... I'll give it a try...

I'm using some parts of z3c.saconfig without knowing too well what
they do... I took this tutorial:
http://grok.zope.org/documentation/how-to/orm-using-megrok.rdb-and-sqlalchemy

... and I didn't look much further.

I'll try to dig into the packages you mentioned. Looks like they might
be helpful

Thanks!

2011/3/24 Wichert Akkerman wich...@wiggy.net:
 On 3/23/11 23:11 , Hector Blanco wrote:

 Yeah... the closing thing is because this is going to be in a
 webserver, and the framework that controls the requests really, really
 messed up everything (mysql daemon, sqlalchemy...) if the http request
 was canceled (If I had a user pressing F5 in Firefox all the time, I
 got coredumps that stopped the server)

 That was me:
 http://groups.google.com/group/sqlalchemy/browse_thread/thread/5a3c7c8056cf6a60/6805bbe38667b9be?lnk=gstq=Hector+Blanco#6805bbe38667b9be

 That's why I decided to commit, close, and such as soon as possible
 (leave the sessions opened as little as possible)

 The problem seems to have improved lately (there was an update of the
 Zope framework recently, and the problem seems to have relaxed a
 bit)... but I'm still scared!! According to some other documents/posts
 I've read, maybe a commit (without the closing) would still work,
 though.

 FWIW I do a fair bit of SQLAlchemy things in Zope and have never seen such
 problems, nor have I ever heard of anyone seeing problems like that using
 Zope and SQL, which is a fairly common setup. Perhaps the missing trick here
 is to use zope.sqlalchemy and/or z3c.saconfig to
 handle the SQLAlchemy/Zope integration.

 Wichert.


-- 
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] Querying with not - the python way (the same way python behaves)

[Hector Blanco]

Hello everyone.

I was wondering what is the best way to perform a query filtering by
not in a python-way.

In python:
 not(None)
True
 not(list())
True

Let's say I have a class that has the typical children relationship:

class Foo(declarative_base):
__tablename__ = foos

_name = Column(name, String(50))

_parentId = Column(parent_id, Integer, ForeignKey(foos.id), 
key=parentId)
_children = relationship(Foo,
collection_class=set,
backref=backref(_parent, remote_side=lambda: Foo.id, 
uselist=False),
)

def __init__(self):
self.name = 
self.parentId = None
self.parent = None
self.children = set()

And I want to query the class where children is not (meaning is None
or is an empty set).

I can easily query with the filter (Foo.children == None) but what
about the empty set? And a comparator suitable for both?  (empty sety
and None) I'm sure there's a way, but googling not sqlalchemy
comparison doesn't help much

If I try the sqlalchemy.not_, I get an SQL programming error (which
doesn't surprise me, because it generates an empty comparison)

Thank you

-- 
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] I'm missing something with the session...

[Hector Blanco]

Hello everyone...

I am getting detached instances error and I don't really know why.
There's something I don't get with the session, but I have been
reading the documentation thoroughly, trying many things and I can't
get rid of all the DetachedInstance exceptions... That's why I think
I'm doing something [deeply] wrong ( I think I have a core concept
error)

I have designed my application in the following way:

The idea is to have my mapped classes (classes serialized in a MySQL
table) and for each class  I would have a manager that contains a
bunch of methods that deal with the database.

Let's say I have:

class Foo(Database.Base):
_id = Column(id, Integer, primary_key=True, key=id)
_name = Column(name, String(50))

Then I will also have a FooManager like this:


from myLibraries.foos import Foo

class FooManager(object):

@classmethod
def getById(cls, idParam, relationshipsToPreLoad=None):
retval = None
if relationshipsToPreLoad is None:
relationshipsToPreLoad = 
DatabaseUtils.getRelationships(Foo.Foo)
session = Database.Session()
try:
if (relationshipsToPreLoad):
retval = session.query(Foo.Foo).options(*
[joinedload_all(relationshipToPreLoad)
for relationshipToPreLoad in
relationshipsToPreLoad]).get(int(idParam))
else:
retval = 
session.query(Foo.Foo).get(int(idParam))
session.commit()
except exc.ResourceClosedError, err:
log.debug('::getById  Manager %s  Got exception %s.\
Probably the request was canceled by 
the user' % (cls.__name__, err))
finally:
session.close()
pass
return retval

@classmethod
def update(cls, element):
if isinstance(element, Foo.Foo):
session = Database.Session()
try:
element = session.merge(element)
session.commit()
finally:
session.close()
log.debug(::update Updated %s with id==%s %
(element.__class__.__name__, element.id))
return element
else:
raise TypeError(Received parameter %s of type %s when 
expecting
%s % (element, type(element), Foo.Foo.__classname__))
return None


So when, in another part of the application I want to load the element
Foo.Foo with id 6, I could just do:

myFooInstance = myLibraries.foos.FooManager.FooManager.getById(6)
print myFooInstance.id
print myFooInstance.name

Or, if I create a new Foo instance, I can do:
newFoo = Foo.Foo()
newFoo.name = fooname
myLibraries.foos.FooManager.FooManager.update(newFoo)

and the newFoo will be added to the database.

The Database module is just a few lines long and contains the session maker:

 Database.py -
DSN = mysql://mysqluser:***@localhost/ev?charset=utf8
engine = create_engine(DSN)
Session = scoped_session(sessionmaker(bind=engine))

Base = declarative_base()
---


The whole idea is separating the access to the database from the
classes itself (the manager's idea is something like go to the
database, do your thing, give me a regular python class, but it's not
working fine. I've been able to find some workarounds but it's still
not working fine. Do you think this Class/ ClassManager type of
implementation a good idea? As you can see, in every method of the
manager I create a new instance of the session, do whatever and close
it. Is that the way the session is supposed to be used? Should the
session be global? (I can create an instance in Database.py)

I saw this:
http://groups.google.com/group/sqlalchemy/browse_thread/thread/f53f98cbbaee7b2b/14c3d8c7229ba0bc?lnk=gstq=DetachedInstanceError#14c3d8c7229ba0bc

which seems to detail a similar problem. I tried both methods detailed
there, but I'm still getting detached instances errors. And it seems
to me that it recommends not to use detached instances (which, as far
as I understand, is what I'm trying to do here)

Any hint will be deeply appreciated. Thank you everyone!

-- 
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] Trying to put a relationship N:M in Mixin (base) class

[Hector Blanco]

Hello everyone.

I have a kind of virtual class that I want to use as base class for
all the elements that are going to be stored in the database.

Initially I had:
-- BaseClass.py --
class BaseClass(object):
_id = Column(id, Integer, primary_key=True, key=id)

def __hash__(self):
return int(self.id)

def setId(self, id):
Set id
self._id = int(id)

def getId(self):
Get id
return self._id

@declared_attr
def id(cls):
return synonym('_id', descriptor=property(cls.getId, cls.setId))
--

And that was working really fine.

But now I want to add a relationship N:M from this BaseClass to
UserGroup, to control the visibility of the objects in the System. I
want that only certain UserGroups can see certain elements.

I.e.:
Let's say I have another class Store and I want only the users
belonging to certain userGroup(s) to be able to see some of the
Store instances (i.e.: only usergroups with ids 5 or 6 will be able
to see stores id== 3,4,5... only usergroups 5, 7 and 8 will be able to
access stores 5, 8) . To accomplish that, I would create an N:M
relationship between the Store and UserGroup classes:

class Store(BaseClass.BaseClass, Database.Base):
__tablename__ = stores

_name = Column(name, String(50))
_number = Column(number, Integer)

_userGroups = relationship(UserGroup, secondary=user_group_store,
primaryjoin=lambda: Store.id == user_group_store.c.store_id,
secondaryjoin=lambda: UserGroup.UserGroup.id ==

user_group_store.c.user_group_id,
collection_class=set
)

By setting this relationship, (and, of course, with the intermediate
table user_group_store which relates UserGroups with Stores) I can
easily filter the stores that a UserGroup can see using:

query = session.query(Store.Store).join('userGroups')
.filter(UserGroup.UserGroup.id == int(userGroupId)) .all()

But now I want to do that not only with the Store class but with
every element in my system, so I can filter every class stored in the
database by UserGroup (something like it says in
http://www.sqlalchemy.org/docs/orm/extensions/declarative.html#mixing-in-relationships,
but N:M and with a different intermediate table per class to relate to
UserGroup)

I tried to move that relationship to the BaseClass detailed above (in
a similar way to the id):

--- BaseClass.py 

from myclasses.database import Tables
#Tables is the module where the intermediate tables are defined.
# If I want to be able to relate UserGroup with Store, in that Tables modules
# I will create a table (in Tables.py) like:
# intermediate_allowed_user_groups_to_stores = Table(
#   intermediate_allowed_user_groups_to_stores,
#   Base.metadata,
#   Column(id, Integer, primary_key=True),
#   Column(element_id, Integer, ForeignKey(stores.id), key=elementId),
#   Column(user_group_id,
#   Integer, ForeignKey(user_groups.id),
#   key=userGroupId)
#)

class BaseClass(object):
sqlRelationships_accelerator = None
internalAttrs_accelerator = None
properties_accelerator = None

_id = Column(id, Integer, primary_key=True, key=id)

@classmethod
def intermediate_allowed_user_groups_to_this(cls):
retval = None
try:
mapper = class_mapper(cls)
except ormExc.UnmappedClassError:
mapper = None

if mapper and (mapper.local_table is not None):
try:
retval = getattr(Tables,

(intermediate_allowed_user_groups_to_%s
% mapper.local_table.name))
except KeyError:
return None
return retval

@declared_attr
def _allowedUserGroups(cls):
if cls:
intermediateTable = 
cls.intermediate_allowed_user_groups_to_this()
if intermediateTable is not None:
return relationship(UserGroup,
secondary=intermediateTable,
primaryjoin=%s._id == 
intermediateTable.elementId % cls.__name__,
secondaryjoin=UserGroup._id == 
intermediateTable.userGroupId,
collection_class=set
)
return None

def __hash__(self):
return int(self.id)

def setId(self, id):
Set id
  

Re: [sqlalchemy] I'm missing something with the session...

[Hector Blanco]

Yeah... the closing thing is because this is going to be in a
webserver, and the framework that controls the requests really, really
messed up everything (mysql daemon, sqlalchemy...) if the http request
was canceled (If I had a user pressing F5 in Firefox all the time, I
got coredumps that stopped the server)

That was me: 
http://groups.google.com/group/sqlalchemy/browse_thread/thread/5a3c7c8056cf6a60/6805bbe38667b9be?lnk=gstq=Hector+Blanco#6805bbe38667b9be

That's why I decided to commit, close, and such as soon as possible
(leave the sessions opened as little as possible)

The problem seems to have improved lately (there was an update of the
Zope framework recently, and the problem seems to have relaxed a
bit)... but I'm still scared!! According to some other documents/posts
I've read, maybe a commit (without the closing) would still work,
though.

2011/3/23 Michael Bayer mike...@zzzcomputing.com:
 There's a lot of detail here but the antipattern I see is you're creating a 
 session just to do a getById() then closing it.    A single Session should 
 frame a logical series of operations.  When those operations are complete, 
 you commit your session if needed, close it out, then throw everything away.  
  Using detached objects is only when moving them in and out of a cache or 
 passing them across thread or process boundaries to another waiting worker 
 that will immediately re-merge them back into a different Session, which is 
 itself framing out a logical series of operations.


 On Mar 23, 2011, at 2:48 PM, Hector Blanco wrote:

 Hello everyone...

 I am getting detached instances error and I don't really know why.
 There's something I don't get with the session, but I have been
 reading the documentation thoroughly, trying many things and I can't
 get rid of all the DetachedInstance exceptions... That's why I think
 I'm doing something [deeply] wrong ( I think I have a core concept
 error)

 I have designed my application in the following way:

 The idea is to have my mapped classes (classes serialized in a MySQL
 table) and for each class  I would have a manager that contains a
 bunch of methods that deal with the database.

 Let's say I have:

 class Foo(Database.Base):
       _id = Column(id, Integer, primary_key=True, key=id)
       _name = Column(name, String(50))

 Then I will also have a FooManager like this:

 
 from myLibraries.foos import Foo

 class FooManager(object):

       @classmethod
       def getById(cls, idParam, relationshipsToPreLoad=None):
               retval = None
               if relationshipsToPreLoad is None:
                       relationshipsToPreLoad = 
 DatabaseUtils.getRelationships(Foo.Foo)
               session = Database.Session()
               try:
                       if (relationshipsToPreLoad):
                               retval = session.query(Foo.Foo).options(*
 [joinedload_all(relationshipToPreLoad)
                                               for relationshipToPreLoad in
 relationshipsToPreLoad]).get(int(idParam))
                       else:
                               retval = 
 session.query(Foo.Foo).get(int(idParam))
                       session.commit()
               except exc.ResourceClosedError, err:
                       log.debug('::getById  Manager %s  Got exception 
 %s.\
                                       Probably the request was canceled by 
 the user' % (cls.__name__, err))
               finally:
                       session.close()
                       pass
               return retval

       @classmethod
       def update(cls, element):
               if isinstance(element, Foo.Foo):
                       session = Database.Session()
                       try:
                               element = session.merge(element)
                               session.commit()
                       finally:
                               session.close()
                               log.debug(::update Updated %s with id==%s %
 (element.__class__.__name__, element.id))
                               return element
               else:
                       raise TypeError(Received parameter %s of type %s when 
 expecting
 %s % (element, type(element), Foo.Foo.__classname__))
                       return None
 

 So when, in another part of the application I want to load the element
 Foo.Foo with id 6, I could just do:

 myFooInstance = myLibraries.foos.FooManager.FooManager.getById(6)
 print myFooInstance.id
 print myFooInstance.name

 Or, if I create a new Foo instance, I can do:
 newFoo = Foo.Foo()
 newFoo.name = fooname
 myLibraries.foos.FooManager.FooManager.update(newFoo)

 and the newFoo will be added to the database.

 The Database module is just a few lines long and contains the session 
 maker:

  Database.py -
 DSN = mysql://mysqluser:***@localhost/ev?charset=utf8
 engine = create_engine(DSN)
 Session

Re: [sqlalchemy] Trying to put a relationship N:M in Mixin (base) class

[Hector Blanco]

Thank you so much!

I'll let you know!

P.S.:
just create the m2m table for the relationship as needed.

... and this is another evidence that my brain is not 100%
functional... Why didn't it occur to me? I dunno...

2011/3/23 Michael Bayer mike...@zzzcomputing.com:

 On Mar 23, 2011, at 5:47 PM, Hector Blanco wrote:

 Hello everyone.


 class BaseClass(object):
       sqlRelationships_accelerator = None
       internalAttrs_accelerator = None
       properties_accelerator = None

       _id = Column(id, Integer, primary_key=True, key=id)

       @classmethod
       def intermediate_allowed_user_groups_to_this(cls):
               retval = None
               try:
                       mapper = class_mapper(cls)
               except ormExc.UnmappedClassError:
                       mapper = None

               if mapper and (mapper.local_table is not None):
                       try:
                               retval = getattr(Tables,
                                       
 (intermediate_allowed_user_groups_to_%s
                                       % mapper.local_table.name))
                       except KeyError:
                               return None
               return retval

       @declared_attr
       def _allowedUserGroups(cls):
               if cls:
                       intermediateTable = 
 cls.intermediate_allowed_user_groups_to_this()
                       if intermediateTable is not None:
                               return relationship(UserGroup,
                                       secondary=intermediateTable,
                                       primaryjoin=%s._id == 
 intermediateTable.elementId % cls.__name__,
                                       secondaryjoin=UserGroup._id == 
 intermediateTable.userGroupId,
                                       collection_class=set
                               )
               return None

 there's an enormous amount of complexity here for me to gather, its not 
 runnable either, which basically means I'm going to skip it.   In particular 
 the whole digging into mappers and finding tables seems unnecessary, just 
 create the m2m table for the relationship as needed.

 For the general case of everyone has a many-to-many to X, a short example 
 is attached.  I hope to blog more about this kind of thing as an updated 
 approach to that discussed in the old Polymorphic Associations post.










       def __hash__(self):
               return int(self.id)

       def setId(self, id):
               Set id
               self._id = int(id)

       def getId(self):
               Get id
               return self._id

       def setAllowedUserGroups(self, allowedUserGroups):
               self._allowedUserGroups = set(allowedUserGroups)

       def getAllowedUserGroups(self):
               return self._allowedUserGroups

       @declared_attr
       def allowedUserGroups(cls):
               return synonym('_allowedUserGroups',
                       descriptor=property(cls.getAllowedUserGroups,
                                                   cls.setAllowedUserGroups))
 

 The intermediate_allowed_user_groups_to_this classmethod tries to
 grab the intermediate table from the Tables module based on the name
 of the table where the actual instances of the class (descending from
 BaseClass) are going to be stored. Going back to the Store class, the
 __tablename__ is stores. The
 intermediate_allowed_user_groups_to_this method will try to grab a
 table called intermediate_allowed_user_groups_to_stores (because
 that is the intermediate table that would link UserGroups and Stores)

 * What I wanted to achieve:
 To filter by userGroup, I just wanted to need adding an intermediate
 table to the Tables module relating the UserGroup with the class to
 filter (as I explained, if I wanted to filter Store, which is stored
 in the table
 stores, I just need to create a table called
 intermediate_allowed_user_groups_to_stores, or if I wanted to filter
 Foo, stored in the foos table, I would just need to create
 intermediate_allowed_user_groups_to_foos and the baseclass, with its
 declared_attribute relationship, helped by the
 intermediate_allowed_user_groups_to_this would take care of the
 rest.

 What I got:

 Traceback (most recent call last):
  File /home/ae/ev-cms/server/src/server/app.py, line 30, in __init__
    SetupDB.setupDB()
  File /home/ae/ev-cms/backlib/database/SetupDB.py, line 26, in setupDB
    populateWithSamples()
  File /home/ae/ev-cms/backlib/database/SetupDB.py, line 86, in
            populateWithSamples
    samples = Store.Store.getSamples()
  File /home/ae/ev-cms/backlib/store/Store.py, line 379, in getSamples
    store = cls()
  File string, line 4, in __init__
  File 
 /home/ae/.buildout/eggs/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/orm/state.py,
 line 111, in initialize_instance
    return manager.events.original_init(*mixed[1:], **kwargs)
  File /home/ae/ev-cms

Re: [sqlalchemy] Trying to put a relationship N:M in Mixin (base) class

[Hector Blanco]

Yeeey!!

It works!

I had to deal with the primaryjoins/secondaryjoins thing but it worked.

I'm attaching it, just in case it can help someone else!

2011/3/23 Hector Blanco white.li...@gmail.com:
 Thank you so much!

 I'll let you know!

 P.S.:
    just create the m2m table for the relationship as needed.

 ... and this is another evidence that my brain is not 100%
 functional... Why didn't it occur to me? I dunno...

 2011/3/23 Michael Bayer mike...@zzzcomputing.com:

 On Mar 23, 2011, at 5:47 PM, Hector Blanco wrote:

 Hello everyone.


 class BaseClass(object):
       sqlRelationships_accelerator = None
       internalAttrs_accelerator = None
       properties_accelerator = None

       _id = Column(id, Integer, primary_key=True, key=id)

       @classmethod
       def intermediate_allowed_user_groups_to_this(cls):
               retval = None
               try:
                       mapper = class_mapper(cls)
               except ormExc.UnmappedClassError:
                       mapper = None

               if mapper and (mapper.local_table is not None):
                       try:
                               retval = getattr(Tables,
                                       
 (intermediate_allowed_user_groups_to_%s
                                       % mapper.local_table.name))
                       except KeyError:
                               return None
               return retval

       @declared_attr
       def _allowedUserGroups(cls):
               if cls:
                       intermediateTable = 
 cls.intermediate_allowed_user_groups_to_this()
                       if intermediateTable is not None:
                               return relationship(UserGroup,
                                       secondary=intermediateTable,
                                       primaryjoin=%s._id == 
 intermediateTable.elementId % cls.__name__,
                                       secondaryjoin=UserGroup._id == 
 intermediateTable.userGroupId,
                                       collection_class=set
                               )
               return None

 there's an enormous amount of complexity here for me to gather, its not 
 runnable either, which basically means I'm going to skip it.   In particular 
 the whole digging into mappers and finding tables seems unnecessary, just 
 create the m2m table for the relationship as needed.

 For the general case of everyone has a many-to-many to X, a short example 
 is attached.  I hope to blog more about this kind of thing as an updated 
 approach to that discussed in the old Polymorphic Associations post.










       def __hash__(self):
               return int(self.id)

       def setId(self, id):
               Set id
               self._id = int(id)

       def getId(self):
               Get id
               return self._id

       def setAllowedUserGroups(self, allowedUserGroups):
               self._allowedUserGroups = set(allowedUserGroups)

       def getAllowedUserGroups(self):
               return self._allowedUserGroups

       @declared_attr
       def allowedUserGroups(cls):
               return synonym('_allowedUserGroups',
                       descriptor=property(cls.getAllowedUserGroups,
                                                   cls.setAllowedUserGroups))
 

 The intermediate_allowed_user_groups_to_this classmethod tries to
 grab the intermediate table from the Tables module based on the name
 of the table where the actual instances of the class (descending from
 BaseClass) are going to be stored. Going back to the Store class, the
 __tablename__ is stores. The
 intermediate_allowed_user_groups_to_this method will try to grab a
 table called intermediate_allowed_user_groups_to_stores (because
 that is the intermediate table that would link UserGroups and Stores)

 * What I wanted to achieve:
 To filter by userGroup, I just wanted to need adding an intermediate
 table to the Tables module relating the UserGroup with the class to
 filter (as I explained, if I wanted to filter Store, which is stored
 in the table
 stores, I just need to create a table called
 intermediate_allowed_user_groups_to_stores, or if I wanted to filter
 Foo, stored in the foos table, I would just need to create
 intermediate_allowed_user_groups_to_foos and the baseclass, with its
 declared_attribute relationship, helped by the
 intermediate_allowed_user_groups_to_this would take care of the
 rest.

 What I got:

 Traceback (most recent call last):
  File /home/ae/ev-cms/server/src/server/app.py, line 30, in __init__
    SetupDB.setupDB()
  File /home/ae/ev-cms/backlib/database/SetupDB.py, line 26, in setupDB
    populateWithSamples()
  File /home/ae/ev-cms/backlib/database/SetupDB.py, line 86, in
            populateWithSamples
    samples = Store.Store.getSamples()
  File /home/ae/ev-cms/backlib/store/Store.py, line 379, in getSamples
    store = cls()
  File string, line 4, in __init__

[sqlalchemy] Re: Getting instances that contains other instances in an N:M relationship

[Hector Blanco]

Got it:

query = session.query(Store.Store)
query = query.join('userGroups', 'users')
query = query.filter(User.User.id == int(userId))
print str(query.all())

From the examples inside the sqlalchemy egg
(http://prdownloads.sourceforge.net/sqlalchemy/SQLAlchemy-0.6.6.tar.gz?download)

In the examples/association/basic_association.py file.

Nice!.

P.S.: Now I'm a little bit down, because I've spent one whole day
figuring out something that is explained inside a file called
basic_association... :-D What will be an advanced_association??

2011/3/16 Hector Blanco white.li...@gmail.com:
 Hello everyone!

 I am reopening that because now I want to go an step further... And
 I'm having troubles.

 Let's say I have an Store class that has a relationship pointing to
 UserGroup that has a relationship pointing to Users.

 I'm trying to create a method getStoresByUserId(parameterUserId) that,
 if I pass a numeric user id as a parameter, would give me a list of
 the stores that said user can see. I have modeled it like:

 class Store(declarativeBase):
        __tablename__ = stores

        _name = Column(name, String(50))

        _userGroups = relationship(UserGroup, secondary=user_group_store,
 order_by=lambda:UserGroup.name,
                primaryjoin=lambda: Store.id == user_group_store.c.store_id,
                secondaryjoin=lambda: UserGroup.id == 
 user_group_store.c.user_group_id,
                collection_class=set
                )

 class UserGroup(declarativeBase):
        __tablename__ = user_groups

        _name = Column(name, String(50))
        #_users: Backref from User

 class User(declarativeBase):
        __tablename__ = users

        _firstName = Column(first_name, String(50))
        _lastName = Column(last_name, String(50))
        _userName = Column(user_name, String(50), unique=True, 
 nullable=False)
        _password = Column(password, String(64), nullable=False)

        _userGroupId = Column(user_group_id, Integer, 
 ForeignKey(user_groups.id))
        _userGroup = relationship(UserGroup, uselist=False,
                backref=backref(_users,
                        collection_class=set
                ))

 So, in the method I want to create,
 (getStoresByUserId(parameterUserId) or something like that) I
 understand that I have to load the Store.userGroups, then load the
 users of the UserGroup(s) and then check that that User.id ==
 parameterId

 I have tried:
 query = query.select_from(join(Store.Store.userGroups,
 UserGroup.UserGroup, UserGroup.UserGroup.users,
 User.User).filter(User.User.id == int(parameterId)))

 ...and... erm... several other thousands of combinations like that...
 Without luck. With that, I get:
 AttributeError: Neither 'property' object nor 'function' object has an
 attribute 'corresponding_column'

 In some other cases I get Stores (instances) but they are not
 properly filtered. It looks like it's getting all the stores assigned
 to any userGroup, without filtering by the user id...

 Now I'm kind of lost.

 Thank you in advance!

 2011/3/16 Hector Blanco white.li...@gmail.com:
 Hello everyone!

 In my application I have a class Store that can contain several
 UserGroups (for permission purposes) and one UserGroup can belong
 to several Stores.

 I want to get the Stores that contain a certain UserGroup (instance):

 I have it modeled like this:

 class Store(declarativeBase):
        __tablename__ = stores

        _id = Column(id, Integer, primary_key=True)
        _name = Column(name, String(50))
        _number = Column(number, Integer)

        _storeGroupId = Column(store_group_id, Integer,
 ForeignKey(store_groups.id))


        # _devices: Backref from Device

        _userGroups = relationship(UserGroup, secondary=user_group_store,
 order_by=lambda:UserGroup.UserGroup.name,
                primaryjoin=lambda: Store.id == user_group_store.c.store_id,
                secondaryjoin=lambda: UserGroup.UserGroup.id ==
 user_group_store.c.user_group_id,
                collection_class=set
                )

 And:

 class UserGroup(declarativeBase):
        __tablename__ = user_groups

        _id = Column(id, Integer, primary_key=True)
        _name = Column(name, String(50))
        #_users: Backref from User

 I want to create a method (something like
 getStoresByUserGroup(userGroup) ) that accepts a userGroup instance
 (or id) and returns only the stores that contain that userGroup.

 That should allow me to hide certain stores for certain user groups.
 The use case is: The user who is currently logged into my application
 will belong to a certain user group. If he wants to access the
 stores stored in the database, he will only see the ones that have
 that user's userGroup among the Store._userGroups set.

 I'm trying to join the Store with the UserGroup, but then I get:
 Can't find any foreign key relationships between 'stores' and
 '%(175967212 user_groups)s

 I'm also trying to use alias, but without any luck so far.

 Do you have any idea

[sqlalchemy] Getting instances that contains other instances in an N:M relationship

[Hector Blanco]

Hello everyone!

In my application I have a class Store that can contain several
UserGroups (for permission purposes) and one UserGroup can belong
to several Stores.

I want to get the Stores that contain a certain UserGroup (instance):

I have it modeled like this:

class Store(declarativeBase):
__tablename__ = stores

_id = Column(id, Integer, primary_key=True)
_name = Column(name, String(50))
_number = Column(number, Integer)

_storeGroupId = Column(store_group_id, Integer,
ForeignKey(store_groups.id))


# _devices: Backref from Device

_userGroups = relationship(UserGroup, secondary=user_group_store,
order_by=lambda:UserGroup.UserGroup.name,
primaryjoin=lambda: Store.id == user_group_store.c.store_id,
secondaryjoin=lambda: UserGroup.UserGroup.id ==
user_group_store.c.user_group_id,
collection_class=set
)

And:

class UserGroup(declarativeBase):
__tablename__ = user_groups

_id = Column(id, Integer, primary_key=True)
_name = Column(name, String(50))
#_users: Backref from User

I want to create a method (something like
getStoresByUserGroup(userGroup) ) that accepts a userGroup instance
(or id) and returns only the stores that contain that userGroup.

That should allow me to hide certain stores for certain user groups.
The use case is: The user who is currently logged into my application
will belong to a certain user group. If he wants to access the
stores stored in the database, he will only see the ones that have
that user's userGroup among the Store._userGroups set.

I'm trying to join the Store with the UserGroup, but then I get:
Can't find any foreign key relationships between 'stores' and
'%(175967212 user_groups)s

I'm also trying to use alias, but without any luck so far.

Do you have any idea, hint... Whatever. I'm kind of lost here. I keep
trying things without knowing very well what I'm doing.

Thank you in advance

-- 
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: Getting instances that contains other instances in an N:M relationship

[Hector Blanco]

Cr*p!... 5 minutes after writing, I got it:

query = session.query(Store.Store).select_from(join(Store.Store,
UserGroup.UserGroup,
Store.Store.userGroups)).filter(UserGroup.UserGroup.id ==
int(userGroupId))

http://www.sqlalchemy.org/docs/orm/tutorial.html#querying-with-joins

Well... it may help someone :)

2011/3/16 Hector Blanco white.li...@gmail.com:
 Hello everyone!

 In my application I have a class Store that can contain several
 UserGroups (for permission purposes) and one UserGroup can belong
 to several Stores.

 I want to get the Stores that contain a certain UserGroup (instance):

 I have it modeled like this:

 class Store(declarativeBase):
        __tablename__ = stores

        _id = Column(id, Integer, primary_key=True)
        _name = Column(name, String(50))
        _number = Column(number, Integer)

        _storeGroupId = Column(store_group_id, Integer,
 ForeignKey(store_groups.id))


        # _devices: Backref from Device

        _userGroups = relationship(UserGroup, secondary=user_group_store,
 order_by=lambda:UserGroup.UserGroup.name,
                primaryjoin=lambda: Store.id == user_group_store.c.store_id,
                secondaryjoin=lambda: UserGroup.UserGroup.id ==
 user_group_store.c.user_group_id,
                collection_class=set
                )

 And:

 class UserGroup(declarativeBase):
        __tablename__ = user_groups

        _id = Column(id, Integer, primary_key=True)
        _name = Column(name, String(50))
        #_users: Backref from User

 I want to create a method (something like
 getStoresByUserGroup(userGroup) ) that accepts a userGroup instance
 (or id) and returns only the stores that contain that userGroup.

 That should allow me to hide certain stores for certain user groups.
 The use case is: The user who is currently logged into my application
 will belong to a certain user group. If he wants to access the
 stores stored in the database, he will only see the ones that have
 that user's userGroup among the Store._userGroups set.

 I'm trying to join the Store with the UserGroup, but then I get:
 Can't find any foreign key relationships between 'stores' and
 '%(175967212 user_groups)s

 I'm also trying to use alias, but without any luck so far.

 Do you have any idea, hint... Whatever. I'm kind of lost here. I keep
 trying things without knowing very well what I'm doing.

 Thank you in advance


-- 
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: Getting instances that contains other instances in an N:M relationship

[Hector Blanco]

Hello everyone!

I am reopening that because now I want to go an step further... And
I'm having troubles.

Let's say I have an Store class that has a relationship pointing to
UserGroup that has a relationship pointing to Users.

I'm trying to create a method getStoresByUserId(parameterUserId) that,
if I pass a numeric user id as a parameter, would give me a list of
the stores that said user can see. I have modeled it like:

class Store(declarativeBase):
__tablename__ = stores

_name = Column(name, String(50))

_userGroups = relationship(UserGroup, secondary=user_group_store,
order_by=lambda:UserGroup.name,
primaryjoin=lambda: Store.id == user_group_store.c.store_id,
secondaryjoin=lambda: UserGroup.id == 
user_group_store.c.user_group_id,
collection_class=set
)

class UserGroup(declarativeBase):
__tablename__ = user_groups

_name = Column(name, String(50))  
#_users: Backref from User

class User(declarativeBase):
__tablename__ = users

_firstName = Column(first_name, String(50))
_lastName = Column(last_name, String(50))
_userName = Column(user_name, String(50), unique=True, nullable=False)
_password = Column(password, String(64), nullable=False)

_userGroupId = Column(user_group_id, Integer, 
ForeignKey(user_groups.id))
_userGroup = relationship(UserGroup, uselist=False,
backref=backref(_users,
collection_class=set
))

So, in the method I want to create,
(getStoresByUserId(parameterUserId) or something like that) I
understand that I have to load the Store.userGroups, then load the
users of the UserGroup(s) and then check that that User.id ==
parameterId

I have tried:
query = query.select_from(join(Store.Store.userGroups,
UserGroup.UserGroup, UserGroup.UserGroup.users,
User.User).filter(User.User.id == int(parameterId)))

...and... erm... several other thousands of combinations like that...
Without luck. With that, I get:
AttributeError: Neither 'property' object nor 'function' object has an
attribute 'corresponding_column'

In some other cases I get Stores (instances) but they are not
properly filtered. It looks like it's getting all the stores assigned
to any userGroup, without filtering by the user id...

Now I'm kind of lost.

Thank you in advance!

2011/3/16 Hector Blanco white.li...@gmail.com:
 Hello everyone!

 In my application I have a class Store that can contain several
 UserGroups (for permission purposes) and one UserGroup can belong
 to several Stores.

 I want to get the Stores that contain a certain UserGroup (instance):

 I have it modeled like this:

 class Store(declarativeBase):
        __tablename__ = stores

        _id = Column(id, Integer, primary_key=True)
        _name = Column(name, String(50))
        _number = Column(number, Integer)

        _storeGroupId = Column(store_group_id, Integer,
 ForeignKey(store_groups.id))


        # _devices: Backref from Device

        _userGroups = relationship(UserGroup, secondary=user_group_store,
 order_by=lambda:UserGroup.UserGroup.name,
                primaryjoin=lambda: Store.id == user_group_store.c.store_id,
                secondaryjoin=lambda: UserGroup.UserGroup.id ==
 user_group_store.c.user_group_id,
                collection_class=set
                )

 And:

 class UserGroup(declarativeBase):
        __tablename__ = user_groups

        _id = Column(id, Integer, primary_key=True)
        _name = Column(name, String(50))
        #_users: Backref from User

 I want to create a method (something like
 getStoresByUserGroup(userGroup) ) that accepts a userGroup instance
 (or id) and returns only the stores that contain that userGroup.

 That should allow me to hide certain stores for certain user groups.
 The use case is: The user who is currently logged into my application
 will belong to a certain user group. If he wants to access the
 stores stored in the database, he will only see the ones that have
 that user's userGroup among the Store._userGroups set.

 I'm trying to join the Store with the UserGroup, but then I get:
 Can't find any foreign key relationships between 'stores' and
 '%(175967212 user_groups)s

 I'm also trying to use alias, but without any luck so far.

 Do you have any idea, hint... Whatever. I'm kind of lost here. I keep
 trying things without knowing very well what I'm doing.

 Thank you in advance


-- 
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] Re: Query a value that is a relationship

[Hector Blanco]

I see... I'll work something out.

Thank you Mr. Bayer!!

2011/3/1 Michael Bayer mike...@zzzcomputing.com:

 On Mar 1, 2011, at 5:50 PM, Hector Blanco wrote:

 Hello everyone:

 Let's say I have a class User  and a class UserGroup. One user can
 belong to one userGroup, an a userGroup can contain several users
 (pretty typical structure). It's a simple relationship I got modeled
 like:

 class UserGroup(declarativeBase):
   Represents a group of users with the same features
   __tablename__ = user_groups

   id = Column(id, Integer, primary_key=True)
   name = Column(name, String(50))
   users = relationship(User, order_by=lambda:User.userName,
 cascade=all, delete, collection_class=set)

 class User(declarativeBase):
   Represents a user
   __tablename__ = users

   id = Column(id, Integer, primary_key=True)
   firstName = Column(first_name, String(50))
   lastName = Column(last_name, String(50))
   email = Column(email, String(60))
   userName = Column(user_name, String(50), unique=True,
 nullable=False)
   password = Column(password, String(64), nullable=False)
   userGroupId = Column(user_group_id, Integer,
 ForeignKey(user_groups.id))

   userGroup = relationship(UserGroup, uselist=False)

 I am working in a tool that accepts generic queries, and, basically, I
 can do something like:

 session.query(User.User).filter(User.User.id  3).values(userName)

 And get tuples with a .userName field with all the userNames of the
 users whose id is  3

 But if I try:
 session.query(User.User).filter(User.User.id  3).values(userGroup)

 well yes values() accepts only scalar columns (and also you should pass the
 attribute, not a string, guess the docs aren't crystal clear on that).


 So here's the question:

 Is there any way of getting the userGroup value somehow starting
 (or querying) User objects? (or what would be the best way, if there
 are many ways)

 typically the columns you're retrieving are the thing you're starting
 from:

 query(UserGroup).join(UserGroup.users).filter(User.id  3).all()
 if you have a lot more join going on and really need a certain entity in the
 left, you can say:
 query(UserGroup).select_from(User).join(User.userGroup).filter(User.id 
 3).all()
 There's a ticket somewhere to allow query() to also accept a relationship()
 attribute that is specifically many-to-one, but that's just a small
 syntactic convenience.   query() in general accepts entities and column
 expressions only.

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


-- 
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] Query a value that is a relationship

[Hector Blanco]

Hello everyone:

Let's say I have a class User  and a class UserGroup. One user can
belong to one userGroup, an a userGroup can contain several users
(pretty typical structure). It's a simple relationship I got modeled
like:

class UserGroup(declarativeBase):
Represents a group of users with the same features
__tablename__ = user_groups

id = Column(id, Integer, primary_key=True)
name = Column(name, String(50))
users = relationship(User, order_by=lambda:User.userName,
cascade=all, delete, collection_class=set)

class User(declarativeBase):
Represents a user
__tablename__ = users

id = Column(id, Integer, primary_key=True)
firstName = Column(first_name, String(50))
lastName = Column(last_name, String(50))
email = Column(email, String(60))
userName = Column(user_name, String(50), unique=True, nullable=False)
password = Column(password, String(64), nullable=False)
userGroupId = Column(user_group_id, Integer, 
ForeignKey(user_groups.id))

userGroup = relationship(UserGroup, uselist=False)

I am working in a tool that accepts generic queries, and, basically, I
can do something like:

session.query(User.User).filter(User.User.id  3).values(userName)

And get tuples with a .userName field with all the userNames of the
users whose id is  3

But if I try:
session.query(User.User).filter(User.User.id  3).values(userGroup)

I get an error:

OperationalError: (OperationalError) (1054, Unknown column
'userGroup' in 'field list') 'SELECT userGroup' ()
session.query(User.User).filter(User.User.id  3).values(userGroup)

So here's the question:

Is there any way of getting the userGroup value somehow starting
(or querying) User objects? (or what would be the best way, if there
are many ways)

Thank you!

-- 
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: Query a value that is a relationship

[Hector Blanco]

Hello everyone:

Let's say I have a class User  and a class UserGroup. One user can
belong to one userGroup, an a userGroup can contain several users
(pretty typical structure). It's a simple relationship I got modeled
like:

class UserGroup(declarativeBase):
   Represents a group of users with the same features
   __tablename__ = user_groups

   id = Column(id, Integer, primary_key=True)
   name = Column(name, String(50))
   users = relationship(User, order_by=lambda:User.userName,
cascade=all, delete, collection_class=set)

class User(declarativeBase):
   Represents a user
   __tablename__ = users

   id = Column(id, Integer, primary_key=True)
   firstName = Column(first_name, String(50))
   lastName = Column(last_name, String(50))
   email = Column(email, String(60))
   userName = Column(user_name, String(50), unique=True, nullable=False)
   password = Column(password, String(64), nullable=False)
   userGroupId = Column(user_group_id, Integer,
ForeignKey(user_groups.id))

   userGroup = relationship(UserGroup, uselist=False)

I am working in a tool that accepts generic queries, and, basically, I
can do something like:

session.query(User.User).filter(User.User.id  3).values(userName)

And get tuples with a .userName field with all the userNames of the
users whose id is  3

But if I try:
session.query(User.User).filter(User.User.id  3).values(userGroup)

I get an error:

OperationalError: (OperationalError) (1054, Unknown column
'userGroup' in 'field list') 'SELECT userGroup' ()
session.query(User.User).filter(User.User.id  3).values(userGroup)

I have also tried:
session.query(User.User.userGroup).filter(User.User.id = 3).all()

but, doing this, I get all the user information, not the userGroup


So here's the question:

Is there any way of getting the userGroup value somehow starting
(or querying) User objects? (or what would be the best way, if there
are many ways)

Thank you!

-- 
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] Find whether a synonym points to a foreign key or a relationship (Looking for your blessing)

[Hector Blanco]

Thank you...

 Of course using all those descriptors for every attribute is a pretty 
 java-esque

Yeah... but you know... It's not easy getting rid of the past... And
I'm pretty O.C.D, so I lve getters/setters... I'm opened to new
experiences, though :-) Any hint, suggestion... whatever! you may have
will be very appreciated...

As usual, thank you so much!


2011/2/27 Michael Bayer mike...@zzzcomputing.com:

 On Feb 27, 2011, at 6:45 PM, Hector Blanco wrote:

 A few days ago I asked what appears in the body of the message, a few
 lines below. To summarize:

 Let's say I have a class User (yeah, to define users in my
 application) and each user can belong to one UserGroup (another
 class of my application). The User class would be something like:


 class User(declarativeBase):
       Represents a user
       __tablename__ = users

       _id = Column(id, Integer, primary_key=True)
       _firstName = Column(first_name, String(50))
       _lastName = Column(last_name, String(50))
       _userName = Column(user_name, String(50), unique=True, 
 nullable=False)
       _password = Column(password, String(64), nullable=False)
       _userGroupId = Column(user_group_id, Integer, 
 ForeignKey(user_groups.id))

       _userGroup = relationship(UserGroup, uselist=False)


       id = synonym('_id', descriptor=property(getId, setId))
       firstName = synonym('_firstName', descriptor=property(getFirstName,
                                       setFirstName))
       lastName = synonym('_lastName', descriptor=property(getLastName, 
 setLastName))
       userName = synonym('_userName', descriptor=property(getUserName, 
 setUserName))
       password = synonym('_password', descriptor=property(getPassword, 
 setPassword))
       userGroupId = synonym('_userGroupId',
                                       descriptor=property(getUserGroupId, 
 setUserGroupId))
       userGroup = synonym('_userGroup', descriptor=property(getUserGroup,
                                       setUserGroup))

 I wanted to find a way to find which synonyms pointed to foreign
 keys and which ones pointed to relationships. Basically, having a
 couple of methods like the following:
     def getRelationships(cls):
 that when invoked with getRelationships(User.User) would return a list
 with [userGroup] (withouth the _ in front)
 and another:
     def getForeignKeys(cls):
 that would return [userGroupId]

 So far I've done this:

 def getRelationships(cls):
       retval = list()
       mapper = sqlalchemy.orm.class_mapper(cls)
       actualNameToSynonym = dict()
       relationships = set()

       for prop in mapper.iterate_properties:
               if isinstance(prop, sqlalchemy.orm.properties.SynonymProperty):
                       actualNameToSynonym[prop.name] = prop.key
                       # dictionary _userName, userName, userGroup, 
 _userGroup

               elif isinstance(prop, 
 sqlalchemy.orm.properties.RelationshipProperty):
                       relationships.add(prop.key)
                       #set with _userGroup, and rest of relationships

       for relationship in relationships:
               retval.append(actualNameToSynonym[relationship])

       return retval

 def getForeignKeys(cls):
       retval = list()
       mapper = sqlalchemy.orm.class_mapper(cls)
       actualNameToSynonym = dict()
       columnsWithForeignKeys = set()

       for prop in mapper.iterate_properties:
               if isinstance(prop, sqlalchemy.orm.properties.SynonymProperty):
                       actualNameToSynonym[prop.name] = prop.key
                       # dictionary _userName, userName, userGroup, 
 _userGroup

               elif isinstance(prop, 
 sqlalchemy.orm.properties.ColumnProperty):
                       for column in prop.columns:
                               if len(column.foreign_keys)  0:
                                       columnsWithForeignKeys.add(prop.key)

       for columnWithForeignKeys in columnsWithForeignKeys:
               retval.append(actualNameToSynonym[columnWithForeignKeys])
       return retval

 Both are very similar: First they create a dictionary mapping the
 synonym's key with the real name (_userGroup, userGroup) and store
 the relationships or the columns that have a foreign key in a set
 (for the method that tries to get relationships, that set would be
 set(_userGroup) and for the one that tries to get foreign keys,
 set(_userGroupId)) . In a second for loop they match that
 underscored name with the name of the synonym to return a list with
 the names of the synonyms, and not the actual columns (basically, to
 transform _userGroupId to userGroupId)

 They seem to work, at least with my not-complicated-at-all classes,
 but I'd like to know what do you guys think of my approach. Is it
 good? Can it break something? Is there a better way?

 No thats a pretty OK way , there's an argument called resolve_synonyms to 
 get_property() in 0.6 but that's gone away in 0.7 anyway

Re: [sqlalchemy] Find whether a synonym points to a foreign key or a relationship (Looking for your blessing)

[Hector Blanco]

A few days ago I asked what appears in the body of the message, a few
lines below. To summarize:

Let's say I have a class User (yeah, to define users in my
application) and each user can belong to one UserGroup (another
class of my application). The User class would be something like:


class User(declarativeBase):
Represents a user
__tablename__ = users

_id = Column(id, Integer, primary_key=True)
_firstName = Column(first_name, String(50))
_lastName = Column(last_name, String(50))
_userName = Column(user_name, String(50), unique=True, nullable=False)
_password = Column(password, String(64), nullable=False)
_userGroupId = Column(user_group_id, Integer, 
ForeignKey(user_groups.id))

_userGroup = relationship(UserGroup, uselist=False)


id = synonym('_id', descriptor=property(getId, setId))
firstName = synonym('_firstName', descriptor=property(getFirstName,
setFirstName))
lastName = synonym('_lastName', descriptor=property(getLastName, 
setLastName))
userName = synonym('_userName', descriptor=property(getUserName, 
setUserName))
password = synonym('_password', descriptor=property(getPassword, 
setPassword))
userGroupId = synonym('_userGroupId',
descriptor=property(getUserGroupId, 
setUserGroupId))
userGroup = synonym('_userGroup', descriptor=property(getUserGroup,
setUserGroup))

I wanted to find a way to find which synonyms pointed to foreign
keys and which ones pointed to relationships. Basically, having a
couple of methods like the following:
 def getRelationships(cls):
that when invoked with getRelationships(User.User) would return a list
with [userGroup] (withouth the _ in front)
and another:
 def getForeignKeys(cls):
that would return [userGroupId]

So far I've done this:

def getRelationships(cls):
retval = list()
mapper = sqlalchemy.orm.class_mapper(cls)
actualNameToSynonym = dict()
relationships = set()

for prop in mapper.iterate_properties:
if isinstance(prop, sqlalchemy.orm.properties.SynonymProperty):
actualNameToSynonym[prop.name] = prop.key
# dictionary _userName, userName, userGroup, 
_userGroup

elif isinstance(prop, 
sqlalchemy.orm.properties.RelationshipProperty):
relationships.add(prop.key)
#set with _userGroup, and rest of relationships

for relationship in relationships:
retval.append(actualNameToSynonym[relationship])

return retval

def getForeignKeys(cls):
retval = list()
mapper = sqlalchemy.orm.class_mapper(cls)
actualNameToSynonym = dict()
columnsWithForeignKeys = set()

for prop in mapper.iterate_properties:
if isinstance(prop, sqlalchemy.orm.properties.SynonymProperty):
actualNameToSynonym[prop.name] = prop.key
# dictionary _userName, userName, userGroup, 
_userGroup

elif isinstance(prop, sqlalchemy.orm.properties.ColumnProperty):
for column in prop.columns:
if len(column.foreign_keys)  0:
columnsWithForeignKeys.add(prop.key)

for columnWithForeignKeys in columnsWithForeignKeys:
retval.append(actualNameToSynonym[columnWithForeignKeys])
return retval

Both are very similar: First they create a dictionary mapping the
synonym's key with the real name (_userGroup, userGroup) and store
the relationships or the columns that have a foreign key in a set
(for the method that tries to get relationships, that set would be
set(_userGroup) and for the one that tries to get foreign keys,
set(_userGroupId)) . In a second for loop they match that
underscored name with the name of the synonym to return a list with
the names of the synonyms, and not the actual columns (basically, to
transform _userGroupId to userGroupId)

They seem to work, at least with my not-complicated-at-all classes,
but I'd like to know what do you guys think of my approach. Is it
good? Can it break something? Is there a better way?

Thank you!



2011/2/18 Hector Blanco white.li...@gmail.com:
 I'll give it a try!!

 Thank you!

 2011/2/18 Michael Bayer mike...@zzzcomputing.com:

 On Feb 17, 2011, at 6:37 PM, Hector Blanco wrote:

 Hello everyone!

 Let's say I have a class defined like this:

 class User(declarativeBase):
       Represents a user
       __tablename__ = users

       _id = Column(id, Integer, primary_key=True)
       _phone = Column(phone, String(16))
       _userName = Column(user_name, String(50), unique=True, 
 nullable=False)
       _password = Column(password, String(64), nullable=False)

       _userGroupId

[sqlalchemy] Backrefs vs. defining the relationships by hand

[Hector Blanco]

Hello everyone...

I'd like to know what do you think it's better: Whether using backrefs
or manually defining the relationships one by one. Are the backrefs
useful to code less code or do they have other advantages?

I.e.: Let's say I have a User and a UserGroup class with (initially)
the relationships defined by hand:

class User(declarativeBase):
__tablename__ = users

_id = Column(id, Integer, primary_key=True)
_email = Column(email, String(60))
_userName = Column(user_name, String(50), unique=True, nullable=False)
_password = Column(password, String(64), nullable=False)
_userGroupId = Column(user_group_id, Integer, 
ForeignKey(user_groups.id))

_userGroup = relationship(UserGroup, uselist=False)

class UserGroup(declarativeBase):
__tablename__ = user_groups

_id = Column(id, Integer, primary_key=True)
_name = Column(name, String(50))

_users = relationship(User, order_by=lambda:User.userName,
cascade=all, delete, collection_class=set)


If, instead, I define that _users (in the UserGroup class) as a backref:


class User(declarativeBase):
__tablename__ = users

_id = Column(id, Integer, primary_key=True)
_email = Column(email, String(60))
_userName = Column(user_name, String(50), unique=True, nullable=False)
_password = Column(password, String(64), nullable=False)
_userGroupId = Column(user_group_id, Integer, 
ForeignKey(user_groups.id))


_userGroup = relationship(UserGroup, uselist=False, backref=backref(
backref = backref(_users,
order_by=lambda:User._userName,
cascade=all, delete,
collection_class=set
)
 ))

and, at a certain point I want to create a resetUsers() method in the
UserGroup class (to empty the _users set) I have to add the users in
that set to the session first and then reset it:

class UserGroup(declarativeBase):
# Yadda, yadda yadda

def resetUsers(self):
Database.Session().add_all(self._users)
self._users = set()

That doesn't happen with the UserGroup._users being a relationship on
its own (not a backref). I can just do self._users = set() and it
seems to work fine. The database looks consistent to me, and all that.

I'd like to know if I'm missing something, or if using backrefs is
better for some reason I don't know yet.

Any advice will be deeply appreciated. Thank you in advance.

-- 
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] Backrefs vs. defining the relationships by hand

[Hector Blanco]

It was a typo! :-)

 They keep the two sides of a relationship in sync on the python side.

I was suspecting using backrefs was better and that it would do some
kind of synchronization like that, yeah... But I was hoping that
someone would say No, it's the same... Because now I won't sleep
soundly at night until I change all my code... But hey... This is what
learning while coding has :-) I will have to expunge my old ideas
from my brain and update my code... I hope I can quickly flush the
changes, though

Thank you!

2011/2/24 Michael Bayer mike...@zzzcomputing.com:

 On Feb 24, 2011, at 1:20 PM, Hector Blanco wrote:

 Hello everyone...

 I'd like to know what do you think it's better: Whether using backrefs
 or manually defining the relationships one by one. Are the backrefs
 useful to code less code or do they have other advantages?

 I.e.: Let's say I have a User and a UserGroup class with (initially)
 the relationships defined by hand:

 class User(declarativeBase):
       __tablename__ = users

       _id = Column(id, Integer, primary_key=True)
       _email = Column(email, String(60))
       _userName = Column(user_name, String(50), unique=True, 
 nullable=False)
       _password = Column(password, String(64), nullable=False)
       _userGroupId = Column(user_group_id, Integer, 
 ForeignKey(user_groups.id))

       _userGroup = relationship(UserGroup, uselist=False)

 class UserGroup(declarativeBase):
       __tablename__ = user_groups

       _id = Column(id, Integer, primary_key=True)
       _name = Column(name, String(50))

       _users = relationship(User, order_by=lambda:User.userName,
 cascade=all, delete, collection_class=set)


 If, instead, I define that _users (in the UserGroup class) as a backref:


 class User(declarativeBase):
       __tablename__ = users

       _id = Column(id, Integer, primary_key=True)
       _email = Column(email, String(60))
       _userName = Column(user_name, String(50), unique=True, 
 nullable=False)
       _password = Column(password, String(64), nullable=False)
       _userGroupId = Column(user_group_id, Integer, 
 ForeignKey(user_groups.id))


       _userGroup = relationship(UserGroup, uselist=False, backref=backref(
               backref = backref(_users,
                       order_by=lambda:User._userName,
                       cascade=all, delete,
                       collection_class=set
               )
         ))

 is that correct that there is backref(backref=backref()) up there ?   clearly 
 that's not how it was intended to be used. unless its a typo.


 and, at a certain point I want to create a resetUsers() method in the
 UserGroup class (to empty the _users set) I have to add the users in
 that set to the session first and then reset it:

 class UserGroup(declarativeBase):
       # Yadda, yadda yadda

       def resetUsers(self):
               Database.Session().add_all(self._users)
               self._users = set()

 That doesn't happen with the UserGroup._users being a relationship on
 its own (not a backref). I can just do self._users = set() and it
 seems to work fine. The database looks consistent to me, and all that.

 I'd like to know if I'm missing something, or if using backrefs is
 better for some reason I don't know yet.

 Any advice will be deeply appreciated. Thank you in advance.

 backref means that there are two relationships() set up that have a 
 back_populates relationship to each other - you can also configure this as 
 two distinct relationships with back_populates:

 class A(...):
    bar = relationship(B, back_populates=foo)

 class B(...):
   foo = relationship(A, back_populates=bar)


 This means appending to one results in an append, or set, on the other, and 
 vice versa, and similar for removes.    They keep the two sides of a 
 relationship in sync on the python side.    That said it is optional, but if 
 you were to mutate both sides, both mutations would have an effect during 
 flush.




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



-- 
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: Optimize a search in several classes (each of them with simple 1:N relationships)

[Hector Blanco]

Amazing:

session.query(Cashier.Cashier).join(Register.Register).join(Store.Store).all()

I hadn't tried before because I thought it would be too straight forward...



2011/2/16 Hector Blanco white.li...@gmail.com:
 Hello everyone!

 I have a class structure like this:

 class Store(declarativeBase):
        __tablename__ = stores

        id = Column(id, Integer, primary_key=True)
        name = Column(name, String(50))
        registers = relationship(Register, cascade=all, delete,
 collection_class=set)

 (One store can have N registers, but a register can be only in one store)

 class Register(declarativeBase):
        __tablename__ = registers
        id = Column(id, Integer, primary_key=True)
        name = Column(name, String(50))
        cashiers = relationship(Cashier, cascade=all, delete, 
 collection_class=set)

 (One Register can have many different cashiers assigned, but only one
 cashier can be assigned to a register) Probably in real life this
 would be more a 1:1 relationship... but let's say there's a team of
 cashiers and the can be assigned to a bunch of different registers

 And well... finally, the Cashier thingy:

 class Cashier(declarativeBase):
        __tablename__ = cashiers
        id = Column(id, Integer, primary_key=True)

 At a certain point, I need to get the cashiers that are in certains
 stores (I receive the stores' ids as a list/set and I need to get all
 the Cashier objects that can be assigned to that store).

 The only solution my (limited and newbie) mind has been able to come up with 
 is:

 1) Get the stores.
 2) For each store, get the registers
 2) For each register, get the cashiers that can be assigned to them

 In order to do that, I have create a method like this (let's say the
 ids of the stores come in the storeIds parameter):

 returnValue = set()
 relationshipsToPreload = [registers, registers.cashiers]  # For
 the joinedload thing...
 stores = session.query(Store.Store).options( *
 [sqlalchemy.orm.joinedload_all(relationshipToPreLoad) for
 relationshipToPreLoad in
 relationshipsToPreload]).filter(Store.Store.ids.in_(storeId)).all()
 session.close()
 for store in stores:
        for register in store.registers:
                for cashier in register.cashiers:
                        returnValue.add(cashier.id)

 I would like to know if you have a better approach to do this. I got
 it working though... is mainly out of curiosity. Maybe I can make a
 bunch of joins that may improve the performance...

 Thank you in advance!


-- 
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: Filtering by a foreign key (now, without typo).

[Hector Blanco]

It was working from gecko...

I hadn't considered my dumbness.. I went trough all the records in my
database and it turns out I was having way more WhateverClass in the
ContainerClass with id == 5 than I thought!!! It was working from the
beginning!

2011/2/12 Hector Blanco white.li...@gmail.com:
 Sorry... I just sow a typo:


 Hello everyone.

 I am trying to get classes whose foreign key is whatever but I
 always get all the entries in the database, instead of the ones that
 match the criterion.

 Let's say I have a couple of classes using declarative base in a
 relationship N:1. I have that modeled like:

 class OtherClass(declarativeBase):
        __tablename__ = other_classes
        _id = Column(id, Integer, primary_key=True)
        id = sqlalchemy.orm.synonym('_id', descriptor=property(getId, setId))


 class WhateverClass(declarativeBase):
        __tablename__ = whatever_classes

        _id = Column(id, Integer, primary_key=True)
        _total = Column(total, Integer)
        _otherClassId = Column(other_class_id, Integer,
                                ForeignKey(other_classes.id))

        _otherClass = relationship(OtherClass, uselist=False)

        id = sqlalchemy.orm.synonym('_id', descriptor=property(getId, setId))
        total = sqlalchemy.orm.synonym('_total',
                descriptor=property(getTotal, setTotal))
        otherClassId = sqlalchemy.orm.synonym('_otherClassId',
                descriptor=property(getOtherClassId, setOtherClassId))
        otherClass = sqlalchemy.orm.synonym('_otherClass',
                descriptor=property(getOtherClass setOtherClass))

 If I try to do:
    from myClasses import WhateverClass
    session.query(WhateverClass.WhateverClass).filter(otherClassId = 5).all()

 I get a list with all the instances of WhateverClass that are stored
 in the database, not only the ones who are linked to the OtherClass
 with id = 5

 But if I do session.query(WhateverClass.WhateverClass).filter(total =
 100).all() I properly get instances of WhateverClass with a total
 =100.

 Do I have to use a joined query or something like that?

 Thank you in advance!


-- 
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] Find whether a synonym points to a foreign key or a relationship

[Hector Blanco]

I'll give it a try!!

Thank you!

2011/2/18 Michael Bayer mike...@zzzcomputing.com:

 On Feb 17, 2011, at 6:37 PM, Hector Blanco wrote:

 Hello everyone!

 Let's say I have a class defined like this:

 class User(declarativeBase):
       Represents a user
       __tablename__ = users

       _id = Column(id, Integer, primary_key=True)
       _phone = Column(phone, String(16))
       _userName = Column(user_name, String(50), unique=True, 
 nullable=False)
       _password = Column(password, String(64), nullable=False)

       _userGroupId = Column(user_group_id, Integer, 
 ForeignKey(user_groups.id))
       _userGroup = relationship(UserGroup, uselist=False)

       def setId(self, id):
               Set id
               self._id = int(id)

       def getId(self):
               Get id
               return self._id

       def setUserGroupById(self, userGroupId):
               userGroupId = int(userGroupId)
               if userGroupId != self.userGroupId:
                       self.userGroup = UserGroupManager.getById(userGroupId)

       def setUserGroup(self, userGroup):
               Set user group
               if isinstance(userGroup, UserGroup):
                       self._userGroup = userGroup
               else:
                       raise TypeError(Trying to set a  + 
 str(type(userGroup)) +  as user group)

       def getUserGroup(self):
               Get user
               return self._userGroup

       #More getters/setters

       id = sqlalchemy.orm.synonym('_id', descriptor=property(getId, setId))
       phone = sqlalchemy.orm.synonym('_phone',
 descriptor=property(getPhone, setPhone))
       userName = sqlalchemy.orm.synonym('_userName',
 descriptor=property(getUserName, setUserName))
       password = sqlalchemy.orm.synonym('_password',
 descriptor=property(getPassword, setPassword))
       userGroupId = sqlalchemy.orm.synonym('_userGroupId',
 descriptor=property(getUserGroup, setUserGroup))
       userGroup = sqlalchemy.orm.synonym('_userGroup',
 descriptor=property(getUserGroup, setUserGroup))

 I have created an utility that, given an instance gives me the names
 of the synonyms in said instance.

 def getProperties(instance):
       properties = list()
       mapper = sqlalchemy.orm.object_mapper(instance)
       for prop in mapper.iterate_properties:
               if isinstance(prop, sqlalchemy.orm.properties.SynonymProperty):
                       properties.append(prop.key)
       return properties

 That would give me [id, phone, userName, password,
 userGroupId, userGroup], so I can more or less generically go
 through all said values and execute things like

 for attribute in getProperties(instanceOfUser):
     value = getattr(instanceOfUser, attribute)

 Is there any way of knowing that said values are ForeignKeys or
 relationships? For instance, I'd like to know that the attribute id
 is a regular (well... kind of regular... it's a Primary key, but it's
 not going to point to anything in another table) numeric attribute,
 but userGroupId is a foreign key and userGroup is a Relationship.
 I've been sneaking in the vars, __dict__, dir of the values returned
 by getattr, but I haven't been able to find anything suitable.


 you just have to poke around and use isinstance() on the MapperProperty 
 objects, and/or check for known attributes.    The choices are 
 ColumnProperty, RelationshipProperty, SynonymProperty, etc.

 synonyms are also superceded in 0.7.  they're not very useful compared to 
 hybrids.




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



-- 
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] Need Urgent Help - SQLAlchemy Relation

[Hector Blanco]

Just a wild guess, but have you tried making your association table like:

#association table
user_group_table = Table('t_user_group', metadata,
   Column('user_id', Integer, ForeignKey('t_user.c.user_id',
       onupdate=CASCADE, ondelete=CASCADE)),
   Column('group_id', Integer, ForeignKey('t_group.c.group_id',
       onupdate=CASCADE, ondelete=CASCADE)),
  Column('project_id', Integer, ForeignKey('t_project.c.project_id',
       onupdate=CASCADE, ondelete=CASCADE))
)

My understanding is that .c. means column so it might need to be
t_user.c.user_id (from table t_user, get the column user_id)

I don't have too much hope with that, but you never know...

2011/2/16 Abdul Gaffar gaffar.infoval...@gmail.com:


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



 -- Mensaje reenviado --
 From: Abdul Gaffar gaffar.infoval...@gmail.com
 To: turboge...@googlegroups.com
 Date: Wed, 16 Feb 2011 16:28:47 +0530
 Subject: Need Urgent Help - SQLAlchemy Relation
 Hi all,

 I need urgent help on SQLAlchemy relations. I have three classes User,
 Group, Project and association user_group_table.



 class User(DeclarativeBase):
    __tablename__ = 't_user'
    user_id = Column(Integer, autoincrement=True, primary_key=True)
    user_name = Column(Unicode(32), unique=True, nullable=False)
    email_address = Column(Unicode(320), unique=True, nullable=False,
                           info={'rum': {'field':'Email'}})

    def __repr__(self):
        return ('User: user_name=%r, email=%r' % (
                self.user_name,
                self.email_address)).encode('utf-8')

    def __unicode__(self):
        return self.user_name



 class Group(DeclarativeBase):
    __tablename__ = 't_group'
    group_id = Column(Integer, autoincrement=True, primary_key=True)
    group_name = Column(Unicode(16), unique=True)

    users=relation('User', secondary=user_group_table,backref='groups')

    def __repr__(self):
        return ('Group: name=%s' % self.group_name).encode('utf-8')

    def __unicode__(self):
        return self.group_name


 class Project(DeclarativeBase):
    __tablename__ = 't_project'

    project_id = Column(Integer, autoincrement=True, primary_key=True)
    project_name = Column(Unicode(80), unique=True, nullable=False)

    project=relation('Group', secondary=auth.user_group_table,
 backref='Project')

    def __repr__(self):
        return Project('%s') % self.project_name


 #association table
 user_group_table = Table('t_user_group', metadata,
    Column('user_id', Integer, ForeignKey('t_user.user_id',
        onupdate=CASCADE, ondelete=CASCADE)),
    Column('group_id', Integer, ForeignKey('t_group.group_id',
        onupdate=CASCADE, ondelete=CASCADE)),
   Column('project_id', Integer, ForeignKey('t_project.project_id',
        onupdate=CASCADE, ondelete=CASCADE))
 )


 I am unable to insert the records into association table
 below is the code snippet for insertion

 user = DBSession.query(User).filter(User.user_name == kw['PM']).one()
 group = DBSession.query(Group).filter(Group.group_name == 'pm').one()
 project = DBSession.query(Project).\            filter(Project.project_id
 == kw['project_id']).one()


                group.users.append(user)
                project.project.append(group)
                DBSession.flush()
                transaction.commit()


 Please help me ASAP.


 Thanx in advance



-- 
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] Find whether a synonym points to a foreign key or a relationship

[Hector Blanco]

Hello everyone!

Let's say I have a class defined like this:

class User(declarativeBase):
Represents a user
__tablename__ = users

_id = Column(id, Integer, primary_key=True)
_phone = Column(phone, String(16))
_userName = Column(user_name, String(50), unique=True, nullable=False)
_password = Column(password, String(64), nullable=False)

_userGroupId = Column(user_group_id, Integer, 
ForeignKey(user_groups.id))
_userGroup = relationship(UserGroup, uselist=False)

def setId(self, id):
Set id
self._id = int(id)

def getId(self):
Get id
return self._id

def setUserGroupById(self, userGroupId):
userGroupId = int(userGroupId)
if userGroupId != self.userGroupId:
self.userGroup = UserGroupManager.getById(userGroupId)

def setUserGroup(self, userGroup):
Set user group
   if isinstance(userGroup, UserGroup):
self._userGroup = userGroup
else:
raise TypeError(Trying to set a  + 
str(type(userGroup)) +  as user group)

def getUserGroup(self):
Get user
return self._userGroup

#More getters/setters

id = sqlalchemy.orm.synonym('_id', descriptor=property(getId, setId))
phone = sqlalchemy.orm.synonym('_phone',
descriptor=property(getPhone, setPhone))
userName = sqlalchemy.orm.synonym('_userName',
descriptor=property(getUserName, setUserName))
password = sqlalchemy.orm.synonym('_password',
descriptor=property(getPassword, setPassword))
userGroupId = sqlalchemy.orm.synonym('_userGroupId',
descriptor=property(getUserGroup, setUserGroup))
userGroup = sqlalchemy.orm.synonym('_userGroup',
descriptor=property(getUserGroup, setUserGroup))

I have created an utility that, given an instance gives me the names
of the synonyms in said instance.

def getProperties(instance):
properties = list()
mapper = sqlalchemy.orm.object_mapper(instance)
for prop in mapper.iterate_properties:
if isinstance(prop, sqlalchemy.orm.properties.SynonymProperty):
properties.append(prop.key)
return properties

That would give me [id, phone, userName, password,
userGroupId, userGroup], so I can more or less generically go
through all said values and execute things like

for attribute in getProperties(instanceOfUser):
 value = getattr(instanceOfUser, attribute)

Is there any way of knowing that said values are ForeignKeys or
relationships? For instance, I'd like to know that the attribute id
is a regular (well... kind of regular... it's a Primary key, but it's
not going to point to anything in another table) numeric attribute,
but userGroupId is a foreign key and userGroup is a Relationship.
I've been sneaking in the vars, __dict__, dir of the values returned
by getattr, but I haven't been able to find anything suitable.

Thank you!

-- 
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] Optimize a search in several classes (each of them with simple 1:N relationships)

[Hector Blanco]

Hello everyone!

I have a class structure like this:

class Store(declarativeBase):
__tablename__ = stores

id = Column(id, Integer, primary_key=True)
name = Column(name, String(50))
registers = relationship(Register, cascade=all, delete,
collection_class=set)

(One store can have N registers, but a register can be only in one store)

class Register(declarativeBase):
__tablename__ = registers 
id = Column(id, Integer, primary_key=True)
name = Column(name, String(50))
cashiers = relationship(Cashier, cascade=all, delete, 
collection_class=set)

(One Register can have many different cashiers assigned, but only one
cashier can be assigned to a register) Probably in real life this
would be more a 1:1 relationship... but let's say there's a team of
cashiers and the can be assigned to a bunch of different registers

And well... finally, the Cashier thingy:

class Cashier(declarativeBase):
__tablename__ = cashiers  
id = Column(id, Integer, primary_key=True)

At a certain point, I need to get the cashiers that are in certains
stores (I receive the stores' ids as a list/set and I need to get all
the Cashier objects that can be assigned to that store).

The only solution my (limited and newbie) mind has been able to come up with is:

1) Get the stores.
2) For each store, get the registers
2) For each register, get the cashiers that can be assigned to them

In order to do that, I have create a method like this (let's say the
ids of the stores come in the storeIds parameter):

returnValue = set()
relationshipsToPreload = [registers, registers.cashiers]  # For
the joinedload thing...
stores = session.query(Store.Store).options( *
[sqlalchemy.orm.joinedload_all(relationshipToPreLoad) for
relationshipToPreLoad in
relationshipsToPreload]).filter(Store.Store.ids.in_(storeId)).all()
session.close()
for store in stores:
for register in store.registers:
for cashier in register.cashiers:
returnValue.add(cashier.id)

I would like to know if you have a better approach to do this. I got
it working though... is mainly out of curiosity. Maybe I can make a
bunch of joins that may improve the performance...

Thank you in advance!

-- 
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] Filtering by a foreign key (now, without typo).

[Hector Blanco]

Sorry... I just sow a typo:


Hello everyone.

I am trying to get classes whose foreign key is whatever but I
always get all the entries in the database, instead of the ones that
match the criterion.

Let's say I have a couple of classes using declarative base in a
relationship N:1. I have that modeled like:

class OtherClass(declarativeBase):
       __tablename__ = other_classes
       _id = Column(id, Integer, primary_key=True)
       id = sqlalchemy.orm.synonym('_id', descriptor=property(getId, setId))


class WhateverClass(declarativeBase):
       __tablename__ = whatever_classes

       _id = Column(id, Integer, primary_key=True)
       _total = Column(total, Integer)
       _otherClassId = Column(other_class_id, Integer,
ForeignKey(other_classes.id))

       _otherClass = relationship(OtherClass, uselist=False)

       id = sqlalchemy.orm.synonym('_id', descriptor=property(getId, setId))
       total = sqlalchemy.orm.synonym('_total',
descriptor=property(getTotal, setTotal))
       otherClassId = sqlalchemy.orm.synonym('_otherClassId',
descriptor=property(getOtherClassId, setOtherClassId))
       otherClass = sqlalchemy.orm.synonym('_otherClass',
descriptor=property(getOtherClass setOtherClass))

If I try to do:
   from myClasses import WhateverClass
   session.query(WhateverClass.WhateverClass).filter(otherClassId = 5).all()

I get a list with all the instances of WhateverClass that are stored
in the database, not only the ones who are linked to the OtherClass
with id = 5

But if I do session.query(WhateverClass.WhateverClass).filter(total =
100).all() I properly get instances of WhateverClass with a total
=100.

Do I have to use a joined query or something like that?

Thank you in advance!

-- 
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] Filtering by a foreign key.

[Hector Blanco]

Hello everyone.

I am trying to get classes whose foreign key is whatever but I
always get all the entries in the database, instead of the ones that
match the criterion.

Let's say I have a couple of classes using declarative base in a
relationship N:1. I have that modeled like:

class OtherClass(declarativeBase):
__tablename__ = other_classes
_id = Column(id, Integer, primary_key=True)
id = sqlalchemy.orm.synonym('_id', descriptor=property(getId, setId))


class WhateverClass(declarativeBase):
__tablename__ = whatever_classes

_id = Column(id, Integer, primary_key=True)
_total = Column(total, Integer)
_otherClassId = Column(other_class_id, Integer,
ForeignKey(other_classes.id))

_otherClass = relationship(Post, uselist=False)

id = sqlalchemy.orm.synonym('_id', descriptor=property(getId, setId))
total = sqlalchemy.orm.synonym('_total',
descriptor=property(getTotal, setTotal))
otherClassId = sqlalchemy.orm.synonym('_otherClassId',
descriptor=property(getOtherClassId, setOtherClassId))
otherClass = sqlalchemy.orm.synonym('_otherClass',
descriptor=property(getOtherClass setOtherClass))

If I try to do:
from myClasses import WhateverClass
session.query(WhateverClass.WhateverClass).filter(otherClassId = 5).all()

I get a list with all the instances of WhateverClass that are stored
in the database, not only the ones who are linked to the OtherClass
with id = 5

But if I do session.query(WhateverClass.WhateverClass).filter(total =
100).all() I properly get instances of WhateverClass with a total
=100.

Do I have to use a joined query or something like that?

Thank you in advance!

-- 
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] Pass query with as parameter (avoid creating a method and hardcoding a query)

[Hector Blanco]

2011/1/16 Tamás Bajusz gbt...@gmail.com:
 Is your work available, or do you plan to put it public somewhere?


Mmm... maybe... contact me privately if you're interested

-- 
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] Inserting in a container class that behaves as list (or set) from the contained class (not from the container)

[Hector Blanco]

Hello list!

I have a couple of classes. One of the behaves as the container of the other:

class ContainerOfSamples(declarativeBase):
__tablename__ = containers

_id = Column(id, Integer, primary_key=True)
_samples = relationship(Samples, cascade=all, delete, 
collection_class=set)

def setSamples(self, samples):
self._samples = samples

def getSamples(self):
return self._samples

def addSample(self, sample):
self._samples.add(sample)

id = sqlalchemy.orm.synonym('_id', descriptor=property(getId, setId))
samples = sqlalchemy.orm.synonym('_samples',
descriptor=property(getSamples, 
setSamples))


class Sample(declarativeBase):
__tablename__ = containers

_id = Column(id, Integer, primary_key=True)
_whatever = Column(whatever, String(20))
_containerId = Column(container_id, Integer, 
ForeignKey(containers.id))
_container = relationship(Container, uselist=False)

def __hash__(self):
return int(self.id)

def setContainer(self, container):
self._container = container

def getContainer(self):
return self._container

id = sqlalchemy.orm.synonym('_id', descriptor=property(getId, setId))
whatever = sqlalchemy.orm.synonym('_whatever',
descriptor=property(getWhatever, 
setWhatever))
container = sqlalchemy.orm.synonym('_container',
descriptor=property(getContainer, 
setContainer))

It's a relationship 1:N (one sample can be in 1 container, 1 container
can have N samples)... basically, a list...

If I have an instance of ContainerOfSamples and I want to add a
sample, I can do:

container = ContainerOfSamples()
sample = Sample()
container.addSample(sample)

And the sample is properly added, the relationships are all
initialized/created/set (however you want to call it) properly... the
containerId in the sample is the id of the  container instance...
perfect.

So now the question is: Is there a way of getting the same effect from
the Sample class? Something like:

sample = Sample()
container = ContainerOfSamples()
sample.container(container)

And then in the container instance the Sample sample would be
added to the container.samples set?

It doesn't seem to work... for some reason, if I try to do that, the
sample._containerId becames the id of the sample...

I don't know if playing with the backref would give me what I
want... I've made a few tries, but it doesn't seem to improve... Maybe
I have a misconception here :-(

Any hints, examples, link to examples... would be helpful and deeply
appreciated.  Thank you in advance!

-- 
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] Inserting in a container class that behaves as list (or set) from the contained class (not from the container)

[Hector Blanco]

Thank you for the quick reply.

 shouldn't this be as simple as sample.container = container

Yeah... I thought so too... And actually, the getter/setters (the
synonym or property) just do that... (and a check for the parameter
type):

class Sample(declarativeBase):
   # yadda, yadda, yadda ...
   def setContainer(self, container):
  if isinstance(container, Container):
 self._container = container
  else:
 raise TypeError(received a %s when expecting a
Container % type(container))

Anyway... if my idea is not wrong, I'll check if the error is
somewhere else. It's good to know that I'm going in the right
direction!

Thank you!

2011/1/21 A.M. age...@themactionfaction.com:

 On Jan 21, 2011, at 12:29 PM, Hector Blanco wrote:

 Hello list!

 I have a couple of classes. One of the behaves as the container of the other:

 class ContainerOfSamples(declarativeBase):
       __tablename__ = containers

       _id = Column(id, Integer, primary_key=True)
       _samples = relationship(Samples, cascade=all, delete, 
 collection_class=set)

       def setSamples(self, samples):
               self._samples = samples

       def getSamples(self):
               return self._samples

       def addSample(self, sample):
               self._samples.add(sample)

       id = sqlalchemy.orm.synonym('_id', descriptor=property(getId, setId))
       samples = sqlalchemy.orm.synonym('_samples',
                                       descriptor=property(getSamples, 
 setSamples))


 class Sample(declarativeBase):
       __tablename__ = containers

       _id = Column(id, Integer, primary_key=True)
       _whatever = Column(whatever, String(20))
       _containerId = Column(container_id, Integer, 
 ForeignKey(containers.id))
       _container = relationship(Container, uselist=False)

       def __hash__(self):
               return int(self.id)

       def setContainer(self, container):
               self._container = container

       def getContainer(self):
               return self._container

       id = sqlalchemy.orm.synonym('_id', descriptor=property(getId, setId))
       whatever = sqlalchemy.orm.synonym('_whatever',
                                       descriptor=property(getWhatever, 
 setWhatever))
       container = sqlalchemy.orm.synonym('_container',
                                       descriptor=property(getContainer, 
 setContainer))


 sample = Sample()
 container = ContainerOfSamples()
 sample.container(container)

 I don't understand the need for the synonyms, but shouldn't this be as simple 
 as sample.container = container? The relationship on sample is already 
 defined... maybe you are confused because you think you need these getters 
 and setters- in the above example, I don't see any need for them.

 Cheers,
 M

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



-- 
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] Re: sqlalchemy rocks my socks off!

[Hector Blanco]

+1

2011/1/16 Jan Müller m...@dfi-net.de:
 +1

 On Jan 15, 9:58 am, Eric Ongerth ericonge...@gmail.com wrote:
 +1

 On Jan 13, 5:08 pm, rdlowrey rdlow...@gmail.com wrote:







  To Michael Bayer: sqlalchemy simplifies my life every day and makes me
  vastly more productive! Many thanks.

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



-- 
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] Pass query with as parameter (avoid creating a method and hardcoding a query)

[Hector Blanco]

Thanks for your help! It was key knowing that I was going in the right
direction.

The problem was that I'm stup... erm... I mean... erm... that I made a
bad mistake (beginner's one)...

I was getting the comparator for the Product class: (getattr(Product,
__eq__)) instead of the comparator for the field/synonym (if I
wanted to check for model == 'foo', I needed to get:
getattr(Product.model, __eq__).

Yey!! It works!

Thank you so much!!

2011/1/16 Michael Bayer mike...@zzzcomputing.com:

 On Jan 15, 2011, at 10:53 PM, Hector Blanco wrote:

 Hello list...

 I would like to allow the users to perform certain queries without me
 (or well... my server) knowing in advance what those queries are going
 to be (without hard-coding the query).

 For instance: I have a “Product” class. One of it's fields is
 manufacturer and another is model

 class Product(declarativeBase):
        def __init__(self):
                self.model = 
                self.manufacturer = 

 I would like the user be able to input an string with a query, such as
 “Product.model != 'foo' or Product.model != 'bar'”
 or:
 Product.model == 'foo'  Product.manufacturer == 'bar'

 I have created a little Python module (queryTree) that tokenizes the
 string and generates a tree for that kind of queries. For the last one
 mentioned above, it would be something like:


                   sqlalchemy.and_
             /                        \
          ==                            ==
   /             \             /               \
 Product.model   foo  Product.manufacturer   bar

 1) The “” string can be converted to (stored as) the sqlalchemy.and_ method
 2) The fields of Product are sqlalchemy.orm.synonym(s). If I pass my
 tree module the class I'm going to perform the query for, it can call
 getattr(cls, model) and get the synonym (I mean: get the
 Product.model synonym itself instead of the “model” string)
 3) Equally, the comparators are get with getattr(Product, __eq__) or
 getattr(Product, __ne__) so I can store in the tree node the
 comparator function instead of the string “==” or “!=”

 But when I try to run the query:
 from mylibs.product import Product
 queryString = Product.model == 'foo'  Product.manufacturer == 'bar'
 session.query(Product.Product).filter(queryTree.getQuery(queryString,
 Product.Product))

 I get an exception:
  File /home/hbr/Documents/my-cms/backlib/product/ProductManager.py,
 line 62, in getByCustomFilter
    retval = 
 Database.session.query(Product.Product).filter(queryTokenizer.getQuery()).all()
  File string, line 1, in lambda
  File 
 /home/hbr/.buildout/eggs/SQLAlchemy-0.6.5-py2.6.egg/sqlalchemy/orm/query.py,
 line 52, in generate
    fn(self, *args[1:], **kw)
  File 
 /home/hbr/.buildout/eggs/SQLAlchemy-0.6.5-py2.6.egg/sqlalchemy/orm/query.py,
 line 942, in filter
    filter() argument must be of type 
 ArgumentError: filter() argument must be of type
 sqlalchemy.sql.ClauseElement or string

 Well everything I can see is correct here, so you just have to ensure 
 getQuery() is returning the root of your tree (which, if it's an and_(), or 
 a x == y, is in fact an instance of ClauseElement).   Don't do anything 
 with eval() or strings, keep it as a tokenized structure on your end.  SQLA's 
 job is to make it into a string.



 With some other tests, I've got some other exceptions that made me
 realize that I could possibly modify somehow the nodes of my tree
 until getting something that is accepted by MySQL as a valid query,
 but that's kind of cheating... I'd like to use pure SqlAlchemy if
 possible (I trust SqlAlchemy more than my programming skills) :-D

 the system you've built to interpret user input into a SQL expression tree 
 should have adequate constraints such that only valid expressions are built 
 in the first place.

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



-- 
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] Get python type from sqlalchemy.orm.synonym

[Hector Blanco]

Hello everyone!

I have created a little module that generates a sqlalchemy query from
an string (yeah, I needed some help:
http://groups.google.com/group/sqlalchemy/browse_thread/thread/6ea3241b1c653444
)

At certain point, I check the type of the field I'm filtering
by creating an instance of the class I'm querying, getting the
contents of said field and checking its type.

I create the instance with cls() (instantiate the class without
passing any parameter to the constructor). That works fine... as long
as I don't need to pass any parameter to the constructor... otherwise,
I would get:
TypeError: __init__() takes exactly [whatever] arguments (1 given).

The field I want to check is a synonym in the class level. I'd like
to know if there's a way to get the Python type (int, list...) from
that synonym (without needed to create an instance of the class)

Let me explain with an example.

Let's say I have a class Product:

 Product.py --
class Product(declarativeBase):
__tablename__ = products

_id = Column(id, Integer, primary_key=True)
_model = Column(model, String(30))
_number = Column(category, Integer)

def __init__(self):
self.model = 
self.number = 0

def setId(self, id):
self._id = int(id)

def getId(self):
return self._id

def setModel(self, model):
self._model = model

def getModel(self):
return self._model

# [...] more code, more getters, setters [...]

id = sqlalchemy.orm.synonym('_id', descriptor=property(getId, setId))
model = sqlalchemy.orm.synonym('_model',
descriptor=property(getModel, setModel))
number = sqlalchemy.orm.synonym('_number',
descriptor=property(getNumber, 
setNumber))

-

The user can input a query as:
Product.model=='foo'  Product.number=='5'
(number may be an string -quoted, I mean- here)

The idea is that then I can pass that string and the class I want to
get results for to my QueryTokenizer class:
queryTokenizer = QueryTokenizer(queryString, classToQuery)
# class to query is the class object: Product.Product, which is what
# if I do prod = Product.Product() would store in prod an instance of
# the Product class

So a call to queryTokenizer.getQuery() would return, for the query
string detailed above:

sqlalchemy.and_( Product.Product.model.__eq__(foo),
   Product.Product.number__eq__(5))

with number being properly casted to an int()

So I can put that in a method (getByCustomFilter, to call it somehow) and do:

from mylibs.product import Product
# ...
queryTokenizer = QueryTokenizer(queryString, Product.Product)
retval = 
Database.session.query(Product.Product).filter(queryTokenizer.getQuery()).all()


The problem is that, in order to perform that cast, I need to find out
the type of the field I'm filtering for (I need to know that in the
instances of Product, Product.number is an int).
To achieve that, I do the following:
I instantiate the class, get the number field of the *instance*,
check its type (will be int) and cast accordingly (cast '5' to int).
Something like:

--- QueryTokenizer---
# [ . . .]
def clean(self, classToQuery):
instance_of_class = classToQuery() # In the example, this the same as 
doing:
#  instance_of_class 
=Product.Product()
type_in_instances = type(getattr(instance_of_class, number))#Gives 
type 'int'
castedValue = type_in_instances(value_to_check) #From the string 5

 # gives the int 5
# [ . . .]

---

But of course, that only works if the constructor of classToQuery
doesn't require arguments. Otherwise the call classToQuery() gives a
type error.

I would like to know if I can get that int from the class itself
(not from an instance). In the class, if I do (getattr(classToQuery,
number)) I get a sqlalchemy.orm.synonym. I would like to know if
from that I can somehow get type 'int' (what I get when in python I
do type(5), type(0)... )

This is not only done so the user can input number == '5' (I could
force the user to input number==5) but also as a layer of security to
make sure the query is correct and that no weird/insecure stuff is
going on.

Thank you!

-- 
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] Get python type from sqlalchemy.orm.synonym

[Hector Blanco]

Thanks for replying so quickly...

 if the user says number == '5' , why not consider that to be a string ?  
 why is casting needed ?  if they want an int, they should type an int, no ?


I don't trust my users :-) I don't think they know what they want,
most of the times :-D


 The problem is that, in order to perform that cast, I need to find out
 the type of the field I'm filtering for (I need to know that in the
 instances of Product, Product.number is an int).

 assert isinstance(Product.number.__clause_element__().type, Integer)



I could use that, yeah... That gives the Sql type the column is using
to be stored in the database, right? (VARCHAR(20), INTEGER...) The
only trouble I may foresee is that some of my classes use custom
types (type decorators,
http://www.sqlalchemy.org/docs/core/types.html#sqlalchemy.types.TypeDecorator
) and some fields are stored as a comma separated string in the
database but in the python instances are lists. I haven't tested it
with that kind of custom types, but if the __clause_element__().type
says TEXT then that may cause some troubles, right?

But still, I think I can use it as a backup remedy if the
instantiation of the class fails.

Thank you!

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



-- 
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] Pass query with as parameter (avoid creating a method and hardcoding a query)

[Hector Blanco]

Hello list...

I would like to allow the users to perform certain queries without me
(or well... my server) knowing in advance what those queries are going
to be (without hard-coding the query).

For instance: I have a “Product” class. One of it's fields is
manufacturer and another is model

class Product(declarativeBase):
       def __init__(self):
               self.model = 
               self.manufacturer = 

I would like the user be able to input an string with a query, such as
“Product.model != 'foo' or Product.model != 'bar'”
or:
Product.model == 'foo'  Product.manufacturer == 'bar'

I have created a little Python module (queryTree) that tokenizes the
string and generates a tree for that kind of queries. For the last one
mentioned above, it would be something like:


                  sqlalchemy.and_
            /                        \
         ==                            ==
  /             \             /               \
Product.model   foo  Product.manufacturer   bar

1) The “” string can be converted to (stored as) the sqlalchemy.and_ method
2) The fields of Product are sqlalchemy.orm.synonym(s). If I pass my
tree module the class I'm going to perform the query for, it can call
getattr(cls, model) and get the synonym (I mean: get the
Product.model synonym itself instead of the “model” string)
3) Equally, the comparators are get with getattr(Product, __eq__) or
getattr(Product, __ne__) so I can store in the tree node the
comparator function instead of the string “==” or “!=”

But when I try to run the query:
from mylibs.product import Product
queryString = Product.model == 'foo'  Product.manufacturer == 'bar'
session.query(Product.Product).filter(queryTree.getQuery(queryString,
Product.Product))

I get an exception:
  File /home/hbr/Documents/my-cms/backlib/product/ProductManager.py,
line 62, in getByCustomFilter
retval = 
Database.session.query(Product.Product).filter(queryTokenizer.getQuery()).all()
  File string, line 1, in lambda
  File 
/home/hbr/.buildout/eggs/SQLAlchemy-0.6.5-py2.6.egg/sqlalchemy/orm/query.py,
line 52, in generate
fn(self, *args[1:], **kw)
  File 
/home/hbr/.buildout/eggs/SQLAlchemy-0.6.5-py2.6.egg/sqlalchemy/orm/query.py,
line 942, in filter
filter() argument must be of type 
ArgumentError: filter() argument must be of type
sqlalchemy.sql.ClauseElement or string


I could easily modify my query generator module to get the query string...:
“and_(Product.model == 'foo', Product.manufacturer == 'bar')” so a
call to eval(“and_(Product.model == 'foo', Product.manufacturer ==
'bar')”) would probably work, but I'd like to avoid the use of eval
because of the security issues it usually implies.

With some other tests, I've got some other exceptions that made me
realize that I could possibly modify somehow the nodes of my tree
until getting something that is accepted by MySQL as a valid query,
but that's kind of cheating... I'd like to use pure SqlAlchemy if
possible (I trust SqlAlchemy more than my programming skills) :-D

Thank you in advance. Every hint will be deeply appreciated.

-- 
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] Pass relationships to joinedload(ing) in a parameter

[Hector Blanco]

Hi list!

I am facing a little problem whose I'm sure has a very simple
solution, but I haven't been able to find it (the solution, I mean)...

I would like to be able to pass the fields (relationships) I want to
pre-load as a parameter. Let's say I have a couple of classes:

-
class ElementsGroup(declarativeBase):
Represents a group of elements
__tablename__ = elements_groups

_id = Column(id, Integer, primary_key=True)

elements = relationship(Element ... , collection_class=set )
elementGroups = relationship(ElementGroup,  ... , 
collection_class=set)
-

And then the Element class:

-
class Element(declarativeBase):
__tablename__ = elements

_id = Column(id, Integer, primary_key=True)
otherThings = relationship(Things,  ... , collection_class=set)
-

I would like to create a method to load objects of ElementsGroups
that would accept a parameter containing which relationships have to
be pre-loaded with a joinedload. Something like:

-
class ElementsGroupManager(object):
@staticmethod
def getAll(relationshipsToPreLoad=list()):
retval = list
try:
if (relationshipsToPreLoad):
retval = 
Database.session.query(ElementsGroup.ElementsGroup).options(joinedload_all(relationshipsToPreLoad)).all()
else:
retval = 
Database.session.query(ElementsGroup.ElementsGroup).all()   
   
finally:
Database.session.close()
return retval
-

And in relationshipsToPreload I can say, for instance:
[elements, elements.otherThings]
(and would preload ElementsGroup.elements and the otherThings field
of each element object in the ElementsGroup.elements
list/relationship)
or
[elementGroups]
which would just pre-load ElementsGroups.elementGroups (and not the
ElementsGroups.elements)

I'm sure it's very easy, but I haven't been able to do it... If I try
to pass relationshipsToPreLoad=['elements', 'elementGroups'] I get
something like:

 Mapper 'Mapper|Element|elements' has no property 'elementGroups'

It looks like it's trying to load (correctly) ElementsGroups.elements
but then it's trying to load ElementsGroups.elements.elementGroups
(which is not what I want... I want to load
ElementsGroups.elementGroups)

Thanks in advance!

-- 
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] Pass relationships to joinedload(ing) in a parameter

[Hector Blanco]

Works like a charm!

Thank you! (once again)

2011/1/3 Michael Bayer mike...@zzzcomputing.com:
 I'd keep each path separate, i.e.

 query.options(*[joinedload_all(path) for path in relationshipsToPreLoad])


 On Jan 3, 2011, at 10:55 AM, Hector Blanco wrote:

 Hi list!

 I am facing a little problem whose I'm sure has a very simple
 solution, but I haven't been able to find it (the solution, I mean)...

 I would like to be able to pass the fields (relationships) I want to
 pre-load as a parameter. Let's say I have a couple of classes:

 -
 class ElementsGroup(declarativeBase):
       Represents a group of elements
       __tablename__ = elements_groups

       _id = Column(id, Integer, primary_key=True)

       elements = relationship(Element ... , collection_class=set )
       elementGroups = relationship(ElementGroup,  ... , 
 collection_class=set)
 -

 And then the Element class:

 -
 class Element(declarativeBase):
       __tablename__ = elements

       _id = Column(id, Integer, primary_key=True)
       otherThings = relationship(Things,  ... , collection_class=set)
 -

 I would like to create a method to load objects of ElementsGroups
 that would accept a parameter containing which relationships have to
 be pre-loaded with a joinedload. Something like:

 -
 class ElementsGroupManager(object):
       @staticmethod
       def getAll(relationshipsToPreLoad=list()):
               retval = list
               try:
                       if (relationshipsToPreLoad):
                               retval = 
 Database.session.query(ElementsGroup.ElementsGroup).options(joinedload_all(relationshipsToPreLoad)).all()
                       else:
                               retval = 
 Database.session.query(ElementsGroup.ElementsGroup).all()
               finally:
                       Database.session.close()
               return retval
 -

 And in relationshipsToPreload I can say, for instance:
 [elements, elements.otherThings]
 (and would preload ElementsGroup.elements and the otherThings field
 of each element object in the ElementsGroup.elements
 list/relationship)
 or
 [elementGroups]
 which would just pre-load ElementsGroups.elementGroups (and not the
 ElementsGroups.elements)

 I'm sure it's very easy, but I haven't been able to do it... If I try
 to pass relationshipsToPreLoad=['elements', 'elementGroups'] I get
 something like:

 Mapper 'Mapper|Element|elements' has no property 'elementGroups'

 It looks like it's trying to load (correctly) ElementsGroups.elements
 but then it's trying to load ElementsGroups.elements.elementGroups
 (which is not what I want... I want to load
 ElementsGroups.elementGroups)

 Thanks in advance!

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


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



-- 
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] Database in inconsistent state in user data (login in a web server) retrieval

[Hector Blanco]

Hello everyone!

I am currently working with a webserver (Grok) that starts different
threads (automatically) for the requests that arrive to it.

The information is serialized in a MySQL database (which is acceded
through SqlAlchemy). The users' information is stored in that MySQL
database. The plugin that said server uses to check if a user is
logged in or not is called very often. The way it is programmed now
(I'll improve that in the future) is: it goes to the database, tries
to extract the user whose username matches with the logged (or the
one trying to log) one and checks if the password stored in the
database matches with the one provided by the user.

For some reason, if a user cancels the login process (hits Esc in his
browser) and tries to login again, something (I don't know what)
crashes:

2010-12-23 13:45:50,841 WARNING [sqlalchemy.pool.QueuePool.0x...5450]
Error closing cursor: (2014, Commands out of sync; you can't run this
command now)

I'm not sure if is is because the cancelling/reloading starts a new
thread (and messes up the transactions that were pending) or because
when the user cancels the loading, the username provided gets
corrupted (empty or something) or what.

This is what I do (as I said, very often) to load the user:

@staticmethod
def getByName(userName):
retval = None
try:
retval = 
Database.session.query(User.User).filter(User.User.userName
== userName).scalar()
finally:
Database.session.commit()
return retval

The session is a global object created in the Database.py file this way:

Session = scoped_session(sessionmaker(bind=...))
session = Session()

I've tried restarting the server (to get a new session) flushing,
expunging, commiting...  Even dropping and re-creating the schema.
Nothing seems to work. I don't know if it's because I'm trying to
access the database too often, or because if userName is None, it
leaves the session in a weird state... (as you can see, I don't
catch any exceptions... yet)

Does anyone have any idea of what can be going wrong? I know it's a
long shot, and that I'm not providing much information, but who
knows... maybe any of you has a hint or a why don't you try this?...
idea. Anything would be appreciated.

Thank you.

-- 
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] Database in inconsistent state in user data (login in a web server) retrieval

[Hector Blanco]

Thank you for your quick reply!

I tried to change the method that grabs the user to:
def getByName(userName):
retval = None
try:
retval = Database.session.query(User.User).filter(
User.User.userName== userName
).scalar()
finally:
Database.session.close()
return retval

but it still doesn't seem to work.

Maybe it's a problem with threading... I setup the database (create
the tables, create and insert in the database a sample test user...)
using Google Chrome and I try to log in with Firefox. The database is
setup in one of the pages the server provides: I write
http://127.0.0.1/test/initdb as the address of the web page to load in
Chrome and when the page is rendered, the database is setup. After
that, I try to log in with the test user using Firefox and that's
when I get the Error closing cursor: (2014, Commands out of sync;
you can't run this command now) I am using MySql administrator and
the values for that test user seem to be properly created properly.

Maybe when I create the database with Chrome Firefox doesn't see it properly?

To add a user, I have created a method update like this:

@staticmethod
def update(user):
if isinstance(user, User.User):
try:
if user.id:
#User already exists in the database
user=Database.session.merge(user)
else:
#User is new
user=Database.session.add(user)
Database.session.commit()
finally:
Database.session.close()
else:
raise TypeError(Received parameter %s of type %s when expecting
%s % (user, type(user), User.User))

When (from Chrome) I invoke the page that creates the database, a new
User() instance is created, with a few default values (userName =
test, for instance) and it's added to the database using this update
method (said user doesn't have an id, so it should be added using
add(user) ). Then I try to login with the user test from Firefox and
it breaks...

2010/12/23 Michael Bayer mike...@zzzcomputing.com:

 On Dec 23, 2010, at 2:09 PM, Hector Blanco wrote:

 Hello everyone!

 I am currently working with a webserver (Grok) that starts different
 threads (automatically) for the requests that arrive to it.

 The information is serialized in a MySQL database (which is acceded
 through SqlAlchemy). The users' information is stored in that MySQL
 database. The plugin that said server uses to check if a user is
 logged in or not is called very often. The way it is programmed now
 (I'll improve that in the future) is: it goes to the database, tries
 to extract the user whose username matches with the logged (or the
 one trying to log) one and checks if the password stored in the
 database matches with the one provided by the user.

 For some reason, if a user cancels the login process (hits Esc in his
 browser) and tries to login again, something (I don't know what)
 crashes:

 2010-12-23 13:45:50,841 WARNING [sqlalchemy.pool.QueuePool.0x...5450]
 Error closing cursor: (2014, Commands out of sync; you can't run this
 command now)

 If connection resources are returned to the pool via garbage collection, this 
 may happen in a distinct, deferred gc thread, producing errors like this.    
 It is common for web app servers to throw some kind of interruption exception 
 when the connection is unexpectedly closed.   The error is caught and logged 
 as a warning only and should be otherwise harmless.  If you could apply a 
 finally: block around connection interruption errors and cleanly close the 
 session, that would probably alleviate the warnings.


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



-- 
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] Database in inconsistent state in user data (login in a web server) retrieval

[Hector Blanco]

Ok! I'll let you know...

Thank you so much!

It seems to fail less with the finally, though :)

2010/12/23 Michael Bayer mike...@zzzcomputing.com:
 it only has to do with what your appserver does when a connection is broken.  
    The finally method is probably never called.    You can change the 
 Python warnings filter to emit those warnings as exceptions, in which you 
 should be able to get stack traces in your logs.


 On Dec 23, 2010, at 3:02 PM, Hector Blanco wrote:

 Thank you for your quick reply!

 I tried to change the method that grabs the user to:
 def getByName(userName):
       retval = None
       try:
               retval = Database.session.query(User.User).filter(
                               User.User.userName== userName
                       ).scalar()
       finally:
               Database.session.close()
       return retval

 but it still doesn't seem to work.

 Maybe it's a problem with threading... I setup the database (create
 the tables, create and insert in the database a sample test user...)
 using Google Chrome and I try to log in with Firefox. The database is
 setup in one of the pages the server provides: I write
 http://127.0.0.1/test/initdb as the address of the web page to load in
 Chrome and when the page is rendered, the database is setup. After
 that, I try to log in with the test user using Firefox and that's
 when I get the Error closing cursor: (2014, Commands out of sync;
 you can't run this command now) I am using MySql administrator and
 the values for that test user seem to be properly created properly.

 Maybe when I create the database with Chrome Firefox doesn't see it 
 properly?

 To add a user, I have created a method update like this:

 @staticmethod
 def update(user):
       if isinstance(user, User.User):
               try:
                       if user.id:
                               #User already exists in the database
                               user=Database.session.merge(user)
                       else:
                               #User is new
                               user=Database.session.add(user)
                       Database.session.commit()
               finally:
                       Database.session.close()
       else:
               raise TypeError(Received parameter %s of type %s when 
 expecting
                                       %s % (user, type(user), User.User))

 When (from Chrome) I invoke the page that creates the database, a new
 User() instance is created, with a few default values (userName =
 test, for instance) and it's added to the database using this update
 method (said user doesn't have an id, so it should be added using
 add(user) ). Then I try to login with the user test from Firefox and
 it breaks...

 2010/12/23 Michael Bayer mike...@zzzcomputing.com:

 On Dec 23, 2010, at 2:09 PM, Hector Blanco wrote:

 Hello everyone!

 I am currently working with a webserver (Grok) that starts different
 threads (automatically) for the requests that arrive to it.

 The information is serialized in a MySQL database (which is acceded
 through SqlAlchemy). The users' information is stored in that MySQL
 database. The plugin that said server uses to check if a user is
 logged in or not is called very often. The way it is programmed now
 (I'll improve that in the future) is: it goes to the database, tries
 to extract the user whose username matches with the logged (or the
 one trying to log) one and checks if the password stored in the
 database matches with the one provided by the user.

 For some reason, if a user cancels the login process (hits Esc in his
 browser) and tries to login again, something (I don't know what)
 crashes:

 2010-12-23 13:45:50,841 WARNING [sqlalchemy.pool.QueuePool.0x...5450]
 Error closing cursor: (2014, Commands out of sync; you can't run this
 command now)

 If connection resources are returned to the pool via garbage collection, 
 this may happen in a distinct, deferred gc thread, producing errors like 
 this.    It is common for web app servers to throw some kind of 
 interruption exception when the connection is unexpectedly closed.   The 
 error is caught and logged as a warning only and should be otherwise 
 harmless.  If you could apply a finally: block around connection 
 interruption errors and cleanly close the session, that would probably 
 alleviate the warnings.


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



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

Re: [sqlalchemy] Database in inconsistent state in user data (login in a web server) retrieval

[Hector Blanco]

With that (catching all the errors) seems to work better.

It also seems that the problem improves if I wait a bit (4 or 5
seconds) after the server is started...



2010/12/23 Hector Blanco white.li...@gmail.com:
 Ok! I'll let you know...

 Thank you so much!

 It seems to fail less with the finally, though :)

 2010/12/23 Michael Bayer mike...@zzzcomputing.com:
 it only has to do with what your appserver does when a connection is broken. 
     The finally method is probably never called.    You can change the 
 Python warnings filter to emit those warnings as exceptions, in which you 
 should be able to get stack traces in your logs.


 On Dec 23, 2010, at 3:02 PM, Hector Blanco wrote:

 Thank you for your quick reply!

 I tried to change the method that grabs the user to:
 def getByName(userName):
       retval = None
       try:
               retval = Database.session.query(User.User).filter(
                               User.User.userName== userName
                       ).scalar()
       finally:
               Database.session.close()
       return retval

 but it still doesn't seem to work.

 Maybe it's a problem with threading... I setup the database (create
 the tables, create and insert in the database a sample test user...)
 using Google Chrome and I try to log in with Firefox. The database is
 setup in one of the pages the server provides: I write
 http://127.0.0.1/test/initdb as the address of the web page to load in
 Chrome and when the page is rendered, the database is setup. After
 that, I try to log in with the test user using Firefox and that's
 when I get the Error closing cursor: (2014, Commands out of sync;
 you can't run this command now) I am using MySql administrator and
 the values for that test user seem to be properly created properly.

 Maybe when I create the database with Chrome Firefox doesn't see it 
 properly?

 To add a user, I have created a method update like this:

 @staticmethod
 def update(user):
       if isinstance(user, User.User):
               try:
                       if user.id:
                               #User already exists in the database
                               user=Database.session.merge(user)
                       else:
                               #User is new
                               user=Database.session.add(user)
                       Database.session.commit()
               finally:
                       Database.session.close()
       else:
               raise TypeError(Received parameter %s of type %s when 
 expecting
                                       %s % (user, type(user), User.User))

 When (from Chrome) I invoke the page that creates the database, a new
 User() instance is created, with a few default values (userName =
 test, for instance) and it's added to the database using this update
 method (said user doesn't have an id, so it should be added using
 add(user) ). Then I try to login with the user test from Firefox and
 it breaks...

 2010/12/23 Michael Bayer mike...@zzzcomputing.com:

 On Dec 23, 2010, at 2:09 PM, Hector Blanco wrote:

 Hello everyone!

 I am currently working with a webserver (Grok) that starts different
 threads (automatically) for the requests that arrive to it.

 The information is serialized in a MySQL database (which is acceded
 through SqlAlchemy). The users' information is stored in that MySQL
 database. The plugin that said server uses to check if a user is
 logged in or not is called very often. The way it is programmed now
 (I'll improve that in the future) is: it goes to the database, tries
 to extract the user whose username matches with the logged (or the
 one trying to log) one and checks if the password stored in the
 database matches with the one provided by the user.

 For some reason, if a user cancels the login process (hits Esc in his
 browser) and tries to login again, something (I don't know what)
 crashes:

 2010-12-23 13:45:50,841 WARNING [sqlalchemy.pool.QueuePool.0x...5450]
 Error closing cursor: (2014, Commands out of sync; you can't run this
 command now)

 If connection resources are returned to the pool via garbage collection, 
 this may happen in a distinct, deferred gc thread, producing errors like 
 this.    It is common for web app servers to throw some kind of 
 interruption exception when the connection is unexpectedly closed.   The 
 error is caught and logged as a warning only and should be otherwise 
 harmless.  If you could apply a finally: block around connection 
 interruption errors and cleanly close the session, that would probably 
 alleviate the warnings.


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



 --
 You received this message because you

Re: [sqlalchemy] Get properties of a class mapped with SqlAlchemy (and its sqlalchemy.orm.synonym)

[Hector Blanco]

First of all, thank you for replying.

I don't really know if I understood your idea.

I dug a bit more in the User class (not the instance, but what it
would be self.__class__) and the problem is that both password and
_password have a __get__:


I changed the getProperties method a bit, to introspect the __class__ thing:

def getProperties(cls):
properties = list()

for varName in vars(cls):
log.debug(Studying prop '%s' of type: %s %(varName,
type(getattr(cls, varName
if varName == password or varName == _password:
valTmp = getattr(cls, varName)
print( \t Has %s a __get()__? %s % (varName, 
getattr(valTmp, __get__)))
print( \t Contents of %s % varName)
for key, val in valTmp.__dict__.iteritems():
print( \t\t %s: %s % (key, val))

return None
#return properties

- And it outputs this (showing only the password thing:) -

Studying prop '_password' of type: class
'sqlalchemy.orm.attributes.InstrumentedAttribute'
 Has _password a __get()__? bound method
InstrumentedAttribute.__get__ of

sqlalchemy.orm.attributes.InstrumentedAttribute object at 0xa26e46c
 Contents of _password
 parententity: Mapper|User|users
 __doc__: None
 impl: sqlalchemy.orm.attributes.ScalarAttributeImpl object at 
0xa3d758c
 key: _password
 comparator: sqlalchemy.orm.properties.Comparator object at 
0xa26e44c
 Studying prop 'password' of type: class
'sqlalchemy.orm.attributes.propertyProxy'
 Has password a __get()__? bound method propertyProxy.__get__ of
sqlalchemy.orm.attributes.propertyProxy object 
at 0xa26eacc
 Contents of password
 _comparator: function comparator at 0xa269bc4
 key: password
 descriptor: property object at 0xa25ef7c
 _parententity: Mapper|User|users
 user_prop: property object at 0xa25ef7c
 __doc__: Get password
 impl: sqlalchemy.orm.attributes._ProxyImpl object at 
0xa26eaec

--

I have also tried to check isinstance(getattr(cls, varName),
sqlalchemy.orm.attributes.InstrumentedAttribute) (even though it may
not be the best option, but...) and the problem is that both
password and _password happen to be InstrumentedAttributes
(propertyProxy extends from InstrumentedAttribute).

I've seen in the attributes.py file an is_instrumented method...
Maybe I could get the vars of an instance (not the class, no... an
instance) which would give me:
([_sa_instance_state, _id, _userName, _password]),
then check if these variables are instrumented (_sa_instance_state
isn't) and then check if the class has the attributes [id,
userName and password] but in order to do that I need to remove
the first character of the attribute name (to get  userName from
_userName) and that seems it's going to mess up with the
performance...

Thank you!

2010/12/20 Michael Bayer mike...@zzzcomputing.com:

 On Dec 20, 2010, at 7:30 PM, Hector Blanco wrote:

 Hello all!

 I have an application running under Python2.6 and the classes are set
 up with properties (in a Python2.4 style, though).
[ . . . ]
 So here's the question:
 Is there any way to get the properties of a class mapped with SqlAlchemy?

 I'd look at the object to see if it has a __get__()  method, since that's 
 what defines a descriptor in Python, not just isinstance(x, property).   
 duck typing


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



-- 
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] Get properties of a class mapped with SqlAlchemy (and its sqlalchemy.orm.synonym)

[Hector Blanco]

I found a maybe way... but I don't know if it's good idea... the
propertyProxy instances have a field called descriptor which the
InstrumentedAttribute don't have... so I can always do this:

--
def getProperties4(cls):
properties = list()

for varKey in vars(cls):
varVal = getattr(cls, varKey)
try:
if descriptor in vars(varVal):
if isinstance(getattr(varVal, descriptor), 
property):
properties.append(varKey)
except TypeError:
pass
print(Properties found: '%s' % properties)
return properties
--
That works...
Properties found: '['id', password', 'userName']'

...but I don't really know what I'm exactly touching here... (and how
dangerous... or correct it may be)

2010/12/21 Hector Blanco white.li...@gmail.com:
 First of all, thank you for replying.

 I don't really know if I understood your idea.

 I dug a bit more in the User class (not the instance, but what it
 would be self.__class__) and the problem is that both password and
 _password have a __get__:


 I changed the getProperties method a bit, to introspect the __class__ thing:

 def getProperties(cls):
        properties = list()

        for varName in vars(cls):
                log.debug(Studying prop '%s' of type: %s %(varName,
                                type(getattr(cls, varName
                if varName == password or varName == _password:
                        valTmp = getattr(cls, varName)
                        print( \t Has %s a __get()__? %s % (varName, 
 getattr(valTmp, __get__)))
                        print( \t Contents of %s % varName)
                        for key, val in valTmp.__dict__.iteritems():
                                print( \t\t %s: %s % (key, val))

        return None
        #return properties

 - And it outputs this (showing only the password thing:) -

 Studying prop '_password' of type: class
 'sqlalchemy.orm.attributes.InstrumentedAttribute'
         Has _password a __get()__? bound method
                                InstrumentedAttribute.__get__ of
                                
 sqlalchemy.orm.attributes.InstrumentedAttribute object at 0xa26e46c
         Contents of _password
                 parententity: Mapper|User|users
                 __doc__: None
                 impl: sqlalchemy.orm.attributes.ScalarAttributeImpl object 
 at 0xa3d758c
                 key: _password
                 comparator: sqlalchemy.orm.properties.Comparator object at 
 0xa26e44c
  Studying prop 'password' of type: class
 'sqlalchemy.orm.attributes.propertyProxy'
         Has password a __get()__? bound method propertyProxy.__get__ of
                                sqlalchemy.orm.attributes.propertyProxy 
 object at 0xa26eacc
         Contents of password
                 _comparator: function comparator at 0xa269bc4
                 key: password
                 descriptor: property object at 0xa25ef7c
                 _parententity: Mapper|User|users
                 user_prop: property object at 0xa25ef7c
                 __doc__: Get password
                 impl: sqlalchemy.orm.attributes._ProxyImpl object at 
 0xa26eaec

 --

 I have also tried to check isinstance(getattr(cls, varName),
 sqlalchemy.orm.attributes.InstrumentedAttribute) (even though it may
 not be the best option, but...) and the problem is that both
 password and _password happen to be InstrumentedAttributes
 (propertyProxy extends from InstrumentedAttribute).

 I've seen in the attributes.py file an is_instrumented method...
 Maybe I could get the vars of an instance (not the class, no... an
 instance) which would give me:
 ([_sa_instance_state, _id, _userName, _password]),
 then check if these variables are instrumented (_sa_instance_state
 isn't) and then check if the class has the attributes [id,
 userName and password] but in order to do that I need to remove
 the first character of the attribute name (to get  userName from
 _userName) and that seems it's going to mess up with the
 performance...

 Thank you!

 2010/12/20 Michael Bayer mike...@zzzcomputing.com:

 On Dec 20, 2010, at 7:30 PM, Hector Blanco wrote:

 Hello all!

 I have an application running under Python2.6 and the classes are set
 up with properties (in a Python2.4 style, though).
                [ . . . ]
 So here's the question:
 Is there any way to get the properties of a class mapped with SqlAlchemy?

 I'd look at the object to see if it has a __get__()  method, since that's 
 what defines a descriptor in Python, not just isinstance(x, property).   
 duck typing


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

Re: [sqlalchemy] Get properties of a class mapped with SqlAlchemy (and its sqlalchemy.orm.synonym)

[Hector Blanco]

Ah... much better :)

def getProperties2(instance):
properties = list()
mapper = sqlalchemy.orm.object_mapper(instance)
for prop in mapper.iterate_properties:
if isinstance(prop, sqlalchemy.orm.properties.SynonymProperty):
properties.append(prop.key)
print(::getProperties2  Returning: %s % properties)
return properties

Gives me (as wanted):

::getProperties2  Returning: ['id', 'userName', 'password']

Thank you!

2010/12/21 Michael Bayer mike...@zzzcomputing.com:
 have you tried mapper.iterate_properties ?


 On Dec 21, 2010, at 11:14 AM, Hector Blanco wrote:

 I found a maybe way... but I don't know if it's good idea... the
 propertyProxy instances have a field called descriptor which the
 InstrumentedAttribute don't have... so I can always do this:

 --
 def getProperties4(cls):
       properties = list()

       for varKey in vars(cls):
               varVal = getattr(cls, varKey)
               try:
                       if descriptor in vars(varVal):
                               if isinstance(getattr(varVal, descriptor), 
 property):
                                       properties.append(varKey)
               except TypeError:
                       pass
       print(Properties found: '%s' % properties)
       return properties
 --
 That works...
       Properties found: '['id', password', 'userName']'

 ...but I don't really know what I'm exactly touching here... (and how
 dangerous... or correct it may be)

 2010/12/21 Hector Blanco white.li...@gmail.com:
 First of all, thank you for replying.

 I don't really know if I understood your idea.

 I dug a bit more in the User class (not the instance, but what it
 would be self.__class__) and the problem is that both password and
 _password have a __get__:


 I changed the getProperties method a bit, to introspect the __class__ thing:

 def getProperties(cls):
        properties = list()

        for varName in vars(cls):
                log.debug(Studying prop '%s' of type: %s %(varName,
                                type(getattr(cls, varName
                if varName == password or varName == _password:
                        valTmp = getattr(cls, varName)
                        print( \t Has %s a __get()__? %s % (varName, 
 getattr(valTmp, __get__)))
                        print( \t Contents of %s % varName)
                        for key, val in valTmp.__dict__.iteritems():
                                print( \t\t %s: %s % (key, val))

        return None
        #return properties

 - And it outputs this (showing only the password thing:) -

 Studying prop '_password' of type: class
 'sqlalchemy.orm.attributes.InstrumentedAttribute'
         Has _password a __get()__? bound method
                                InstrumentedAttribute.__get__ of
                                
 sqlalchemy.orm.attributes.InstrumentedAttribute object at 0xa26e46c
         Contents of _password
                 parententity: Mapper|User|users
                 __doc__: None
                 impl: sqlalchemy.orm.attributes.ScalarAttributeImpl object 
 at 0xa3d758c
                 key: _password
                 comparator: sqlalchemy.orm.properties.Comparator object at 
 0xa26e44c
  Studying prop 'password' of type: class
 'sqlalchemy.orm.attributes.propertyProxy'
         Has password a __get()__? bound method propertyProxy.__get__ of
                                sqlalchemy.orm.attributes.propertyProxy 
 object at 0xa26eacc
         Contents of password
                 _comparator: function comparator at 0xa269bc4
                 key: password
                 descriptor: property object at 0xa25ef7c
                 _parententity: Mapper|User|users
                 user_prop: property object at 0xa25ef7c
                 __doc__: Get password
                 impl: sqlalchemy.orm.attributes._ProxyImpl object at 
 0xa26eaec

 --

 I have also tried to check isinstance(getattr(cls, varName),
 sqlalchemy.orm.attributes.InstrumentedAttribute) (even though it may
 not be the best option, but...) and the problem is that both
 password and _password happen to be InstrumentedAttributes
 (propertyProxy extends from InstrumentedAttribute).

 I've seen in the attributes.py file an is_instrumented method...
 Maybe I could get the vars of an instance (not the class, no... an
 instance) which would give me:
 ([_sa_instance_state, _id, _userName, _password]),
 then check if these variables are instrumented (_sa_instance_state
 isn't) and then check if the class has the attributes [id,
 userName and password] but in order to do that I need to remove
 the first character of the attribute name (to get  userName from
 _userName) and that seems it's going to mess up with the
 performance...

 Thank you!

 2010/12/20 Michael Bayer mike...@zzzcomputing.com

[sqlalchemy] Get properties of a class mapped with SqlAlchemy (and its sqlalchemy.orm.synonym)

[Hector Blanco]

Hello all!

I have an application running under Python2.6 and the classes are set
up with properties (in a Python2.4 style, though).

Everything seems to be working fine with SqlAlchemy (version 0.6.5,
just in case) as it explains here:
http://www.sqlalchemy.org/docs/orm/extensions/declarative.html#defining-synonyms

The problem is that sometimes I want to get the properties of a class
without knowing in advance the name or number of said properties.

Before introducing SqlAlchemy, I had a little function that extracted
them from the class (the __class__ attribute in an instance):

def iter_properties_of_class(cls):
retval = list()
for varname in vars(cls):
value = getattr(cls, varname)
if isinstance(value, property):
list.append(varname)
return retval

Now they're not instance of property anymore.

I dug out a little and I found that what before were “properties” now
are  type: class 'sqlalchemy.orm.attributes.propertyProxy' So I
thought... oh, ok... then I just have to check if they're instance of
that propertyProxy class... And so I changed my “auxiliary” method to:
import sqlalchemy
[ . . . ]
if isinstance(getattr(cls, varname), 
sqlalchemy.orm.attributes.propertyProxy):
retval.append(varName)
but I get this error:
'module' object has no attribute 'propertyProxy'

I also tried with...
if isinstance(getattr(cls, varname),
sqlalchemy.orm.attributes.propertyProxy.propertyProxy)
… getting the same error,

or to import propertyProxy directly...
from sqlalchemy.orm.attributes import propertyProxy
… getting:
ImportError: cannot import name propertyProxy


So here's the question:
Is there any way to get the properties of a class mapped with SqlAlchemy?

Just in case, all my classes (at least for the moment) are implemented
using the Declarative method. An small example could be my User class:

 User.py -

class User(Base):
Represents a user
__tablename__ = users

_id = Column(id, Integer, primary_key=True)
_userName = Column(user_name, String(50))
_password = Column(password, String(64))

def __init__(self):
Initialize object
self._id = -1
self._userName = 
self._password = 

def setId(self, id):
Set id
self._id = int(id)

def getId(self):
Get id
return self._id
def setUserName(self, userName):
Set userName
self._userName = userName

def getUserName(self):
Get userName
return self._userName

def setPassword(self, password):
Set password
m = hashlib.sha256()
m.update(password)
self._password = m.hexdigest()

def getPassword(self):
Get password
return self._password

id = synonym('_id', descriptor=property(getId, setId))
userName = synonym('_userName', descriptor=property(getUserName, 
setUserName))
password = synonym('_password', descriptor=property(getPassword, 
setPassword))
-

I'd like to know the best way to get a list() containing: [id,
userName, password]

Thank you in advance!

-- 
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] Re: Modeling a Tree-looking structure in SqlAlchemy.

[Hector Blanco]

Thank you all... As soon as I have the webserver where I'm going to
use that structure up and running, I'll try it and i'll let you
know...

2010/12/13 Laurent Rahuel laurent.rah...@gmail.com:
 Hello,

 You should also take a look at http://pypi.python.org/pypi/sqlamp/0.5.2, an 
 implementation of Materialized Path for SQLAlchemy.

 Regards,

 Laurent

 Le 13 déc. 2010 à 23:30, Russell Warren a écrit :

 Sorry, I just saw I messed up the nested sets SQLA example link.  Here
 is the right one:
 http://www.sqlalchemy.org/trac/browser/examples/nested_sets/nested_sets.py

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


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



-- 
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] Modeling a Tree-looking structure in SqlAlchemy.

[Hector Blanco]

hello everyone!

I was wondering which is the best way to model a tree-looking
structure with SqlAlchemy.

Right now, I have this two classes:


#!/usr/bin/python2.6
class MyNode(object):
def __init__(self):
self.items = list()

def addItem(self, item):
if isinstance(item, MyNode) or isinstance(item, MyLeaf):
self.items.append(item)

def __str__(self):
retval = str()
for item in self.items:
if isinstance(item, MyNode):
retval = retval + \n Node:  + str(item) +  
\n
else:
retval = \nLeaf:  + str(item) + retval
return retval

class MyLeaf(object):
def __init__(self):
self.content = I'm a leaf
def __str__(self):
return self.content


As you can see, a node can contain (in its .items list) other nodes,
other leafs and a mix of nodes and leafs.

When I model that using SqlAlchemy, both MyNode and MyLeaf will be
stored their own tables and will have a unique id field in the
database.

I was wondering if the best thing to do is using two relationships in
the MyNode class or trying to do it with one (closer to how it is
now).

If I use two relationships, the first one (which I could call
.otherNodes, for instance) could give me the other instances of
MyNode and the second one (.leaves, for instance) would give
instances of MyLeaf.

And well... I really don't know if it's doable putting both of them
together in a unique relationship.

I know I'm going to need intermediate tables to model this structure,
but I don't know if it's doable (or good idea) to make only one table
that may look like:

nodes_towards_content = Table(
nodes_towards_content,
metadata,
Column(node_id, Integer, ForeignKey(nodes.id)),
Column(other_node_id, Integer, ForeignKey(nodes.id)),
Column(other_leaf_id, Integer, ForeignKey(leaves.id))
)

So if I insert a node, the column nodes_to_content.c.other_node_id
would take the id of that inserted node while
nodes_to_content.c.other_leaf_id would be null, and if I insert a
leave, the process would be the opposite.

If I use a unique table, I am also a bit concerned of how to retrieve
the objects (what to tell the query, I mean). For the moment I have
always had relationships that returned one type of objects (or objects
that inherited from others), but this would be getting two totally
different objects... So if I want to retrieve all the items of a
MyNode instance, I don't know what to put in the relationship:

class MyNode:
[ ... ]
def getAllNodes():
items = session.query(?).all() #What to put in   ? 
object, maybe?

Thank you in advance!

-- 
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] Inheriting custom collection to create another custom collection. Issues with the appenders/parents

[Hector Blanco]

Thanks for your interest! And yeah... sorry for the typos... I was
writing the code right in the email...

I'm afraid I won't be able to test it until December 1 but make sure
as soon as I get my hands on the computer where that problem was
happening, I'll test it.

I really appreciate your effort! :) I'll keep you posted!

2010/11/13 jason kirtland j...@discorporate.us:
 Hi Hector,

 On Fri, Nov 12, 2010 at 7:46 AM, Hector Blanco white.li...@gmail.com wrote:
 Hello everyone.

 I was wondering if it's possible to inherit a custom collection to
 create another custom collection.

 A few days ago I was trying to use my own class as a custom_collection
 (http://groups.google.com/group/sqlalchemy/msg/ba1c64c3d227f586).
 Thanks to Michael Bayer I was able to do it, but now I would like to
 go one step further, and inherit my custom collection to create
 another custom collection.

 To simplify a little what I asked in the other message, let's say I have a:

 def ClassA(declarativeBase):
        __tablename__ = aes
        id = Column(id, Integer, primary_key=True)
        _whatever = Column(type, String(64))
        def __init__(self):
                self._whatever = whatever

 Then I have my custom collection for instances of ClassA:

 def ContainerOfA(dict):
        __emulates__ = set
        def __init__(self):
                self._field = I'm a great... awesom! container

        #I also defined the appender, remover and iterator
       �...@collection.iterator
        def __iter__(self):
                return self.itervalues()

       �...@collection.appender
        def append(self, item):
                self[item.getUniqueHash()] = item

       �...@collection.remover
        def remove(self, item):
                if item.getUniqueHash() in self.keys():
                        del self[item.getUniqueHash()]

 And then I was happily able to use it in any relationships:

 def YetAnotherClass(declarativeBase):
        id = Column(id, Integer, primary_key=True)
        classesA = relationship(ClassA,
                uselist=True,
                secondary=intermediate_table,
                collection_class=lambda: ContainerOfA(),
                cascade=all, delete, delete-orphan,
                single_parent=True
        )

 Now I needed to extend ClassA in a Class B and ContainerOfA in
 ContainerOfB. I added the polymorphic stuff to ClassA and ClassB
 to create a joined table inheritance, as detailed in
 http://www.sqlalchemy.org/docs/orm/inheritance.html#joined-table-inheritance
 . (it seems to be working fine, that's why I am not completely
 detailing it here)

 def ClassB(ClassA):
        __tablename__ = bs #Sorry for that
        __mapper_args__ = {'polymorphic_identity': 'ClassB'}
        id = Column(id, Integer, ForeignKey('aes.id'), primary_key=True)
        def __init__(self):
                self._anotherWhatever = another whatever

 def ContainerOfB(ContainerOfA):
        def __init__(self):
                super(ContainerOfB, self).__init__()
        def anotherMethodOnlyForBInstances(self):
                # do interesting stuff for B classes

 Then I tried to use it in a relationship:

 def YetYetAnotherClass(declarativeBase):
        id = Column(id, Integer, primary_key=True)
        classesB = relationship(ClassB,
                uselist=True,
                secondary=another_intermediate_table,
                collection_class=lambda: ContainerOfB(),
                cascade=all, delete, delete-orphan,
                single_parent=True
        )

 But when I tried to append a ClassB instance through the
 relationship detailed above, I got this exception:

 Type ContainerOfB must elect an appender method to be a collection class

 I haven't been able to replicate this behavior.  When testing your
 code I did notice that you are using 'def' to declare your classes,
 which won't actually create the type.  I make that same typo myself
 periodically and it can be quite tricky to track down the one def'd
 class that's causing seemingly unrelated errors.

 Anyhow, I've attached the working test case I put together.  If you
 can modify this to replicate your behavior, we can track down any bugs
 that might be present in the collection API's appender metadata
 bookkeeping.  You definitely should not have to re-declare an
 @appender on a subclass- the collection mechanics should be sweeping
 over your inherited class and transparently picking up the methods.
 This is definitely working for the cases in the SQLA unit tests, but
 it's definitely possible you've found some corner case with that dict
 that's declared to be emulating a set.

 Cheers,
 Jason

 I thought... ok, ok... let's just explicitly add the 'appender' to
 the ContainerOfB class...  The only thing I need to do is calling the
 appender of the super class, anyway... no biggie and so I did:

 def ContainerOfB(ContainerOfA):
        # [ . . . ] #
       �...@collection.appender
        def append(self, classBInstance

[sqlalchemy] Inheriting custom collection to create another custom collection. Issues with the appenders/parents

[Hector Blanco]

Hello everyone.

I was wondering if it's possible to inherit a custom collection to
create another custom collection.

A few days ago I was trying to use my own class as a custom_collection
(http://groups.google.com/group/sqlalchemy/msg/ba1c64c3d227f586).
Thanks to Michael Bayer I was able to do it, but now I would like to
go one step further, and inherit my custom collection to create
another custom collection.

To simplify a little what I asked in the other message, let's say I have a:

def ClassA(declarativeBase):
__tablename__ = aes
id = Column(id, Integer, primary_key=True)
_whatever = Column(type, String(64))
def __init__(self):
self._whatever = whatever

Then I have my custom collection for instances of ClassA:

def ContainerOfA(dict):
__emulates__ = set
def __init__(self):
self._field = I'm a great... awesom! container

#I also defined the appender, remover and iterator
@collection.iterator
def __iter__(self):
return self.itervalues()

@collection.appender
def append(self, item):
self[item.getUniqueHash()] = item

@collection.remover
def remove(self, item):
if item.getUniqueHash() in self.keys():
del self[item.getUniqueHash()]

And then I was happily able to use it in any relationships:

def YetAnotherClass(declarativeBase):
id = Column(id, Integer, primary_key=True)
classesA = relationship(ClassA,
uselist=True,
secondary=intermediate_table,
collection_class=lambda: ContainerOfA(),
cascade=all, delete, delete-orphan,
single_parent=True
)

Now I needed to extend ClassA in a Class B and ContainerOfA in
ContainerOfB. I added the polymorphic stuff to ClassA and ClassB
to create a joined table inheritance, as detailed in
http://www.sqlalchemy.org/docs/orm/inheritance.html#joined-table-inheritance
. (it seems to be working fine, that's why I am not completely
detailing it here)

def ClassB(ClassA):
__tablename__ = bs #Sorry for that
__mapper_args__ = {'polymorphic_identity': 'ClassB'}
id = Column(id, Integer, ForeignKey('aes.id'), primary_key=True)
def __init__(self):
self._anotherWhatever = another whatever

def ContainerOfB(ContainerOfA):
def __init__(self):
super(ContainerOfB, self).__init__()
def anotherMethodOnlyForBInstances(self):
# do interesting stuff for B classes

Then I tried to use it in a relationship:

def YetYetAnotherClass(declarativeBase):
id = Column(id, Integer, primary_key=True)
classesB = relationship(ClassB,
uselist=True,
secondary=another_intermediate_table,
collection_class=lambda: ContainerOfB(),
cascade=all, delete, delete-orphan,
single_parent=True
)

But when I tried to append a ClassB instance through the
relationship detailed above, I got this exception:

 Type ContainerOfB must elect an appender method to be a collection class

I thought... ok, ok... let's just explicitly add the 'appender' to
the ContainerOfB class...  The only thing I need to do is calling the
appender of the super class, anyway... no biggie and so I did:

def ContainerOfB(ContainerOfA):
# [ . . . ] #
@collection.appender
def append(self, classBInstance):
return super(ContainerOfB, self).append(classBInstance)

But then... another exception when I tried to add an instance of ClassB():

 InvalidRequestError: Instance ClassB at 0xba9726c is already associated 
 with an instance of class 'mylibraries.classesTest.YetYetAnotherClass' via 
 its YetYetAnotherClass.classesB attribute, and is only allowed a single 
 parent.

Well... I need the cascade to properly delete the items
(http://www.sqlalchemy.org/docs/orm/session.html#deleting) and in
order to use that, I need the single_parent = True.

Then funny thing is that if I totally rewrite the appender method in
ContainerOfB:

def ContainerOfB(ContainerOfA):
# [ . . . ] #
@collection.appender
def append(self, classBInstance):
# write here the exact same code than ContainerOfA changing
# the reference to the item parameter by classBInstance
# (that's the only difference)

then everything is working fine. I have made some more tests, and the
inheritance ClassA - ClassB seems to be working fine. In said tests I
removed the cascade and the single_parent parameters of the
classesB relationship. By doing that, I was able to insert instances
of ClassB in the classesB container and all the information was
properly stored in the database (the polymorphic identity was added
properly, the foreign key of the ClassB() instance was 

Re: [sqlalchemy] Re: Two relationships with a same backref name. Is that (even) possible or I got everything wrong?

[Hector Blanco]

Yeah... I'm pretty newbie myself with sqlalchemy, so when I discovered
that I could specify primary joins, secondary... and all that juicy
stuff in the backref I got so into writing it from A -- to -- B
that I forgot that it can be done B --from-- A

:) Thanks again!

2010/11/12 Eric Ongerth ericonge...@gmail.com:
 You're welcome, and I hope that works for you.  I went through the
 same process a few years ago when picking up SqlAlchemy... the backref
 facility is so cool that it's easy to forget that it's optional and
 that most relationship backrefs /could/ be handled as just another
 relationship on the opposite mapper.

 On Nov 12, 7:31 am, Hector Blanco white.li...@gmail.com wrote:
 2010/11/12 Eric Ongerth ericonge...@gmail.com:

  Hi Hector,

  If I'm not mistaken, everywhere you wrote
  (MyObject.id==MyObject.containerId),
  you meant to write: (Container.id==MyObject.containerId).

 Ups... yeah... great eye.

  Instead of the backref technique, why not just create the MyObject--
 Container relationship a single time in your MyObject class.  That
  should be able to coexist with your first code example (with no
  backrefs).

 Oh, right!! That's a great approach... I was so blinded with the
 backref thing that I didn't think it could be the other way around!

 I'll do that!

 Thank you Eric!

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



-- 
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] Two relationships with a same backref name. Is that (even) possible or I got everything wrong?

[Hector Blanco]

I have a class that has two relationships to the same type of objects.
One of the relationships will store objects of type VR and the other
objects with a type CC. One object can only be in one of the lists
(relationships) at the same time:

This is the container class and its two relationships:

class Container(rdb.Model):
rdb.metadata(metadata)
rdb.tablename(containers)

id = Column(id, Integer, primary_key=True)

relation1 = relationship(MyObject,
uselist=True,
primaryjoin=lambda: and_((MyObject.id == MyObject.containerId),
(MyObject._type == VR)),
cascade=all, delete, delete-orphan
)

relation2 = relationship(MyObject,
uselist=True,
primaryjoin=lambda: and_((MyObject.id == MyObject.containerId),
(MyObject._type == CC)),
cascade=all, delete, delete-orphan
)

I don't think there's need to mention, but, MyObject.containerId is
a ForeignKey pointing to the Container.id.

I'd like to know if there's a way to create a backref so I will be
able to access the container through the MyObject class. The idea
would be having something like:

relation1 = relationship(MyObject,
uselist=True,
primaryjoin=lambda: and_((MyObject.id == MyObject.containerId),
(MyObject._type == VR)),
cascade=all, delete, delete-orphan,
backref=backref('container', order_by=id)
)

relation2 = relationship(MyObject,
uselist=True,
primaryjoin=lambda: and_((MyObject.id == MyObject.containerId),
(MyObject._type == CC)),
cascade=all, delete, delete-orphan,
backref=backref('container', order_by=id)
)

But of course, that fails because it's trying to add two .container
fields to the MyObject class.

I have also seen that you can define joins in the backref, but I
haven't been able to find examples about how to define it. And I am
still not very sure that that would allow me to have to backrefs with
the same name/identifier.

I just need to know if it's even possible having two backrefs with the
same name. Actually, a you really got the whole concept wrong may
help too (if that's the case) . If it's doable, does any of you know
where can I find examples of advanced backref usage? With primary
joins, secondary joins and all that juicy stuff...

Thank you in advance!!

-- 
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] (Newbie) Using a custom collection extending from a dict(). Is that doable?

[Hector Blanco]

Shoot!! It works!! :D :D

-- Parent.py (extract) --
# . . .
child1 = relationship(
Child,
uselist=True,
primaryjoin=lambda: and_((Parent.id == Child.parent_id), 
(Child.type
== VR)),
collection_class=lambda: ZepConnector(VR)
)

child2 = relationship(
Child,
uselist=True,
primaryjoin=lambda: and_((Parent.id == Child.parent_id), 
(Child.type
== CC)),
collection_class=lambda: ZepConnector(CC)
)

---
Do you know any documentation where I can understand why?  Conor
already mentioned
(http://groups.google.com/group/sqlalchemy/msg/d8fbbbff6d961332) the
importance of using lambdas, strings... to avoid mistakes,

-- Quote: -
In SQLAlchemy you get around circular dependencies by:

* Using strings as the target of ForeignKey()
* Using class name strings as the target of a relation (declarative
  only)
* Using strings or callables as primaryjoin/secondaryjoin arguments
  in a relationship()
-

but I'd like to understand a little bit more how does it work (what's
going on internally) so I won't make similar errors in the future.

Thank you so much...

2010/11/9 Michael Bayer mike...@zzzcomputing.com:

 On Nov 8, 2010, at 6:36 PM, Hector Blanco wrote:

 methods that I have implemented and that need to be there. That would
 be the ZepConnector (and, for purposes of the example, it's method
 foo() it's the one I need to use). As you can see in the following
 lines, I randomly test its availability in the addChild1() method of
 the Parent.

       child1 = relationship(
               Child,
               uselist=True,
               primaryjoin=lambda: and_((Parent.id == Child.parent_id), 
 (Child.type
 == VR)),
               collection_class=ZepConnector(VR)
               )

 So this is incorrect - collection_class takes a class or other callable as an 
 argument that will produce an instance of your collection.  The ZepConnector 
 source you have below indicates that ZepConnector(VR) is an instance of the 
 collection.   You need to use a lambda: there.   The other errors you're 
 getting would appear to extend from that (and is also why __init__ is called 
 on ZepConnector - you're calling it yourself).


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



-- 
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] (Newbie) Using a custom collection extending from a dict(). Is that doable?

[Hector Blanco]

Hello everyone...

I'm trying to use a custom collection to connect (or relate) two
classes but I haven't been able to do it. Maybe I got the whole
concept of the custom collections wrong, but let me explain what I am
doing (and see if someone can give me a hint, or something)

I have a Parent class (which some of you will remember from other
questions) with a couple of children. One of the children fields
stores children whose type is VR and the other children with a CC
type.

I don't really need persistence for the collection used to store the
children, but I need it to be of an special class so it will have some
methods that I have implemented and that need to be there. That would
be the ZepConnector (and, for purposes of the example, it's method
foo() it's the one I need to use). As you can see in the following
lines, I randomly test its availability in the addChild1() method of
the Parent.

- Parent.py -

from megrok import rdb
from sqlalchemy import Column
from sqlalchemy import and_
from sqlalchemy.orm import relationship
from sqlalchemy.types import Integer
from sqlalchemy.types import String
from mylibraries.database.tests.Child import Child
from mylibraries.database.tests.Tables import testMetadata
from mylibraries.database.tests.ZepConnector import ZepConnector

class Parent(rdb.Model):
rdb.metadata(testMetadata)
rdb.tablename(parents_table)
rdb.tableargs(schema='test2', useexisting=False)

id = Column(id, Integer, primary_key=True, nullable=False, 
unique=True)
_whateverField1 = Column(whatever_field1, String(16)) #Irrelevant
_whateverField2 = Column(whatever_field2, String(16)) #Irrelevant

child1 = relationship(
Child,
uselist=True,
primaryjoin=lambda: and_((Parent.id == Child.parent_id), 
(Child.type
== VR)),
collection_class=ZepConnector(VR)
)

child2 = relationship(
Child,
uselist=True,
primaryjoin=lambda: and_((Parent.id == Child.parent_id), 
(Child.type
== CC)),
collection_class=ZepConnector(CC)
)

def __init__(self):
print Parent __init__
self._whateverField1 = Whatever1
self._whateverField2 = Whatever2
self.child1 = ZepConnector(VR)
self.child2 = ZepConnector(CC)

def addChild1(self, child):
if isinstance(child, Child):
print(::addChild1  Testing .foo method:  + 
str(self.child1.foo()))   
# The line above doesn't really makes much but testing 
the
accessibility of the .foo() method.
# As I explain later, it doesn't work
self.child1.append(child)

def addChild2(self, child):
if isinstance(child, Child):
self.child2.append(child)



Please note that I'm using megrok. For those who are not familiar with
it, allow me to explain that it is just a tool that writes the mappers
itself and makes it a little bit programmer friendly.

I guess The mapping of the Parent() class in regular SqlAlchemy would
be something like:

mapper(Parent, parents_table, properties={
id = Column(id, Integer, primary_key=True, nullable=False, 
unique=True)
_whateverField1 = Column(whatever_field1, String(16)) #Irrelevant
_whateverField2 = Column(whatever_field2, String(16)) #Irrelevant
child1 = relationship( # etc, etc, etc
})
#

but I'm 100%... erm... 90% certain that using that tool is not what
lead me to ask what I'm going to ask here (I mean: I don't think is
interfering with the Collections thing)


A child is a very simple class:

--- Child.py --

import random

from megrok import rdb
from sqlalchemy import Column
from sqlalchemy import ForeignKey
from sqlalchemy.types import Integer
from sqlalchemy.types import String
from mylibraries.database.tests.Tables import testMetadata

class Child(rdb.Model):
rdb.metadata(testMetadata)
rdb.tablename(children_table)
rdb.tableargs(schema='test2', useexisting=False)

parent_id = Column(parent_id, Integer,
ForeignKey(test2.parents_table.id), primary_key=True)
type = Column(type, String(2), nullable=True, primary_key=True)
hasher = Column(hasher, String(5))

def __init__(self):
self.type = None
self.hasher = self.generateHasher()

def setType(self, typeParameter):
if typeParameter in set([VR, CC]):
self.type = typeParameter

@staticmethod
def generateHasher():
retval = str()
for i in 

Re: [sqlalchemy] Simple relationship 1:1 fails with Foreign key assocated with column ------- could not find table

[Hector Blanco]

Hi Conor and rest of the group:

I think I got it!

Investigating the metadata instance (printing the contents of
metadata.__dict__), I realized that in it appear references to the
tables although not as table_name but schema_name.table_name.
Let's say my schema (in MySQL terminology) is called test:
The following line:
  id = Column(id, Integer, ForeignKey(parent_table.id), primary_key = True)
fails, but this:
  id = Column(id, Integer, ForeignKey(test.parent_table.id),
primary_key = True)
works fine.

If I do:
   for fieldKey, fieldVal in metadata.__dict__.iteritems():
   print(::engine_created  field metadata. + str(fieldKey) +
==  + str(fieldVal))

I get (among other things) this:
::engine_created  field metadata.tables == {'test.children_table':
Table('children_table', MetaData(None), Column('id', Integer(),
ForeignKey('test.parents_table.id'), table=children_table,
primary_key=True, nullable=False), Column('type', String(length=2,
convert_unicode=False, assert_unicode=None, unicode_error=None,
_warn_on_bytestring=False), table=children_table), schema='test'),
'test.parents_table': Table('parents_table', MetaData(None),
Column('id', Integer(), table=parents_table, primary_key=True,
nullable=False), Column('whatever_field', String(length=16,
convert_unicode=False, assert_unicode=None, unicode_error=None,
_warn_on_bytestring=False), table=parents_table), schema='test')}

As you can see, the parent table appears as test.parents_table, not
parents_table.

I don't think this was SqlAlchemy fault, but more the megrok.rdb
thing that I'm using, but well... Maybe this will help someone else.

2010/10/29 Conor conor.edward.da...@gmail.com:
 On 10/29/2010 05:31 PM, Hector Blanco wrote:

 Hello, group!

 I am still dealing with the relationship I asked before
 (http://groups.google.com/group/sqlalchemy/browse_thread/thread/c1d46daf35116999).

 To tell the truth, I'm not even sure if this is a question I should
 ask in the SqlAlchemy forum because I'm also dealing with Megrok.rdb
 (http://pypi.python.org/pypi/megrok.rdb) and I don't know if that's
 what is causing the problem, but I'm pretty lost...  As I explained in
 my other question, I think the rdb.Model thing that appears in the
 classes is just a tool to create the mapper class -- table in a
 slightly more transparent way for the programmer. That's why I thought
 I may get some help here.

 In this message, I have simplified the code (compared to my former
 question) to make it clearer, but well... The fact is that now I'm
 getting a problem with a simple 1:1 relationship (if I can fix it, I
 will be able to move to the more complicated stuff as I detailed in
 the former question)

 I am getting this error:
 Foreign key assocated with column 'children_table.id' could not find
 table 'parents_table' with which to generate a foreign key to target
 column 'id'

 I have a file, called Tables.py where all the classes and auxiliary
 (or intermediate) tables that I'm going to use in my application are
 defined:

 Tables.py 

 class Parent(rdb.Model):
   rdb.metadata(metadata)
   rdb.tablename(parents_table)

   id = Column(id, Integer, primary_key=True)
   _whateverField= Column(whatever_field, String(16)) #Irrelevant

   child1 = relationship(Child, uselist=False)

 class Child(rdb.Model):
   rdb.metadata(metadata)
   rdb.tablename(children_table)
   id = Column(id, Integer, ForeignKey(Parent.id), primary_key = True)
   type = Column(type, String(2)) #Irrelevant (for this example)

 The target of a ForeignKey should be a string, e.g.:

 ForeignKey(parents_table.id)

  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
 And then I have two different Python .py files (Parent.py and
 Child.py) where the methods that manage said classes are implemented.
 In those files, the static area of each class is copied from Tables.py
 with some changes in the quotes (where I can use the object itself, I
 use it):

 Parent.py 

 from child import Child
 metadata = rdb.MetaData()

 class Parent(rdb.Model):
   rdb.metadata(metadata)
   rdb.tablename(parents_table)

   id = Column(id, Integer, primary_key=True)
   _whateverField= Column(whatever_field, String(16)) #Irrelevant

   child1 = relationship(Child, uselist=False) #No quotation marks on this
 Child
  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
 And

 Child.py  
 metadata = rdb.MetaData()

 class Child(rdb.Model):
   rdb.metadata(metadata)
   rdb.tablename(children_table)
   id = Column(id, Integer, ForeignKey(parent_table.id), primary_key =
 True)
   type = Column(type, String(2)) #Irrelevant (for this example)

 These class definitions should be merged with those in Tables.py. You should
 only have one class Parent statement and one class Child statement. You
 may be confusing this with the non-declarative class setup, where you define
 the table first, class 2nd, and mapper 3rd. It looks

Re: [sqlalchemy] Relationship between a class with two different instances of other class (Newbie)

[Hector Blanco]

Hi Conor and rest of the list...

Well... I'm afraid I need to keep it separated. In the real model each
child is a list that is accessed in different ways (and do different
things) depending on which child it is.

I was wondering what it would be better (more correct) from a
relational point of view:

Keeping the ids of the children in the parent (as foreign keys) or
having the id of the parent in the children. I guess from a relational
point of view it's more correct having the id of the parent in the
children, right?

So I think it's better if I go with Conor's last solution:

child1 = relationship(
Child,
primaryjoin=lambda: and_(Child.parent_id == Parent.id, Child.type == 1),
uselist=False)
child2 = relationship(
Child,
primaryjoin=lambda: and_(Child.parent_id == Parent.id, Child.type == 2),
uselist=False)

I guess the relationship in this case is a 1:N (1 parent has 2
children) and it sounds right to keep the id of the parent in the
children, and not viceversa.

I also asked the same question in stackoverflow
(http://stackoverflow.com/questions/4055332/relational-database-design-two-relations-11-or-one-12),
and someone said it's more correct this last approach (of keeping the
parent_id in the children and distinguish the type of the children
through a Child.type field).

2010/10/29 Conor conor.edward.da...@gmail.com:
 On 10/29/2010 11:51 AM, Hector Blanco wrote:

 Thanks Conor!

 The callable works like a charm! It's great news! (I've been trying to
 figure out this for 3 days... yeah... I guess I'm not that smart)

 Now that I have it working, a “design” question pops up. Nothing
 technical, really.

 As Connor mentioned in his reply:
 “Usually we consider the table with the foreign key as the child
 table, but that's just being picky”

 That's very true, and now I don't know how to design it...

 It would be easier to design it if we had more concrete names instead of
 Parent and Child. What is the actual use case? Is this a tree hierarchy?
 Does each parent have exactly two children?

 I can do it the way I asked or...

 class Child(rdb.Model):
   rdb.metadata(metadata)
   rdb.tablename(children_table)
   id = Column(id, Integer, primary_key=True)
   parent_id = Column(id, Integer, ForeignKey(“parent_table.id”))  # New!
   type = Column(type, ShortInteger)# New!

   field1 = Column(“field1”, String(64))  #Irrelevant
   def __init__(self):
   self.field1 = “hello world”

 class Parent(rdb.Model):
   rdb.metadata(metadata)
   rdb.tablename(parent_table”)

   id = Column(id, Integer, primary_key=True)

   child1 = relationship(
   # Well... this I still don't know how to write it down,
   # but it would be something like:
   #  Give me all the children whose “parent_id” is my “id”
   #  AND type == 1
   # I'll deal with the joins and that depending on your answer, 
 guys
   )

   child2 = relationship(
   #  Would be same as above
   #  AND type == 2
   )

 This may be good for adding new children to the parent class... If I
 add a “Parent.child3”, I just need to create a new relationship very
 similar to the already existing ones.

 The way I asked in my former question would imply creating a new
 relationship AND adding a new foreign key to the parent.

 I'd like to know what people that know much more about databases think :)

 I'm confused as to why you would want separate child1, child2, etc.
 relationships instead of a single children relationship. Is Child.type
 really something you want for distinguishing children, or is it something
 you added to try and make the relationships work?

 Assuming you really do want to keep separate child1 and child2
 relationships, and they are both one-to-one relationships, they would look
 like this:

 # omit uselist=False if this is a one-to-many relationship
 child1 = relationship(
 Child,
 primaryjoin=lambda: and_(Child.parent_id == Parent.id, Child.type == 1),
 uselist=False)
 child2 = relationship(
 Child,
 primaryjoin=lambda: and_(Child.parent_id == Parent.id, Child.type == 2),
 uselist=False)

 -Conor

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


-- 
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] Simple relationship 1:1 fails with Foreign key assocated with column ------- could not find table

[Hector Blanco]

Oh, and regarding the other part of your answer:

 These class definitions should be merged with those in Tables.py. You should
 only have one class Parent statement and one class Child statement. You
 may be confusing this with the non-declarative class setup, where you define
 the table first, class 2nd, and mapper 3rd. It looks like rdb uses the
 declarative approach, where the table and mapper are defined as part of the
 class in one step.

You were right again. I was creating a new instance of rdb.metadata()
in every class, and that's wrong (let me rephrase it... I THINK its
wrong). If my understanding is correct, that metadata thing is what
keeps track of the mapping (what classes are mapped to what tables,
etcetera). It actually needs to be global.

-- 
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] [Solved? ] Creating a simple 1:2 relationship with MeGrok and SqlAlchemy

[Hector Blanco]

Hi List...

I have been asking a lot lately about a 1:2 relationship with MeGrok
and SqlAlchemy, and I think I've solved it.

I have created a mini how to just in case it could help anyone.

ODT  http://www.hectorblanco.org/files/odt/Megrok%20Relation%201:2.odt
PDF  http://www.hectorblanco.org/files/pdf/Megrok%20Relation%201:2.pdf

If you wanna take a look, and criticize, correct... whatever, I'll be thankful.

I'd like to thank to everyone who helped me. And thank you to everyone
who tried, even by reading my lng emails.

-- 
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] Simple relationship 1:1 fails with Foreign key assocated with column ------- could not find table

[Hector Blanco]

Thank you again, Connor. I'll give it a try on Monday and I'll let you
know the results.

I kind of suspected that having the classes defined in two different
places was making the whole thing go nuts, but that's the way I found
it (and I was trying to keep it consistent with what was there) but I
don't think that under any concept re-writing code is a good idea so
I'll try to change it.

Thank you for the very well explained and detailed reply.

2010/10/29 Conor conor.edward.da...@gmail.com:
 On 10/29/2010 05:31 PM, Hector Blanco wrote:

 Hello, group!

 I am still dealing with the relationship I asked before
 (http://groups.google.com/group/sqlalchemy/browse_thread/thread/c1d46daf35116999).

 To tell the truth, I'm not even sure if this is a question I should
 ask in the SqlAlchemy forum because I'm also dealing with Megrok.rdb
 (http://pypi.python.org/pypi/megrok.rdb) and I don't know if that's
 what is causing the problem, but I'm pretty lost...  As I explained in
 my other question, I think the rdb.Model thing that appears in the
 classes is just a tool to create the mapper class -- table in a
 slightly more transparent way for the programmer. That's why I thought
 I may get some help here.

 In this message, I have simplified the code (compared to my former
 question) to make it clearer, but well... The fact is that now I'm
 getting a problem with a simple 1:1 relationship (if I can fix it, I
 will be able to move to the more complicated stuff as I detailed in
 the former question)

 I am getting this error:
 Foreign key assocated with column 'children_table.id' could not find
 table 'parents_table' with which to generate a foreign key to target
 column 'id'

 I have a file, called Tables.py where all the classes and auxiliary
 (or intermediate) tables that I'm going to use in my application are
 defined:

 Tables.py 

 class Parent(rdb.Model):
   rdb.metadata(metadata)
   rdb.tablename(parents_table)

   id = Column(id, Integer, primary_key=True)
   _whateverField= Column(whatever_field, String(16)) #Irrelevant

   child1 = relationship(Child, uselist=False)

 class Child(rdb.Model):
   rdb.metadata(metadata)
   rdb.tablename(children_table)
   id = Column(id, Integer, ForeignKey(Parent.id), primary_key = True)
   type = Column(type, String(2)) #Irrelevant (for this example)

 The target of a ForeignKey should be a string, e.g.:

 ForeignKey(parents_table.id)

  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
 And then I have two different Python .py files (Parent.py and
 Child.py) where the methods that manage said classes are implemented.
 In those files, the static area of each class is copied from Tables.py
 with some changes in the quotes (where I can use the object itself, I
 use it):

 Parent.py 

 from child import Child
 metadata = rdb.MetaData()

 class Parent(rdb.Model):
   rdb.metadata(metadata)
   rdb.tablename(parents_table)

   id = Column(id, Integer, primary_key=True)
   _whateverField= Column(whatever_field, String(16)) #Irrelevant

   child1 = relationship(Child, uselist=False) #No quotation marks on this
 Child
  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
 And

 Child.py  
 metadata = rdb.MetaData()

 class Child(rdb.Model):
   rdb.metadata(metadata)
   rdb.tablename(children_table)
   id = Column(id, Integer, ForeignKey(parent_table.id), primary_key =
 True)
   type = Column(type, String(2)) #Irrelevant (for this example)

 These class definitions should be merged with those in Tables.py. You should
 only have one class Parent statement and one class Child statement. You
 may be confusing this with the non-declarative class setup, where you define
 the table first, class 2nd, and mapper 3rd. It looks like rdb uses the
 declarative approach, where the table and mapper are defined as part of the
 class in one step.

 Also, it is a good idea to make the first argument to relationship() a
 string, as it lets you avoid worrying about which order classes are defined.
 Example:

 # This works even if Child hasn't been defined yet.
 child1 = relationship(Child, uselist=False)

  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
 When I try to use these classes, I get:
 Foreign key assocated with column 'children_table.id' could not find
 table 'parents_table' with which to generate a foreign key to target
 column 'id'

 This is probably due to the foreign key issue above.

 But if I take a look to the tables with a MySQL Query Browser, the
 table parents_table is there, happily and properly created.

 In some other places, I have had similar problems, but I've been able
 to fix them by delaying the imports. I had been able to (kind of)
 import the Parent type in the Child file so I can use the Parent
 object directly. It would be a little bit as if in this case I was
 able to do:

 from parent import Parent
 [ . . . ]

 class

[sqlalchemy] Relationship between a class with two different instances of other class (Newbie)

[Hector Blanco]

Hello list...

I wrote a couple of days ago about how to model an structure of three
classes 
(http://groups.google.com/group/sqlalchemy/browse_thread/thread/5ba5c4ad16f789d6#).
I thing I almost have it, but I am still getting problems mapping an
structure like this.

class Child(rdb.Model):
def __init__(self):
self.field1 = “hello world”

class Parent(rdb.Model):
def __init__(self):
self.child1 = Child()
self.child2 = Child()

The “Parent” class has two different instances of a Child() class. I
am not even sure about how to treat this (two different 1:1
relationships or a 1:2 relationship).

My last try is this mapping:

(don't let the rdb.Model thing fool you, is just something that
automatically maps the class to the table specified in rdb.tablename).

class Child(rdb.Model):
rdb.metadata(metadata)
rdb.tablename(children_table)
id = Column(id, Integer, primary_key=True)
field1 = Column(“field1”, String(64))   #Irrelevant
def __init__(self):
self.field1 = “hello world”

class Parent(rdb.Model):
rdb.metadata(metadata)
rdb.tablename(parent_table”)

id = Column(id, Integer, primary_key=True)
child1_id = Column(child_1_id, Integer, 
ForeignKey(children_table.id))
child2_id = Column(child_2_id, Integer, 
ForeignKey(children_table.id))

child1 = relationship(Child,
primaryjoin = (child1_id==children_table.id)
)

child2 = relationship(Child,
primaryjoin = (child2_id==children_table.id)
)

I have tried (almost) everything. I say “almost” because obviously I
haven't tried the right thing. I keep getting errors that sqlalchemy
can't determine the relationship between the two tables.

It looks very similar to:
http://www.sqlalchemy.org/docs/orm/relationships.html#multiple-relationships-against-the-same-parent-child

but I also understand that maybe I can't get this working that way,
because I am actually putting two ForeignKeys from the same table
(“children_table”) in the parent table. I am not sure how that will
work out, or is correct or what... :-(

Thank you!

-- 
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] Relationship between a class with two different instances of other class (Newbie)

[Hector Blanco]

Thanks Conor!

The callable works like a charm! It's great news! (I've been trying to
figure out this for 3 days... yeah... I guess I'm not that smart)

Now that I have it working, a “design” question pops up. Nothing
technical, really.

As Connor mentioned in his reply:
“Usually we consider the table with the foreign key as the child
table, but that's just being picky”

That's very true, and now I don't know how to design it...

I can do it the way I asked or...

class Child(rdb.Model):
rdb.metadata(metadata)
rdb.tablename(children_table)
id = Column(id, Integer, primary_key=True)
parent_id = Column(id, Integer, ForeignKey(“parent_table.id”))  # New!
type = Column(type, ShortInteger)# New!

field1 = Column(“field1”, String(64))  #Irrelevant
def __init__(self):
self.field1 = “hello world”

class Parent(rdb.Model):
rdb.metadata(metadata)
rdb.tablename(parent_table”)

id = Column(id, Integer, primary_key=True)

child1 = relationship(
# Well... this I still don't know how to write it down,
# but it would be something like:
#  Give me all the children whose “parent_id” is my “id”
#  AND type == 1
# I'll deal with the joins and that depending on your answer, 
guys
)

child2 = relationship(
#  Would be same as above
#  AND type == 2
)

This may be good for adding new children to the parent class... If I
add a “Parent.child3”, I just need to create a new relationship very
similar to the already existing ones.

The way I asked in my former question would imply creating a new
relationship AND adding a new foreign key to the parent.

I'd like to know what people that know much more about databases think :)



2010/10/29 Conor conor.edward.da...@gmail.com:
 On 10/29/2010 09:43 AM, Hector Blanco wrote:

 Hello list...

 I wrote a couple of days ago about how to model an structure of three
 classes
 (http://groups.google.com/group/sqlalchemy/browse_thread/thread/5ba5c4ad16f789d6#).
 I thing I almost have it, but I am still getting problems mapping an
 structure like this.

 class Child(rdb.Model):
   def __init__(self):
   self.field1 = “hello world”

 class Parent(rdb.Model):
   def __init__(self):
   self.child1 = Child()
   self.child2 = Child()

 The “Parent” class has two different instances of a Child() class. I
 am not even sure about how to treat this (two different 1:1
 relationships or a 1:2 relationship).

 My last try is this mapping:

 (don't let the rdb.Model thing fool you, is just something that
 automatically maps the class to the table specified in rdb.tablename).

 class Child(rdb.Model):
   rdb.metadata(metadata)
   rdb.tablename(children_table)
   id = Column(id, Integer, primary_key=True)
   field1 = Column(“field1”, String(64))   #Irrelevant
   def __init__(self):
   self.field1 = “hello world”

 class Parent(rdb.Model):
   rdb.metadata(metadata)
   rdb.tablename(parent_table”)

   id = Column(id, Integer, primary_key=True)
   child1_id = Column(child_1_id, Integer, 
 ForeignKey(children_table.id))
   child2_id = Column(child_2_id, Integer, 
 ForeignKey(children_table.id))

   child1 = relationship(Child,
   primaryjoin = (child1_id==children_table.id)
   )

   child2 = relationship(Child,
   primaryjoin = (child2_id==children_table.id)
   )

 I have tried (almost) everything. I say “almost” because obviously I
 haven't tried the right thing. I keep getting errors that sqlalchemy
 can't determine the relationship between the two tables.

 It looks very similar to:
 http://www.sqlalchemy.org/docs/orm/relationships.html#multiple-relationships-against-the-same-parent-child

 but I also understand that maybe I can't get this working that way,
 because I am actually putting two ForeignKeys from the same table
 (“children_table”) in the parent table. I am not sure how that will
 work out, or is correct or what... :-(

 Thank you!

 Usually we consider the table with the foreign key as the child table, but
 that's just being picky. The problem is that SQLAlchemy is treating
 children_table.id as a literal instead of a clause, so your join would be
 like (parent JOIN child ON parent.child1_id = 'children_table.id').
 Obviously that is not what you want. There are several ways to formulate
 primaryjoin/secondaryjoin.

 Pass the whole thing in as a string:

 child1 = relationship(Child, primaryjoin=Parent.child1_id == Child.id)

 Use the column objects directly (this requires that Child be defined before
 Parent):

 child1 = relationship(Child, primaryjoin=child1_id==Child.id)

 Use a callable (my favorite):

 child1 = relationship(Child, primaryjoin=lambda: Parent.child1_id ==
 Child.id)

 -Conor

 --
 You received

[sqlalchemy] Simple relationship 1:1 fails with Foreign key assocated with column ------- could not find table

[Hector Blanco]

Hello, group!

I am still dealing with the relationship I asked before
(http://groups.google.com/group/sqlalchemy/browse_thread/thread/c1d46daf35116999).

To tell the truth, I'm not even sure if this is a question I should
ask in the SqlAlchemy forum because I'm also dealing with Megrok.rdb
(http://pypi.python.org/pypi/megrok.rdb) and I don't know if that's
what is causing the problem, but I'm pretty lost...  As I explained in
my other question, I think the rdb.Model thing that appears in the
classes is just a tool to create the mapper class -- table in a
slightly more transparent way for the programmer. That's why I thought
I may get some help here.

In this message, I have simplified the code (compared to my former
question) to make it clearer, but well... The fact is that now I'm
getting a problem with a simple 1:1 relationship (if I can fix it, I
will be able to move to the more complicated stuff as I detailed in
the former question)

I am getting this error:
Foreign key assocated with column 'children_table.id' could not find
table 'parents_table' with which to generate a foreign key to target
column 'id'

I have a file, called Tables.py where all the classes and auxiliary
(or intermediate) tables that I'm going to use in my application are
defined:

Tables.py 

class Parent(rdb.Model):
rdb.metadata(metadata)
rdb.tablename(parents_table)

id = Column(id, Integer, primary_key=True)
_whateverField= Column(whatever_field, String(16)) #Irrelevant

child1 = relationship(Child, uselist=False)

class Child(rdb.Model):
rdb.metadata(metadata)
rdb.tablename(children_table)
id = Column(id, Integer, ForeignKey(Parent.id), primary_key = True)
type = Column(type, String(2)) #Irrelevant (for this example)
 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
And then I have two different Python .py files (Parent.py and
Child.py) where the methods that manage said classes are implemented.
In those files, the static area of each class is copied from Tables.py
with some changes in the quotes (where I can use the object itself, I
use it):

Parent.py 

from child import Child
metadata = rdb.MetaData()

class Parent(rdb.Model):
rdb.metadata(metadata)
rdb.tablename(parents_table)

id = Column(id, Integer, primary_key=True)
_whateverField= Column(whatever_field, String(16)) #Irrelevant

child1 = relationship(Child, uselist=False) #No quotation marks on this 
Child
 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
And

Child.py  
metadata = rdb.MetaData()

class Child(rdb.Model):
rdb.metadata(metadata)
rdb.tablename(children_table)
id = Column(id, Integer, ForeignKey(parent_table.id), primary_key = 
True)
type = Column(type, String(2)) #Irrelevant (for this example)
 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
When I try to use these classes, I get:
Foreign key assocated with column 'children_table.id' could not find
table 'parents_table' with which to generate a foreign key to target
column 'id'

But if I take a look to the tables with a MySQL Query Browser, the
table parents_table is there, happily and properly created.

In some other places, I have had similar problems, but I've been able
to fix them by delaying the imports. I had been able to (kind of)
import the Parent type in the Child file so I can use the Parent
object directly. It would be a little bit as if in this case I was
able to do:

from parent import Parent
[ . . . ]

class Child(rdb.Model):
[ . . . ]
id = Column(id, Integer, ForeignKey(Parent.id), primary_key = True)

and that usually fixed the problem but in this specific case, I can't
really do that: In the Parent file I need to import the Child and that
gives a very, very nasty circular dependency problem.

Is there a way to tell the Child.py file something like Hey, dude...
Here's the parent_table that you need! ?  (Well... In a more Pythonic
way, of course... I don't think 'dude'is a reserved keywork in Python,
or an SqlAlchemy type). I don't know, something like:

from whatever.repository.of.tables import parent_table

 so I can, without quotes, use:

id = Column(id, Integer, ForeignKey(parent_table.id), primary_key = True)

(I guess that may work)

Thank you all.

-- 
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] Containers/collections with SQLAlchemy

[Hector Blanco]

Hi group!

I am trying to migrate my application from ZopeDB to MySql. I am using
sqlalchemy under megrok.rdb.

I have a class which inherits from list() and has a few extra methods.
Reading the chapter about custom collections in sqlalchemy
(http://www.sqlalchemy.org/docs/orm/collections.html#custom-collection-implementations),
I thought that I'd be great having that class as a custom collection
(extending from list() but simulating a set(), where the items can't
be repeated)

That class that extends from list, acts as a container of other
classes (mmm... yeah, as all the collections do... pretty obvious). It
is used (as an attribute) in other classes as well:

class MyEntry(object):
def __init__(self):
self.field1 = “field1”
self.field2 = “field2”


class MyContainer(list):
__emulates__ = set
def __init__(self):
super(MyContainer,self).__init__()

def add(self, myEntry):
if isinstance(myEntry, MyEntry):
if not(myEntry in self):
super(MyContainer, self).append(myEntry)

def getByField1(self, field1):
for element in self:
if element.field1 == field1:
return element
return None
#   [ ...
#   more useful methods,
#   overloading to make the list behave like a set,
#   yada yada yada
#   ...]


class MyClass(object):
def __init__(self):
self.container1 = MyContainer()
self.container2 = MyContainer()
self.anotherField = “hello world”
def getContainer1():
return self.container1
def getContainer2():
return self.container2

I see clearly the MyEntry and (more or less clearly) MyClass
classes modeled in tables.
I also see clearly the intermediate table for MyContainer:

my_containers_table = Table(
my_containers_table,
metadata,
Column(id, Integer,  primary_key=True),
Column(my_entry_id, Integer, ForeignKey(my_entries_table.id))
)

So, in the MyClass class, each of MyContainer() instances can have an
id and when someone wants to retrieve the MyEntry() elements that are
in container1 (to say so), the my_containers_table can be used as a
middle table to get said MyEntries.

but I don't know how to link the MyContainer(list) object with
my_containers_table (and from there with MyClass) :-(

I'm not even sure whether MyClass.container1 and MyClass.container2
should be ForeignKeys or Relations.

How can I establish the relationship between MyClass.container1 or
MyClass.container2 with my_containers_table?

On one hand, I want MyClass.container1 and MyClass.container2 to be
foreign keys, but on the other, I want them to be instances of
MyContainer(list)... And that's where I start banging my head on the
wall :-)

In my mind (preferably before banging it against the wall) I see this schema:

 +---Entry+
 | id = 1|
 |field1   | +container1---+
 |field2   |  |id = 10|
 +-+ |  foreign[0] = 1   |
|  foreign[1] = 2   |+- myClass +
 +---Entry---++--+   |  id = 101 |
 |id = 2 | |
anotherField|
 |field1   | |
container1 = 10  |
 |field2   | +container2---+| container2 = 20  |
 +-+ |  id = 20  | +-+
|  foreign[0] = 3   |
 +---Entry---++-+
 |id = 3|
 |field1  |
 |field2  |
 ++

[I hope the Ascii thing is properly displayed]

When I want to get all what is in myClass.container1, the system
should go to my_containers_table with the myClass.container1's id (10)
and retrieve all the MyEntries (id=1 and id=2 in the example above)
pointed by the ForeingKey of my_containers_table. That's what I want
the system to do. But that's not what it's doing.

Any tip will be deeply appreciated. Links to manuals,
documentations... whatever (I'm a total newbie in sqlmyalchemy)

Thank you again!

-- 
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] Automatically execute a procedure on saving/loading from Sql database with SQLAlchemy (and MeGrok)

[Hector Blanco]

Hello everyone.

First of all, thank you for reading this (no matter whether you can/want to
help me or not) :-)

Second, the question:

I am using sqlalchemy under MeGrok (http://pypi.python.org/pypi/megrok.rdb)
to have my Python/Grok classes stored over a RDBMS (MySql) database.

I have a Python class in which one of the fields is a list of strings. I
don't really find worthy to create a table to relate the class with the
fields (is just a list that can take certain names of days of the week) so I
was planning to store them in MySql as an string where the values would be
separated with a comma (or semicolon).

On the other hand, is very useful to have that field as a list (in Python
classes) so here's my question:

Is there a way to automatically execute an stored procedure (SQL preferably,
but I could also do it on the Python side) so when the class is saved,
that list field will be automatically joined (with whatever separator
character) and when the class (or that field) is loaded, it will be
automatically split-ed (so it will come back as a list)?

In my brain, I have dreamed about something like an special type of *
sqlalchemy.Column* in which you can specify something like *on_save =
execute this()* and *on_load = execute that()*... :-)

I also asked this very same question in the Grok-dev mail list. They
suggested me the use of decorators, which I find an interesting idea, but I
don't really know where to put a decorator to ensure that when that field is
saved, it's saved as an array, and when it's loaded, it's loaded as a list.

Oh, and, for the record, I am a newbie with this Grok over MySql thing so it
may not make any sense what I just asked but... I had to try.

Thank you in advance.

-- 
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] Re: Automatically execute a procedure on saving/loading from Sql database with SQLAlchemy (and MeGrok)

[Hector Blanco]

Wow... I just saw this...
http://www.sqlalchemy.org/docs/05/reference/sqlalchemy/types.html#sqlalchemy.types.TypeDecorator
Maybe that's what I need! I'll confirm (for future questions) :-)

2010/10/25 Hector Blanco white.li...@gmail.com

 Hello everyone.

 First of all, thank you for reading this (no matter whether you can/want to
 help me or not) :-)

 Second, the question:

 I am using sqlalchemy under MeGrok (http://pypi.python.org/pypi/megrok.rdb)
 to have my Python/Grok classes stored over a RDBMS (MySql) database.

 I have a Python class in which one of the fields is a list of strings. I
 don't really find worthy to create a table to relate the class with the
 fields (is just a list that can take certain names of days of the week) so I
 was planning to store them in MySql as an string where the values would be
 separated with a comma (or semicolon).

 On the other hand, is very useful to have that field as a list (in Python
 classes) so here's my question:

 Is there a way to automatically execute an stored procedure (SQL
 preferably, but I could also do it on the Python side) so when the class
 is saved, that list field will be automatically joined (with whatever
 separator character) and when the class (or that field) is loaded, it will
 be automatically split-ed (so it will come back as a list)?

 In my brain, I have dreamed about something like an special type of *
 sqlalchemy.Column* in which you can specify something like *on_save =
 execute this()* and *on_load = execute that()*... :-)

 I also asked this very same question in the Grok-dev mail list. They
 suggested me the use of decorators, which I find an interesting idea, but I
 don't really know where to put a decorator to ensure that when that field is
 saved, it's saved as an array, and when it's loaded, it's loaded as a list.

 Oh, and, for the record, I am a newbie with this Grok over MySql thing so
 it may not make any sense what I just asked but... I had to try.

 Thank you in advance.



-- 
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] Re: Automatically execute a procedure on saving/loading from Sql database with SQLAlchemy (and MeGrok)

[Hector Blanco]

Yuuup... It works like a charm!

I have created a simple class that gets a dictionary and serializes its
values. The underlying database is MySql...

Just in case my code may help someone (or if someone has any suggestions...)
here it goes:

from sqlalchemy import types
import logging
log = logging.getLogger(__name__)

class SimpleDict(types.TypeDecorator):
impl = types.String
 size = -1
 __separatorChar = chr(0x1D)
 __boolPrefix = b_
 __intPrefix = i_
 __floatPrefix = f_
 __nullPrefix = n_
 __specialPrefixes = set([__boolPrefix, __intPrefix, __floatPrefix,
__nullPrefix])
 __nullValues = set([null, None])

def __init__(self, length = 1024):
 self.size = int(length)
 super(ZepSimpleDict, self).__init__(self.size)

def __toString(self, value):
 retval = None
 if isinstance(value, bool):
 retval = self.__boolPrefix + str(value)
 elif isinstance(value, float):
 retval = self.__floatPrefix + str(value)
 elif isinstance(value, int):
 retval = self.__intPrefix + str(value)
 elif (value is None) or (value in self.__nullValues):
 retval = self.__nullPrefix + str(None)
 else:
 retval = str(value)
 return retval

def __fromString(self, value):
 retval = None
 prefix = None
 actualValue = None
 if len(value)  2:
 prefix = value[0:2]
 if (prefix in self.__specialPrefixes):
 actualValue = value[2:]
 if prefix == self.__boolPrefix:
 if actualValue == True:
 retval = True
 elif actualValue == False:
 retval = False
 else:
 retval = value
 elif prefix == self.__floatPrefix:
 try:
 retval = float(actualValue)
 except ValueError:
 retval = value
 elif prefix == self.__intPrefix:
 try:
 retval = int(actualValue)
 except ValueError:
 retval = value
 elif prefix == self.__nullPrefix:
 if actualValue == str(None):
 retval = None
 else:
 retval = value
 else:
 retval = value
 else:
 retval = value
 return retval


 def process_bind_param(self, value, dialect):
 value_tmp = None
 flattenedValue = list()
 retval = None

if isinstance(value, dict):
 value_tmp = dict()
 for key, val in value.iteritems():
 value_tmp[self.__toString(key)] = self.__toString(val)
 else:
 value_tmp = None

if (value_tmp is not None):
 for key, val in value_tmp.iteritems():
 flattenedValue.append(key)
 flattenedValue.append(val)
 retval = self.__separatorChar.join(flattenedValue)
 else:
 retval = None
  return retval

def process_result_value(self, value, dialect):
 retval = dict()
 value_tmp = value.split(self.__separatorChar)
 if (len(value_tmp)  0):
 if (len(value_tmp) % 2 != 0):
 log.warn(process_result_value  Processing an string with odd number of
elements. This should not have happened.)
 for i in range(0, len(value_tmp), 2):
 retval[self.__fromString(value_tmp[i])] = self.__fromString(value_tmp[i+1])
 return retval



In my previous message, I said:
*In my brain, I have dreamed about something like an special type
of sqlalchemy.Column in which you can specify something like on_save =
execute this() and on_load = execute that()... *

This does exactly that! :)

-- 
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] Re: Automatically execute a procedure on saving/loading from Sql database with SQLAlchemy (and MeGrok)

[Hector Blanco]

I hate when the identation gets messed up... Gonna try again:

Yuuup... It works like a charm!

I have created a simple class that gets a dictionary and serializes
its values. The underlying database is MySql...

Just in case my code may help someone (or if someone has any
suggestions...) here it goes:



from sqlalchemy import types
import logging
log = logging.getLogger(__name__)

class ZepSimpleDict(types.TypeDecorator):
impl = types.String
size = -1
__separatorChar = chr(0x1D)
__boolPrefix = b_
__intPrefix = i_
__floatPrefix = f_
__nullPrefix = n_
__specialPrefixes = set([__boolPrefix, __intPrefix, __floatPrefix,
__nullPrefix])
__nullValues = set([null, None])

def __init__(self, length = 1024):
self.size = int(length)
super(ZepSimpleDict, self).__init__(self.size)

def __toString(self, value):
retval = None
if isinstance(value, bool):
retval = self.__boolPrefix + str(value)
elif isinstance(value, float):
retval = self.__floatPrefix + str(value)
elif isinstance(value, int):
retval = self.__intPrefix + str(value)
elif (value is None) or (value in self.__nullValues):
retval = self.__nullPrefix + str(None)
else:
retval = str(value)
return retval

def __fromString(self, value):
retval = None
prefix = None
actualValue = None
if len(value)  2:
prefix = value[0:2]
if (prefix in self.__specialPrefixes):
actualValue = value[2:]
if prefix == self.__boolPrefix:
if actualValue == True:
retval = True
elif actualValue == False:
retval = False
else:
retval = value
elif prefix == self.__floatPrefix:
try:
retval = float(actualValue)
except ValueError:
retval = value
elif prefix == self.__intPrefix:
try:
retval = int(actualValue)
except ValueError:
retval = value
elif prefix == self.__nullPrefix:
if actualValue == str(None):
retval = None
else:
retval = value
else:
retval = value
else:
retval = value
return retval


def process_bind_param(self, value, dialect):
value_tmp = None
flattenedValue = list()
retval = None

if isinstance(value, dict):
value_tmp = dict()
for key, val in value.iteritems():
value_tmp[self.__toString(key)] = 
self.__toString(val)
else:
value_tmp = None

if (value_tmp is not None):
for key, val in value_tmp.iteritems():
flattenedValue.append(key)
flattenedValue.append(val)
retval = self.__separatorChar.join(flattenedValue)
else:
retval = None

return retval

def process_result_value(self, value, dialect):
retval = dict()
value_tmp = value.split(self.__separatorChar)
if (len(value_tmp)  0):
if (len(value_tmp) % 2 != 0):
log.warn(process_result_value  Processing an 
string with odd
number of elements. This should not have happened.)
for i in range(0, len(value_tmp), 2):
retval[self.__fromString(value_tmp[i])] = 
self.__fromString(value_tmp[i+1])
return retval