[sqlalchemy] self-referential many-to-many relationships and mixins
I am using python 3.3 and sqlalchemy 0.8.2
I am trying to define a self-referential many-to-many relationship for a
class where the primary key is provided by a mixin. Defining the primary
directly in the class works. Using the mixin does not.
I would be grateful for any suggestions or pointers to relevant
documentation.
Below is an example showing my problem. As given the example works.
Uncommenting the line #id = ... in 'Base' and commenting out the
corresponding line in 'A' breaks the example. Is there any way to define
the primary key in Base and getting the 'requires' relation to work?
---
from sqlalchemy import Column, Integer, String, DateTime, Table,
ForeignKey,create_engine
from sqlalchemy.ext.declarative import declarative_base,declared_attr
from sqlalchemy.orm import sessionmaker, relationship, backref
class Base(object):
#id = Column(Integer, primary_key=True)
pass
Base = declarative_base(cls=Base)
association_table = Table('association',
Base.metadata,
Column('prerequisite', Integer,
ForeignKey('a.id')),
Column('dependency', Integer, ForeignKey('a.id')))
class A(Base):
__tablename__ = a
id = Column(Integer, primary_key=True)
requires = relationship(A,
secondary = association_table,
primaryjoin=(id==association_table.c.prerequisite),
secondaryjoin=(id==association_table.c.dependency),
backref = backref(required_by))
if __name__ == __main__:
engine = create_engine('sqlite:///:memory:', echo=False)
Session = sessionmaker(bind=engine)
session = Session()
Base.metadata.create_all(engine)
T=A()
U=A()
session.add(T)
session.add(U)
T.requires.append(U)
session.commit()
print(T,T.id,T.requires,T.required_by)
print(U,U.id,U.requires,U.required_by)
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] self-referential many-to-many relationships and mixins
On Tue, Aug 13, 2013 at 2:07 PM, till.plewe till.pl...@gmail.com wrote:
I am using python 3.3 and sqlalchemy 0.8.2
I am trying to define a self-referential many-to-many relationship for a
class where the primary key is provided by a mixin. Defining the primary
directly in the class works. Using the mixin does not.
I would be grateful for any suggestions or pointers to relevant
documentation.
Below is an example showing my problem. As given the example works.
Uncommenting the line #id = ... in 'Base' and commenting out the
corresponding line in 'A' breaks the example. Is there any way to define
the primary key in Base and getting the 'requires' relation to work?
---
from sqlalchemy import Column, Integer, String, DateTime, Table,
ForeignKey,create_engine
from sqlalchemy.ext.declarative import declarative_base,declared_attr
from sqlalchemy.orm import sessionmaker, relationship, backref
class Base(object):
#id = Column(Integer, primary_key=True)
pass
Base = declarative_base(cls=Base)
association_table = Table('association',
Base.metadata,
Column('prerequisite', Integer,
ForeignKey('a.id')),
Column('dependency', Integer, ForeignKey('a.id')))
class A(Base):
__tablename__ = a
id = Column(Integer, primary_key=True)
requires = relationship(A,
secondary = association_table,
primaryjoin=(id==association_table.c.prerequisite),
secondaryjoin=(id==association_table.c.dependency),
backref = backref(required_by))
if __name__ == __main__:
engine = create_engine('sqlite:///:memory:', echo=False)
Session = sessionmaker(bind=engine)
session = Session()
Base.metadata.create_all(engine)
T=A()
U=A()
session.add(T)
session.add(U)
T.requires.append(U)
session.commit()
print(T,T.id,T.requires,T.required_by)
print(U,U.id,U.requires,U.required_by)
You can make it work by using strings as the primaryjoin and
secondaryjoin parameters and referring to A.id rather than just id:
class A(Base):
__tablename__ = a
requires = relationship(A,
secondary = association_table,
primaryjoin=A.id==association.c.prerequisite,
secondaryjoin=A.id==association.c.dependency,
backref = backref(required_by))
This technique is described in the Configuring Relationships section
of the declarative documentation:
http://docs.sqlalchemy.org/en/rel_0_8/orm/extensions/declarative.html#configuring-relationships
Hope that helps,
Simon
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] self-referential many-to-many relationships and mixins
Thank you. That does the trick.
Till
On Tue, Aug 13, 2013 at 10:54 PM, Simon King si...@simonking.org.uk wrote:
On Tue, Aug 13, 2013 at 2:07 PM, till.plewe till.pl...@gmail.com wrote:
I am using python 3.3 and sqlalchemy 0.8.2
I am trying to define a self-referential many-to-many relationship for a
class where the primary key is provided by a mixin. Defining the primary
directly in the class works. Using the mixin does not.
I would be grateful for any suggestions or pointers to relevant
documentation.
Below is an example showing my problem. As given the example works.
Uncommenting the line #id = ... in 'Base' and commenting out the
corresponding line in 'A' breaks the example. Is there any way to define
the primary key in Base and getting the 'requires' relation to work?
---
from sqlalchemy import Column, Integer, String, DateTime, Table,
ForeignKey,create_engine
from sqlalchemy.ext.declarative import declarative_base,declared_attr
from sqlalchemy.orm import sessionmaker, relationship, backref
class Base(object):
#id = Column(Integer, primary_key=True)
pass
Base = declarative_base(cls=Base)
association_table = Table('association',
Base.metadata,
Column('prerequisite', Integer,
ForeignKey('a.id')),
Column('dependency', Integer, ForeignKey('a.id')))
class A(Base):
__tablename__ = a
id = Column(Integer, primary_key=True)
requires = relationship(A,
secondary = association_table,
primaryjoin=(id==association_table.c.prerequisite),
secondaryjoin=(id==association_table.c.dependency),
backref = backref(required_by))
if __name__ == __main__:
engine = create_engine('sqlite:///:memory:', echo=False)
Session = sessionmaker(bind=engine)
session = Session()
Base.metadata.create_all(engine)
T=A()
U=A()
session.add(T)
session.add(U)
T.requires.append(U)
session.commit()
print(T,T.id,T.requires,T.required_by)
print(U,U.id,U.requires,U.required_by)
You can make it work by using strings as the primaryjoin and
secondaryjoin parameters and referring to A.id rather than just id:
class A(Base):
__tablename__ = a
requires = relationship(A,
secondary = association_table,
primaryjoin=A.id==association.c.prerequisite,
secondaryjoin=A.id==association.c.dependency,
backref = backref(required_by))
This technique is described in the Configuring Relationships section
of the declarative documentation:
http://docs.sqlalchemy.org/en/rel_0_8/orm/extensions/declarative.html#configuring-relationships
Hope that helps,
Simon
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy]
Hi,
I am trying to build an attribute_mapped_collection reference from
table people (Mapped class is called Person). However, I would like to
get a list of entities for each key.
I have the following tables with the relevant PK and FK listed
Person:
- id
PersonToAddress:
- id
- person_id
- address_id
role # this is the mapped special key
Address:
- id
to establish a relationship i do the following (only the relationships
are included in the listing)
class PersonToAddress:
person = relationship( __table_to_classnames__['people'],
backref=backref('people_to_addresses',
collection_class=attribute_mapped_collection(role)))
class Person:
addresses_by_role =
association_proxy('people_to_addresses','address',creator = lambda
k,v:PeopleToAddress(role=k,address=v))
Now querying yields this result:
p = Session.query(Person).get(id=1)
print p.addresses_by_role
{u'home': Address object at 0x29568d0, u'work': Address object at 0x2a3eb90}
I would like to get a list as value for the dict, such that i can
assign more than one entity to any one key. The output should look
like this:
{u'home': [Address object at 0x29568d0,Address object at ...] ,
u'work': [Address object at 0x2a3eb90]}
Now in the database whenever i set a new value for a key(=role), the
entry in PersonToAddress' table is replaced (not added). This is
consistent with having a 1-key to 1-value mapping. Can I however
change the behaviour in such a way that more than one Addresses are
allowed for one Person using the same key(=role in this example)?
I should note that i tried supplying the uselist=True parameter in the
backref argument to PersonToAddress.person. This, however does
nothing.
Adding uselist=True to the parameters of the relationship (as opposed
to the backref) does create a list in both the backref and
addresses_by_role's values. The list, however only contains an
element, and if a new one is added, the entry in the db is
changed.Still only 1 element of the list is ever present in
PersonToAddress' table.
Am i overlooking something in the way attribute_mapped_collection
should be used ?
Paul
--
paulbalom...@gmail.com
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] attribute_mapped_collection use as a key-list dictionary
sorry for having forgot to add a subject
2013/8/13 Paul Balomiri paulbalom...@gmail.com:
Hi,
I am trying to build an attribute_mapped_collection reference from
table people (Mapped class is called Person). However, I would like to
get a list of entities for each key.
I have the following tables with the relevant PK and FK listed
Person:
- id
PersonToAddress:
- id
- person_id
- address_id
role # this is the mapped special key
Address:
- id
to establish a relationship i do the following (only the relationships
are included in the listing)
class PersonToAddress:
person = relationship( __table_to_classnames__['people'],
backref=backref('people_to_addresses',
collection_class=attribute_mapped_collection(role)))
class Person:
addresses_by_role =
association_proxy('people_to_addresses','address',creator = lambda
k,v:PeopleToAddress(role=k,address=v))
Now querying yields this result:
p = Session.query(Person).get(id=1)
print p.addresses_by_role
{u'home': Address object at 0x29568d0, u'work': Address object at
0x2a3eb90}
I would like to get a list as value for the dict, such that i can
assign more than one entity to any one key. The output should look
like this:
{u'home': [Address object at 0x29568d0,Address object at ...] ,
u'work': [Address object at 0x2a3eb90]}
Now in the database whenever i set a new value for a key(=role), the
entry in PersonToAddress' table is replaced (not added). This is
consistent with having a 1-key to 1-value mapping. Can I however
change the behaviour in such a way that more than one Addresses are
allowed for one Person using the same key(=role in this example)?
I should note that i tried supplying the uselist=True parameter in the
backref argument to PersonToAddress.person. This, however does
nothing.
Adding uselist=True to the parameters of the relationship (as opposed
to the backref) does create a list in both the backref and
addresses_by_role's values. The list, however only contains an
element, and if a new one is added, the entry in the db is
changed.Still only 1 element of the list is ever present in
PersonToAddress' table.
Am i overlooking something in the way attribute_mapped_collection
should be used ?
Paul
--
paulbalom...@gmail.com
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
--
paulbalom...@gmail.com
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] attribute_mapped_collection use as a key-list dictionary
On Aug 13, 2013, at 11:44 AM, Paul Balomiri paulbalom...@gmail.com wrote:
I would like to get a list as value for the dict, such that i can
assign more than one entity to any one key. The output should look
like this:
{u'home': [Address object at 0x29568d0,Address object at ...] ,
u'work': [Address object at 0x2a3eb90]}
Now in the database whenever i set a new value for a key(=role), the
entry in PersonToAddress' table is replaced (not added). This is
consistent with having a 1-key to 1-value mapping. Can I however
change the behaviour in such a way that more than one Addresses are
allowed for one Person using the same key(=role in this example)?
OK, an attribute_mapped_collection is just an adapter for what is basically a
sequence. Instead of a sequence of objects, it's a sequence of (key, object).
So by itself, attribute_mapped_collection can only store mapped objects, not
collections as values.
When using the association proxy, there is a way to get a dictionary of values,
but the association proxy only knows how to close two hops into one. So to
achieve that directly, you'd need one relationship that is a key/value mapping
to a middle object, then that middle object has a collection of things.So
here PersonToAddress would be more like PersonAddressCollection, and then each
Address object would have a person_address_collection_id. That's obviously
not the traditional association object pattern - instead of a collection of
associations to scalars, it's a collection of collections, since that's really
the structure you're looking for here.
To approximate the collection of collections on top of a traditional
association pattern is tricky. The simplest way is probably to make a
read-only @property that just fabricates a dictionary of collections on the
fly, reading from the pure collection of PersonToAddress objects. If you want
just a quick read-only system, I'd go with that.
Otherwise, we need to crack open the collection mechanics completely, and since
you want association proxying, we need to crack that open as well. I've worked
up a proof of concept for this idea which is below, and it was not at all
trivial to come up with. In particular I stopped at getting
Person.addresses_by_role['role'].append(Address()) to work, since that means
we'd need two distinctly instrumented collections, it's doable but is more
complex.Below I adapted collections.defaultdict() to provide us with a
collection of collections over a single collection and also the association
proxy's base collection adapter in order to reduce the hops:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
import collections
from sqlalchemy.orm.collections import collection, collection_adapter
from sqlalchemy.ext.associationproxy import association_proxy,
_AssociationCollection
Base = declarative_base()
class GroupByKeyCollection(collections.defaultdict):
def __init__(self, keyfunc):
super(GroupByKeyCollection, self).__init__(list)
self.keyfunc = keyfunc
@collection.appender
def add(self, value, _sa_initiator=None):
key = self.keyfunc(value)
self[key].append(value)
@collection.remover
def remove(self, value, _sa_initiator=None):
key = self.keyfunc(value)
self[key].remove(value)
@collection.internally_instrumented
def __setitem__(self, key, value):
adapter = collection_adapter(self)
# the collection API usually provides these events transparently, but
due to
# the unusual structure, we pretty much have to fire them ourselves
# for each item.
for item in value:
item = adapter.fire_append_event(item, None)
collections.defaultdict.__setitem__(self, key, value)
@collection.internally_instrumented
def __delitem__(self, key, value):
adapter = collection_adapter(self)
for item in value:
item = adapter.fire_remove_event(item, None)
collections.defaultdict.__delitem__(self, key, value)
@collection.iterator
def iterate(self):
for collection in self.values():
for item in collection:
yield item
@collection.converter
def _convert(self, target):
for collection in target.values():
for item in collection:
yield item
def update(self, k):
raise NotImplementedError()
class AssociationGBK(_AssociationCollection):
def __init__(self, lazy_collection, creator, value_attr, parent):
getter, setter = parent._default_getset(parent.collection_class)
super(AssociationGBK, self).__init__(
lazy_collection, creator, getter, setter, parent)
def _create(self, key, value):
return self.creator(key, value)
def _get(self, object):
return self.getter(object)
def _set(self, object, key, value):
return
Re: [sqlalchemy] Mapping views as Table/ORM
Seems like a reasonable way to do this until maybe one day proper support is added to sqlalchemy. I'm still missing one thing though, it seems like there is a feature that allows you to reflect views, but it reflects them as a Table and as such when you later on try to create_all(), it will recreate the view as a table in the database, is there any way to use reflection while avoiding this side effect? בתאריך יום שישי, 9 באוגוסט 2013 13:11:59 UTC+3, מאת werner: On 09/08/2013 10:55, temp...@gmail.com javascript: wrote: It seems that SQLAlchemy has no support for creating views by a View construct or something like that but you can map them as a Table or even an ORM class when applicable, and query from them, the problem is that SQLAlchemy will than try to create them as a new table when you issue metadata.create_all(), is there a convenient way around this, without having to pass a list of tables to create_all? Maybe this recipe will help. http://www.sqlalchemy.org/trac/wiki/UsageRecipes/Views Werner -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Mapping views as Table/ORM
maybe use a separate MetaData collection when reflecting your views, so that create_all() isn't impacted. The View recipe could also be enhanced to support reflection, you can use the inspector to get at lists of columns individually: http://docs.sqlalchemy.org/en/rel_0_8/core/schema.html?highlight=inspector.get_columns#sqlalchemy.engine.reflection.Inspector.get_columns On Aug 13, 2013, at 2:45 PM, temp4...@gmail.com wrote: Seems like a reasonable way to do this until maybe one day proper support is added to sqlalchemy. I'm still missing one thing though, it seems like there is a feature that allows you to reflect views, but it reflects them as a Table and as such when you later on try to create_all(), it will recreate the view as a table in the database, is there any way to use reflection while avoiding this side effect? בתאריך יום שישי, 9 באוגוסט 2013 13:11:59 UTC+3, מאת werner: On 09/08/2013 10:55, temp...@gmail.com wrote: It seems that SQLAlchemy has no support for creating views by a View construct or something like that but you can map them as a Table or even an ORM class when applicable, and query from them, the problem is that SQLAlchemy will than try to create them as a new table when you issue metadata.create_all(), is there a convenient way around this, without having to pass a list of tables to create_all? Maybe this recipe will help. http://www.sqlalchemy.org/trac/wiki/UsageRecipes/Views Werner -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. signature.asc Description: Message signed with OpenPGP using GPGMail
Re: [sqlalchemy] Code organization with declarative models
On Tuesday, August 13, 2013 12:59:57 AM UTC+3, Ams Fwd wrote: On 08/12/2013 02:50 PM, George Sakkis wrote: Hello everyone, this is more of a code architecture and design question but I'm wondering what the best practices are regarding declarative models. On the one extreme, models are pretty barebone, with little more than the columns, relationships and possibly a few declared attributes and properties (python and/or hybrid). On the other extreme, models are much heavier, encapsulating pretty much the business logic of the application in methods (and classmethods or staticmethods for querying the database). Between these two extremes are models with some common or important business logic kept inside the class and the rest defined elsewhere (where this elsewhere might be the controllers or the resource layer or the Data Access Objects or whatever the nomenclature happens to be). So where should the line be drawn between what belongs in a declarative class and what not? For example, I suspect that models should be completely decoupled from the Session; any Session-related code (for querying/updating/deleting objects) should not live inside the declarative class. Still I haven't seen this being mentioned explicitly in the docs and can't put my finger on it. Any insight would be appreciated. Thanks, George -- 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+...@googlegroups.com javascript:. To post to this group, send email to sqlal...@googlegroups.comjavascript:. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. My 2cents: Usually I keep the models absolutely barebones (as you suggested). As far as I am concerned they are not aware of any 'business' logic and only deal with CRUD operations and perhaps complex CRUD if the need arises. I usually have another abstraction which is generally a factory that delegates db tasks to the model and deals with all business logicky stuff, sessions, complex relationships etc. (a bit like Django's managers but not quite as coupled to the model i.e. the model does not know about it). This has worked quite well for me in the past and although it is a bit more work is quite flexible. HTH AM Yes, this helps and it's close to my experience as well. One thing though - even if only dealing with CRUD operations (especially complex) in the model, it's not barebones any more. For starters you need a reference to a (typically global) Session object. Then you have to decide what, say, a Model.create() method should do: does it only initialize and return a new transient object or does it also add it to the session? Or maybe it should call flush() or commit() on top of that? I've been actually trying to dig myself out of a similar hole lately where, to make things worse, the create logic often lives in Model.__init__. In addition to simply initializing a particular object, it may also hit the db to fetch other objects that are needed, instantiate a bunch of new children objects, call flush() and/or commit(), insert a log entry row in another table and more.. So although viewed from the outside it's just CRUD, it has all sorts of business logic and assumptions bundled with it. Regards, George -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Join textual query to SA query?
Hey guys, Questions about the following code in which I'm trying to take a textqual query and join it to a query builder query. 1) What is the correct way to do the in for the list of ids in the first query? My current way doesn't work and I'm not able to find a real good example 2) How can I Join complicated query 2 with complicated query 1. Essentially join query 2 on sm.StufffModel.id == query1.id Complicated query 1: image_res = db.session.query(id, depth, parent_id, name, s3_key).from_statement( WITH RECURSIVE graph(root_id, id, name, parent_id) AS ( SELECT e.id, e.id, e.name, e.parent_id, 1 as depth FROM entities e UNION ALL SELECT graph.root_id, e.id, e.name, e.parent_id, depth + 1 FROM graph JOIN entities e ON e.parent_id=graph.id ) SELECT g.id,g.depth, g.parent_id, name, ii.s3_key FROM graph g JOIN entity_map em ON g.id=em.left_id JOIN stufff_images si ON em.right_id=si.id JOIN image_instance ii ON si.image_id=ii.image_id WHERE root_id in (:ids) AND ii.width=120 ORDER BY depth ASC LIMIT 1; ).params(ids=,.join([str(i) for i in ids]))) Complicated query 2: query = db.session.query( sm.StufffModel.id, sm.EntityTypesModel.type, sm.StufffModel.hotness, sm.StufffModel.created_at, sm.StufffModel.name) query = query.join(sm.EntityTypesModel) query = query.filter(sm.StufffModel.id.in_(ids)) res = query.all() Thanks, Amir -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Cross-schema foreign keys reflection
cross-schema reflection is supported on PG but has caveats, see http://docs.sqlalchemy.org/en/rel_0_8/dialects/postgresql.html#remote-cross-schema-table-introspection for a discussion of recommended usage patterns. On Aug 13, 2013, at 5:11 PM, Jason ja...@deadtreepages.com wrote: Hello, I am using reflection for my tables and I have the relationships defined manually which are simply relationship(MyOtherModel). This worked fine when all of the tables were in the same schema (this is Postgres). Now I have moved MyOtherModel into a different schema and now I get there are no foreign keys linking these tables error. Both models have their schema specified in the __table_args__. If I specify the primaryjoin on the relation all is well again. Is this a known behaviour when using foreign keys that cross schema boundaries? I don't necessarily think this is a bug (which is why I didn't include a full code example), but it is a behaviour I didn't expect. -- Jason -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. signature.asc Description: Message signed with OpenPGP using GPGMail
Re: [sqlalchemy] Join textual query to SA query?
On Aug 10, 2013, at 4:41 PM, Amir Elaguizy aelag...@gmail.com wrote: Hey guys, Questions about the following code in which I'm trying to take a textqual query and join it to a query builder query. 1) What is the correct way to do the in for the list of ids in the first query? My current way doesn't work and I'm not able to find a real good example the IN operator in SQL works like this: x IN (1, 2, 3, 4, 5, ...) so if you want to bind values, you have to list them out: x IN (:value1, :value2, :value3, :value4, ...) there's no magic acceptance of arrays or anything like that in most SQL drivers. 2) How can I Join complicated query 2 with complicated query 1. Essentially join query 2 on sm.StufffModel.id == query1.id Complicated query 1: image_res = db.session.query(id, depth, parent_id, name, s3_key).from_statement( WITH RECURSIVE graph(root_id, id, name, parent_id) AS ( SELECT e.id, e.id, e.name, e.parent_id, 1 as depth FROM entities e UNION ALL SELECT graph.root_id, e.id, e.name, e.parent_id, depth + 1 FROM graph JOIN entities e ON e.parent_id=graph.id ) SELECT g.id,g.depth, g.parent_id, name, ii.s3_key FROM graph g JOIN entity_map em ON g.id=em.left_id JOIN stufff_images si ON em.right_id=si.id JOIN image_instance ii ON si.image_id=ii.image_id WHERE root_id in (:ids) AND ii.width=120 ORDER BY depth ASC LIMIT 1; ).params(ids=,.join([str(i) for i in ids]))) Complicated query 2: query = db.session.query( sm.StufffModel.id, sm.EntityTypesModel.type, sm.StufffModel.hotness, sm.StufffModel.created_at, sm.StufffModel.name) query = query.join(sm.EntityTypesModel) query = query.filter(sm.StufffModel.id.in_(ids)) res = query.all() normally you can make a select() using text fragments, like select(['x', 'y', 'z']).select_from(foo).where(bar 5), though with that CTE and all that unless you want to write it using the expression language (which I would) it's easiest to keep that as text(). There's a ticket to make a hybrid text()/FROM element for this kind of thing (#2478). for now if it doesn't complain about nesting around that WITH, you can do a select: from sqlalchemy import text, select t1 = text((with recursive ... etc etc ORDER BY depth ASC LIMIT 1) AS my_query) s1 = select([id]).select_from(t1).alias() q = session.query(Entity.x, Entity.y, ...).join(...).filter(...).join(s1, s1.c.id == Entity.id) Thanks, Amir -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. signature.asc Description: Message signed with OpenPGP using GPGMail
[sqlalchemy] CTE name quoting bug in 0.8
Hi,
I updated sqlalchemy from 0.7.8 to 0.8.2 and one of my queries started
failing. I had a CTE expression with capital letters in it's name, and in
0.8 it wasn't getting consistently quoted, but it worked in 0.7.
I narrowed it down, and it only seems to happen in a query containing
multiple subqueries referring to the same CTE.
Here's an example that reproduces it. If I remove the second subquery from
this example, there's no issues:
import sys
import sqlalchemy as sa
import sqlalchemy.orm
import psycopg2
engine = sa.create_engine('postgresql://', creator=lambda:
psycopg2.connect(dbname=sys.argv[1]), echo=True)
Session = sa.orm.scoped_session(sa.orm.sessionmaker(bind=engine))
session = Session()
cte = session.query(sa.literal(1).label(id)).cte(name='CTE')
firstSubquery = session.query(cte.c.id).subquery()
secondSubquery = session.query(cte.c.id).subquery()
query = session.query(firstSubquery, secondSubquery)
print query.all()
The resulting query looks like this. Note that the last reference to CTE
is not quoted like the others, causing a ProgrammingError when it can't be
found.
WITH CTE AS
(SELECT :param_1 AS id)
SELECT anon_1.id AS anon_1_id, anon_2.id AS anon_2_id
FROM (SELECT CTE.id AS id
FROM CTE) AS anon_1, (SELECT CTE.id AS id
FROM CTE) AS anon_2
Thanks,
Jesse
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Updating a one-to-many relationship
I'm afraid there are still some bugs in here that hopefully you can help
with.
class Creator(Base):
__tablename__ = creators
id = Column(Integer, primary_key = True)
company_id = Column(Integer, ForeignKey('companies.id'))
creator = Column(String(100), nullable=False, unique=True)
def __init__(self, creator):
self.creator = creator
def __repr__(self):
return '%s' % self.creator # otherwise returns a single entry list
for some reason (e.g. would display [user])
class Company(Base):
__tablename__ = companies
id = Column(Integer, primary_key = True)
company = Column(String(100), unique=True, nullable=False) #might want
to revise string sizes at some point
creator = relationship(Creator, backref=companies, cascade=all)
def __init__(self, company, creator):
self.company = company
#self.creator.append(Creator(creator))
existing_creator =
session.query(Creator).filter_by(creator=creator).first()
#self.creator.append(existing_creator or Creator(creator))
if existing_creator:
print True
self.creator.append(existing_creator)
else:
self.creator.append(Creator(creator))
def __repr__(self):
return '%s, created by %s' % (self.company, self.creator[0])
1) Weird __repr__ error:
class Creator(Base):
def __repr__(self):
return '%s' % self.creator
class Company(Base):
def __repr__(self):
return '%s, created by %s' % (self.company, self.creator[0])
c=Company(Company1, mike)
session.add(c)
c=Company(Company2, mike)
True
session.add(c)
c=Company(Company3, john)
session.add(c)
c=Company(Company4, mike)
True
session.add(c)
session.query(Company).all()
[Traceback (most recent call last):
File stdin, line 1, in module
File stdin, line 17, in __repr__
However, if I divide the query lines among every add() statement, there is
no __repr__ error.
c=Company(Company1, mike)
session.add(c)
session.query(Company).all()
[Company1, created by mike]
c=Company(Company2, mike)
True
session.add(c)
session.query(Company).all()
[Company1, created by mike, Company2, created by mike]
c=Company(Company3, john)
session.add(c)
session.query(Company).all()
[Company1, created by mike, Company2, created by mike, Company3, created by
john]
c=Company(Company4, mike)
True
session.add(c)
session.query(Company).all()
[Company1, created by mike, Company2, created by mike, Company3, created by
john, Company4, created by mike]
2) Creator.companies only shows the most recently added company:
session.query(Company).all()
[Company1, created by mike, Company2, created by mike, Company3, created by
john, Company4, created by mike]
session.query(Creator).all()
[mike, john]
a=session.query(Creator).first()
a[0].companies
a.companies
Company4, created by mike
3) Weird Company.creator error:
session.query(Company).all()
[Company1, created by mike, Company2, created by mike, Company3, created by
john, Company4, created by mike]
session.query(Company.creator).all()
[(False,), (False,), (False,), (False,), (True,), (False,), (False,),
(True,)]
a=session.query(Company).first()
a.creator
[mike]
Anyone have any ideas?
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] DeferredReflection and mapper
Hello,
How do I go from class like defeinition to below with mapper.
The docs in 0.8 say I can use:
from sqlalchemy.ext.declarative import DeferredReflectionBase =
declarative_base()
class MyClass(DeferredReflection, Base):
__tablename__ = 'mytable'
but how do I do below with DefferedReflection
--
from sqlalchemy import Table
from sqlalchemy.orm import mapper, relation
class Recall(object):
def __init__(self, **kw):
automatically mapping attributes
for key, value in kw.iteritems():
setattr(self, key, value)
recall_table = Table('recall_db', metadata,
autoload=True,autoload_with=engine)
mapper(Recall, recall_table,primary_key=[recall_table.c.RECORD_ID])
I'm using pyramid, and I want to autoload tables in models.py which does
not have the engine bound yet. I will bind in in main function with
DeferredReflection.prepare(engine)
Thanks
Lucas
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
