[sqlalchemy] Re: concurrent versions of SA
Peter, PD wrote: Hi, please can someone help me with this (maybe simple solution): Is it possible to install two (or more) versions of SA in one Python environment and import specific version? E.g. when migrating from old version. If you installed using easy_setup then you can do: import pkg_resources pkg_resources.require(sqlalchemy) # get latest version or: pkg_resources.require(sqlalchemy==0.4.3) # use a specific version Werner --~--~-~--~~~---~--~~ 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: concurrent versions of SA
werner wrote: Peter, PD wrote: Hi, please can someone help me with this (maybe simple solution): Is it possible to install two (or more) versions of SA in one Python environment and import specific version? E.g. when migrating from old version. If you installed using easy_setup then you can do: Meant easy_install - haven't had my tea yet:) import pkg_resources pkg_resources.require(sqlalchemy) # get latest version or: pkg_resources.require(sqlalchemy==0.4.3) # use a specific version Werner --~--~-~--~~~---~--~~ 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] Beginner inheritance question
Hi there, this is perhaps a stupid question, but we can't get it figured out from the docs on inheritance... Is it possible in sqlalchemy to have a hierarchy of classes of which only the root class in the hierarchy is mapped to the database at all. (So the subclasses do not have any persisted attributes of their own, and are all in essence mapped to the same table.) I suppose single inheritance would work here, but I'd like to keep open the option of one day adding one special subclass which DOES in fact want to persist extra attributes - this time using joined table inheritance. Is something (or a mix) like this possible? Thanks - i --~--~-~--~~~---~--~~ 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: concurrent versions of SA
PD wrote: Hi, please can someone help me with this (maybe simple solution): Is it possible to install two (or more) versions of SA in one Python environment and import specific version? E.g. when migrating from old version. You might also want to look into: http://pypi.python.org/pypi/virtualenv Projects like turbogears recommend it, I haven't got around to try it out yet. Werner --~--~-~--~~~---~--~~ 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] How to replace a property in a declarative way
Hi,
I simplified my problem to a small example (see model below).
A Product has several Tags attached. A beachball is green, blue, round
and big:
p=Product()
p.name = Beachball
p.tags = [greenTag, blueTag, roundTag, bigTag]
DBSession.save(p)
Often I just want to see the color-related tags, not the other tags.
Therefore, I group all color tags in a Collection:
c=Collection()
c.name = Colors
c.tags = [greenTag, blueTag, brownTag]
DBSession.save(c)
See what colors are attached to the product:
p = DBSession.query(Product).filter(Product.name == Beachball).first()
p.colors
[Tag: id=1706, name=green, Tag: id=1707, name=blue]
Here's my issue. To get the colors, I defined a property, which
results in an extra query.
I want the color Tags to be fetched in the same query when I fetch the
Product itself (optionally with lazy loading)
I guess I need another relation() definition with a Collection join
and Collection name filter applied.
I couldn't come up with the code to get me there. How should I tackle
this?
Cheers,
Kees
The model:
product_tag_table = Table('product_tag', metadata,
Column('product_id', Integer, ForeignKey('product.id',
onupdate=CASCADE, ondelete=CASCADE)),
Column('tag_id', Integer, ForeignKey('tag.id',
onupdate=CASCADE, ondelete=CASCADE))
)
tag_collection_table = Table('tag_collection', metadata,
Column('tag_id', Integer, ForeignKey('tag.id',
onupdate=CASCADE, ondelete=CASCADE)),
Column('collection_id', Integer, ForeignKey('collection.id',
onupdate=CASCADE, ondelete=CASCADE))
)
class Product(DeclarativeBase):
__tablename__ = 'product'
id = Column(Integer, autoincrement=True, primary_key=True)
name = Column(Unicode(255), nullable=False)
tags = relation('Tag', secondary=product_tag_table,
backref='products')
# XXX: REPLACE THIS PROPERTY WITH A FILTERED RELATION?
def _get_colors(self):
color_tags=DBSession.query(Collection).\
filter(Collection.name==Colors).first().tags
return filter(lambda tag: tag in self.tags, color_tags)
colors = property(_get_colors)
class Tag(DeclarativeBase):
__tablename__ = 'tag'
id = Column(Integer, autoincrement=True, primary_key=True)
name = Column(Unicode(255), unique=True, nullable=False)
class Collection(DeclarativeBase):
__tablename__ = 'collection'
id = Column(Integer, autoincrement=True, primary_key=True)
name = Column(Unicode(255), unique=True, nullable=False)
tags = relation('Tag', secondary=tag_collection_table,
backref='collections')
--~--~-~--~~~---~--~~
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] object serializer
Hello, I'm busy to make a kind of mapper exporter/serializer. My goal is to be able to serialize (in JSON, XML, ...) any Mapper object (including relations, etc) At the moment I've the following code (not finished at all) : http://www.pastie.org/625787 As you can see I'm iterating over mapper.iterate_properties list (line 60), and before I goes any further I wondered if it's the right way to do it ? (I would like to have all the properties, including column type, etc) Another question: I see that the property columns of an sqlalchemy.orm.properties.ColumnProperty instance is a list and I wondered in which cases the list can contains more than one item ? Thanks, Julien -- Julien Cigar Belgian Biodiversity Platform http://www.biodiversity.be Université Libre de Bruxelles (ULB) Campus de la Plaine CP 257 Bâtiment NO, Bureau 4 N4 115C (Niveau 4) Boulevard du Triomphe, entrée ULB 2 B-1050 Bruxelles Mail: jci...@ulb.ac.be @biobel: http://biobel.biodiversity.be/person/show/471 Tel : 02 650 57 52 No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. --~--~-~--~~~---~--~~ 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: How to replace a property in a declarative way
On Sep 22, 5:57 am, Kees van den Broek kees...@gmail.com wrote:
Hi,
I simplified my problem to a small example (see model below).
A Product has several Tags attached. A beachball is green, blue, round
and big:
p=Product()
p.name = Beachball
p.tags = [greenTag, blueTag, roundTag, bigTag]
DBSession.save(p)
Often I just want to see the color-related tags, not the other tags.
Therefore, I group all color tags in a Collection:
c=Collection()
c.name = Colors
c.tags = [greenTag, blueTag, brownTag]
DBSession.save(c)
See what colors are attached to the product: p =
DBSession.query(Product).filter(Product.name == Beachball).first()
p.colors
[Tag: id=1706, name=green, Tag: id=1707, name=blue]
Here's my issue. To get the colors, I defined a property, which
results in an extra query.
I want the color Tags to be fetched in the same query when I fetch the
Product itself (optionally with lazy loading)
I guess I need another relation() definition with a Collection join
and Collection name filter applied.
I couldn't come up with the code to get me there. How should I tackle
this?
Cheers,
Kees
The model:
product_tag_table = Table('product_tag', metadata,
Column('product_id', Integer, ForeignKey('product.id',
onupdate=CASCADE, ondelete=CASCADE)),
Column('tag_id', Integer, ForeignKey('tag.id',
onupdate=CASCADE, ondelete=CASCADE))
)
tag_collection_table = Table('tag_collection', metadata,
Column('tag_id', Integer, ForeignKey('tag.id',
onupdate=CASCADE, ondelete=CASCADE)),
Column('collection_id', Integer, ForeignKey('collection.id',
onupdate=CASCADE, ondelete=CASCADE))
)
class Product(DeclarativeBase):
__tablename__ = 'product'
id = Column(Integer, autoincrement=True, primary_key=True)
name = Column(Unicode(255), nullable=False)
tags = relation('Tag', secondary=product_tag_table,
backref='products')
# XXX: REPLACE THIS PROPERTY WITH A FILTERED RELATION?
def _get_colors(self):
color_tags=DBSession.query(Collection).\
filter(Collection.name==Colors).first().tags
return filter(lambda tag: tag in self.tags, color_tags)
colors = property(_get_colors)
class Tag(DeclarativeBase):
__tablename__ = 'tag'
id = Column(Integer, autoincrement=True, primary_key=True)
name = Column(Unicode(255), unique=True, nullable=False)
class Collection(DeclarativeBase):
__tablename__ = 'collection'
id = Column(Integer, autoincrement=True, primary_key=True)
name = Column(Unicode(255), unique=True, nullable=False)
tags = relation('Tag', secondary=tag_collection_table,
backref='collections')
--~--~-~--~~~---~--~~
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: How to replace a property in a declarative way
On Sep 22, 9:39 am, Conor conor.edward.da...@gmail.com wrote:
On Sep 22, 5:57 am, Kees van den Broek kees...@gmail.com wrote:
Hi,
I simplified my problem to a small example (see model below).
A Product has several Tags attached. A beachball is green, blue, round
and big:
p=Product()
p.name = Beachball
p.tags = [greenTag, blueTag, roundTag, bigTag]
DBSession.save(p)
Often I just want to see the color-related tags, not the other tags.
Therefore, I group all color tags in a Collection:
c=Collection()
c.name = Colors
c.tags = [greenTag, blueTag, brownTag]
DBSession.save(c)
See what colors are attached to the product: p =
DBSession.query(Product).filter(Product.name == Beachball).first()
p.colors
[Tag: id=1706, name=green, Tag: id=1707, name=blue]
Here's my issue. To get the colors, I defined a property, which
results in an extra query.
I want the color Tags to be fetched in the same query when I fetch the
Product itself (optionally with lazy loading)
I guess I need another relation() definition with a Collection join
and Collection name filter applied.
I couldn't come up with the code to get me there. How should I tackle
this?
Cheers,
Kees
Sorry for the double post.
Here is how I would construct the colors relation (put this inside
the Product declaration):
def _secondaryjoin():
tag = Tag.__table__
collection = Collection.__table__
exists_clause = exists(
[1],
((tag_collection_table.c.tag_id == tag.c.id)
(collection.c.name == Colors)),
from_obj=tag_collection_table.join(collection)).correlate(tag)
# print exists_clause
# EXISTS (SELECT 1
# FROM tag_collection JOIN collection ON collection.id =
tag_collection.collection_id
# WHERE tag_collection.tag_id = tag.id AND collection.name
= :name_1)
retval = ((tag.c.id == product_tag_table.c.tag_id)
exists_clause)
# print retval
# tag.id = product_tag.tag_id AND (EXISTS (SELECT 1
# FROM tag_collection JOIN collection ON collection.id =
tag_collection.collection_id
# WHERE tag_collection.tag_id = tag.id AND collection.name
= :name_1))
return retval
colors = relation('Tag',
secondary=product_tag_table,
secondaryjoin=_secondaryjoin,
viewonly=True)
del _secondaryjoin
The key here is adding a custom secondaryjoin to the relation to add
your own filters. The viewonly bit keeps you from trying to modify the
relation, because you should modify the tags relation instead. This
may not be the fastest clause to use as your secondaryjoin, but it was
the most straightforward one I could come up with.
As for eagerloading the colors relation, you can do:
q = session.query(Product)
q = q.options(eagerload(colors))
# print q
# SELECT product.id AS product_id, product.name AS product_name,
tag_1.id AS tag_1_id, tag_1.name AS tag_1_name
# FROM product LEFT OUTER JOIN product_tag AS product_tag_1 ON
product.id = product_tag_1.product_id LEFT OUTER JOIN tag AS tag_1 ON
tag_1.id = product_tag_1.tag_id AND (EXISTS (SELECT 1
# FROM tag_collection JOIN collection ON collection.id =
tag_collection.collection_id
# WHERE tag_collection.tag_id = tag_1.id AND collection.name
= :name_1))
-Conor
The model:
product_tag_table = Table('product_tag', metadata,
Column('product_id', Integer, ForeignKey('product.id',
onupdate=CASCADE, ondelete=CASCADE)),
Column('tag_id', Integer, ForeignKey('tag.id',
onupdate=CASCADE, ondelete=CASCADE))
)
tag_collection_table = Table('tag_collection', metadata,
Column('tag_id', Integer, ForeignKey('tag.id',
onupdate=CASCADE, ondelete=CASCADE)),
Column('collection_id', Integer, ForeignKey('collection.id',
onupdate=CASCADE, ondelete=CASCADE))
)
class Product(DeclarativeBase):
__tablename__ = 'product'
id = Column(Integer, autoincrement=True, primary_key=True)
name = Column(Unicode(255), nullable=False)
tags = relation('Tag', secondary=product_tag_table,
backref='products')
# XXX: REPLACE THIS PROPERTY WITH A FILTERED RELATION?
def _get_colors(self):
color_tags=DBSession.query(Collection).\
filter(Collection.name==Colors).first().tags
return filter(lambda tag: tag in self.tags, color_tags)
colors = property(_get_colors)
class Tag(DeclarativeBase):
__tablename__ = 'tag'
id = Column(Integer, autoincrement=True, primary_key=True)
name = Column(Unicode(255), unique=True, nullable=False)
class Collection(DeclarativeBase):
__tablename__ = 'collection'
id = Column(Integer, autoincrement=True, primary_key=True)
name = Column(Unicode(255), unique=True, nullable=False)
tags = relation('Tag', secondary=tag_collection_table,
backref='collections')
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the
[sqlalchemy] SqlAlchemy results differ from its generated SQL
Hi, When I run a simple query like this: qry = session.query(CheckHistory) qry = qry.filter(and_(CheckHistory.CHECK_DATE = '1/1/2007', CheckHistory.CHECK_DATE = '1/1/2008')) res = qry.all() I get one CheckHistory row and a None. If I run the echoed SQL code in my database's query analyzer, I get almost 5000 results. Last Friday, this worked just fine. However, over the weekend, we upgraded from MS SQL Server 2000 to 2005. I don't see how this could have messed up SqlAlchemy, but something weird is going on here. Does anyone have any hints for troubleshooting this? I am using SqlAlchemy 0.5.6 on Windows XP with Python 2.5. Thanks, Mike --~--~-~--~~~---~--~~ 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] Pre-commit validation spanning multiple tables/ORM classes
In my application, I have a set of tables that model parts of what are conceptually composite objects, and I need to validate that the state of the objects is coherent before committing them to the database. In the course of building up the network of objects, the state may be temporarily inconsistent (because it will, in general, be impossible to maintain consistency at every step of the process), but I want to make sure it is consistent by the time it hits the database. I think the answer is to make a SessionExtension and use before_commit(), but I still have some questions about exactly how that works. To make the question concrete, I have a one-to-many relationship, and the relevant state is the state of the parent+children composite. Any change to a child's attributes needs to trigger re-validation of the parent, and obviously any change in membership in the collection of children needs to trigger revalidation. In particular, if a child moves from one parent to another, then *both* parents must be re- validated before the transaction is committed. All this validation needs to occur even though the parent table is not modified in any of those cases. And I think I will likely want this to work also in a many-to-many relationship, where any change to the association table should trigger validation of all related (or newly unrelated) objects. Furthermore, I want to work with these objects as individual Parent and Child objects, not a single ParentWithChildren object. Or at a minimum, I want to be able to pass around and modify Child objects on their own; if I get the Children every time I ask for the Parent, that's fine. The @validates decorator is largely useless for this purpose, as it validates a particular attribute of a particular class, and it gets called at the wrong time, and in the case of collections, only gets called on append events, not remove events (afaict). So if I do this with a SessionExtension.before_commit(), I would have to iterate through the new, dirty, and deleted instances lists, inspect the type of each instance, and do whatever is required. I am not sure, though, how to handle the case of a change in membership in the parent/child relationship -- the child instance that is present in the dirty list will have only the new parent on it -- how do I find out what the old parent was, so I can validate it? If a flush has already occurred, the old value is already lost in the context of the current transaction, and I think that if I open a new transaction inside a before_commit() validator I'm just asking for trouble. Do I need to instrument the Child class with a descriptor that tracks changes to the parent and remembers the old parent? Or can I set the cascade option in such a way that the old parent will end up in the dirty list, even though there are no changes to its underlying table, and in fact it may never have been explicitly loaded into the session? (I must admit to be somewhat unsure of what the different cascade options do -- but they don't seem to be useful for tracking something like this.) And lastly, what do I do inside before_commit() if I want to prevent the commit from proceeding? Do I just raise an exception? Any particular type of exception, or is it my choice? Sorry for the long question, and thanks for any assistance, Randall --~--~-~--~~~---~--~~ 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] Adjacency tree linked list
Hi guys,
I’m to build an adjacency list in SA and have looked at the
basic_tree.py example provided, however, I need to order the children.
Now I don’t really want to use nested sets and I know linked lists can
be implemented in SA quite easily.
So naturally I tried something like
trees = Table('treenodes', metadata,
Column('id', Integer, Sequence('treenode_id_seq', optional=True),
primary_key=True),
Column('parent_id', Integer, ForeignKey('treenodes.id'),
nullable=True),
Column('name', String(50), nullable=False),
Column('next_id', Integer, ForeignKey('treenodes.id'),
unique=True, nullable=True)
)
mapper(TreeNode, trees, properties={
'children': relation(TreeNode, cascade=all,
backref=backref(parent, remote_side=
[trees.c.id]),
collection_class=attribute_mapped_collection('name'),
lazy=False, join_depth=3),
'next' :relation(TreeNode, uselist=False,
remote_side=trees.c.id,
backref=backref('previous',
uselist=False))}
)
Of course this does not work as it's a many to many relation (and so
needs another table). I would really prefer if I could get away with
just using one table though. Is there a way to do it?
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
-~--~~~~--~~--~--~---
[sqlalchemy] Inheritance + Pseudo-adjacency-list?
I'm having some trouble developing my model, and was hoping someone on
this list could help...
Here's what I want:
A BizEntity object
A Person and Company object (both descended from BizEntity, using
joined table inheritance)
A Company.employees attribute, which points to a list of Persons who
work for the company
A Person.company attribute, which points back to the company that
person works for
Whenever I try to combine inheritance with this sort of pseudo-
adjacency-list, I get really odd things happening when I try to query
from the tables...like getting the wrong company back when I query by
id.
Any ideas out there? Anyone done something like this?
MODEL (so far):
(NOTE: the commented out lines are left over from some of my previous
attempts to get things working.)
class BizEntity(Base):
__tablename__ = 'biz_entities'
id = Column('bizentity_id', Integer, primary_key=True)
type = Column('bizentity_type', String(30), nullable=False)
__mapper_args__ = {'polymorphic_on': type}
class Company(BizEntity):
__tablename__ = 'companies'
id = Column(Integer, ForeignKey('biz_entities.bizentity_id'),
primary_key=True)
name = Column('company_name', String(50))
#~ employees = relation(Person, backref=backref(company,
remote_side=[])
#~ backref('parent', remote_side=[nodes.c.id])
__mapper_args__ = {'polymorphic_identity': 'company'}
class Person(BizEntity):
__tablename__ = 'people'
id = Column('bizentity_id', Integer, ForeignKey
('biz_entities.bizentity_id'), primary_key=True)
first_name = Column('first_name', String(50))
middle_init = Column('middle_init', String(1))
last_name = Column('last_name', String(50))
#~ company = relation(Company, backref=backref('employees',
order_by=id))
__mapper_args__ = {'polymorphic_identity':'person'}
--~--~-~--~~~---~--~~
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: Pre-commit validation spanning multiple tables/ORM classes
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Randall Nortman Sent: 22 September 2009 16:31 To: sqlalchemy Subject: [sqlalchemy] Pre-commit validation spanning multiple tables/ORM classes [SNIP] So if I do this with a SessionExtension.before_commit(), I would have to iterate through the new, dirty, and deleted instances lists, inspect the type of each instance, and do whatever is required. I am not sure, though, how to handle the case of a change in membership in the parent/child relationship -- the child instance that is present in the dirty list will have only the new parent on it -- how do I find out what the old parent was, so I can validate it? If a flush has already occurred, the old value is already lost in the context of the current transaction, and I think that if I open a new transaction inside a before_commit() validator I'm just asking for trouble. Do I need to instrument the Child class with a descriptor that tracks changes to the parent and remembers the old parent? Or can I set the cascade option in such a way that the old parent will end up in the dirty list, even though there are no changes to its underlying table, and in fact it may never have been explicitly loaded into the session? (I must admit to be somewhat unsure of what the different cascade options do -- but they don't seem to be useful for tracking something like this.) I can't answer most of your question, but as far as finding out what the old parent was, could you use the get_history function? http://www.sqlalchemy.org/docs/05/reference/orm/mapping.html#sqlalchemy .orm.attributes.get_history Hope that helps, Simon --~--~-~--~~~---~--~~ 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: checking a relationship exists
thanks - but how does that query specify a town? It seems like that would just return true if the current user had any town relationships? Or am I reading it wrong? On Sep 19, 5:46 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Sep 19, 2009, at 11:41 AM, joeformd wrote: When we tried that it made a sql query to get all the towns (towns are loaded lazily) - that could be an awful lot of information, so this would be a much faster query so, the most succinct (and efficient) query of all is session.scalar(exists().where(user_towns.c.user_id==current_user.id)) should return True/False right back On Sep 19, 4:33 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Sep 19, 2009, at 10:19 AM, joeformd wrote: Thanks for the speedy response Michael, we came up with this: current_user_has_town = bool(session.query(User).filter (User.id==current_user.id).filter(User.towns.contains (current_user)).count()) which maybe has slightly clearer intent? if you already have the current_user, why not just say town in current_user.towns --~--~-~--~~~---~--~~ 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: Pre-commit validation spanning multiple tables/ORM classes
My issue with SQLA validators is that they don't allow inconsistent state even on fields of a single object, which makes multi-field validation impossible. Eg. imagine you have fields tax_id and country_code on a customer object. For country code 'us', tax_id should be 9 digits long; for country code 'ca', tax_id should be 15 digits long . If you create appropriate validators on both fields, you will never be able to change either the tax_id or country_code of any customer object. The solution would be to allow inconsistent objects but do validation just before commit. I've been planning on implementing such validation for a project I'm working on, so any suggestions are welcome. On Sep 22, 5:30 pm, Randall Nortman wondercl...@gmail.com wrote: In my application, I have a set of tables that model parts of what are conceptually composite objects, and I need to validate that the state of the objects is coherent before committing them to the database. In the course of building up the network of objects, the state may be temporarily inconsistent (because it will, in general, be impossible to maintain consistency at every step of the process), but I want to make sure it is consistent by the time it hits the database. I think the answer is to make a SessionExtension and use before_commit(), but I still have some questions about exactly how that works. To make the question concrete, I have a one-to-many relationship, and the relevant state is the state of the parent+children composite. Any change to a child's attributes needs to trigger re-validation of the parent, and obviously any change in membership in the collection of children needs to trigger revalidation. In particular, if a child moves from one parent to another, then *both* parents must be re- validated before the transaction is committed. All this validation needs to occur even though the parent table is not modified in any of those cases. And I think I will likely want this to work also in a many-to-many relationship, where any change to the association table should trigger validation of all related (or newly unrelated) objects. Furthermore, I want to work with these objects as individual Parent and Child objects, not a single ParentWithChildren object. Or at a minimum, I want to be able to pass around and modify Child objects on their own; if I get the Children every time I ask for the Parent, that's fine. The @validates decorator is largely useless for this purpose, as it validates a particular attribute of a particular class, and it gets called at the wrong time, and in the case of collections, only gets called on append events, not remove events (afaict). So if I do this with a SessionExtension.before_commit(), I would have to iterate through the new, dirty, and deleted instances lists, inspect the type of each instance, and do whatever is required. I am not sure, though, how to handle the case of a change in membership in the parent/child relationship -- the child instance that is present in the dirty list will have only the new parent on it -- how do I find out what the old parent was, so I can validate it? If a flush has already occurred, the old value is already lost in the context of the current transaction, and I think that if I open a new transaction inside a before_commit() validator I'm just asking for trouble. Do I need to instrument the Child class with a descriptor that tracks changes to the parent and remembers the old parent? Or can I set the cascade option in such a way that the old parent will end up in the dirty list, even though there are no changes to its underlying table, and in fact it may never have been explicitly loaded into the session? (I must admit to be somewhat unsure of what the different cascade options do -- but they don't seem to be useful for tracking something like this.) And lastly, what do I do inside before_commit() if I want to prevent the commit from proceeding? Do I just raise an exception? Any particular type of exception, or is it my choice? Sorry for the long question, and thanks for any assistance, Randall --~--~-~--~~~---~--~~ 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: Pre-commit validation spanning multiple tables/ORM classes
bojanb wrote: My issue with SQLA validators is that they don't allow inconsistent state even on fields of a single object, which makes multi-field validation impossible. meaning, you set A.a and you can't depend on A.b being correct yet ? Well sure. How would you have it done ? Something has to trigger the validate event at some point. So if you need to wait for all of A.a, A.b, A.c, etc. to be setup first, then sure you'd throw your validation into before_flush() or mapper extension before_insert()/before_update() - or just tailor your classes' interface as needed, such as A.set_values(a, b, c). Personally I opt for the latter since its simple and produces an immediate validation effect rather than waiting for a 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Pre-commit validation spanning multiple tables/ORM classes
meaning, you set A.a and you can't depend on A.b being correct yet ? Well sure. How would you have it done ? Something has to trigger the validate event at some point. So if you need to wait for all of A.a, A.b, A.c, etc. to be setup first, then sure you'd throw your validation into before_flush() or mapper extension before_insert()/before_update() - or just tailor your classes' interface as needed, such as A.set_values(a, b, c). Personally I opt for the latter since its simple and produces an immediate validation effect rather than waiting for a flush. What I meant to say is that validators don't fit well with something that I'm attempting to do. I'll probably implement a little validation framework that will do validations just before a flush. I'll be sure to post it here if its interesting enough! --~--~-~--~~~---~--~~ 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: SqlAlchemy results differ from its generated SQL
On Sep 22, 2009, at 11:09 AM, Mike Driscoll wrote: Hi, When I run a simple query like this: qry = session.query(CheckHistory) qry = qry.filter(and_(CheckHistory.CHECK_DATE = '1/1/2007', CheckHistory.CHECK_DATE = '1/1/2008')) res = qry.all() I get one CheckHistory row and a None. If I run the echoed SQL code in my database's query analyzer, I get almost 5000 results. Last Friday, this worked just fine. However, over the weekend, we upgraded from MS SQL Server 2000 to 2005. I don't see how this could have messed up SqlAlchemy, but something weird is going on here. Does anyone have any hints for troubleshooting this? I am using SqlAlchemy 0.5.6 on Windows XP with Python 2.5. turn on echo='debug' on your engine and watch the SQL statements and results. Also query(SomeClass).criterion.all() is not capable of returning None within the result list - it only returns entities or an empty list. --~--~-~--~~~---~--~~ 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: object serializer
On Sep 22, 2009, at 8:34 AM, Julien Cigar wrote: Hello, I'm busy to make a kind of mapper exporter/serializer. My goal is to be able to serialize (in JSON, XML, ...) any Mapper object (including relations, etc) At the moment I've the following code (not finished at all) : http://www.pastie.org/625787 As you can see I'm iterating over mapper.iterate_properties list (line 60), and before I goes any further I wondered if it's the right way to do it ? (I would like to have all the properties, including column type, etc) its likely the best way at the moment. Another question: I see that the property columns of an sqlalchemy.orm.properties.ColumnProperty instance is a list and I wondered in which cases the list can contains more than one item ? a single attribute can be mapped to multiple columns explicitly so that both columns will always have the same value (see the docs on mapping to joins), and its also the case when using composite properties. --~--~-~--~~~---~--~~ 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: Inheritance + Pseudo-adjacency-list?
On Sep 22, 2009, at 11:59 AM, Kevin H wrote:
I'm having some trouble developing my model, and was hoping someone on
this list could help...
Here's what I want:
A BizEntity object
A Person and Company object (both descended from BizEntity, using
joined table inheritance)
A Company.employees attribute, which points to a list of Persons who
work for the company
A Person.company attribute, which points back to the company that
person works for
Whenever I try to combine inheritance with this sort of pseudo-
adjacency-list, I get really odd things happening when I try to query
from the tables...like getting the wrong company back when I query by
id.
Any ideas out there? Anyone done something like this?
I'm doing this. The first thing to do is to definitely be on 0.5.6
at the least.
the next thing is to define the employees/company thing only once, as
a relation/backref pair on just one of your mapped classes. doing it
twice will mess things up for sure.
your example also mentions a table called nodes which from
everything else mentioned below would be erroneous. you don't need
remote_side when mapping between Company and Person.
None of this would cause the wrong Company to come back from a
simple query by id, though. If that is really the effect you're
seeing then something more fundamental might be amiss.
MODEL (so far):
(NOTE: the commented out lines are left over from some of my previous
attempts to get things working.)
class BizEntity(Base):
__tablename__ = 'biz_entities'
id = Column('bizentity_id', Integer, primary_key=True)
type = Column('bizentity_type', String(30), nullable=False)
__mapper_args__ = {'polymorphic_on': type}
class Company(BizEntity):
__tablename__ = 'companies'
id = Column(Integer, ForeignKey('biz_entities.bizentity_id'),
primary_key=True)
name = Column('company_name', String(50))
#~ employees = relation(Person, backref=backref(company,
remote_side=[])
#~ backref('parent', remote_side=[nodes.c.id])
__mapper_args__ = {'polymorphic_identity': 'company'}
class Person(BizEntity):
__tablename__ = 'people'
id = Column('bizentity_id', Integer, ForeignKey
('biz_entities.bizentity_id'), primary_key=True)
first_name = Column('first_name', String(50))
middle_init = Column('middle_init', String(1))
last_name = Column('last_name', String(50))
#~ company = relation(Company, backref=backref('employees',
order_by=id))
__mapper_args__ = {'polymorphic_identity':'person'}
--~--~-~--~~~---~--~~
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: Beginner inheritance question
On Sep 22, 2009, at 3:42 AM, Iwan wrote: Hi there, this is perhaps a stupid question, but we can't get it figured out from the docs on inheritance... Is it possible in sqlalchemy to have a hierarchy of classes of which only the root class in the hierarchy is mapped to the database at all. (So the subclasses do not have any persisted attributes of their own, and are all in essence mapped to the same table.) I suppose single inheritance would work here, but I'd like to keep open the option of one day adding one special subclass which DOES in fact want to persist extra attributes - this time using joined table inheritance. Is something (or a mix) like this possible? it is absolutely possible. Any subclass mapper that does not have a table attribute will map as single table inheritance to the superclass. Any mapper in the inheritance hierarchy that does declare table will be mapped to a join of that table against the parent's mapped table (or join). so single/joined can be mixed in any combination. It is possible that there would be some query idiosyncrasies when switching a class from single to joined or vice versa - such as if you filter on a subclass attribute, joined table inheritance would require such features as with_polymorphic or of_type (or querying among the underlying tables directly, something I do often), whereas single table inheritance would be more straightforward. So while switching is not that hard, its also not 100% transparent either. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
