Re: [sqlalchemy] association_proxy

[Jonathan Vanasco]

On Monday, March 16, 2020 at 12:31:09 PM UTC-4, Mike Bayer wrote:
>
> I sometimes get a "moderators spam report" for SQLAlchemy and then I know 
> I have to go to the admin interface on the website.   I likely approve them 
> really quick before you see them.   as far as originals missing i dont know 
> where to go for that.
>
>
Maybe you are replying before approving? This is just so odd - i've looked 
through the admin interface many times and can't find them.  I'll ask a 
google friend if they know how this happens.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/6f34d835-0804-4b4a-9a1c-ec94da25f796%40googlegroups.com.

Re: [sqlalchemy] association_proxy

[Mike Bayer]

I sometimes get a "moderators spam report" for SQLAlchemy and then I know I 
have to go to the admin interface on the website. I likely approve them really 
quick before you see them. as far as originals missing i dont know where to go 
for that.


On Mon, Mar 16, 2020, at 12:13 PM, Jonathan Vanasco wrote:
> Mike-
> 
> where do messages like these come from? The approval queue? The originals are 
> often missing. I looked in the admin and didn't see them pending either.
> 

> --
>  SQLAlchemy - 
>  The Python SQL Toolkit and Object Relational Mapper
> 
> http://www.sqlalchemy.org/
> 
>  To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> description.
>  --- 
>  You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
>  To unsubscribe from this group and stop receiving emails from it, send an 
> email to sqlalchemy+unsubscr...@googlegroups.com.
>  To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/ec15f94b-626b-458c-8ae0-10062aeeadcf%40googlegroups.com
>  
> .

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/91020909-1b01-41ed-b955-dfdeba5841a7%40www.fastmail.com.

Re: [sqlalchemy] association_proxy

[Jonathan Vanasco]

Mike-

where do messages like these come from? The approval queue?  The originals 
are often missing.  I looked in the admin and didn't see them pending 
either.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/ec15f94b-626b-458c-8ae0-10062aeeadcf%40googlegroups.com.

Re: [sqlalchemy] association_proxy

[Mike Bayer]

On Wed, Mar 11, 2020, at 1:50 PM, Damian Yurzola wrote:
> Folks:
> 
> I'm trying to achieve the following.
> I have a legacy table (Parent), which has a good foreign key to a tiny table 
> that complements it (Child).
> 
> For the use case, querying Parent without joining with Child is not 
> meaningful.
> 
> class Child(Base):
>  __tablename__ = "child"
>  id = Column(Integer, primary_key=True, unique=True)
>  name = Column(String)
> 
> 
> class Parent(Base):
>  __tablename__ = "parent"
>  id = Column(Integer, primary_key=True)
>  child_id = Column(Integer, ForeignKey(Child.id), nullable=False,)
> 
> @declared_attr
> def child(self):
> return relationship("Child", uselist=False, lazy="joined")
> 
> @declared_attr
> def child_name(self):
> return association_proxy("child", "name")
> 
> @hybrid_property
> def child_name(self):
> return self.child.name if self.child else None
> 
> @child_name.expression
> def child_name(cls):
> return case([(cls.child, Child.name),], else_=None)
> 
> @declared_attr
> def child_name_2(self):
> return association_proxy("child", "name")
> 
> When I query Parent, I know I get the right python objects
> 
> q = Query([Parent])
> 
> SELECT parent.id AS parent_id, parent.child_id AS parent_child_id, child_1.id 
> AS child_1_id, child_1.name AS child_1_name 
> FROM parent LEFT OUTER JOIN child AS child_1 ON child_1.id = parent.child_id
> 
> Yielding the right answer
> 
> <__main__.Parent object at 0x7f58ac978f50>
> <__main__.Parent object at 0x7f58ac978fd0>
> <__main__.Parent object at 0x7f58ac987110>
> <__main__.Parent object at 0x7f58ac987250>
> <__main__.Parent object at 0x7f58ac987390>
> <__main__.Parent object at 0x7f58ac987610>
> <__main__.Parent object at 0x7f58ac9876d0>
> <__main__.Parent object at 0x7f58ac987850>
> <__main__.Parent object at 0x7f58ac9879d0>
> <__main__.Parent object at 0x7f58ac987b10>
> 
> 
> But somehow when I query more:
> 
> q = Query([Parent, Parent.child_name])
> 
> The join condition gets pushed one level out... leaving '

the Query will never add a JOIN condition automatically that is available for 
querying. you're trying to take advantage of the "eager" left outer join which 
is not something you can access directly. See 
https://docs.sqlalchemy.org/en/13/orm/loading_relationships.html#the-zen-of-joined-eager-loading
 for background on this.

to get the Parent object back plus "child_name" in the tuple that's directly 
returned by Query, explicit is the only way to do it:

q = s.query(Parent, Parent.child_name).join(Parent.child)

I'm not sure how pandas works here but since Parent.child_name is available, to 
make use of the eager loading you would simply send it a generator expression:

rows = ((parent, parent.child_name) for parent in s.query(Parent))




> 
> SELECT parent.id AS parent_id, parent.child_id AS parent_child_id, CASE WHEN 
> (child.id = parent.child_id) THEN child.name END AS child_name, child_1.id AS 
> child_1_id, child_1.name AS child_1_name 
> FROM child, parent LEFT OUTER JOIN child AS child_1 ON child_1.id = 
> parent.child_id
> 
> Implying an additional unfiltered join between the childe and the correct 
> filtered version.
> 
> (<__main__.Parent object at 0x7f947749c3d0>, None)
> (<__main__.Parent object at 0x7f947749c450>, None)
> (<__main__.Parent object at 0x7f947749c550>, None)
> (<__main__.Parent object at 0x7f947749c650>, None)
> (<__main__.Parent object at 0x7f947749c750>, None)
> (<__main__.Parent object at 0x7f947749c950>, None)
> (<__main__.Parent object at 0x7f947749ca10>, None)
> (<__main__.Parent object at 0x7f947749cb90>, None)
> (<__main__.Parent object at 0x7f947749cd10>, None)
> (<__main__.Parent object at 0x7f947749ce50>, None)
> (<__main__.Parent object at 0x7f947749c3d0>, '0')
> (<__main__.Parent object at 0x7f947749c450>, '1')
> (<__main__.Parent object at 0x7f947749c550>, '2')
> (<__main__.Parent object at 0x7f947749c650>, '3')
> (<__main__.Parent object at 0x7f947749c750>, '4')
> (<__main__.Parent object at 0x7f947749c950>, '5')
> (<__main__.Parent object at 0x7f947749ca10>, '6')
> (<__main__.Parent object at 0x7f947749cb90>, '7')
> (<__main__.Parent object at 0x7f947749cd10>, '8')
> (<__main__.Parent object at 0x7f947749ce50>, '9')
> 
> I think this might be implied by the Query rather than by the relationship.
> 
> However I want my users to be able to Query Parent directly without needing 
> to know that under the hood, Parents need joins with Child to be meaningful.
> Why do you ask?
> 
> Because we need the SQL statement to be complete and not rely on python 
> objects as the query will be actually run by Pandas.
> 
> 
> Am I going about this the wrong way?
> 
> Thanks!!!
> 

> --
>  SQLAlchemy - 
>  The Python SQL Toolkit and Object Relational Mapper
> 
> http://www.sqlalchemy.org/
> 
>  To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> description.
>  --- 
>  You received this message because you 

[sqlalchemy] association_proxy

[Damian Yurzola]

Folks:

I'm trying to achieve the following.
I have a legacy table (Parent), which has a good foreign key to a tiny 
table that complements it (Child).

For the use case, querying Parent without joining with Child is not 
meaningful.

class Child(Base):
__tablename__ = "child"
id = Column(Integer, primary_key=True, unique=True)
name = Column(String)


class Parent(Base):
__tablename__ = "parent"
id = Column(Integer, primary_key=True)
child_id = Column(Integer, ForeignKey(Child.id), nullable=False,)

@declared_attr
def child(self):
return relationship("Child", uselist=False, lazy="joined")

@declared_attr
def child_name(self):
return association_proxy("child", "name")

@hybrid_property
def child_name(self):
return self.child.name if self.child else None

@child_name.expression
def child_name(cls):
return case([(cls.child, Child.name),], else_=None)

@declared_attr
def child_name_2(self):
return association_proxy("child", "name")

When I query Parent, I know I get the right python objects

q = Query([Parent])

SELECT parent.id AS parent_id, parent.child_id AS parent_child_id, child_1.id 
AS child_1_id, child_1.name AS child_1_name 
FROM parent LEFT OUTER JOIN child AS child_1 ON child_1.id = parent.child_id

Yielding the right answer

<__main__.Parent object at 0x7f58ac978f50>
<__main__.Parent object at 0x7f58ac978fd0>
<__main__.Parent object at 0x7f58ac987110>
<__main__.Parent object at 0x7f58ac987250>
<__main__.Parent object at 0x7f58ac987390>
<__main__.Parent object at 0x7f58ac987610>
<__main__.Parent object at 0x7f58ac9876d0>
<__main__.Parent object at 0x7f58ac987850>
<__main__.Parent object at 0x7f58ac9879d0>
<__main__.Parent object at 0x7f58ac987b10>


But somehow when I query more:

q = Query([Parent, Parent.child_name])

The join condition gets pushed one level out... leaving 

SELECT parent.id AS parent_id, parent.child_id AS parent_child_id, CASE 
WHEN (child.id = parent.child_id) THEN child.name END AS child_name, child_1
.id AS child_1_id, child_1.name AS child_1_name 
FROM child, parent LEFT OUTER JOIN child AS child_1 ON child_1.id = parent.
child_id

Implying an additional unfiltered join between the childe and the correct 
filtered version.

(<__main__.Parent object at 0x7f947749c3d0>, None)
(<__main__.Parent object at 0x7f947749c450>, None)
(<__main__.Parent object at 0x7f947749c550>, None)
(<__main__.Parent object at 0x7f947749c650>, None)
(<__main__.Parent object at 0x7f947749c750>, None)
(<__main__.Parent object at 0x7f947749c950>, None)
(<__main__.Parent object at 0x7f947749ca10>, None)
(<__main__.Parent object at 0x7f947749cb90>, None)
(<__main__.Parent object at 0x7f947749cd10>, None)
(<__main__.Parent object at 0x7f947749ce50>, None)
(<__main__.Parent object at 0x7f947749c3d0>, '0')
(<__main__.Parent object at 0x7f947749c450>, '1')
(<__main__.Parent object at 0x7f947749c550>, '2')
(<__main__.Parent object at 0x7f947749c650>, '3')
(<__main__.Parent object at 0x7f947749c750>, '4')
(<__main__.Parent object at 0x7f947749c950>, '5')
(<__main__.Parent object at 0x7f947749ca10>, '6')
(<__main__.Parent object at 0x7f947749cb90>, '7')
(<__main__.Parent object at 0x7f947749cd10>, '8')
(<__main__.Parent object at 0x7f947749ce50>, '9')

I think this might be implied by the Query rather than by the relationship.

However I want my users to be able to Query Parent directly without needing 
to know that under the hood, Parents need joins with Child to be meaningful.
Why do you ask?

Because we need the SQL statement to be complete and not rely on python 
objects as the query will be actually run by Pandas.


Am I going about this the wrong way?

Thanks!!!

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/5e701ca2-7e4b-4e70-9569-524a62f897f4%40googlegroups.com.

[sqlalchemy] association_proxy usage in non-declarative style

[Phazorx]

Hello, i am trying to figure out how association_proxy() could be used
in case of  regular rather than declarative style definitions. I
can't figure out what can be done to mitigate the issue and hence i
seek help here.

Thanks in advance...

The code below is copy/pasted sample from the official docs without
declarative part and when run outputs this:

Traceback (most recent call last):
  File d.py, line 36, in module
'keywords': association_proxy('kw', 'keyword')
  File /usr/local/python/3.2/lib/python3.2/site-packages/
SQLAlchemy-0.7.4-py3.2.egg/sqlalchemy/orm/__init__.py, line 1114, in
mapper
return Mapper(class_, local_table, *args, **params)
  File /usr/local/python/3.2/lib/python3.2/site-packages/
SQLAlchemy-0.7.4-py3.2.egg/sqlalchemy/orm/mapper.py, line 201, in
__init__
self._configure_properties()
  File /usr/local/python/3.2/lib/python3.2/site-packages/
SQLAlchemy-0.7.4-py3.2.egg/sqlalchemy/orm/mapper.py, line 818, in
_configure_properties
self._configure_property(key, prop, False)
  File /usr/local/python/3.2/lib/python3.2/site-packages/
SQLAlchemy-0.7.4-py3.2.egg/sqlalchemy/orm/mapper.py, line 1016, in
_configure_property
% (key, prop))
sqlalchemy.exc.ArgumentError:
keywords=sqlalchemy.ext.associationproxy.AssociationProxy object at
0x34ad190 is not an instance of MapperProperty or Column



#!/usr/bin/env python3
# -*- coding: utf-8 -*-

from sqlalchemy import Column, Integer, String, ForeignKey, Table
from sqlalchemy.orm import relationship, mapper
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.schema import MetaData

metadata = MetaData()

class User():
def __init__(self, name):
self.name = name

class Keyword():
def __init__(self, keyword):
self.keyword = keyword

user_table = Table('user', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(64))
)

keyword_table = Table('keyword', metadata,
Column('id', Integer, primary_key=True),
Column('keyword', String(64))
)

userkeywords_table = Table('userkeywords', metadata,
Column('user_id', Integer, ForeignKey(user.id)),
Column('keyword_id', Integer, ForeignKey(keyword.id))
)

mapper(Keyword, keyword_table)

mapper(User, user_table,
properties={
'kw': relationship(Keyword, secondary=userkeywords_table),
'keywords': association_proxy('kw', 'keyword')
})

user = User('jek')
user.keywords.append('cheese inspector')
print (user.keywords)
user.keywords.append('snack ninja')
print (user.kw)

-- 
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] association_proxy introspection

[sandro dentella]

Hi,

i just discovered association_proxy and like it very much. It
definetely helps in some situations. I already have a library that
setup gui instrospecting the mapper, to allow editing and filtering of
records.

the only way I found to get the association_proxy of a class is
checking its attributes::

for key, value in vars(d.t.mapper.class_).iteritems():
if isinstance(value, AssociationProxy):
print key

I can't find a way to get information on the column it is proxying,
that would be needed to me in order to setup a proper representation:
how can I find it?

I see there's no property on the mapper, corresponding to an
association_proxy, is it possible to instrospect the mapper and see
get some info?

thanks in advance
sandro
*:-)

-- 
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] association_proxy as property?

[A . M .]

Hello,

To generate json from our SQLAlchemy model objects, we are using 
iterate_properties to determine how to dictify the object. One of our objects 
uses association_proxy which we would like to represent in the JSON. 
Unfortunately, because it is not a property, the dictification misses this 
property. I feel like I am missing something simple here.

How can I make an association_proxy appear to be a property which appears in 
iterate_properties of the mapper?

Cheers,
M


-- 
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] association_proxy as property?

[Michael Bayer]

On Nov 16, 2010, at 11:14 AM, A.M. wrote:

 Hello,
 
 To generate json from our SQLAlchemy model objects, we are using 
 iterate_properties to determine how to dictify the object. One of our 
 objects uses association_proxy which we would like to represent in the JSON. 
 Unfortunately, because it is not a property, the dictification misses this 
 property. I feel like I am missing something simple here.
 
 How can I make an association_proxy appear to be a property which appears in 
 iterate_properties of the mapper?

1. why not use dir(myobject) to get a view of your object as a whole ?
2. implicit conversion to JSON and such is a little sloppy.   You'd be better 
off using a structured approach like Colander: http://docs.repoze.org/colander/


-- 
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] association_proxy as property?

[A.M.]

On Nov 16, 2010, at 11:43 AM, Michael Bayer wrote:

 
 On Nov 16, 2010, at 11:14 AM, A.M. wrote:
 
 Hello,
 
 To generate json from our SQLAlchemy model objects, we are using 
 iterate_properties to determine how to dictify the object. One of our 
 objects uses association_proxy which we would like to represent in the JSON. 
 Unfortunately, because it is not a property, the dictification misses this 
 property. I feel like I am missing something simple here.
 
 How can I make an association_proxy appear to be a property which appears in 
 iterate_properties of the mapper?
 
 1. why not use dir(myobject) to get a view of your object as a whole ?

We swiped dictify from sprox.saormprovider:
http://bitbucket.org/percious/sprox/src/tip/sprox/saormprovider.py#cl-321

We like the behavior that a REST call to retrieve an SQLAlchemy object returns 
pks for relationships (instead of loading the entire result hierarchy), so 
subsequent REST calls are required to dig deeper into the tree of results.

Using dir() is definitely a good suggestion which I will try. You are probably 
right that we are using iterate_properties improperly.

 2. implicit conversion to JSON and such is a little sloppy.   You'd be better 
 off using a structured approach like Colander: 
 http://docs.repoze.org/colander/

It looks like I would have to either re-define all objects using the Colander 
syntax or implement a method which converts existing SQLAlchemy models to 
Colander schema objects. Even if the latter function already exists, I still 
have the problem of determining automatically which properties to encode, no?

Cheers,
M

-- 
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] association_proxy as property?

[jason kirtland]

On Tue, Nov 16, 2010 at 9:05 AM, A.M. age...@themactionfaction.com wrote:

 On Nov 16, 2010, at 11:43 AM, Michael Bayer wrote:


 On Nov 16, 2010, at 11:14 AM, A.M. wrote:
 To generate json from our SQLAlchemy model objects, we are using 
 iterate_properties to determine how to dictify the object. One of our 
 objects uses association_proxy which we would like to represent in the 
 JSON. Unfortunately, because it is not a property, the dictification misses 
 this property. I feel like I am missing something simple here.

 How can I make an association_proxy appear to be a property which appears 
 in iterate_properties of the mapper?

 2. implicit conversion to JSON and such is a little sloppy.   You'd be 
 better off using a structured approach like Colander: 
 http://docs.repoze.org/colander/

 It looks like I would have to either re-define all objects using the Colander 
 syntax or implement a method which converts existing SQLAlchemy models to 
 Colander schema objects. Even if the latter function already exists, I still 
 have the problem of determining automatically which properties to encode, no?

You may find you'll need to do even further work to determine which
properties to encode.  I do the same (using Flatland for
serialization), and part of that challenge was determining where the
edges of the business objects were.  (If you have relations, maybe
some of them are part of the object (as user's email addresses) and
some of them aren't (a User-Users list of the user's friends). In the
end I went with a combination of class annotation and heuristics based
on iterating mapper properties.  This allowed me to traverse the
mappings to reliably find the edges and also include the occasional
transient attribute or other oddball that needed to be in the
serialized form.

-- 
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] association_proxy as property?

[Michael Bayer]

On Nov 16, 2010, at 12:05 PM, A.M. wrote:

 
 2. implicit conversion to JSON and such is a little sloppy.   You'd be 
 better off using a structured approach like Colander: 
 http://docs.repoze.org/colander/
 
 It looks like I would have to either re-define all objects using the Colander 
 syntax or implement a method which converts existing SQLAlchemy models to 
 Colander schema objects. Even if the latter function already exists, I still 
 have the problem of determining automatically which properties to encode, no?

Jek's suggestion is good here, also in my experience when I need to use JSON 
for something, it implies I'm communicating with some other system that doesn't 
know about my mapped objects - meaning, even if the JSON structure is initially 
driven by the ORM completely, I need the JSON structure to remain independent 
of changes in the ORM model in any case.   But usually I'm dealing with a 
service that looks dramatically different from my ORM models anyway.   Its 
again the same dichotomy in SQLAlchemy itself, your json messages are not your 
domain objects ;)

I haven't used Colander and instead have something homegrown - a core feature 
is that it maintains a mapping of object attributes, in some cases 
dot-separated paths, to field names that would be generated in JSON or XML.   I 
also use it to translate against .csv and .xls formats.   Most apps I work on 
end up that way, so I go straight to explicit mappings up front since I know 
the variability is going to be great.


-- 
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] association_proxy problem

[DimonB]

Hi!

When trying to remove element from association_proxy exception occure.
Here is code example:

## CODE EXAMPLE #
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base,
DeclarativeMeta
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.orm import scoped_session, sessionmaker, relation,
backref

engine = create_engine('sqlite://')
meta = MetaData()
meta.bind = engine

class Base(declarative_base(metadata=meta)):
_decl_class_registry = {}
def __init__(self, *args, **kwargs):
for key, val in kwargs.iteritems():
self.__setattr__(key, val)

class Client(Base):
__tablename__ = 'client'
id = Column('id', Integer, primary_key=True)
name = Column('name', String(50))
groups = association_proxy('client_group', 'group', creator=lambda
x: ClientGroup(client=x))

class Group(Base):
__tablename__ = 'group'
id = Column('id', Integer, primary_key=True)
name = Column('name', String(50))
clients = association_proxy('client_group', 'client',
creator=lambda x: GroupClient(client=x))

class GroupClient(Base):
__tablename__ = 'clientgroup'
client_id = Column('client_id', Integer, ForeignKey('client.id'),
primary_key = True)
group_id = Column('group_id', Integer, ForeignKey('group.id'),
primary_key = True)
client = relation(Client, backref = backref('client_group'))
group = relation(Group, backref = backref('client_group'))

meta.create_all()

session = scoped_session(sessionmaker(bind=engine))

clnt = Client(name='test')
session.add(clnt)
grp = Group(name='tg')
session.add(grp)

grp.clients.append(clnt)

#session.flush()


#grp.clients.remove(clnt)
clnt.groups.remove(grp)
session.flush()

#

-- 
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] association_proxy problem

[Michael Bayer]

thanks for sending a nice, succinct example...very rare these days.   Use this:

class GroupClient(Base):
   __tablename__ = 'clientgroup'
   
   client_id = Column('client_id', Integer, ForeignKey('client.id'),primary_key 
= True)
   group_id = Column('group_id', Integer, ForeignKey('group.id'), primary_key = 
True)
   client = relation(Client, backref = backref('client_group', cascade='all, 
delete-orphan'))
   group = relation(Group, backref = backref('client_group'))




On Jul 28, 2010, at 9:00 AM, DimonB wrote:

 from sqlalchemy import *
 from sqlalchemy.ext.declarative import declarative_base,
 DeclarativeMeta
 from sqlalchemy.ext.associationproxy import association_proxy
 from sqlalchemy.orm import scoped_session, sessionmaker, relation,
 backref
 
 engine = create_engine('sqlite://')
 meta = MetaData()
 meta.bind = engine
 
 class Base(declarative_base(metadata=meta)):
_decl_class_registry = {}
def __init__(self, *args, **kwargs):
for key, val in kwargs.iteritems():
self.__setattr__(key, val)
 
 class Client(Base):
__tablename__ = 'client'
id = Column('id', Integer, primary_key=True)
name = Column('name', String(50))
groups = association_proxy('client_group', 'group', creator=lambda
 x: ClientGroup(client=x))
 
 class Group(Base):
__tablename__ = 'group'
id = Column('id', Integer, primary_key=True)
name = Column('name', String(50))
clients = association_proxy('client_group', 'client',
 creator=lambda x: GroupClient(client=x))
 
 class GroupClient(Base):
__tablename__ = 'clientgroup'
client_id = Column('client_id', Integer, ForeignKey('client.id'),
 primary_key = True)
group_id = Column('group_id', Integer, ForeignKey('group.id'),
 primary_key = True)
client = relation(Client, backref = backref('client_group'))
group = relation(Group, backref = backref('client_group'))
 
 meta.create_all()
 
 session = scoped_session(sessionmaker(bind=engine))
 
 clnt = Client(name='test')
 session.add(clnt)
 grp = Group(name='tg')
 session.add(grp)
 
 grp.clients.append(clnt)
 
 #session.flush()
 
 
 #grp.clients.remove(clnt)
 clnt.groups.remove(grp)
 session.flush()

-- 
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] association_proxy question... I think

[Sergey V.]

Hi all,

I've got two tables: Users (id, password) and UserProperties (user_id,
name, value). Is there a way to map the properties stored in
UserProperties as attributes of User object?

I mean,

john = User('john', 'password')
john.name = John Smith # creates UserProperty('john', 'name', 'John
Smith')

The set of possible attributes is fixed so I don't mind hard-wiring
them to the User object one by one, something like that:

class User(...):
   ...
   name = some_magic_function(UserProperty, 'name')
   address = some_magic_function(UserProperty, 'address')

I've read on association_proxy but couldn't figure out how to use it
in this case.

Thanks,

--

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] association_proxy question... I think

[marten luter]

See examples in sqlalchemy named dictlike.py und another 2 .

here there are for example
http://www.google.com/codesearch/p?hl=en#lIxjJFXSjns/trunk/code/prereq/SQLAlchemy-0.4.8/examples/vertical/dictlike.pyq=VerticalPropertyDictMixinsa=Ncd=1ct=rc

On Wed, Dec 2, 2009 at 10:30 AM, Sergey V. sergey.volob...@gmail.comwrote:

 Hi all,

 I've got two tables: Users (id, password) and UserProperties (user_id,
 name, value). Is there a way to map the properties stored in
 UserProperties as attributes of User object?

 I mean,

 john = User('john', 'password')
 john.name = John Smith # creates UserProperty('john', 'name', 'John
 Smith')

 The set of possible attributes is fixed so I don't mind hard-wiring
 them to the User object one by one, something like that:

 class User(...):
   ...
   name = some_magic_function(UserProperty, 'name')
   address = some_magic_function(UserProperty, 'address')

 I've read on association_proxy but couldn't figure out how to use it
 in this case.

 Thanks,

 --

 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.comsqlalchemy%2bunsubscr...@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] association_proxy only returning one object instead of a list or group of objects

[SmokeyD]

Hey everyone,

I am running into a problem with association_proxy attributes in
SQLAlchemy 0.5 and python 2.6.
I pasted my model (a simplified version) all the way below.
My model has a Permission object, Project object and a Group object,
and it uses an AccessRule object to grant a permission to a group on a
specific project. To test everything I populated my database with one
permission instance, one project instance and two group instances. I
added the permission object for both groups to the project object.
Up to the last line of the interactive shell which illustrates the
problem, everything works fine. But when I call project.groups
[permission] I don't get a collection of groups which have the
specific permission on the project, I just get one group (the last one
that was granted the permission).
Does anyone have a clue how I can get all the groups from a project
which have a certain permission?
Cheers,

Dolf.

=== python shell ==
 a=session.query(model.AccessRule)
 print (a[0].group_id,a[0].project_id,a[0].permission_id)
(1L, 1L, 1L)
 print (a[1].group_id,a[1].project_id,a[1].permission_id)
(2L, 1L, 1L)
 manage=session.query(model.Permission).get(1)
 manage.permission_name
u'manage'
 manage.permission_id
1L
 g1=s.query(model.Group).get(1)
 g1.group_id
1L
 g2=s.query(model.Group).get(2)
 g2.group_id
2L
 pr.permissions[g1]
Permission: name=manage
 pr.permissions[g2]
Permission: name=manage
 pr.groups[manage]
Group: name=users

=== model.py
==
def _create_access_rule(group,project,permission):
return AccessRule(group,project,permission)

class AccessRule(DeclarativeBase):
__tablename__ = 'tg_access_rule'
def __init__(self,group,project,permission):
self.group=group
self.project=project
self.permission=permission
group_id=Column(Integer,ForeignKey
(tg_group.group_id),primary_key=True)
project_id=Column(Integer,ForeignKey
(project.project_id),primary_key=True)
permission_id=Column(Integer,ForeignKey
(tg_permission.permission_id),primary_key=True)

class Group(DeclarativeBase):
__tablename__ = 'tg_group'
group_id = Column(Integer, autoincrement=True, primary_key=True)
group_name = Column(Unicode(16), unique=True, nullable=False)
permissions_by_project = relation
(AccessRule,collection_class=attribute_mapped_collection
('project'),backref='group')
permissions=association_proxy
('permissions_by_project','permission',creator=_create_access_rule)

class Permission(DeclarativeBase):
__tablename__ = 'tg_permission'
permission_id = Column(Integer, autoincrement=True,
primary_key=True)
permission_name = Column(Unicode(16), unique=True, nullable=False)
description = Column(Unicode(255))
groups_by_project = relation
(AccessRule,collection_class=attribute_mapped_collection
('project'),backref='permission')
groups=association_proxy
('groups_by_project','group',creator=_create_access_rule)

class Project(DeclarativeBase):
__tablename__ = 'project'
project_id = Column(Integer, primary_key=True)
project_name = Column(Unicode(255), nullable=False)
permissions_by_group = relation
(AccessRule,collection_class=attribute_mapped_collection
('group'),backref='project')
permissions=association_proxy
('permissions_by_group','permission',creator=_create_access_rule)
groups_by_permission = relation
(AccessRule,collection_class=attribute_mapped_collection
('permission'))
groups=association_proxy
('groups_by_permission','group',creator=_create_access_rule)


--~--~-~--~~~---~--~~
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] association_proxy with association object - declarative

[GHZ]

I tried to use the example from:
http://www.sqlalchemy.org/docs/05/reference/ext/associationproxy.html#simplifying-association-object-relations

But with declarative syntax.  Any idea why this is going wrong?


from sqlalchemy import Table, Column, Integer, String, MetaData,
ForeignKey, Sequence, create_engine
from sqlalchemy.orm import relation, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.associationproxy import association_proxy

engine = create_engine('sqlite:///:memory:', echo=True)

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

Base = declarative_base()

def _create_uk_by_keyword(keyword):
A creator function.
We expect keywords to already be in the DB.. therefore, just
search and return the existing keyword
return session.query(Keyword).filter_by(keyword=keyword).one()

class User(Base):

__tablename__ = 'users'

id = Column(Integer, primary_key=True)
name = Column(String)

keywords = association_proxy('user_keywords', 'keyword',
creator=_create_uk_by_keyword)

class Keyword(Base):

__tablename__ = 'keywords'

id = Column(Integer, primary_key=True)
keyword = Column(String)

class UserKeyword(Base):

__tablename__ = 'userkeywords'

id = Column(Integer, primary_key=True)

user_id = Column(Integer, ForeignKey(users.id))
keyword_id = Column(Integer, ForeignKey(keywords.id))

user = relation(User, backref='user_keywords')
keyword = relation(Keyword)


Base.metadata.create_all(engine)

kw = Keyword(keyword='kw 1')
session.add(kw)
session.flush()

user = User(name='bob')
user.keywords=['kw 1']


which gives me:


user.keywords=['kw 1']
  File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.3-
py2.6.egg/sqlalchemy/ext/associationproxy.py, line 207, in __set__
self._set(proxy, values)
  File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.3-
py2.6.egg/sqlalchemy/ext/associationproxy.py, line 259, in _set
proxy.extend(values)
  File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.3-
py2.6.egg/sqlalchemy/ext/associationproxy.py, line 394, in extend
self.append(v)
  File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.3-
py2.6.egg/sqlalchemy/ext/associationproxy.py, line 386, in append
self.col.append(item)
  File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.3-
py2.6.egg/sqlalchemy/orm/collections.py, line 909, in append
item = __set(self, item, _sa_initiator)
  File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.3-
py2.6.egg/sqlalchemy/orm/collections.py, line 884, in __set
item = getattr(executor, 'fire_append_event')(item, _sa_initiator)
  File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.3-
py2.6.egg/sqlalchemy/orm/collections.py, line 581, in
fire_append_event
return self.attr.fire_append_event(self.owner_state, item,
initiator)
  File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.3-
py2.6.egg/sqlalchemy/orm/attributes.py, line 629, in
fire_append_event
value = ext.append(state, value, initiator or self)
  File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.3-
py2.6.egg/sqlalchemy/orm/attributes.py, line 815, in append
child_state.get_impl(self.key).append(child_state, state.obj(),
initiator, passive=PASSIVE_NO_CALLABLES)
  File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.3-
py2.6.egg/sqlalchemy/orm/attributes.py, line 900, in get_impl
return self.manager.get_impl(key)
  File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.3-
py2.6.egg/sqlalchemy/orm/attributes.py, line 1317, in get_impl
return self[key].impl
KeyError: 'user'


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] association_proxy question

[GHZ]

I am trying to use the association_proxy for attributes that link to
tables containing mostly static data

e.g. my static data is:
COUNTRY
COUNTRY.CODE
COUNTRY.NAME

and the data I am changing is:
USER
USER.COUNTRY_CODE

I use the association_proxy as I want to be able to say:
user = User()
user.country_name = 'DENMARK'
#rather than  user.country_cde = 'DK'

In class User()   (declarative)
I have:
country_code = Column(String, ForeignKey('COUNTRY.CODE'))
country = relation('Country', uselist=False)
country_name = association_proxy('country', 'name',
creator=my_creator) #proxy to COUNTRY.NAME

where:
def my_creator(country_name):
country = session.query(Country).filter_by(name=country_name).one
()


i.e. I will only link to existing countries.. and not add a new one.

Is there a better way to do this?

In this instance I can create a new session in my_creator, but I would
really want to be using the same session, incase I have added, deleted
countries inside the same transaction.  Making the session global
doesn't seem right.

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] association_proxy import fail

[Vortexmind]

Hi all
I'm new to python and was playing around with Elixir tutorial (a
wrapper for Sqlalchemy).
I get a failed import with this code

from elixir import *

metadata.bind = sqlite:///movies.sqlite
metadata.bind.echo = True

class Movie(Entity):
title = Field(Unicode(30))
year = Field(Integer)
description = Field(Unicode)

def __repr__(self):
return 'Movie %s (%d)' % (self.title, self.year)


If I load it in a python shell (either python or ipython) I get this
error

ImportError: cannot import name association_proxy

from trace, this is the call that fails
from sqlalchemy.ext.associationproxy import association_proxy

I checked in the SqlAlchemy docs, and I found that it's a feature
present in version = 0.31
As I'm using gentoo linux, I've checked out and I have installed 0.37
so I should have it.

Any clue?



--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---