Re: [sqlalchemy] Query before_compile issue
> > today, we have this issue, which I can confirm has existed since version > 1.2 over two years ago, reported for the first time ever here, and then a > day later in https://github.com/sqlalchemy/sqlalchemy/issues/4947 . > Even more funny is that this Issue uses a tenant in the provided test-case, which was exactly my use-case too! Very good to see a proper fix for this! > -- 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/CAEE1YnhQPXQiy88nemU4p0%3D_rxGTXo-M8rJD7_7RJEe4QyFNHA%40mail.gmail.com.
Re: [sqlalchemy] Query before_compile issue
On Wed, Oct 23, 2019 at 3:16 PM Mike Bayer wrote: > > > On Tue, Oct 22, 2019, at 12:30 PM, eric.lemo...@gmail.com wrote: > > > > > it sounds like you should use a bound parameter with a lambda inside of > it, there's not an explicit "on lazyload" hook at the moment. > > > Yep, that seems to be doing the job! > > > > that worked? wow > > :) > SQLAlchemy always has a solution for me. But often uneasy to find :-) > guess you are using a threadlocal to get the context? > Yes indeed. -- Eric -- 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/CAEE1YnjRsi9kh-V3NtVUQL--TqbCaGPCj4V%3Drj8v2sc8bq%3DU%3Dg%40mail.gmail.com.
Re: [sqlalchemy] Query before_compile issue
it sounds like you should use a bound parameter with a lambda inside of it, > there's not an explicit "on lazyload" hook at the moment. > Yep, that seems to be doing the job! -- 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/CAEE1Yngbpq8s4EoJOoLQ07t852VLK0-5RGMQuzM7rREBSaGBjA%40mail.gmail.com.
Re: [sqlalchemy] Query before_compile issue
On Tue, Oct 22, 2019 at 5:59 PM Mike Bayer wrote: > > > On Tue, Oct 22, 2019, at 11:58 AM, Eric Lemoine wrote: > > > > The first time before_compile is called twice, one call for the SELECT FROM > "user" query, and another call for the SELECT FROM "address" query. But the > second time before_compile is called only once. It's called for the SELECT > FROM "user" query, but not for the SELECT FROM "addresses" query. Is it > expected? > > I am using SQLAlchemy 1.3.10. > > > sure, it sounds like u.addresses is already loaded. if you want > u.addresses to emit a query every time it is called you need to use the > "dynamic" loader strategy, that is, lazy="dynamic". > > > > I think the problem is related to "baked queries". I don't have the > problem with enable_baked_queries set to False in sessionmaker. > > > oh right that too, those queries are cached. but whatever changes you > made to the query should also be cached, how come you need it to be > different every time? > I have HTTP queries with authenticated users associated to tenants. And I wanted to use the Filtered Query pattern to only select objects bound to the current tenant, without having to write complex queries (with explicit filters for the current tenant) in the request handling code. But that doesn't work, because of that "baked queries" issue. The Filtered Query example in the wiki page works because the filter is fixes (obj.public == True). In my case I want something like obj.tenant_id == get_tenant_id_from_request(). Thanks Mike. -- Eric -- 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/CAEE1YngiAsqqUwFJAaHZC0pj%3DU0pUtfGtEA2UPxv0toaCNcEwg%40mail.gmail.com.
Re: [sqlalchemy] Query before_compile issue
> The first time before_compile is called twice, one call for the SELECT FROM > "user" query, and another call for the SELECT FROM "address" query. But the > second time before_compile is called only once. It's called for the SELECT > FROM "user" query, but not for the SELECT FROM "addresses" query. Is it > expected? > > I am using SQLAlchemy 1.3.10. > > > sure, it sounds like u.addresses is already loaded. if you want > u.addresses to emit a query every time it is called you need to use the > "dynamic" loader strategy, that is, lazy="dynamic". > I think the problem is related to "baked queries". I don't have the problem with enable_baked_queries set to False in sessionmaker. Thanks for your response. -- 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/6ff6bf6c-0fa6-4795-a6f7-0b35d9417a08%40googlegroups.com.
[sqlalchemy] Query before_compile issue
Hi I want to use the FilteredQuery pattern [1], but I am facing a problem related to SQLAlchemy not calling "before_compile" as often as I'd expect it. Here's a simple example: @event.listens_for(Query, "before_compile", retval=True) def before_compile(query): print("X before_compiled called X") return query class User(Base): __tablename__ = "user" id = Column(Integer, primary_key=True) name = Column(String) addresses = relationship("Address", back_populates="user") class Address(Base): __tablename__ = "address" id = Column(Integer, primary_key=True) email = Column(String) user_id = Column(Integer, ForeignKey("user.id")) user = relationship("User", back_populates="addresses") and then I do the following twice: for u in Session().query(User): for a in u.addresses: print(u.name, a.email) The first time before_compile is called twice, one call for the SELECT FROM "user" query, and another call for the SELECT FROM "address" query. But the second time before_compile is called only once. It's called for the SELECT FROM "user" query, but not for the SELECT FROM "addresses" query. Is it expected? I am using SQLAlchemy 1.3.10. The full test case is here: https://gist.github.com/elemoine/3fa86da54fc1195e314fa18999d05a68 [1] https://github.com/sqlalchemy/sqlalchemy/wiki/FilteredQuery -- 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/ef750756-4a8e-4043-bd5e-873a188612ec%40googlegroups.com.
Re: [sqlalchemy] can't adapt type 'centroid'
On Tue, Nov 27, 2012 at 2:43 AM, Michael Bayer mike...@zzzcomputing.com wrote: trying to get the attention of the geoalchemy guy here, maybe try their list: https://groups.google.com/forum/?fromgroups#!forum/geoalchemy The discussion has been taken to the GeoAlchemy list: https://groups.google.com/d/msg/geoalchemy/uqr6DVzotaA/aHXcGfUDca8J. -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 94 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] comparator_factory __lshift__ __rshift__
Hi I'm trying to add __lshift__ and __rshift__ to my (now well known :) Geometry's comparator_factory, but it looks like SQLAlchemy isn't happy with it. from sqlalchemy.types import UserDefinedType class Geometry(UserDefinedType): class comparator_factory(UserDefinedType.Comparator): def __lshift__(self, other): return self.op('')(other) def __rshift__(self, other): return self.op('')(other) from sqlalchemy import Table, Column, MetaData lakes = Table('lake', MetaData(), Column('geom', Geometry) ) to_left_expr = lakes.c.geom 'POINT(1 2)' to_right_expr = lakes.c.geom 'POINT(1 2)' This is the traceback: Traceback (most recent call last): File comparator_factory.py, line 21, in module to_left_expr = lakes.c.geom 'POINT(1 2)' TypeError: unsupported operand type(s) for : 'Column' and 'str' Is it me doing something wrong, or is SQLAlchemy just not dealing with and for the moment? FYI, __add__ works just fine. Thank you. -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] comparator_factory __lshift__ __rshift__
On Tue, Sep 4, 2012 at 4:36 PM, Michael Bayer mike...@zzzcomputing.com wrote: Those methods aren't part of the contract at the moment but I can add them in. I can live without them, it'd just be nice to be able to use the actual PostGIS operators in SQLAlchemy apps. Thank you! -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] comparator_factory __lshift__ __rshift__
On Tue, Sep 4, 2012 at 4:45 PM, Michael Bayer mike...@zzzcomputing.com wrote: they're in tip. thanks for testing all this ! Great! Another issue, with contains this time: from sqlalchemy.types import UserDefinedType class Geometry(UserDefinedType): class comparator_factory(UserDefinedType.Comparator): def contains(self, other): return self.op('~')(other) from sqlalchemy import Table, Column, MetaData lakes = Table('lake', MetaData(), Column('geom', Geometry) ) print str(lakes.c.geom.contains('POINT(1 2)')) produces this error: Traceback (most recent call last): File comparator_factory.py, line 18, in module print str(lakes.c.geom.contains('POINT(1 2)')) File /home/elemoine/src/sqlalchemy/lib/sqlalchemy/sql/operators.py, line 375, in contains return self.operate(contains_op, other, **kwargs) File /home/elemoine/src/sqlalchemy/lib/sqlalchemy/sql/expression.py, line 2204, in operate return op(self.comparator, *other, **kwargs) File /home/elemoine/src/sqlalchemy/lib/sqlalchemy/sql/operators.py, line 571, in contains_op return a.contains(b, escape=escape) TypeError: contains() got an unexpected keyword argument 'escape' I should be able to override contains, no? -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] comparator_factory __lshift__ __rshift__
On Tue, Sep 4, 2012 at 5:18 PM, Michael Bayer mike...@zzzcomputing.com wrote: can't do __contains__ due to Python behavior: class Foo(object): def __add__(self, other): return (self, add, other) def __contains__(self, other): return (self, contains, other) f1 = Foo() assert f1 + 5 == (f1, add, 5) assert 5 in f1 == (f1, contains, 5), 5 in f1 second assertion fails, it forces a bool() on the result. __nonzero__() is required to return True/False/int, I suppose we could make a custom int subclass but that's getting really weird. Oh right. Thanks for the explanation. -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] comparator_factory __lshift__ __rshift__
On Tue, Sep 4, 2012 at 5:20 PM, Eric Lemoine eric.lemo...@camptocamp.com wrote: On Tue, Sep 4, 2012 at 5:18 PM, Michael Bayer mike...@zzzcomputing.com wrote: can't do __contains__ due to Python behavior: class Foo(object): def __add__(self, other): return (self, add, other) def __contains__(self, other): return (self, contains, other) f1 = Foo() assert f1 + 5 == (f1, add, 5) assert 5 in f1 == (f1, contains, 5), 5 in f1 second assertion fails, it forces a bool() on the result. __nonzero__() is required to return True/False/int, I suppose we could make a custom int subclass but that's getting really weird. Oh right. Thanks for the explanation. Actually, I was referring to contains, not __contains__. -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] comparator_factory __lshift__ __rshift__
On Tue, Sep 4, 2012 at 5:30 PM, Michael Bayer mike...@zzzcomputing.com wrote: On Sep 4, 2012, at 11:24 AM, Eric Lemoine wrote: On Tue, Sep 4, 2012 at 5:20 PM, Eric Lemoine eric.lemo...@camptocamp.com wrote: On Tue, Sep 4, 2012 at 5:18 PM, Michael Bayer mike...@zzzcomputing.com wrote: can't do __contains__ due to Python behavior: class Foo(object): def __add__(self, other): return (self, add, other) def __contains__(self, other): return (self, contains, other) f1 = Foo() assert f1 + 5 == (f1, add, 5) assert 5 in f1 == (f1, contains, 5), 5 in f1 second assertion fails, it forces a bool() on the result. __nonzero__() is required to return True/False/int, I suppose we could make a custom int subclass but that's getting really weird. Oh right. Thanks for the explanation. Actually, I was referring to contains, not __contains__. that should just work out of the gate. There's an escape kw you need to ignore as it loops around, otherwise works: def test_contains(self): class MyType(UserDefinedType): class comparator_factory(UserDefinedType.Comparator): def contains(self, other, **kw): return self.op(-)(other) self.assert_compile( Column('x', MyType()).contains(5), x - :x_1 ) Ok I just didn't have **kw. I'm wondering if I should add it to all of my comparator functions. -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] comparator_factory __lshift__ __rshift__
On Tue, Sep 4, 2012 at 5:38 PM, Michael Bayer mike...@zzzcomputing.com wrote: On Sep 4, 2012, at 11:32 AM, Eric Lemoine wrote: On Tue, Sep 4, 2012 at 5:30 PM, Michael Bayer mike...@zzzcomputing.com wrote: On Sep 4, 2012, at 11:24 AM, Eric Lemoine wrote: On Tue, Sep 4, 2012 at 5:20 PM, Eric Lemoine eric.lemo...@camptocamp.com wrote: On Tue, Sep 4, 2012 at 5:18 PM, Michael Bayer mike...@zzzcomputing.com wrote: can't do __contains__ due to Python behavior: class Foo(object): def __add__(self, other): return (self, add, other) def __contains__(self, other): return (self, contains, other) f1 = Foo() assert f1 + 5 == (f1, add, 5) assert 5 in f1 == (f1, contains, 5), 5 in f1 second assertion fails, it forces a bool() on the result. __nonzero__() is required to return True/False/int, I suppose we could make a custom int subclass but that's getting really weird. Oh right. Thanks for the explanation. Actually, I was referring to contains, not __contains__. that should just work out of the gate. There's an escape kw you need to ignore as it loops around, otherwise works: def test_contains(self): class MyType(UserDefinedType): class comparator_factory(UserDefinedType.Comparator): def contains(self, other, **kw): return self.op(-)(other) self.assert_compile( Column('x', MyType()).contains(5), x - :x_1 ) Ok I just didn't have **kw. I'm wondering if I should add it to all of my comparator functions. well to the non-underscored names, it should be harmless. the underscored names are Python API and won't change. So it's *required* for contains, and harmless for other non-underscored names. I don't understand why, but I can probably live with it. -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: column_expression issue/question
Hi I'm facing another column_expression issue. It is not related to the previous issue, and I'm sure if SQLAlchemy can help me with this one. from sqlalchemy.types import UserDefinedType from sqlalchemy.sql import func class Geometry(UserDefinedType): def column_expression(self, col): return func.ST_AsBinary(col, type_=self) from sqlalchemy import Table, Column, MetaData lakes = Table('lake', MetaData(), Column('geom', Geometry) ) from sqlalchemy.sql.expression import select, alias s = select([lakes]) a = alias(s, 'name') s = a.select() print s The print statement returns this: SELECT ST_AsBinary(name.geom) AS geom FROM (SELECT ST_AsBinary(lake.geom) AS geom FROM lake) AS name Which is expected. But the execution of this statement fails in my PostGIS database, because ST_AsBinary cannot receive a bytea value as input. (the outer ST_AsBinary call receives what's returned from the inner ST_AsBinary call). It would work if ST_AsBinary was idempotent, but it's not in PostGIS 2 (http://trac.osgeo.org/postgis/ticket/1869). I'm stuck on this one. -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: column_expression issue/question
On Sunday, September 2, 2012, Michael Bayer wrote: fixed in tip. Thanks. You may want to correct one of the generated SQL strings in the doc ( http://docs.sqlalchemy.org/en/latest/core/types.html?highlight=column_expression#types-sql-value-processing ). I'm a little uncertain that the fix might hit some other cases that aren't handled yet, if you get any errors about .name or .key with more complex expressions let me know. Will do. -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] column_expression issue/question
Hi I use 0.8's column_expression. Like this: - from sqlalchemy.types import UserDefinedType from sqlalchemy.sql import func class Geometry(UserDefinedType): def column_expression(self, col): return func.ST_AsBinary(col, type_=self) from sqlalchemy import Table, Column, MetaData lakes = Table('lake', MetaData(), Column('geom', Geometry) ) from sqlalchemy.sql import select s = select([lakes]) print s The final print statement returns this: SELECT ST_AsBinary(lake.geom) AS geom_1 FROM lake. My issue is with the geom_1 label being generated. My column name being geom I'd expect the following to work: s = select([lakes]) for row in conn.execute(s): geom = row['geom'] but it won't work because row does not have a geom item. Is there a solution to this issue? Thanks, -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: associationproxy for one-to-many
Thanks a lot Mike! This gives me interesting paths to research. For the record, I finally wrote my own association_proxy. This is an hybrid between SQLAlchemy's association_proxy and hybrid_property. class _association_proxy(object): # A specific association proxy implementation def __init__(self, target, value_attr): self.target = target self.value_attr = value_attr def __get__(self, obj, type=None): if obj is None: # For hybrid descriptors that work both at the instance # and class levels we could return an SQL expression here. # The code of hybrid_property in SQLAlchemy illustrates # how to do that. raise AttributeError return getattr(getattr(obj, self.target), self.value_attr) def __set__(self, obj, val): o = getattr(obj, self.target) # if the obj as no child object or if the child object # does not correspond to the new value then we need to # read a new child object from the database if not o or getattr(o, self.value_attr) != val: relationship_property = class_mapper(obj.__class__) \ .get_property(self.target) child_cls = relationship_property.argument o = Session.query(child_cls).filter( getattr(child_cls, self.value_attr) == val).first() setattr(obj, self.target, o) Thanks again Mike for your support. -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: associationproxy for one-to-many
On Mon, Apr 16, 2012 at 10:49 PM, Eric Lemoine eric.lemo...@camptocamp.com wrote: Hi I'd like to use an associationproxy for a simple many-to-one relationship: class Child(Base): __tablename__ = 'child' id = Column(Integer, primary_key=True) name = Column(Unicode) class Parent(Base): __tablename__ = 'parent' id = Column(Integer, primary_key=True) child_id = Column(Integer, ForeignKey('child.id') child_ = relationship(Child) child = association_proxy('child_', 'name', creator=) Now 'child' is a read-only, dictionary-like table. I never want to insert new rows in this table. So I actually pass the following creator to the association_proxy constructor: def creator(name): return Session.query(Child).filter_by(name=name).first() That does the job for create. But I cannot find a solution for update. On update I'd like to replace the current Child object in the Parent object by a new Child object read from the 'child' table. Using a specific setter (in a getset_factory) does not work for me, as the setter receives the Child object (and the value), not the Parent object – I'd need a ref to the Parent object to be able to change its Child object in child_. Maybe I'm doing it all wrong and using an associationproxy is not the way to go for that case. Thanks for any suggestion. No comment for this? Thanks, -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: associationproxy for one-to-many
On Fri, Apr 20, 2012 at 9:28 PM, Michael Bayer mike...@zzzcomputing.com wrote: On Apr 20, 2012, at 8:45 AM, Eric Lemoine wrote: On Mon, Apr 16, 2012 at 10:49 PM, Eric Lemoine eric.lemo...@camptocamp.com wrote: Hi I'd like to use an associationproxy for a simple many-to-one relationship: class Child(Base): __tablename__ = 'child' id = Column(Integer, primary_key=True) name = Column(Unicode) class Parent(Base): __tablename__ = 'parent' id = Column(Integer, primary_key=True) child_id = Column(Integer, ForeignKey('child.id') child_ = relationship(Child) child = association_proxy('child_', 'name', creator=) Now 'child' is a read-only, dictionary-like table. I never want to insert new rows in this table. So I actually pass the following creator to the association_proxy constructor: def creator(name): return Session.query(Child).filter_by(name=name).first() That does the job for create. But I cannot find a solution for update. On update I'd like to replace the current Child object in the Parent object by a new Child object read from the 'child' table. Using a specific setter (in a getset_factory) does not work for me, as the setter receives the Child object (and the value), not the Parent object – I'd need a ref to the Parent object to be able to change its Child object in child_. Maybe I'm doing it all wrong and using an associationproxy is not the way to go for that case. the associationproxy is good for collections but in this case I'm not sure what you're getting versus a regular @property or @hybrid_property. Yeah I was wondering too. And I actually did not know about the existence of hybrid_property. I will note that I think I do a use case a little bit similar to this, where Parent.children is a dictionary keyed on name. But instead of sticking Session.query() into creator, I use a recipe similar to the unique object recipe: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/UniqueObject . This is also very good to know (and study). If the associationproxy is doing it's job, modifying Parent.child, as a string, should be updating Parent._child.name with the new value. If you wanted to replace that with just swap this other Child in, you could do that in a before_flush() event, not unlike the insert versions recipe: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/VersionedRows but just sticking with @property or hybrid might be more straightforward here, if it works. Yeah. Thanks a lot Mike! This gives me interesting paths to research. -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] associationproxy for one-to-many
Hi I'd like to use an associationproxy for a simple many-to-one relationship: class Child(Base): __tablename__ = 'child' id = Column(Integer, primary_key=True) name = Column(Unicode) class Parent(Base): __tablename__ = 'parent' id = Column(Integer, primary_key=True) child_id = Column(Integer, ForeignKey('child.id') child_ = relationship(Child) child = association_proxy('child_', 'name', creator=) Now 'child' is a read-only, dictionary-like table. I never want to insert new rows in this table. So I actually pass the following creator to the association_proxy constructor: def creator(name): return Session.query(Child).filter_by(name=name).first() That does the job for create. But I cannot find a solution for update. On update I'd like to replace the current Child object in the Parent object by a new Child object read from the 'child' table. Using a specific setter (in a getset_factory) does not work for me, as the setter receives the Child object (and the value), not the Parent object – I'd need a ref to the Parent object to be able to change its Child object in child_. Maybe I'm doing it all wrong and using an associationproxy is not the way to go for that case. Thanks for any suggestion. -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: simple locking issue
On Thursday, March 8, 2012, Michael Bayer mike...@zzzcomputing.com wrote: On Mar 8, 2012, at 7:28 AM, Eric Lemoine wrote: On Thu, Mar 8, 2012 at 3:59 PM, Eric Lemoine eric.lemo...@camptocamp.com wrote: Hi My code basically does: --- engine = create_engine() table = Table('table', MetaData(), Column('id', Integer, primary_key=True)) table.create(bind=engine) Session = scoped_session(sessionmaker(bind=engine)) Session.execute(text('SELECT id from table')) --- The last statement blocks forever. It's like table.create created a transaction, and locked the table. Session.execute is blocked on this lock. I use Postgres. Any idea what I'm doing wrong? Here's the actual test case: --- from sqlalchemy import Table, MetaData, Column, create_engine from sqlalchemy import types, text engine = create_engine('postgresql://www-data:www-data@localhost :5432/c2cgeoportal_test') table = Table('table_d', MetaData(), Column('id', types.Integer, primary_key=True) ) table.create(bind=engine) from sqlalchemy.orm import sessionmaker Session = sessionmaker(bind=engine) Session().execute(text('SELECT id FROM table_d')) # blocks forever! table.drop(bind=engine) if you do a ps -ef | grep post you can see it's making it to the drop. The drop is blocking because the execute of a SELECT has returned an open cursor for you to consume. The table has a reader within a transaction distinct from that of the drop. Obviously. Thank you Michael. -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] simple locking issue
Hi My code basically does: --- engine = create_engine() table = Table('table', MetaData(), Column('id', Integer, primary_key=True)) table.create(bind=engine) Session = scoped_session(sessionmaker(bind=engine)) Session.execute(text('SELECT id from table')) --- The last statement blocks forever. It's like table.create created a transaction, and locked the table. Session.execute is blocked on this lock. I use Postgres. Any idea what I'm doing wrong? Thank you. -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] engine in column_reflect events
On Tue, Feb 28, 2012 at 5:20 PM, Eric Lemoine eric.lemo...@camptocamp.com wrote: On Tue, Feb 28, 2012 at 4:46 PM, Michael Bayer mike...@zzzcomputing.com wrote: wowwell yeah, though putting it in the column_info is not how i'd want to do that. I'd like to change the API for 0.8. Actually the whole inspector should be passed to the event. this is http://www.sqlalchemy.org/trac/ticket/2418 which has a patch. For now, uerg, putting in the column_info is pretty hacky and then we're stuck with itlet me do some other things for a bit and if you have workarounds until 0.8 that would help, though I'm guessing you dont... Thanks for your answer. I have a workaround, but which forces me to have my reflection code at the application level as opposed to in GeoAlchemy. Another issue: GeoAlchemy defines a specific Column, namely GeometryColumn [*]. GeometryColumn is actually a function that returns a column property created with column_property. And I cannot make SQLAlchemy reflection code use GeometryColumn instead of Column. I may be asking too much. What do you think? Thanks. [*] https://github.com/geoalchemy/geoalchemy/blob/master/geoalchemy/geometry.py#L163 -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] engine in column_reflect events
On Wed, Feb 29, 2012 at 4:52 PM, Michael Bayer mike...@zzzcomputing.com wrote: Ok I'm assuming geometrycolumn doesn't go into a Table it gets stuck on a declarative class, Table wouldn't accept it. I think that's correct. We use GeometryExtensionColumn in a Table. GeometryExtensionColumn is a subclass of Column used in a @compiles decorator. See https://github.com/geoalchemy/geoalchemy/blob/master/geoalchemy/geometry.py#L151. I would modify this using the declarative reflection recipe, which sets up everything that gets sent to mapper(). Iterate through the reflected table columns and generate the appropriate column_property() based on column type. i'll look into that. Thanks a lot! -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] engine in column_reflect events
On Wed, Feb 29, 2012 at 4:52 PM, Michael Bayer mike...@zzzcomputing.com wrote: Ok I'm assuming geometrycolumn doesn't go into a Table it gets stuck on a declarative class, Table wouldn't accept it. I would modify this using the declarative reflection recipe, which sets up everything that gets sent to mapper(). I guess you're referring to: http://docs.sqlalchemy.org/en/latest/orm/examples.html#examples-declarative-reflection. Iterate through the reflected table columns and generate the appropriate column_property() based on column type. -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] engine in column_reflect events
Hi I'd like to do queries from a column_reflect listener, but this isn't currently possible because column_reflect events do not include an engine/bind object. Would adding a bind key/value to the column_info argument make sense? FYI, I'm trying to reflect PosGIS geometry columns. Thank you. -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] engine in column_reflect events
On Tue, Feb 28, 2012 at 4:46 PM, Michael Bayer mike...@zzzcomputing.com wrote: wowwell yeah, though putting it in the column_info is not how i'd want to do that. I'd like to change the API for 0.8. Actually the whole inspector should be passed to the event. this is http://www.sqlalchemy.org/trac/ticket/2418 which has a patch. For now, uerg, putting in the column_info is pretty hacky and then we're stuck with itlet me do some other things for a bit and if you have workarounds until 0.8 that would help, though I'm guessing you dont... Thanks for your answer. I have a workaround, but which forces me to have my reflection code at the application level as opposed to in GeoAlchemy. -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: porting GeoAlchemy to 0.7
On Thu, Jun 9, 2011 at 7:21 PM, Michael Bayer mike...@zzzcomputing.com wrote: On Jun 9, 2011, at 12:37 PM, Eric Lemoine wrote: On Thu, Jun 9, 2011 at 6:28 PM, Michael Bayer mike...@zzzcomputing.com wrote: That's the default adaption provided by TypeEngine.adapt(). Provide your own adapt() that does what's needed. For examples see Interval, Enum. Ok, I'll take a look at adapt(). Note that our Geometry type isn't specific to Oracle though. When you get it going, if you can show us what you're doing, we can create a prototypical version of your type, demonstrating the kind of add new arguments per dialect functionality it has, and add it to our test suite, to ensure those usage patterns don't break. SQLAlchemy usually uses distinct type classes per backend to handle backend-specific arguments, so your approach of allowing DB-specific keyword arguments to a single type, which while entirely appropriate in your case, isn't a pattern we test for at the moment. See the attached patch. Please tell if I should create a Trac ticket and attach my patch to it. Cheers, -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. diff --git a/test/sql/test_types.py b/test/sql/test_types.py --- a/test/sql/test_types.py +++ b/test/sql/test_types.py @@ -273,6 +273,18 @@ Float().dialect_impl(pg).__class__ ) +def test_user_defined_dialect_specific_args(self): +class MyType(types.UserDefinedType): +def __init__(self, foo='foo', **kwargs): +self.foo = foo +self.dialect_specific_args = kwargs +def adapt(self, cls): +return cls(foo=self.foo, **self.dialect_specific_args) +t = MyType(bar='bar') +a = t.dialect_impl(testing.db.dialect) +eq_(a.foo, 'foo') +eq_(a.dialect_specific_args['bar'], 'bar') + @testing.provide_metadata def test_type_coerce(self): test ad-hoc usage of custom types with type_coerce().
Re: [sqlalchemy] Re: porting GeoAlchemy to 0.7
On Tue, Jun 14, 2011 at 3:46 PM, Michael Bayer mike...@zzzcomputing.com wrote: On Jun 14, 2011, at 3:17 AM, Eric Lemoine wrote: Here's our TypeEngine: So dialect-specific parameters are stored in self.kwargs. I can try to add a test to SQLAlchemy if you indicate me where this test should go. thanks, I need to add the elements of it to the user-defined types tests in test.sql.test_types. I also want to figure out what that _compiler_dispatch call is about, I talked to Tobias Sauerwein, who wrote this code. Here's his answer: --- We had to do this for MS-SQL. Take a look at 'mssql.CastDBSpatialElementFunction': https://bitbucket.org/geoalchemy/geoalchemy/src/b2e6de8b2b1a/geoalchemy/mssql.py#cl-83 The function '_compiler_dispatch' just makes sure, that we are using the correct geometry type in the cast. -- have you tried subclassing UserDefinedType instead ? Not yet. If you tell me that's what we should do I'll give it a try. Cheers, -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: porting GeoAlchemy to 0.7
On Thu, Jun 9, 2011 at 7:21 PM, Michael Bayer mike...@zzzcomputing.com wrote: On Jun 9, 2011, at 12:37 PM, Eric Lemoine wrote: On Thu, Jun 9, 2011 at 6:28 PM, Michael Bayer mike...@zzzcomputing.com wrote: That's the default adaption provided by TypeEngine.adapt(). Provide your own adapt() that does what's needed. For examples see Interval, Enum. Ok, I'll take a look at adapt(). Note that our Geometry type isn't specific to Oracle though. When you get it going, if you can show us what you're doing, we can create a prototypical version of your type, demonstrating the kind of add new arguments per dialect functionality it has, and add it to our test suite, to ensure those usage patterns don't break. SQLAlchemy usually uses distinct type classes per backend to handle backend-specific arguments, so your approach of allowing DB-specific keyword arguments to a single type, which while entirely appropriate in your case, isn't a pattern we test for at the moment. Hi Michael Here's our TypeEngine: class GeometryBase(TypeEngine): Base Geometry column type for all spatial databases. Converts bind/result values to/from a generic Persistent value. This is used as a base class and overridden into dialect specific Persistent values. name = 'GEOMETRY' def __init__(self, dimension=2, srid=4326, spatial_index=True, **kwargs): self.dimension = dimension self.srid = srid self.spatial_index = spatial_index self.kwargs = kwargs super(GeometryBase, self).__init__() def bind_processor(self, dialect): def process(value): if value is not None: if isinstance(value, SpatialElement): if isinstance(value.desc, SpatialElement): return value.desc.desc return value.desc else: return value else: return value return process def result_processor(self, dialect, coltype=None): def process(value): if value is not None: return PersistentSpatialElement(value) else: return value return process def _compiler_dispatch(self, *args): Required for the Cast() operator when used for the compilation of DBSpatialElement return self.name def adapt(self, cls, **kwargs): return cls(dimension=self.dimension, srid=self.srid, spatial_index=self.spatial_index, **self.kwargs) So dialect-specific parameters are stored in self.kwargs. I can try to add a test to SQLAlchemy if you indicate me where this test should go. Cheers -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: porting GeoAlchemy to 0.7
On Mon, Jun 6, 2011 at 9:47 PM, Eric Lemoine eric.lemo...@camptocamp.com wrote: Hi i'm currently in the process of porting GeoAlchemy to SQLAlchemy 0.7. The first issue I'm having is related to before_create and after_create DDL listeners we have in GeoAlchemy. We use before_create and after_create listeners to prevent SQLA from adding the geometry column, and do it ourselves. Basically, the before_create function removes the geometry column from table._columns, and the after_create function adds the geometry column by calling the AddGeometryColumn SQL function. I'm trying to use a similar mechanism with 0.7, relying on before_create and after_create event listeners. That doesn't work, because setting table._colums seems to have no effect, i.e. SQLA still attempts to add the gemetry column. I've been thinking about resetting table.c (setting it to None or something) and using table.append_column to add all columns but the geometry column in before_create, but I'm wondering if that's the proper way. Thanks for any guidance on that, PS: I was hoping to get inspiration from examples/postgis.py, but this example looks outdated. Maybe it should be removed from the 0.7 code base. Hi Here's another issue with porting GeoAlchemy to SQLAlchemy 0.7. So GeoA defines a TypeEngine, which looks like this: class Geometry(TypeEngine): def __init__(self, dimension=2, srid=4326, spatial_index=True, **kwargs): self.dimension = dimension self.srid = srid self.spatial_index = True self.kwargs = kwargs super(GeometryBase, self).__init__() Using the Geometry type with Oracle requires passing an additional argument to the constructor, namely diminfo: Geometry(dimension=2, srid=4326, spatial_index=True, diminfo='the_diminfo_string') Then our Oracle-specific code uses type.kwargs['diminfo'] to access the diminfo value. This worked well with SQLA 0.6, but it doesn't work with SQLA 0.7. It doesn't work with 0.7 because SQLA may clone the type instance, and because of the way SQLA clones object (constructor_copy), the clone does not have self.kwargs['diminfo']. What is the recommended way to address the issue? We've considered using an additional_args argument: class Geometry(TypeEngine): def __init__(self, dimension=2, srid=4326, spatial_index=True, additional_args={}, **kwargs): self.dimension = dimension self.srid = srid self.spatial_index = True self.additional_args = additional_args self.kwargs = kwargs super(GeometryBase, self).__init__() which would be used like this: Geometry(dimension=2, srid=4326, spatial_index=True, additional_args={'diminfo'='the_diminfo_string'}) but introducing an additional_args argument doesn't look very pythonic. Thanks a lot for any guidance on the way to address the issue. -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: porting GeoAlchemy to 0.7
On Thu, Jun 9, 2011 at 6:28 PM, Michael Bayer mike...@zzzcomputing.com wrote: On Jun 9, 2011, at 3:57 AM, Eric Lemoine wrote: Hi Here's another issue with porting GeoAlchemy to SQLAlchemy 0.7. So GeoA defines a TypeEngine, which looks like this: class Geometry(TypeEngine): def __init__(self, dimension=2, srid=4326, spatial_index=True, **kwargs): self.dimension = dimension self.srid = srid self.spatial_index = True self.kwargs = kwargs super(GeometryBase, self).__init__() Using the Geometry type with Oracle requires passing an additional argument to the constructor, namely diminfo: Geometry(dimension=2, srid=4326, spatial_index=True, diminfo='the_diminfo_string') Then our Oracle-specific code uses type.kwargs['diminfo'] to access the diminfo value. This worked well with SQLA 0.6, but it doesn't work with SQLA 0.7. It doesn't work with 0.7 because SQLA may clone the type instance, and because of the way SQLA clones object (constructor_copy), the clone does not have self.kwargs['diminfo']. That's the default adaption provided by TypeEngine.adapt(). Provide your own adapt() that does what's needed. For examples see Interval, Enum. Ok, I'll take a look at adapt(). Note that our Geometry type isn't specific to Oracle though. Thanks again. -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: porting GeoAlchemy to 0.7
Here's the code I've come up with: class GeometryDDL(object): try: from sqlalchemy import event except ImportError: # SQLAlchemy 0.6 use_event = False columns_attribute = '_columns' else: # SQLALchemy 0.7 use_event = True columns_attribute = 'columns' def __init__(self, table): if self.use_event: event.listen(table, 'before_create', self.before_create) event.listen(table, 'before_drop', self.before_drop) event.listen(table, 'after_create', self.after_create) event.listen(table, 'after_drop', self.after_drop) else: for e in ('before-create', 'after-create', 'before-drop', 'after-drop'): table.ddl_listeners[e].append(self) self._stack = [] def __call__(self, event, table, bind): spatial_dialect = DialectManager.get_spatial_dialect(bind.dialect) if event in ('before-create', 'before-drop'): Remove geometry column from column list (table._columns), so that it does not show up in the create statement (create table tab (..)). Afterwards (on event 'after-create') restore the column list from self._stack. regular_cols = [c for c in table.c if not isinstance(c.type, Geometry)] gis_cols = set(table.c).difference(regular_cols) self._stack.append(table.c) setattr(table, self.columns_attribute, expression.ColumnCollection(*regular_cols)) if event == 'before-drop': for c in gis_cols: spatial_dialect.handle_ddl_before_drop(bind, table, c) elif event == 'after-create': setattr(table, self.columns_attribute, self._stack.pop()) for c in table.c: if isinstance(c.type, Geometry): spatial_dialect.handle_ddl_after_create(bind, table, c) elif event == 'after-drop': setattr(table, self.columns_attribute, self._stack.pop()) def before_create(self, target, connection, **kw): self('before-create', target, connection) def before_drop(self, target, connection, **kw): self('before-drop', target, connection) def after_create(self, target, connection, **kw): self('after-create', target, connection) def after_drop(self, target, connection, **kw): self('after-drop', target, connection) Thanks, -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] porting GeoAlchemy to 0.7
Hi i'm currently in the process of porting GeoAlchemy to SQLAlchemy 0.7. The first issue I'm having is related to before_create and after_create DDL listeners we have in GeoAlchemy. We use before_create and after_create listeners to prevent SQLA from adding the geometry column, and do it ourselves. Basically, the before_create function removes the geometry column from table._columns, and the after_create function adds the geometry column by calling the AddGeometryColumn SQL function. I'm trying to use a similar mechanism with 0.7, relying on before_create and after_create event listeners. That doesn't work, because setting table._colums seems to have no effect, i.e. SQLA still attempts to add the gemetry column. I've been thinking about resetting table.c (setting it to None or something) and using table.append_column to add all columns but the geometry column in before_create, but I'm wondering if that's the proper way. Thanks for any guidance on that, PS: I was hoping to get inspiration from examples/postgis.py, but this example looks outdated. Maybe it should be removed from the 0.7 code base. -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Pypi release policy
On Monday, February 14, 2011, Michael Bayer mike...@zzzcomputing.com wrote: On Feb 14, 2011, at 12:11 PM, Tarek Ziadé wrote: On Mon, Feb 14, 2011 at 6:00 PM, Michael Bayer mike...@zzzcomputing.com wrote: On Feb 14, 2011, at 5:53 AM, Tarek Ziadé wrote: On Mon, Feb 14, 2011 at 11:44 AM, Wichert Akkerman wich...@wiggy.net wrote: .. you don't release at pypi a version that breaks the latest stable. or if you do, you check the hidden attribute on that release, to avoid this problem with installers SQLAlchemy 0.7b1 is hidden. The hidden flag only hides it from humans though, not from setuptools. oh true...I forgot it's not hidden in the simple index :/ OK so you're the expert - how does one release a beta on pypi without crashing everyone's stable install ? should i just stick to sourceforge until final release ? Unfortunately, Setuptools will pick the latest version and won't care about beta tags (zc.buildout has such feature -- prefer-final, and Distutils2 too) So I guess the best way with the current eco-system is to avoid pushing any unstable release to PyPI or... if you have the time to do so, push a new 0.7 beta that makes sure people that run on the latest 0.6 can run it -- with deprecation warnings all over the place :) I'll pull it off of Pypi. We definitely do push for as much backwards compat as possible, but the release does remove / hard change things that were raising warnings throughout 0.6, as well as lots of apps rely upon undocumented behaviors which may have changed, so its inevitable that some apps will need adjustment. But the code itself should not be considered as bug-free as a stable release so in any case its not appropriate to push it into installations without explicit consent. But aren't apps supposed to use =0.6.99 to avoid backward compats issues? Apps that don't will also break when 0.7 final is on pypi. -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Pypi release policy
On Tuesday, February 15, 2011, Tarek Ziadé ziade.ta...@gmail.com wrote: On Tue, Feb 15, 2011 at 3:27 PM, Eric Lemoine eric.lemo...@camptocamp.com wrote .. But aren't apps supposed to use =0.6.99 to avoid backward compats issues? Apps that don't will also break when 0.7 final is on pypi. There are different things here: 1/ PyPI allows projects to publish any release, and easy_install will pick the latest one, whether it's a final (==stable) or not. You can publish your trunk if you want. 2/ An application that defines a dependency can define it in different flavors: a - Give me the latest release that was made available at PyPI b - Give me the latest release from the 0.6.x series, it can use a 0.7 or 0.6.99 c - Give me version XX --- best practice once in production For applications that are using 2.a, the interpretation of most people is that the latest release at PyPI they are depending on is not a development release. If they want a development release, they do it explicitly in their environment to leave on the edge. 3/ a user types easy_install SQLAlchemy and wants the latest stable So, yeah, when 0.7.1 final will be out, some apps will break -- but they've been warned and they can choose to change their code or pin their dependency to the 0.6.x series. But right now, it's a development release that has been published for feedback as opposed to a final release. The less disruptive process (until distutils2 is available) in that case is to let people opt in to be beta testers, and let SQLAlchemy means latest stable, whether it's called by easy_install SQLAlchemy or in the install_requires option in setuptools. Thanks for the detailed response Tarek. It means that beta testers won't be able to download SQLAlchemy betas from PyPI, and will have to install betas from source? Cheers, -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] INSERT RETURNING question
Hi Probably a very simple question. I use the ORM for inserts, with postgres 8.3. How can I get the ids resulting from my inserts' RETURNING clauses? I haven't been able to find the information in the doc. Thanks a lot, -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: INSERT RETURNING question
On Tuesday, January 18, 2011, Eric Lemoine eric.lemo...@camptocamp.com wrote: Hi Probably a very simple question. I use the ORM for inserts, with postgres 8.3. How can I get the ids resulting from my inserts' RETURNING clauses? I haven't been able to find the information in the doc. Doing just_inserted_obj.id causes a SELECT ... WHERE id= query, which I'd like to avoid. -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: INSERT RETURNING question
On Tue, Jan 18, 2011 at 11:33 PM, Michael Bayer mike...@zzzcomputing.com wrote: On Jan 18, 2011, at 4:47 PM, Eric Lemoine wrote: On Tuesday, January 18, 2011, Eric Lemoine eric.lemo...@camptocamp.com wrote: Hi Probably a very simple question. I use the ORM for inserts, with postgres 8.3. How can I get the ids resulting from my inserts' RETURNING clauses? I haven't been able to find the information in the doc. Doing just_inserted_obj.id causes a SELECT ... WHERE id= query, which I'd like to avoid. That sounds like you're calling commit() which is expiring all data, resulting in a re-fetch when you hit .id. Just call session.flush(), then get the .id from your objects, before a commit() occurs. I did not expect that obj.id (the primary key) would be expired, as doing SELECT id WHERE id= didn't make sense to me. Thanks. -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] passive Query
Thanks Mike and Alexandre for your responses. On Mon, Jan 17, 2011 at 5:27 PM, Michael Bayer mike...@zzzcomputing.com wrote: Well obviously you could mock it. I don't want to mock it because I want to check the resulting query statement. A few years back Jason wrote a statement recorder/replayer which can simulate a DBAPI receiving/returning an exact series of statements/results, it involves first recording the database interaction into memory and specifically is not for mocking, though I wonder if the idea could be adapted somehow. That's in our source tree under test/lib/engines/ReplayableSession. Usually for testing I use a real DB and just run the tests in a transaction that's rolled back. This approach works very well, and is described at http://www.sqlalchemy.org/docs/orm/session.html#joining-a-session-into-an-external-transaction . That's good to know. -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] passive Query
Hello For testing purposed I'd like to do query.all(). query.get() and query.count() with no actual communication with the DBMS. Is this possible? Thanks a lot, -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Insert record error
On Thursday, January 13, 2011, William Hudspeth bhudsp...@edac.unm.edu wrote: Thanks for responding, I tried changing the geometry object definition, as well as the model definition and I get the same result... dream_geom=MULTIPOLYGON((-120.000 43.833,-96.833 43.833,-96.833 26.000,-120.000 26.000,-120.000 43.833)) Your multipolygon WKT is ill-formed. Try with three opening and closing brackets. If that still doesn't work I suggest that we continue the discussion on the GeoAlchemy list. Cheers, -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Mapper.get_property question
On Wednesday, January 12, 2011, Michael Bayer mike...@zzzcomputing.com wrote: The name of the property from the mapper perspective is name. That's the contract of declarative: class MyClass(some_declarative_base): __tablename__ = 'j' x = Column(Integer, key='z') y = Column('p', Integer, key='w') == t = Table('j', metadata, Column('x', Integer, key='z'), Column('p', Integer, key='w') ) mapper(MyClass, t, properties={ 'x':t.c.z, 'y':t.c.w }) if you were just using mapper(), then Column.key is what specifies the attribute names in the mapping. To go from name_key, you could say class_mapper(MyClass)._columntoproperty[t.c.name_key]. It's now clear. Thanks a lot. -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Insert record error
On Thursday, January 13, 2011, wilbur bhudsp...@edac.unm.edu wrote: Hi, I am getting an error trying to insert records into a PostgreSQL database with SQLAlchemy. My table definition: class dream4_eta_15km_pm10(Base): __tablename__='pm10_dream_rasters' gid=Column(Integer,Sequence('pm10_dream_rasters_gid_seq'),primary_key=True) rundate=Column(DateTime(timezone=True),nullable=False) datetime=Column(DateTime(timezone=True),nullable=False) location_raw=Column(VARCHAR,nullable=False) location_class=Column(VARCHAR,nullable=False) timezone=Column(DateTime(timezone=False),nullable=False) the_geom=GeometryColumn(Polygon(2),nullable=False) max_pm10=Column(Float,nullable=False) mean_pm10=Column(Float,nullable=False) and, my postgresql table definition: reason=# \d pm25_dream_rasters Table public.pm25_dream_rasters Column | Type | Modifiers + +-- gid | integer | not null default nextval('pm25_dream_rasters_gid_seq'::regclass) the_geom | geometry | rundate | timestamp with time zone | datetime | timestamp with time zone | location_raw | character varying | location_class | character varying | timezone | timestamp(6) without time zone | max_pm25 | double precision | mean_pm25 | double precision | Indexes: pm25_dream_rasters_pkey PRIMARY KEY, btree (gid) pm25_24hour_select btree (((datetime - rundate) '24:00:00'::interval)) pm25_dream_raster_timezone_idx btree (timezone) Check constraints: enforce_dims_the_geom CHECK (ndims(the_geom) = 2) enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL) enforce_srid_the_geom CHECK (srid(the_geom) = 4326) And I get the following error: sqlalchemy.exc.ProgrammingError: (ProgrammingError) can't adapt 'INSERT INTO pm10_dream_rasters (gid, rundate, datetime, location_raw, location_class, timezone, the_geom, max_pm10, mean_pm10) VALUES (% (gid)s, %(rundate)s, %(datetime)s, %(location_raw)s, % (location_class)s, %(timezone)s, GeomFromText(%(GeomFromText_1)s, % (GeomFromText_2)s), %(max_pm10)s, %(mean_pm10)s)' {'rundate': '2011-01-10 00:00:00Z', 'location_raw': '/geodata/edac_dream/ eta_dream4/dream_raw_tiff/20110110/D011011_t00_pm10.tif', 'GeomFromText_1': 'POLYGON((-120.000 43.833,-96.833 43.833,-96.833 26.000,-120.000 26.000,-120.000 43.833))', 'location_class': '/geodata/ edac_dream/eta_dream4/dream_reclass_tiff/20110110/ D011011_t00_pm10.tif', 'datetime': '2011-01-10 00:00:00Z', 'mean_pm10': 0.0, 'gid': 1372073L, 'timezone': '2011-01-10 00:00:00', 'GeomFromText_2': 4326, 'max_pm10': 0.0} I am a little concerned about the value that is being set for the 'gid' variable (e.g. 1372073L). Does this simply indicate some kind of long integer? Is it a possible source of my error? You're sending a Polygon while you have a geometrytype = MultiPolygon constraint. Could it be your problem? -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Mapper.get_property question
Hi Mapper.get_property doesn't behave as I'd expect it to, so I'd just like to know if my understanding is incorrect. In the following testcase I'd expect the first test to pass and the second to fail, but I get the opposite. Thanks a lot. from sqlalchemy import Column, Integer, String from sqlalchemy.orm.util import class_mapper from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.exc import InvalidRequestError Base = declarative_base() class User(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) name = Column(String, key='name_key') try: class_mapper(User).get_property('name_key') except InvalidRequestError: print error 1 try: class_mapper(User).get_property('name') except InvalidRequestError: print error 2 -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: engine bound to Session
On Sun, May 30, 2010 at 6:20 PM, Lance Edgar lance.ed...@gmail.com wrote: On 5/30/2010 9:43 AM, Eric Lemoine wrote: On Sun, May 30, 2010 at 4:39 PM, Eric Lemoine eric.lemo...@camptocamp.com wrote: Hello I use Pylons. Pylons does: Session = scoped_session(sessionmaker()) and then: Session.configure(bind=engine) My question: with a reference to Session how can I get the engine that's bound to it? I tried Session.get_bind() but I get this error: TypeError: get_bind() takes at least 2 arguments (1 given). Session.get_bind(mapper=None) seems to do the trick, but I'm not sure this is the good way. Could someone confirm? Thanks again. Not sure if this is proper, but I've always just used Session.bind. Then again I've really only used it when testing things out, because at runtime my engines are defined in such a way that I never need to discover them through the Session. Thanks for your response. I just noticed that Pylons itself uses Session.bind [*], so I guess it should be ok. [*] http://pylonshq.com/project/pylonshq/browser/pylons/templates/default_project/%2Bpackage%2B/websetup.py_tmpl#L21 -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- 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] engine bound to Session
Hello I use Pylons. Pylons does: Session = scoped_session(sessionmaker()) and then: Session.configure(bind=engine) My question: with a reference to Session how can I get the engine that's bound to it? I tried Session.get_bind() but I get this error: TypeError: get_bind() takes at least 2 arguments (1 given). Thanks a lot, -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: engine bound to Session
On Sun, May 30, 2010 at 4:39 PM, Eric Lemoine eric.lemo...@camptocamp.com wrote: Hello I use Pylons. Pylons does: Session = scoped_session(sessionmaker()) and then: Session.configure(bind=engine) My question: with a reference to Session how can I get the engine that's bound to it? I tried Session.get_bind() but I get this error: TypeError: get_bind() takes at least 2 arguments (1 given). Session.get_bind(mapper=None) seems to do the trick, but I'm not sure this is the good way. Could someone confirm? Thanks again. -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- 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] postgresql text search
Hi Are there examples of using PostgreSQL's full-text search with SQLAlchemy? I'm interested in any kind of information about that. Thanks, -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- 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] postgresql text search
On Fri, May 28, 2010 at 3:12 PM, Michael Bayer mike...@zzzcomputing.com wrote: we support the to_tsquery() syntax through the match() operator: http://www.sqlalchemy.org/trac/browser/test/dialect/test_postgresql.py#L1748 Thank you very much. -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- 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] postgres character(num) issue
Hi I use PostgreSQL and I have this column if one of my tables: ens character(60). I use autoload in my model for that table. SQLAlchemy 0.5.8 always gives me strings with 60 characters (with trailing spaces) when doing queries. I wouldn't expect that. Is there a way to change that behavior? Thanks a lot, -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- 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] postgres character(num) issue
On Thu, Apr 15, 2010 at 2:12 PM, Michael Bayer mike...@zzzcomputing.com wrote: you'd use VARCHAR, CHAR is fixed width. If you want to force it, use a TypeDecorator that calls strip() on the returned values. Yes, thanks. I got confused because of the concat operator (||) in PostgreSQL. The operator considers the blank characters as semantically insignificant and remove them. -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: missing table in FROM CLAUSE
On Thu, Jan 7, 2010 at 11:29 AM, Eric Lemoine eric.lemo...@camptocamp.com wrote: Hi I'm having a problem with a query where the FROM clause doesn't include a table that is actually required for the query. Here's my code: -- subq = subquery(None, columns=[communes.c.nom.label(commune)], whereclause=and_(fiches_aggregees.c.id_zonage== zonages.c.gid, zonages.c.gid ==croisements.c.gid_zonage, croisements.c.gid_commune == communes.c.gid), distinct=True) query = select([suq.c.commune, func.count('*').label('nbfiches')), func.sum(fiches_aggregees.c.nbtaxons).label('nbtaxons')].group_by(subq.c.commune) meta.Session.execute(query).fetchall() -- Here's the query as seen in the postgres logs: -- SELECT anon_1.commune, count(E'*') AS nbfiches, sum(fiches_agregees.nbtaxons) AS nbtaxons FROM (SELECT DISTINCT communes.nom AS commune FROM communes, zonages, croisements WHERE fiches_agregees.id_zonage = zonages.gid AND zonages.gid = croisements.gid_zonage AND croisements.gid_commune = communes.gid) AS anon_1, fiches_agregees GROUP BY anon_1.commune -- The query fails because the fiches_agregees table isn't in the FROM clause of the subquery. I can't figure out why it's not, given there's a reference to it in the WHERE clause. Got it: subq = subquery(..., correlate=False) Thanks, -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- 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] issue with column_property
Hi Here's my case: I have - my own TypeEngine class, MyTypeEngine - a Table with a Column using MyTypeEngine: table = Table(tablename, metadata, Column(columname, MyTypeEngine()), autoload=True, autoload_with=engine ) - a class: class MyClass(object): pass - and a mapping: mapper(MyClass, table) Pretty standard. Now, instead of queries like this: SELECT columnname, ... FROM tablename I'd like queries like this: SELECT somefunc(columnname),... FROM tablename I thought I could get that by overriding my column's ColumnProperty with something like that: mapper(MyClass, table, properties={ columname: column_property( sql.func.somefunc(table.c.columname).label(columnname) ) }) but it doesn't work as I'd like because it seems that my TypeEngine is no longer involved after querying my table - the function returned by my TypeEngine's result_process method doesn't seem to be called. Is this expected? Do I have solutions to that problem? Thanks a lot, -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: issue with column_property
On Tue, Sep 29, 2009 at 9:04 PM, Michael Bayer mike...@zzzcomputing.com wrote: Eric Lemoine wrote: Hi Here's my case: I have - my own TypeEngine class, MyTypeEngine - a Table with a Column using MyTypeEngine: table = Table(tablename, metadata, Column(columname, MyTypeEngine()), autoload=True, autoload_with=engine ) - a class: class MyClass(object): pass - and a mapping: mapper(MyClass, table) Pretty standard. Now, instead of queries like this: SELECT columnname, ... FROM tablename I'd like queries like this: SELECT somefunc(columnname),... FROM tablename I thought I could get that by overriding my column's ColumnProperty with something like that: mapper(MyClass, table, properties={ columname: column_property( sql.func.somefunc(table.c.columname).label(columnname) ) }) but it doesn't work as I'd like because it seems that my TypeEngine is no longer involved after querying my table - the function returned by my TypeEngine's result_process method doesn't seem to be called. Is this expected? Do I have solutions to that problem? somefunc() needs to specify the return type using type_=MyType, unless its a known generic function that knows to pass through the type of the first argument as that of the result (such as lower(), for example). it's a postgis function. How do I specify the return type using type_=MyType? Sorry i didn't follow you on that one. thanks -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] dynamic query selection
Hello list I have a simple mapping between a class and a table. What I'd like to be able to do is dynamically change the query selection (based on some HTTP param). More precisely I'd like that the ORM generate SELECT some_sql_function(col1), col2, col3 FROM ... instead of the default SELECT col1, col2, col3, FROM ... And, as I said, I'd like this to be dynamic, so I don't want this to be hardwired in my mapping definition. Is this possible? Thanks a lot, -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: dynamic query selection
On Sunday, September 13, 2009, Michael Bayer mike...@zzzcomputing.com wrote: On Sep 13, 2009, at 5:32 AM, Eric Lemoine wrote: Hello list I have a simple mapping between a class and a table. What I'd like to be able to do is dynamically change the query selection (based on some HTTP param). More precisely I'd like that the ORM generate SELECT some_sql_function(col1), col2, col3 FROM ... instead of the default SELECT col1, col2, col3, FROM ... And, as I said, I'd like this to be dynamic, so I don't want this to be hardwired in my mapping definition. Is this possible? Thanks a lot, the most straightforward way is to use the Query in that fashion: query(func.some_sql_function(MyClass.col1), MyClass.col2, MyClass.col3) However, i suspect you might be wanting to load MyClass instances with the value of col1 replaced. you guessed correctly it might be easiest just to say: query(func.some_sql_function(MyClass.col1), MyClass) and then manually piece together the first column onto the instances as they are received. I think I'll go with that. Thanks Michael. The third way which is the least visually appealing is to use query.from_statement(), rows from any statement you pass within will be mapped to the columns based on names. the statement within can be a select() construct or a string, or you can even use another Query () and then call query.statement on it to get at the select(). -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: connectionless queries with Spatial data (PostGIS)
On Sun, Apr 19, 2009 at 10:17 PM, Michael Bayer mike...@zzzcomputing.com wrote: I dont know much about MapFish but it appears to have a client/server, calls itself a web applciation framework, and seems completely complicated compared to just we'd like to use PostGIS with SQLAlchemy. I'm just looking for smooth integration with PostGIS and other geospatial SQL extensions. If we decide my observation that GeoDjango has done a lot of work that needs to be re-done isn't really valid, and everything they've done is only useful for Django web applications, then that idea should be scrapped, and a simple library which builds upon SQLAlchemy expression constructs should be created. Hi MapFish's python package [1] basically provides the following: 1- a paster_create_template entry point [2] for creating MapFish applications with paster create -t mapfish. MapFish applications are basically Pylons applications with an extra command (paster mf-layer) for creating RESTful web services relying on PostGIS tables 2- a Geometry sqlalchemy.types.TypeEngine [3] # example: table = Table('postgis_table_name', metadata, Column('gid', Integer, primary_key=True), Column('the_geom', Geometry(4326)) ) Geometries read from PostGIS are converted into Shapely [4] geometries. 3- the implementation of the MapFish RESTful protocol for reading, creating, updating and deleting geographic objects [5] We're interested in feedback and collaboration on that.Thanks! [1] https://trac.mapfish.org/trac/mapfish/browser/trunk/MapFish/server/python [2] https://trac.mapfish.org/trac/mapfish/browser/trunk/MapFish/server/python/mapfish/util.py [3] https://trac.mapfish.org/trac/mapfish/browser/trunk/MapFish/server/python/mapfish/sqlalchemygeom.py [4] http://trac.gispython.org/lab/wiki/Shapely [5] https://trac.mapfish.org/trac/mapfish/browser/trunk/MapFish/server/python/mapfish/lib/protocol.py -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: connectionless queries with Spatial data (PostGIS)
On Sun, Apr 19, 2009 at 11:37 PM, Michael Bayer mike...@zzzcomputing.com wrote: how easily can the SQLAlchemy constructs be used independently of the Pylons/RESTful stuff ? By installing the package and only import mapfish.sqlalchemygeom. But a separate python package may make sense, and I guess what we could do that if people are interested. do you include comparator objects like those demonstrated in the postgis.py example ? e.g.: No, but I'd be interested in looking into that more closely. print session .query(Road).filter(Road.road_geom.intersects(r1.road_geom)).all() session.query(Road).filter(Road.road_geom == 'LINESTRING(189412 252431,189631 259122)').one() the GeomFromText function is called implicitly with the above examples. -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Selecting from a self-referential mapper: recursive joins?
Eric, a friendly comment: you do sound as crazy as Svil :-) Eric 2008/11/25, Eric Ongerth [EMAIL PROTECTED]: Yes, I am very glad to be free of multiversion and bitemporal concerns, although I will eventually be setting this up for partial multimaster asynchronous replication (but with a lot of intentional compromises in order to avoid the major, currently unsolved, problems with that entire field). As for different NodeTypes and polymorphic associations to values, I am indeed taking part in something similar, but I do not allow the polymorphism to exist on the Python side of the code | database distinction, nor do I even allow the polymorphism to even enter the ORM layer in mappers. All of my polymorphism is strictly contained within the database using a 'vertical tables' approach. All of that dbcook stuff scares me, though I think I can see why you want it. I will eventually need to wade into the waters of stricter type checking and conversion on my tables, and that will get me into a lot of similar concerns but hopefully not as deeply! Also, yes, there are definitely a lot of 80/20 concerns and willingness go for creative and seemingly partial solutions when appropriate. I found this wiki article very interesting (though I had already reinvented most of what it refers to before I ever knew about it): http://en.wikipedia.org/wiki/Entity-attribute-value_model It was encouraging to invent a wheel, later to find that the design decisions I made aligned nicely with an entire existing field of wheels. In this case I don't consider the work of reinvention to be wasted, because this project has been my vehicle for learning Python, sqlalchemy and much more. Wish I could offer to work with you, but this is my spare-time project, I don't even have a coding job yet. I'm supporting myself with manual labor while rolling this out. I think my project will at least get me a decent job once it's far enough into beta stage to show to anyone; or possibly it could become my own business next year. Eric On Nov 25, 1:04 pm, [EMAIL PROTECTED] wrote: Thanks for your reply. I have been following your posts with interest over the past half year (or I thought even longer). At first I thought you were crazy. But now I've found myself creating a model of similar complexity, as necessary to express the domain I'm working on. i think you're better than me, as u dont have the burden of bitemporal/multiversion stuff, and the completely different NodeTypes (hence polymorphic-assoc to values). seems your domain is better mathematicalizable - or u havent hit the edge of the door yet. having less types is also a relief, u dont need a dbcook layer to automate/hide mappings and value-type-related stuff (right now in a prototype scenario i have say 100 tables, 20 m2m assoc, 220 foreign keys - finally will probably be 200/100/600 or similar... it's unthinkable to handle them one by one, esp. if most of team has never seen sql). 1st make sure your model is correct. then, this full_heritage() func (mine is called similar, order_of_inheritance()) has the trouble of firing too many queries itself. once u start counting the queries fired around your current model, then u'll realize that such step-by-step recursive stuff - which is nice and pretty ok for c/python/lisp/whatever - isn't going to be very fast... but this also depends on the size of searchables. yours are simple and maybe 1000 queries arent going to be too bad. in my case, 1 awful complex bitemporal query is 100x faster than 1000 smaller bitemporal ones. i found it might be better in certain cases to get more data (2x) than needed and filter it further in python, than go crazy expressing the filter in flat set arithmetics (what sql is, more or less). (small) compromises are not a bad thing if placed properly. beware, my initial code about this graph-data-inheritance was the size of yours, while currently it's like 5x bigger - but can work either way (oh well 95% there). the moral i got from all this is, if the whole wardrobe wont get through the door, there are a lot of seemingly partial solutions that at the end may do better than initial requirement - and should NOT be ignored - but are notoriously difficult to guess as the initial target obvious solution obscures them. be it turning upside down, disassembling / cutting (the wardrobe or the door.. or another door), replacing with 3 small cupboards... swapping rooms... burning it and using plain hangers off the walls... moving elsewhere... have fun, and eeer... i'm sorta looking for job. too bad this _very_ interesting times project doesnt pay bills well. svilwww.svilendobrev.com The purpose of my model is to ingest all of the easily expressible facts about the characteristics ('features') of categories and (recursive) subcategories of items within specific sub-regions of a domain of physical items, and to encode and
[sqlalchemy] Re: polymorphic_union and key
On Mon, Oct 20, 2008 at 7:46 PM, Michael Bayer [EMAIL PROTECTED] wrote: its not a known issue but sounds like a bug. a very concise test case which we can use as a unit test would help here. I hope this is good enough: from sqlalchemy import * from sqlalchemy.orm import * engine = create_engine('sqlite://', echo=True) metadata =MetaData(engine) Session = scoped_session(sessionmaker(bind=engine)) refugees_table = Table('refugee', metadata, Column('refugee_fid', Integer, primary_key=True), Column('refugee_name', Unicode, key='name')) offices_table = Table('office', metadata, Column('office_fid', Integer, primary_key=True), Column('office_name', Unicode, key='name')) pjoin = polymorphic_union({ 'refugee': refugees_table, 'office': offices_table }, 'type', 'pjoin') metadata.create_all() class Location(object): pass class Refugee(Location): pass class Office(Location): pass location_mapper = mapper(Location, pjoin, polymorphic_on=pjoin.c.type, polymorphic_identity='location') refugee_mapper = mapper(Refugee, refugees_table, inherits=location_mapper, concrete=True, polymorphic_identity='refugee') office_mapper = mapper(Office, offices_table, inherits=location_mapper, concrete=True, polymorphic_identity='office') engine.execute(insert into refugee values(1, \refugee1\)) engine.execute(insert into refugee values(2, \refugee2\)) engine.execute(insert into office values(1, \office1\)) engine.execute(insert into office values(2, \office2\)) # these two pass, good! assert Session.query(Refugee).get(1).name == refugee1 assert Session.query(Refugee).get(2).name == refugee2 #assert Session.query(Office).get(1).office_name == office1 #assert Session.query(Office).get(2).office_name == office2 # these two fail, bad! assert Session.query(Office).get(1).name == office1 assert Session.query(Office).get(2).name == office2 -- Eric --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~--- from sqlalchemy import * from sqlalchemy.orm import * engine = create_engine('sqlite://', echo=True) metadata =MetaData(engine) Session = scoped_session(sessionmaker(bind=engine)) refugees_table = Table('refugee', metadata, Column('refugee_fid', Integer, primary_key=True), Column('refugee_name', Unicode, key='name')) offices_table = Table('office', metadata, Column('office_fid', Integer, primary_key=True), Column('office_name', Unicode, key='name')) pjoin = polymorphic_union({ 'refugee': refugees_table, 'office': offices_table }, 'type', 'pjoin') metadata.create_all() class Location(object): pass class Refugee(Location): pass class Office(Location): pass location_mapper = mapper(Location, pjoin, polymorphic_on=pjoin.c.type, polymorphic_identity='location') refugee_mapper = mapper(Refugee, refugees_table, inherits=location_mapper, concrete=True, polymorphic_identity='refugee') office_mapper = mapper(Office, offices_table, inherits=location_mapper, concrete=True, polymorphic_identity='office') engine.execute(insert into refugee values(1, \refugee1\)) engine.execute(insert into refugee values(2, \refugee2\)) engine.execute(insert into office values(1, \office1\)) engine.execute(insert into office values(2, \office2\)) # these two pass, good! assert Session.query(Refugee).get(1).name == refugee1 assert Session.query(Refugee).get(2).name == refugee2 #assert Session.query(Office).get(1).office_name == office1 #assert Session.query(Office).get(2).office_name == office2 # these two fail, bad! assert Session.query(Office).get(1).name == office1 assert Session.query(Office).get(2).name == office2
[sqlalchemy] Re: polymorphic_union and key
I have to check but I think I get the same error if I name the keys differently in each table. Eric 2008/10/21, [EMAIL PROTECTED] [EMAIL PROTECTED]: concrete polymorphism with good deal of inheritance and same-keys does not work in current SA. there's no way to differ between X.id=1 and Y.id=1 in the polymunion. there were ideas to add some type-discriminator in the union's primary key, and use that in the machinery, but AFAIK no much is done. my set of concrete-inh testcases still fails in same ways as before (i think there is one more failing since 0.5) Mike will know better. On Tuesday 21 October 2008 10:48:02 Eric Lemoine wrote: On Mon, Oct 20, 2008 at 7:46 PM, Michael Bayer [EMAIL PROTECTED] wrote: its not a known issue but sounds like a bug. a very concise test case which we can use as a unit test would help here. I hope this is good enough: from sqlalchemy import * from sqlalchemy.orm import * engine = create_engine('sqlite://', echo=True) metadata =MetaData(engine) Session = scoped_session(sessionmaker(bind=engine)) refugees_table = Table('refugee', metadata, Column('refugee_fid', Integer, primary_key=True), Column('refugee_name', Unicode, key='name')) offices_table = Table('office', metadata, Column('office_fid', Integer, primary_key=True), Column('office_name', Unicode, key='name')) pjoin = polymorphic_union({ 'refugee': refugees_table, 'office': offices_table }, 'type', 'pjoin') metadata.create_all() class Location(object): pass class Refugee(Location): pass class Office(Location): pass location_mapper = mapper(Location, pjoin, polymorphic_on=pjoin.c.type, polymorphic_identity='location') refugee_mapper = mapper(Refugee, refugees_table, inherits=location_mapper, concrete=True, polymorphic_identity='refugee') office_mapper = mapper(Office, offices_table, inherits=location_mapper, concrete=True, polymorphic_identity='office') engine.execute(insert into refugee values(1, \refugee1\)) engine.execute(insert into refugee values(2, \refugee2\)) engine.execute(insert into office values(1, \office1\)) engine.execute(insert into office values(2, \office2\)) # these two pass, good! assert Session.query(Refugee).get(1).name == refugee1 assert Session.query(Refugee).get(2).name == refugee2 #assert Session.query(Office).get(1).office_name == office1 #assert Session.query(Office).get(2).office_name == office2 # these two fail, bad! assert Session.query(Office).get(1).name == office1 assert Session.query(Office).get(2).name == office2 -- Eric --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: polymorphic_union and key
Great. Thanks! 2008/10/22, Michael Bayer [EMAIL PROTECTED]: I forgot to mention i fixed this for 0.4 + 0.5 in r5180/5181...thanks for the test ! On Oct 21, 2008, at 3:48 AM, Eric Lemoine wrote: On Mon, Oct 20, 2008 at 7:46 PM, Michael Bayer [EMAIL PROTECTED] wrote: its not a known issue but sounds like a bug. a very concise test case which we can use as a unit test would help here. I hope this is good enough: from sqlalchemy import * from sqlalchemy.orm import * engine = create_engine('sqlite://', echo=True) metadata =MetaData(engine) Session = scoped_session(sessionmaker(bind=engine)) refugees_table = Table('refugee', metadata, Column('refugee_fid', Integer, primary_key=True), Column('refugee_name', Unicode, key='name')) offices_table = Table('office', metadata, Column('office_fid', Integer, primary_key=True), Column('office_name', Unicode, key='name')) pjoin = polymorphic_union({ 'refugee': refugees_table, 'office': offices_table }, 'type', 'pjoin') metadata.create_all() class Location(object): pass class Refugee(Location): pass class Office(Location): pass location_mapper = mapper(Location, pjoin, polymorphic_on=pjoin.c.type, polymorphic_identity='location') refugee_mapper = mapper(Refugee, refugees_table, inherits=location_mapper, concrete=True, polymorphic_identity='refugee') office_mapper = mapper(Office, offices_table, inherits=location_mapper, concrete=True, polymorphic_identity='office') engine.execute(insert into refugee values(1, \refugee1\)) engine.execute(insert into refugee values(2, \refugee2\)) engine.execute(insert into office values(1, \office1\)) engine.execute(insert into office values(2, \office2\)) # these two pass, good! assert Session.query(Refugee).get(1).name == refugee1 assert Session.query(Refugee).get(2).name == refugee2 #assert Session.query(Office).get(1).office_name == office1 #assert Session.query(Office).get(2).office_name == office2 # these two fail, bad! assert Session.query(Office).get(1).name == office1 assert Session.query(Office).get(2).name == office2 -- Eric from sqlalchemy import * from sqlalchemy.orm import * engine = create_engine('sqlite://', echo=True) metadata =MetaData(engine) Session = scoped_session(sessionmaker(bind=engine)) refugees_table = Table('refugee', metadata, Column('refugee_fid', Integer, primary_key=True), Column('refugee_name', Unicode, key='name')) offices_table = Table('office', metadata, Column('office_fid', Integer, primary_key=True), Column('office_name', Unicode, key='name')) pjoin = polymorphic_union({ 'refugee': refugees_table, 'office': offices_table }, 'type', 'pjoin') metadata.create_all() class Location(object): pass class Refugee(Location): pass class Office(Location): pass location_mapper = mapper(Location, pjoin, polymorphic_on=pjoin.c.type, polymorphic_identity='location') refugee_mapper = mapper(Refugee, refugees_table, inherits=location_mapper, concrete=True, polymorphic_identity='refugee') office_mapper = mapper(Office, offices_table, inherits=location_mapper, concrete=True, polymorphic_identity='office') engine.execute(insert into refugee values(1, \refugee1\)) engine.execute(insert into refugee values(2, \refugee2\)) engine.execute(insert into office values(1, \office1\)) engine.execute(insert into office values(2, \office2\)) # these two pass, good! assert Session.query(Refugee).get(1).name == refugee1 assert Session.query(Refugee).get(2).name == refugee2 #assert Session.query(Office).get(1).office_name == office1 #assert Session.query(Office).get(2).office_name == office2 # these two fail, bad! assert Session.query(Office).get(1).name == office1 assert Session.query(Office).get(2).name == office2 --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] polymorphic_union and key
Hi I use concrete inheritance with two tables. I therefore use polymorphic_union. Each table has a Column object defined with a key: Column(office_name, types.Unicode, key=name). And the key doesn't work for the second table, the field name exists in the mapped objects resulting from a query but it is set to None; the field office_name also exists and is properly set. I use 5.0rc1. Is it a known issue or me doing something wrong. I can post my code if necessary. Thanks a lot. Eric --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: polymorphic_union and key
Will try 2008/10/20, Michael Bayer [EMAIL PROTECTED]: its not a known issue but sounds like a bug. a very concise test case which we can use as a unit test would help here. On Oct 20, 2008, at 1:33 PM, Eric Lemoine wrote: Hi I use concrete inheritance with two tables. I therefore use polymorphic_union. Each table has a Column object defined with a key: Column(office_name, types.Unicode, key=name). And the key doesn't work for the second table, the field name exists in the mapped objects resulting from a query but it is set to None; the field office_name also exists and is properly set. I use 5.0rc1. Is it a known issue or me doing something wrong. I can post my code if necessary. Thanks a lot. Eric --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLAlchemy 0.5.0 Release Candidate 1 Released
Hi. I just wanted to mention that we've been using 0.5 since the first beta in our Pylons-based web-mapping framework (www.mapfish.org) and it has given up entire satisfaction. We caricatuy use: regular CRUD operations, type definition (for PostGIS' geometry column), and concrete table inheritance. We're about to release a new version of MapFish so we'd be very happy to see SA 0.5 out :) Eric 2008/9/11, Michael Bayer [EMAIL PROTECTED]: Hey list - I've just put out 0.5rc1. This release is *great*, as I am using it heavily every day for a project here - it's got my personal seal of approval. 0.5 truly rocks in general.In my particular application, I'm making very heavy usage of declarative, single table inheritance, SessionExtension, synonyms, and pretty involved column- oriented ORM queries.I've used it to rewrite a CMS driven application that was formerly written with Hibernate and it runs about five times faster. So if its good enough for me, it should be good enough for anyone :). In this case release candidate 1 means, API is complete and ready to be used, and we'd like to get user feedback to ensure things work as well for them as they are working for us. The migration guide has been updated and cleaned up, a good read of it at http://www.sqlalchemy.org/trac/wiki/05Migration should make the upgrade path from 0.4 pretty clear. Download SQLA 0.5.0rc1 at: http://www.sqlalchemy.org/download.html 0.5.0rc1 - orm - Query now has delete() and update(values) methods. This allows to perform bulk deletes/updates with the Query object. - The RowTuple object returned by Query(*cols) now features keynames which prefer mapped attribute names over column keys, column keys over column names, i.e. Query(Class.foo, Class.bar) will have names foo and bar even if those are not the names of the underlying Column objects. Direct Column objects such as Query(table.c.col) will return the key attribute of the Column. - Added scalar() and value() methods to Query, each return a single scalar value. scalar() takes no arguments and is roughly equivalent to first()[0], value() takes a single column expression and is roughly equivalent to values(expr).next()[0]. - Improved the determination of the FROM clause when placing SQL expressions in the query() list of entities. In particular scalar subqueries should not leak their inner FROM objects out into the enclosing query. - Joins along a relation() from a mapped class to a mapped subclass, where the mapped subclass is configured with single table inheritance, will include an IN clause which limits the subtypes of the joined class to those requsted, within the ON clause of the join. This takes effect for eager load joins as well as query.join(). Note that in some scenarios the IN clause will appear in the WHERE clause of the query as well since this discrimination has multiple trigger points. - AttributeExtension has been refined such that the event is fired before the mutation actually occurs. Addtionally, the append() and set() methods must now return the given value, which is used as the value to be used in the mutation operation. This allows creation of validating AttributeListeners which raise before the action actually occurs, and which can change the given value into something else before its used. - column_property(), composite_property(), and relation() now accept a single or list of AttributeExtensions using the extension keyword argument. - query.order_by().get() silently drops the ORDER BY from the query issued by GET but does not raise an exception. - Added a Validator AttributeExtension, as well as a @validates decorator which is used in a similar fashion as @reconstructor, and marks a method as validating one or more mapped attributes. - class.someprop.in_() raises NotImplementedError pending the implementation of in_ for relation [ticket:1140] - Fixed primary key update for many-to-many collections where the collection had not been loaded yet [ticket:1127] - Fixed bug whereby deferred() columns with a group in conjunction with an otherwise unrelated synonym() would produce an AttributeError during deferred load. - The before_flush() hook on SessionExtension takes place before the list of new/dirty/deleted is calculated for the final time, allowing routines within before_flush() to further change the state of the Session before the flush proceeds. [ticket:1128] - The extension argument to Session and others can now optionally be a list, supporting events sent to multiple
[sqlalchemy] Re: seeking advice for multi-table query
Thanks Mike for your response. In your response you mention having my own function around polymorphic_union and call it on every request based on the table list in the request params. Instead it seems to me that I can construct my union using polymorphic_union once for good (with all the tables) and use with_polymorphic on each request. Is my understanding correct? Eric 2008/8/31, Michael Bayer [EMAIL PROTECTED]: On Aug 31, 2008, at 4:44 PM, Eric Lemoine wrote: Hello Here's my case. I have 3 tables, summits, parkings, and huts. They all have an elevation column. And I want to do queries like that: the summits, parkings, and huts that are higher than 2000 meters, the summits that are lower than 1000 meters, the parkings and huts that are between 500 and 2000 meters. What's to be noted is that the list of tables to query isn't fixed, it actually depends on some parameter in the received HTTP request. The more naive approach involves doing separate queries. For example if the HTTP request has tables=summits,huts, then the queries s.query(Summit).filter() and s.query(Hut).filter() are performed. This might not be efficient because of the separate database queries. I thought about using Concrete Table Inheritance for that, but I'm not sure if it can meet my the list of tables to query varies from one HTTP request to another. I'd appreciate some advice and guidance on that issue. if the tables must be three separate tables, and not linked to a common inherited table which contains the attributes common to all three, then that is exactly concrete table inheritance. The only way to issue SQL across all of them in one execution, whether or not you consider the pattern to be concrete inheritance, is to use a UNION (or UNION ALL which may be more efficient). SQLA's concrete feature does support linking a base mapper to a UNION query, and I think you'll find it works pretty well.You can vary the specifics of the UNION which is issued for a particular Query, using the with_polymorphic() method (select_from() and from_statement() can work too). The reason using SQLA's inheritance as opposed to just using union() by itself is advantageous, is that Query can return classes polymorhically, that is, it knows to return a Hut, Summit or Parking based on the discriminator column. There's two main issues with concrete - one is that its slightly awkward to construct the UNION, SQLA would like you to construct this query semi-manually using the polymorphic_union function (it can also be constructed using the plain union(table1, table2, ...) construct, polymorphic_union() is just a helper) and this UNION query quickly becomes inefficient if it needs to be joined to other tables or nested in a subquery, for example. Constructing it on a per-query basis implies you'd want to build yourself a function that can call polymorphic_union() with the right arguments based on an incoming set of classes (or tables, however your application needs to do it), so there's some awkwardness there which you wouldn't have with joined table inheritance. The other is that concrete inheritance plays poorly with relations to other tables which are shared among more than one class in the hierarchy, since a foreign key is needed for each individual table relationship and SQLA needs you to define individual relation()s for each one. Its always much easier to use single or joined-table inheritance for a hierarchy where the base class or classes define much of the behavior/attributes of subclasses. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] seeking advice for multi-table query
Hello Here's my case. I have 3 tables, summits, parkings, and huts. They all have an elevation column. And I want to do queries like that: the summits, parkings, and huts that are higher than 2000 meters, the summits that are lower than 1000 meters, the parkings and huts that are between 500 and 2000 meters. What's to be noted is that the list of tables to query isn't fixed, it actually depends on some parameter in the received HTTP request. The more naive approach involves doing separate queries. For example if the HTTP request has tables=summits,huts, then the queries s.query(Summit).filter() and s.query(Hut).filter() are performed. This might not be efficient because of the separate database queries. I thought about using Concrete Table Inheritance for that, but I'm not sure if it can meet my the list of tables to query varies from one HTTP request to another. I'd appreciate some advice and guidance on that issue. Thanks, Eric --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: composites
Mike, a question related to you saying composites aren't a necessary feature in the first place. Would it be possible using just Python properties and synonym() to do something similar to the composites example in the doc, namely query(Vertex).filter(Vertex.start == Point(3, 4))? Thanks. Eric 2008/8/21, Michael Bayer [EMAIL PROTECTED]: On Aug 21, 2008, at 9:03 AM, [EMAIL PROTECTED] wrote: hi i plan to implement embedded structures in dbcook (as opposed to referenced structures living in separate tables), and composite props seems to fit nicely. the idea is to achieve something like: class Point( embeddableBase): x = Int() y = Int() class Vertex( base): p1 = Point() p2 = Point() which should create a table/mapping with columns ( p1_x, p1_y, p2_x, p2_y ) and a mapper with the p1, p2 as composite_props. the plain columns are still accessible via the mapper, right? e.g. query(Vertex).filter( Vertex.p1_x 4 ) can composite's props be used in query expressions? e.g. query(Vertex).filter( Vertex.p1.x 4 ) Not by default, the composite attribute p1 and p2 would prevent p1_x, p1_y, etc. from being mapped. you could try explicitly mapping them, i havent experimented much with that. it becomes a gray area since which attribute would it favor for the ultimate value to be persisted? plain descriptors look like a better solution here (composites are probably an unnecessary feature in the first place). can composite's props be assigned separately ? e.g. would this work? v = Vertex( ...) v.p1.x = 3 v.p2 = Point( 1,2) v.p1_y = 5 yeah that wont work at all. With a descriptor based approach (and a Point object that knows how to proxy to the parent object), it could. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: composites
Just realized that my question is similar to az's :) Eric 2008/8/21, Eric Lemoine [EMAIL PROTECTED]: Mike, a question related to you saying composites aren't a necessary feature in the first place. Would it be possible using just Python properties and synonym() to do something similar to the composites example in the doc, namely query(Vertex).filter(Vertex.start == Point(3, 4))? Thanks. Eric 2008/8/21, Michael Bayer [EMAIL PROTECTED]: On Aug 21, 2008, at 9:03 AM, [EMAIL PROTECTED] wrote: hi i plan to implement embedded structures in dbcook (as opposed to referenced structures living in separate tables), and composite props seems to fit nicely. the idea is to achieve something like: class Point( embeddableBase): x = Int() y = Int() class Vertex( base): p1 = Point() p2 = Point() which should create a table/mapping with columns ( p1_x, p1_y, p2_x, p2_y ) and a mapper with the p1, p2 as composite_props. the plain columns are still accessible via the mapper, right? e.g. query(Vertex).filter( Vertex.p1_x 4 ) can composite's props be used in query expressions? e.g. query(Vertex).filter( Vertex.p1.x 4 ) Not by default, the composite attribute p1 and p2 would prevent p1_x, p1_y, etc. from being mapped. you could try explicitly mapping them, i havent experimented much with that. it becomes a gray area since which attribute would it favor for the ultimate value to be persisted? plain descriptors look like a better solution here (composites are probably an unnecessary feature in the first place). can composite's props be assigned separately ? e.g. would this work? v = Vertex( ...) v.p1.x = 3 v.p2 = Point( 1,2) v.p1_y = 5 yeah that wont work at all. With a descriptor based approach (and a Point object that knows how to proxy to the parent object), it could. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] srid autodiscovery mechanism
Hello I created my own type, Geometry, to deal with PostGIS' geometry column type. class Geometry(TypeEngine): def __init__(self, srid=-1, dims=2): super(Geometry, self).__init__() self.srid = srid self.dims = dims def get_col_spec(self): return 'GEOMETRY()' def compare_values(self, x, y): return x.equals(y) def convert_bind_param(self, value, engine): convert value from a geometry object to database if value is None: return None else: return SRID=%s;%s % (self.srid, value.wkb.encode('hex')) def convert_result_value(self, value, engine): convert value from database to a geometry object if value is None: return None else: return loads(value.decode('hex')) So far, so good; user can do: wifi_table = Table('wifi', metadata, Column('the_geom', Geometry(4326)), autoload=True) But ultimately I'd like that my users can do: wifi_table = Table('wifi', metadata, autoload=True) I tried this: from sqlalchemy.databases import postgres postgres.ischema_names['geometry'] = Geometry This is ok, but during reflection, when SQLA creates Geometry objects, it obviously passes no srid argument to the Geometry constructor, so the Geometry objects all end up with the srid property set to -1. The proper srid value to pass to the Geometry constructor is actually in a PostGIS table (geometry_columns). So if a geometry column is discovered, the table's srid value could be read from that table and passed to the Geometry constructor. I thought about doing something like that: from sqlalchemy.databases import postgres def geometry_factory(): // go read srid associated with table from geometry_columns srid = return Geometry(srid) postgres.ischema_names['geometry'] = geometry_factory but geometry_factory doesn't have any connection object to go read the srid value. My question is simple: do you see solutions to my problem? Thanks, -- Eric --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] autoload=True and asdecimal=False
Hello When using autoload the created Numeric columns have asdecimal set to True (I use postgres, dunno if it's different with other databases). If would like that they have asdecimal set to False. Can I force that without having to override the results of the autoload with explicit Numeric(asdecimal=False) columns in my Table object? Thanks a lot, -- Eric --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] autoload primary key
Hello Are there particular conditions for the autoload mechanism to discover the primary key. If I remove Line 12 of this code http://paste.turbogears.org/paste/3183, I get this traceback http://paste.turbogears.org/paste/3182. Thanks a lot, -- Eric --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: autoload primary key
On Fri, Jul 11, 2008 at 11:00 PM, Michael Bayer [EMAIL PROTECTED] wrote: ive never observed a scenario where SQLA could not reflect the primary key status of a column. Feel free to share wtih us what database youre using and the exact DDL used to generate the table in question. Well, the primary key wasn't actually set in the DB. Sorry for the noise. -- Eric --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] synonyms question
Hello To override attribute behavior the 0.5 doc gives this example: class MyAddress(object): def _set_email(self, email): self._email = email def _get_email(self): return self._email email = property(_get_email, _set_email) mapper(MyAddress, addresses_table, properties = { 'email':synonym('_email', map_column=True) }) What won't work if I just set the python property and don't use the synonym func: mapper(MyAddress, addresses_table) What difference does it make? I couldn't find an explanation in the doc. Sorry if that's a dumb question! Thanks, -- Eric --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: synonyms question
On Mon, Jul 7, 2008 at 10:24 PM, Michael Bayer [EMAIL PROTECTED] wrote: On Jul 7, 2008, at 3:29 PM, Eric Lemoine wrote: Hello To override attribute behavior the 0.5 doc gives this example: class MyAddress(object): def _set_email(self, email): self._email = email def _get_email(self): return self._email email = property(_get_email, _set_email) mapper(MyAddress, addresses_table, properties = { 'email':synonym('_email', map_column=True) }) What won't work if I just set the python property and don't use the synonym func: mapper(MyAddress, addresses_table) What difference does it make? I couldn't find an explanation in the doc. Sorry if that's a dumb question! without synonym(), just add _email:addresses_table.c.email to your mapper properties dict so that the email name is made available. In that case, on DB read, SA will set _email directly and won't go through _set_email(). Is that correct? And with email:synonym('_email', map_column=True), will SA set _email directly or will it go through _set_email()? My feeling is that it will set it directly, so it is exactly the same as doing _email:addresses_table.c.email. And if one uses neither email:synonym('_email', map_column=True) nor _email:addresses_table.c.email then SA will go through _set_email(). Is my understanding correct? -- Eric --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: synonyms question
On Mon, Jul 7, 2008 at 11:51 PM, Michael Bayer [EMAIL PROTECTED] wrote: On Jul 7, 2008, at 5:46 PM, Eric Lemoine wrote: without synonym(), just add _email:addresses_table.c.email to your mapper properties dict so that the email name is made available. In that case, on DB read, SA will set _email directly and won't go through _set_email(). Is that correct? yes. And with email:synonym('_email', map_column=True), will SA set _email directly or will it go through _set_email()? My feeling is that it will set it directly, so it is exactly the same as doing _email:addresses_table.c.email. the mapper will always set _email directly. It never goes through user-defined descriptors since it is essentially writing to obj.__dict__ directly. If you want Python code to take effect for data as it leaves the database, you might look into creating a TypeDecorator with the desired behavior. That explains it all. Thanks Michael. -- Eric --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: PostgreSQL and inserting
Hi. Currently sa does select after insert to get the new serial value? Is my understanding correct? Thx 2008/6/7, Michael Bayer [EMAIL PROTECTED]: On Jun 6, 2008, at 9:11 PM, Cliff Wells wrote: Just an FYI, in PostgreSQL 8.2.4 they added a new feature returning which can be used to avoid the need for an additional query (to get the value of serial columns) when inserting records: test=# create table foo ( id serial primary key not null, name text ); test=# insert into foo ( name ) values ( 'test 1' ) returning id; id 1 (1 row) test=# insert into foo ( name ) values ( 'test 2' ) returning *; id | name +- 2 | test 2 (1 row) Thought it would be worth mentioning. the PG dialect supports RETURNING; I think its the pg_returning keyword argument to insert(). Still remaining to do is to modify the PG dialect such that this is used automatically when available for the primary key columns, or perhaps in combination with a dialect agnostic hint, so that the ORM makes usage of it implicitly. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] table union through ORM
Hello Here's my thing: I have two DB tables, refugees and offices, which are independent tables (no foreign key). I already have two mappers for those tables: mapper(Refugee, refugees_table) mapper(Office, offices_table) In addition to being able to do independent queries on each table, I'd like to do queries in both tables (UNION) and get results in objects of type Location (a Python class of my own). In other words, I'd like to do queries using session.query(Location). I've tried various things (Mapping a Class against Multiple Tables in the doc in particular), but with no luck. Does anyone have suggestions? Thanks a lot, -- Eric --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: table union through ORM
On Tue, May 6, 2008 at 4:24 PM, Michael Bayer [EMAIL PROTECTED] wrote: On May 6, 2008, at 7:49 AM, Eric Lemoine wrote: Hello Here's my thing: I have two DB tables, refugees and offices, which are independent tables (no foreign key). I already have two mappers for those tables: mapper(Refugee, refugees_table) mapper(Office, offices_table) In addition to being able to do independent queries on each table, I'd like to do queries in both tables (UNION) and get results in objects of type Location (a Python class of my own). In other words, I'd like to do queries using session.query(Location). I've tried various things (Mapping a Class against Multiple Tables in the doc in particular), but with no luck. Does anyone have suggestions? this would be concrete table inheritance: http://www.sqlalchemy.org/docs/04/mappers.html#advdatamapping_mapper_inheritance_concrete Both Refugee and Office would need to include Location as at least one of their base classes. In the Concrete Table Inheritance example in the doc, there's an employee table. In my case, there's no location table, and that's why I haven't seen Concrete Table Inheritance as my case's solution. So I'm still confused on how I'm going to achieve what I want. Thanks a lot, -- Eric --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: table union through ORM
Thanks a lot. I'll try that 2008/5/6, Michael Bayer [EMAIL PROTECTED]: On May 6, 2008, at 11:13 AM, Eric Lemoine wrote: In the Concrete Table Inheritance example in the doc, there's an employee table. In my case, there's no location table, and that's why I haven't seen Concrete Table Inheritance as my case's solution. So I'm still confused on how I'm going to achieve what I want. you wouldnt have the location table, and the Location mapper would be mapped directly to the polymorphic union , i.e. mapper(Location, pjoin). --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: table union through ORM
On Tue, May 6, 2008 at 5:36 PM, Eric Lemoine [EMAIL PROTECTED] wrote: Thanks a lot. I'll try that It works great! I would never have found how to do it without support from you Michael. The doc wasn't explicit enough for me to figure it out by myself. But support on the mailing list is awesome as usual. Thanks again, -- Eric --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: sequence-related question
Michael, yes, i think the seq is automatically executed in that case. Regarding the usage of currval i dunno. Sorry 2008/4/30, Michael Bayer [EMAIL PROTECTED]: On Apr 30, 2008, at 3:45 PM, Eric Lemoine wrote: With psycopg2, I know people using this: sql = INSERT INTO \%s\ (%s) VALUES (%s) % (self.table, columns, values) cursor = db.cursor() cursor.execute(str(sql), values) cursor.execute(SELECT currval('%s'); % sequence_name) id = cursor.fetchone()[0] self.db.commit() I'm wondering if this is safe. And if so, if there's a way to do the same with SA. this is a slight bit less safe than SQLA's default practice, in the sense that if the application used the same connection in two concurrent threads (which is a bad practice in itself), the results may be incorrect. What I dont see above is how the sequence is getting executed. Is the column a SERIAL column, and the sequence is executed automatically ? or is the nextval(seqname) embedded into the VALUES clause above literally ? also still curious why usage of currval is even needed. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] sequence-related question
Hello I insert a new line in a table using this: campfacility = Campfacility(prop1, prop2) model.Session.save(campfacility) model.Session.commit() The campfacility id is handled by a postgres sequence. What I'd like to do is: campfacility = Campfacility(prop1, prop2) seq = Sequence('some_sequence') model.Session.execute(seq) model.Session.save(campfacility) model.Session.commit() to know before inserting the line what id it will get. The above code doesn't seem thread-safe to me: thread 1 thread 2 execute(seq) - nextid = n execute(seq) - nextid = n model.Session.save(campfacility) model.Session.save(campfacility) - BUG, nextid isn't correct I'm sure there's a way to make this thread-safe but right now I just don't know how. Can someone help? Thanks a lot, PS: I just love SA ;-) -- Eric --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: sequence-related question
On Wed, Apr 30, 2008 at 9:02 PM, Michael Bayer [EMAIL PROTECTED] wrote: On Apr 30, 2008, at 12:56 PM, Eric Lemoine wrote: thread 1 thread 2 execute(seq) - nextid = n execute(seq) - nextid = n model.Session.save(campfacility) model.Session.save(campfacility) - BUG, nextid isn't correct whats correct here, you'd like the integer identifier to be in exact row-insert order ? if the column is a non-primary key column, the sequence will be executed inline within the executed SQL so that it will in fact be in row insert order (i.e. update table set foo_id=nextval(myseq)). you can also do this at flush time by assigning func.nextval(literal_column(my_sequence_name)) to the mapped attribute (assuming its not a PK). for primary keys we need to know the ID beforehand in most cases since PG historically has not had a way to get that ID back nicely after insert (it has INSERT RETURNING now but we haven't standardized on that yet). Yes, the sequence is my table's PK. What I want to know is the PK value of the line I'm going to insert (or I've just inserted). So I guess this is indeed INSERT RETURNING. So if its a PK, I would question why you actually need an incrementing id in row-insert order on the table in the first place. Usually, if I want to load records in the order in which they were inserted in a foolproof way, I'll use a UTC timestamp column with an index for thatsince the information you are looking for here is what was inserted when? That's no what I want. See above. I hope it's clear enough this time. Thanks a lot Michael, -- Eric --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: sequence-related question
On Wed, Apr 30, 2008 at 9:02 PM, Michael Bayer [EMAIL PROTECTED] wrote: On Apr 30, 2008, at 12:56 PM, Eric Lemoine wrote: thread 1 thread 2 execute(seq) - nextid = n execute(seq) - nextid = n model.Session.save(campfacility) model.Session.save(campfacility) - BUG, nextid isn't correct Just realized that my schema with the two threads didn't come up as expected, which problably didn't help understand my issue. -- Eric --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: sequence-related question
On Wed, Apr 30, 2008 at 9:23 PM, Eric Lemoine [EMAIL PROTECTED] wrote: On Wed, Apr 30, 2008 at 9:02 PM, Michael Bayer [EMAIL PROTECTED] wrote: On Apr 30, 2008, at 12:56 PM, Eric Lemoine wrote: thread 1 thread 2 execute(seq) - nextid = n execute(seq) - nextid = n model.Session.save(campfacility) model.Session.save(campfacility) - BUG, nextid isn't correct whats correct here, you'd like the integer identifier to be in exact row-insert order ? if the column is a non-primary key column, the sequence will be executed inline within the executed SQL so that it will in fact be in row insert order (i.e. update table set foo_id=nextval(myseq)). you can also do this at flush time by assigning func.nextval(literal_column(my_sequence_name)) to the mapped attribute (assuming its not a PK). for primary keys we need to know the ID beforehand in most cases since PG historically has not had a way to get that ID back nicely after insert (it has INSERT RETURNING now but we haven't standardized on that yet). Yes, the sequence is my table's PK. What I want to know is the PK value of the line I'm going to insert (or I've just inserted). So I guess this is indeed INSERT RETURNING. With psycopg2, I know people using this: sql = INSERT INTO \%s\ (%s) VALUES (%s) % (self.table, columns, values) cursor = db.cursor() cursor.execute(str(sql), values) cursor.execute(SELECT currval('%s'); % sequence_name) id = cursor.fetchone()[0] self.db.commit() I'm wondering if this is safe. And if so, if there's a way to do the same with SA. Thanks, -- Eric --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: sequence-related question
On Wed, Apr 30, 2008 at 9:44 PM, Michael Bayer [EMAIL PROTECTED] wrote: On Apr 30, 2008, at 3:23 PM, Eric Lemoine wrote: Yes, the sequence is my table's PK. What I want to know is the PK value of the line I'm going to insert (or I've just inserted). So I guess this is indeed INSERT RETURNING. if you pre-execute the sequence, the number you get back from it is yours to keep and will never come up again (unless the sequence is manually manipulated). Its safe to use for a primary key value at any time regardless of concurrent threads which also use that sequence. SQLAlchemy does this process for you automatically, so if you just set the Sequence() on your table's primary key Column, you can safely save and flush your instances without assigning any identifier, and the newly generated id is present on the corresponding class attributes, i.e.: mytable = Table('mytable', metadata, Column('id', Sequence('my_seq'), primary_key=True), ...) mapper(MyClass, mytable, ...) x = MyClass() session.save(x) session.flush() newly_inserted_id = x.id no threading issues to worry about. Same thing happens with raw inserts, if you leave the id column out of the values list: result = engine.execute(mytable.insert()) newly_inserted_id = result.last_inserted_ids()[0] Great! Thanks, -- Eric --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: what happens on save?
Thanks Michael for the detailed explanations and the patch. This is really helping! 2008/4/26, Michael Bayer [EMAIL PROTECTED]: On Apr 25, 2008, at 4:21 PM, Eric Lemoine wrote: Module sqlalchemy.orm.mapper:1198 in _postfetch elif not c.primary_key and c.key in params and self._get_state_attr_by_column(state, c) != params[c.key]: Module shapely.geometry.base:255 in __ne__ return not self.equals(other) Module shapely.predicates:30 in __call__ raise RuntimeError() # breakpoint FIXME As you can see, the __ne__ method of my object does get called. this occurs well after any attribute history detection has happened (which is where comparsions are supposed to happen, if needed). The mapper has inserted the row, then it goes through the list of parameters which were inserted into the row and compares them to what is present on the object, so that it can detect Column-level defaults and other auto-generated values which need to be placed on the instance. This methodology is out of date since nowadays we have an explicit listing of which columns were auto generated - so try out the attached patch which refines the methodology in that section and should solve the issue. The patch is also ticket #1015 which needs test coverage before it can be committed (but is high priority for 0.4.6). --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] what happens on save?
Hello I have the following code in my pylons app: refugee = Refugee(value, geometry) model.Session.save(refugee) model.Session.commit() Refugee is the class mapped to my Table object (refugees_table). geometry is an instance of a custom type, for which I created a Geometry(TypeEngine) class. In commit(), SQLAlchemy compares the geometry object to some other geometry object (yes, my Geometry class defines the compare_values method). I'd just like to know what object my geometry object is compared to? I'm just saving a new object in the db table so why there's a need to compare it to something else? I know this is a weird question, but I have other problems that are the consequences of this object comparison, so I'm trying to understand what it is going on in SQLAlchemy. Thanks, -- Eric --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: what happens on save?
On Fri, Apr 25, 2008 at 5:55 PM, Michael Bayer [EMAIL PROTECTED] wrote: On Apr 25, 2008, at 11:37 AM, Eric Lemoine wrote: Hello I have the following code in my pylons app: refugee = Refugee(value, geometry) model.Session.save(refugee) model.Session.commit() Refugee is the class mapped to my Table object (refugees_table). geometry is an instance of a custom type, for which I created a Geometry(TypeEngine) class. In commit(), SQLAlchemy compares the geometry object to some other geometry object (yes, my Geometry class defines the compare_values method). I'd just like to know what object my geometry object is compared to? I'm just saving a new object in the db table so why there's a need to compare it to something else? actually, it shouldnt be compared to anything. are you on 0.4 ? It does compare something, AFICT. I'm on 0.4.5. -- Eric --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: what happens on save?
On Fri, Apr 25, 2008 at 8:48 PM, Eric Lemoine [EMAIL PROTECTED] wrote: On Fri, Apr 25, 2008 at 5:55 PM, Michael Bayer [EMAIL PROTECTED] wrote: On Apr 25, 2008, at 11:37 AM, Eric Lemoine wrote: Hello I have the following code in my pylons app: refugee = Refugee(value, geometry) model.Session.save(refugee) model.Session.commit() Refugee is the class mapped to my Table object (refugees_table). geometry is an instance of a custom type, for which I created a Geometry(TypeEngine) class. In commit(), SQLAlchemy compares the geometry object to some other geometry object (yes, my Geometry class defines the compare_values method). I'd just like to know what object my geometry object is compared to? I'm just saving a new object in the db table so why there's a need to compare it to something else? actually, it shouldnt be compared to anything. are you on 0.4 ? It does compare something, AFICT. I'm on 0.4.5. Here is the stack trace: Module unhcr.controllers.refugees:80 in post model.Session.commit() Module sqlalchemy.orm.scoping:98 in do return getattr(self.registry(), name)(*args, **kwargs) Module sqlalchemy.orm.session:544 in commit self.transaction.commit() Module sqlalchemy.orm.session:250 in commit self._prepare_impl() Module sqlalchemy.orm.session:234 in _prepare_impl self.session.flush() Module sqlalchemy.orm.session:757 in flush self.uow.flush(self, objects) Module sqlalchemy.orm.unitofwork:233 in flush flush_context.execute() Module sqlalchemy.orm.unitofwork:445 in execute UOWExecutor().execute(self, tasks) Module sqlalchemy.orm.unitofwork:930 in execute self.execute_save_steps(trans, task) Module sqlalchemy.orm.unitofwork:945 in execute_save_steps self.save_objects(trans, task) Module sqlalchemy.orm.unitofwork:936 in save_objects task.mapper._save_obj(task.polymorphic_tosave_objects, trans) Module sqlalchemy.orm.mapper:1158 in _save_obj mapper._postfetch(uowtransaction, connection, table, state, c, c.last_inserted_params(), value_params) Module sqlalchemy.orm.mapper:1198 in _postfetch elif not c.primary_key and c.key in params and self._get_state_attr_by_column(state, c) != params[c.key]: Module shapely.geometry.base:255 in __ne__ return not self.equals(other) Module shapely.predicates:30 in __call__ raise RuntimeError() # breakpoint FIXME As you can see, the __ne__ method of my object does get called. -- Eric --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] alias in mapping
Hello I'm trying to use the key argument in my mapping: refugees_table = Table('refugee', MetaData(config['pylons.g'].sa_unhcr_webgis_engine), Column('refugee_fid', types.Integer, primary_key=True), Column('id', types.Float, key='_id'), autoload=True, schema='hqmap') But I get this Error Traceback when calling mapper(Refugee, refugees_table): Module ?:25 in module from unhcr.model.refugees import refugees_table, Refugee Module ?:47 in module mapper(Refugee, refugees_table) Module sqlalchemy.orm:518 in mapper : return Mapper(class_, local_table, *args, **params) Module sqlalchemy.orm.mapper:152 in __init__ self._compile_tables() Module sqlalchemy.orm.mapper:436 in _compile_tables raise exceptions.ArgumentError(Cant resolve column + str(col)) class 'sqlalchemy.exceptions.ArgumentError': Cant resolve column refugee.id Any idea? -- Eric --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: alias in mapping
On Mon, Apr 21, 2008 at 10:37 AM, Eric Lemoine [EMAIL PROTECTED] wrote: Hello I'm trying to use the key argument in my mapping: refugees_table = Table('refugee', MetaData(config['pylons.g'].sa_unhcr_webgis_engine), Column('refugee_fid', types.Integer, primary_key=True), Column('id', types.Float, key='_id'), autoload=True, schema='hqmap') But I get this Error Traceback when calling mapper(Refugee, refugees_table): Module ?:25 in module from unhcr.model.refugees import refugees_table, Refugee Module ?:47 in module mapper(Refugee, refugees_table) Module sqlalchemy.orm:518 in mapper : return Mapper(class_, local_table, *args, **params) Module sqlalchemy.orm.mapper:152 in __init__ self._compile_tables() Module sqlalchemy.orm.mapper:436 in _compile_tables raise exceptions.ArgumentError(Cant resolve column + str(col)) class 'sqlalchemy.exceptions.ArgumentError': Cant resolve column refugee.id Any idea? I figured that if I remove autoload=True I don't have the problem. Is this expected? I thought I could use autoload and do some overriding. -- Eric --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: alias in mapping
On Mon, Apr 21, 2008 at 5:17 PM, Michael Bayer [EMAIL PROTECTED] wrote: On Apr 21, 2008, at 4:37 AM, Eric Lemoine wrote: Hello I'm trying to use the key argument in my mapping: refugees_table = Table('refugee', MetaData(config['pylons.g'].sa_unhcr_webgis_engine), Column('refugee_fid', types.Integer, primary_key=True), Column('id', types.Float, key='_id'), autoload=True, schema='hqmap') But I get this Error Traceback when calling mapper(Refugee, refugees_table): Module ?:25 in module from unhcr.model.refugees import refugees_table, Refugee Module ?:47 in module mapper(Refugee, refugees_table) Module sqlalchemy.orm:518 in mapper : return Mapper(class_, local_table, *args, **params) Module sqlalchemy.orm.mapper:152 in __init__ self._compile_tables() Module sqlalchemy.orm.mapper:436 in _compile_tables raise exceptions.ArgumentError(Cant resolve column + str(col)) class 'sqlalchemy.exceptions.ArgumentError': Cant resolve column refugee.id Any idea? I'd advise upgrading to the most recent 0.4 version since you're apparently on 0.4.0 there. Ok. Also, if id is a primary key column on the table, you're probably going to need the primary_key=True flag to remain on the Table definition. id isn't a primary key, refugee_fid is and is already flagged as such. If you'd like your mapper to have a different idea of what the primary key for this table is, use the primary_key=[cols] argument on mapper(). Thanks a lot, -- Eric --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] access mapped object attributes
Hello Have a question related to mapped objects and reflecting tables. If have this simple configuration: messages = Table('messages', meta, autoload=True) class Message(object): pass mapper(Message, messages) Given a Message object, do I have a way to retrieve all the attributes that result from the database mapping? I don't know these attributes upfront since I use autoload=True, but still, I'd like to access them in a safe way (object.__dict__ doesn't seem to be an option). Can someone help? Thanks a lot, -- Eric --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: access mapped object attributes
On 11/5/07, Paul Johnston [EMAIL PROTECTED] wrote: Hi, Given a Message object, do I have a way to retrieve all the attributes that result from the database mapping? Try this: for col in Message.c: It works. thanks a lot Paul, -- Eric --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] RuntimeWarning with overriden reflected table
Hi I use a reflected table with an overriden column: summits_table = Table('sommets_out', MetaData(config['pylons.g'].sa_search_engine), Column('geom', Geometry), autoload=True) and I get RuntimeWarning when accessing the table: /home/elemoine/virtual-python/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta6-py2.4.egg/sqlalchemy/databases/postgres.py:446: RuntimeWarning: Did not recognize type 'geometry' of column 'geom' Is this behavior expected? Thanks, -- Eric --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: RuntimeWarning with overriden reflected table
On 10/10/07, Michael Bayer [EMAIL PROTECTED] wrote: On Oct 10, 2007, at 10:01 AM, Eric Lemoine wrote: Hi I use a reflected table with an overriden column: summits_table = Table('sommets_out', MetaData(config['pylons.g'].sa_search_engine), Column('geom', Geometry), autoload=True) and I get RuntimeWarning when accessing the table: /home/elemoine/virtual-python/lib/python2.4/site-packages/ SQLAlchemy-0.4.0beta6-py2.4.egg/sqlalchemy/databases/postgres.py:446: RuntimeWarning: Did not recognize type 'geometry' of column 'geom' Is this behavior expected? it is, Postgres is reporting geometry as the type of the column and we dont have that type available within postgres.py; so it replaces it with the NullType. Afterwards, your overridden Column replaces it (i.e. post-reflection), so it all works out. Better solution, would be to add your Geomtry type to postgres.py. Thanks for your answer. So basically you're saying that this RuntimeWarning is harmless, aren't you? Are you suggesting that I should add Geometry to postgres.py for my specific need, or that support for this Postgres (PostGIS actually) type should be added to SQLAlchemy? -- Eric --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---