[sqlalchemy] Re: SQLAlchemy Netezza Dialect
I created an actual repo for this, so if anyone finds bugs, or wants to submit patches: https://github.com/deontologician/netezza_sqlalchemy On Tue, Mar 4, 2014 at 7:18 PM, Josh Kuhn wrote: > Since there wasn't one out there already, I took a shot at it. It's really > rough, and mostly cribs its implementation from the Postgres dialect (since > Netezza is a postgresql derivative). > > https://gist.github.com/deontologician/9358574 > > It handles a couple of quirks I ran across, like adding "distribute on" > clauses to create table statements, and ensuring limit clauses don't get > sql params (since it doesn't like that). > > With pandas incorporating SQLA, I figure this might be useful to some > people out there. > > --Josh > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Replacing a single column in a select (Core language)
That works great. My one stumbling block was that the column objects couldn't be compared directly, so I compared their string representation (I had two separate calls to A.alias() which made `is` not a valid comparison in the comprehension) On Thu, Mar 27, 2014 at 11:49 AM, Michael Bayer wrote: > > On Mar 27, 2014, at 10:08 AM, Josh Kuhn wrote: > > I have a situation where I need to produce a select object, and then > later, one of the fields needs to be "zeroed" out conditionally. > > so something like: > > def select_ab(param): > from_obj = join(A, B, A.c.b == B.c.b) > return select([A.c.a, B.c.b, B.c.d], from_obj=from_obj).where(A.c.a == > param) > > ... > > sql_query = select_ab(34) > > # Would like to do something like this: > if some_condition: >sql_query = sql_query.replace_column(B.c.d, literal(0)) > > engine.execute(sql_query) > > > I tried to hack together a "replace_column" function using > Select.with_only_columns, but as the documentation indicates, I need the > original list of columns in order to really do what I want to do, I can't > take the select's columns. And in my case, the original select's columns > are trapped inside the function select_ab. I'd like to be able to replace a > column on any arbitrary select if the column exists in the select. > > Is there a way to do what I'm trying to do? Or do I just need to keep the > original columns around? > > > the original columns are in the select() object. They’re first in their > original form in a list called _raw_columns, that is, if you had > select([sometable, somecol, someothercol]) it would be literally those > three elements, and then there is a public accessor called inner_columns > which is derived directly from _raw_columns, it just expands a selectable > (e.g. something with a .c. collection) into individual columns. > > So it should be feasible to say something equivalent to: > select.with_only_columns([(literal(0) if c is my_special_thing else c) for > c in select.inner_columns]) > > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Replacing a single column in a select (Core language)
I have a situation where I need to produce a select object, and then later, one of the fields needs to be "zeroed" out conditionally. so something like: def select_ab(param): from_obj = join(A, B, A.c.b == B.c.b) return select([A.c.a, B.c.b, B.c.d], from_obj=from_obj).where(A.c.a == param) ... sql_query = select_ab(34) # Would like to do something like this: if some_condition: sql_query = sql_query.replace_column(B.c.d, literal(0)) engine.execute(sql_query) I tried to hack together a "replace_column" function using Select.with_only_columns, but as the documentation indicates, I need the original list of columns in order to really do what I want to do, I can't take the select's columns. And in my case, the original select's columns are trapped inside the function select_ab. I'd like to be able to replace a column on any arbitrary select if the column exists in the select. Is there a way to do what I'm trying to do? Or do I just need to keep the original columns around? -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] SQLAlchemy Netezza Dialect
Since there wasn't one out there already, I took a shot at it. It's really rough, and mostly cribs its implementation from the Postgres dialect (since Netezza is a postgresql derivative). https://gist.github.com/deontologician/9358574 It handles a couple of quirks I ran across, like adding "distribute on" clauses to create table statements, and ensuring limit clauses don't get sql params (since it doesn't like that). With pandas incorporating SQLA, I figure this might be useful to some people out there. --Josh -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Association proxy info property (or lack thereof)
Thanks for the reply! I'll add the info property. Glad to see I wasn't
going about this completely the wrong way
On Sun, Feb 23, 2014 at 1:16 PM, Michael Bayer wrote:
>
> On Feb 23, 2014, at 12:59 AM, Josh Kuhn wrote:
>
> > I'm writing some code to serialize some SA models to JSON, and for
> columns and relationships, it's convenient to tag which fields should be
> serialized with the info dictionary like so:
> >
> > class Thing(Base):
> > id = Column(Integer, primary_key=True, info={'jsonify': False})
> > name = Column(String, info={'jsonify': True})
> > relationship(SomeModel, info={'jsonify': True}
> >
> > Then in the Base class, you can iterate over properties like:
> >
> > for attr, column in inspect(self.__class__).columns.items():
> > if column.info.get('jsonify', False):
> > json_output[attr] = getattr(self, attr)
> > for attr, rel in inspect(self.__class__).relationships.items():
> > # similar, with some recursion depending on rel.uselist etc...
> >
> > But for association_proxies, there is no info property, nor is there any
> mapper.association_proxies attribute.
> >
> > I can obviously hack around this in some way, but I'm wondering if I'm
> going about it wrong. Is the best way to get the association_proxies from
> the mapper to filter through .all_orm_descriptors?
>
> yes, from that point of view an association proxy is in a different class
> of attribute than something like a column or relationship. The hierarchy
> of “attributes that are mapped” is called MapperProperty. Association
> proxy isn’t in that hierarchy, but the .all_orm_descriptors collection was
> added so that things like hybrid attributes and association proxies can be
> found, based on their membership in the much more general “_InspectionAttr”
> hierarchy.
>
> >
> > Also, why don't association_proxies have an info property?
>
> no particular reason. Do you think .info would be independent of the
> .info that’s present on the proxies attributes? Or it could have a copy
> (or be the same collection) as that of the “left” or “right” attributes its
> proxying (probably not though). Perhaps we’d want to have .info,
> .local_info, .remote_info. It might be something that we just want to
> stick on the base “_InsepctionAttr” so that everything gets an info, hybrid
> attributes, etc.
>
>
> >
> > What would be the recommended way to do this kind of annotation on the
> models for json serialization?
>
> Probably assume we’ll add .info to these attributes and for now just tack
> an .info dictionary onto your association proxy.
>
> I’ve added
> https://bitbucket.org/zzzeek/sqlalchemy/issue/2971/move-info-from-mapperproperty-down-tofor
> that.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Association proxy info property (or lack thereof)
I'm writing some code to serialize some SA models to JSON, and for columns
and relationships, it's convenient to tag which fields should be serialized
with the info dictionary like so:
class Thing(Base):
id = Column(Integer, primary_key=True, info={'jsonify': False})
name = Column(String, info={'jsonify': True})
relationship(SomeModel, info={'jsonify': True}
Then in the Base class, you can iterate over properties like:
for attr, column in inspect(self.__class__).columns.items():
if column.info.get('jsonify', False):
json_output[attr] = getattr(self, attr)
for attr, rel in inspect(self.__class__).relationships.items():
# similar, with some recursion depending on rel.uselist etc...
But for association_proxies, there is no info property, nor is there any
mapper.association_proxies attribute.
I can obviously hack around this in some way, but I'm wondering if I'm
going about it wrong. Is the best way to get the association_proxies from
the mapper to filter through .all_orm_descriptors?
Also, why don't association_proxies have an info property?
What would be the recommended way to do this kind of annotation on the
models for json serialization?
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Dynamic order by clause
I don't know if this is what you're thinking, but you can also just build a query object in different ways if you want to query = session.query(Study).options( joinedload(Study.system), joinedload(Study.site)). filter(System.system_id=41) if order_by_study_id: if descending: query = query.order_by(Study.study_id.desc()) else: query = query.order_by(Study.study_id) ... # whatever other branches etc. final_results = query.all() On Thu, Feb 13, 2014 at 8:49 PM, Michael Bayer wrote: > everything in python is ultimately in a namespace, the names are strings, > the values are the objects. > > like if you had “myapp.model” as a module, and in that module were Study > and Site, you could say: > > from myapp import model > Study = getattr(model, “Study”) > > same thing. > > If you want to poke into the registry of class names in declarative, you > can look inside of Base._decl_class_registry: > > def query(clsname, colname): > cls = Base._decl_class_registry[clsname] > col = getattr(cls, colname) > >q = query(cls).filtert(cls.foo == ‘bar’).order_by(col) > > > this kind of thing is very easy in Python once you get the central idea > that everything in Python is the same kind of object each with a name. > > > > On Feb 13, 2014, at 7:56 PM, Tony Garcia wrote: > > Actually, now I see that your suggestion would get me the column object > (not a string), but it would still restrict me to the study table. > > > On Thu, Feb 13, 2014 at 7:53 PM, Tony Garcia wrote: > >> Oops -- disregard the [start:end] at the end of the query and replace >> that with .all() >> >> >> On Thu, Feb 13, 2014 at 7:50 PM, Tony Garcia wrote: >> >>> Hmm.. I see what you're saying, but the column can be from any of the >>> tables queried from, not just the Study table. So it could be >>> Study.study_id, System.system_name, Site.site_id, etc. Also won't that >>> getattr() call just return a string? I was under the impression that you >>> had to pass a column object to order_by(). So if implemented the solution >>> for dynamic sort direction given in the stackoverflow link above (which >>> takes advantage of the fact that you can access the .asc() or .desc() >>> methods >>> as attributes on the column object), I have this: >>> >>> def my_query(sort_direction='asc'): >>> column_sorted = getattr(Study.study_id, sort_direction)() >>> >>> query = Study.query.options( >>> db.joinedload(Study.system), >>> db.joinedload(Study.site)).\ >>> filter(System.system_id==41).\ >>> order_by(column_sorted)[start:end] >>> return query >>> >>> How can I modify this so that it doesn't just sort on Study.study_id and >>> my method signature would be my_query(sort_column, sort_direction)? >>> Maybe this isn't possible using the ORM and I have to dip down into the >>> SQL expression language, but I thought I'd ask. >>> >>> Thanks. >>> >>> >>> On Thu, Feb 13, 2014 at 7:08 PM, Michael Bayer >> > wrote: >>> On Feb 13, 2014, at 6:21 PM, Tony Garcia wrote: > Hello, > I'm new to SQLAlchemy and have searched high and low for a solution to my problem so I'm hoping someone here can help. I have a query where I need to apply the 'order by' clause dynamically (both the column and the direction). So a 'static' version of my query would be: > > studies = session.query(Study).options( > joinedload(Study.system), > joinedload(Study.site)). > filter(System.system_id=41). > order_by(Study.study_id.desc()). > all() > > However the order can be asc or desc and it could be any column from the 3 tables. I found this post on Stackoverflow which helps with a dynamic sort direction (asc, desc), but it doesn't help me with the dynamic column: > > http://stackoverflow.com/questions/20904226/python-sqlalchemy-dynamic-order-by “dynamic” attribute access in Python is using the getattr() builtin function: def my_query(order_by_column): query = session.query(Study).filter(Study.system_id=41).order_by(getattr(Study, order_by_column)) that seems like what you’re asking, hope it helps. >>> >> > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https
Re: [sqlalchemy] Parent child relationships
I think you need to use the remote_side argument for the children
relationship, since it's the same table
http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#adjacency-list-relationships
On Thu, Feb 13, 2014 at 12:04 PM, Michael Bayer wrote:
>
> On Feb 13, 2014, at 11:53 AM, Michael Hipp wrote:
>
> > I'm trying to do something like this:
> >
> > class Animal(Base):
> >__tablename__ = 'animals'
> >id_ = Column(Integer, primary_key=True)
> >
> >sire_id = Column(Integer, ForeignKey('animals.id_'))
> >dam_id = Column(Integer, ForeignKey('animals.id_'))
> >
> >sire = relationship('Animal', foreign_keys=[sire_id])
> >dam = relationship('Animal', foreign_keys=[dam_id])
> >children = relationship('Animal', foreign_keys=[sire_id, dam_id])
> >
> > It gives:
> >
> > sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join
> condition between parent/child tables on relationship Animal.first_owner -
> there are multiple foreign key paths linking the tables. Specify the
> 'foreign_keys' argument, providing a list of those columns which should be
> counted as containing a foreign key reference to the parent table.
> >
> > I've spent lots of time here, but I'm just not seeing the answer for
> this father-mother-children relationship.
> > http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html
>
> I don’t see a “first_owner” relationship defined above, so the above
> example is not complete.
>
> The approach using “foreign_keys” is the correct approach to resolving
> ambiguity in join conditions, however.
>
> If the documented approach is not working you’d need to provide a succinct
> self-contained example I can run.
>
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Relationships using "max"
Would I be correct in assuming this Version object won't be in the same
identity map as the other Version objects?
On Fri, Jan 31, 2014 at 9:45 PM, Michael Bayer wrote:
>
> On Jan 31, 2014, at 8:51 PM, Josh Kuhn wrote:
>
> This is pretty amazing. I get it to *almost* work. The issue seems to be
> that I don't use the database column names as my model attribute names. The
> db column names are really obfuscated, so it's more like:
>
> class Version(Base):
> id = Column('vrsn_nbr', Integer, primary_key=True)
> date = Column('dt', DateTime)
>
> etc..
>
> It seems when this relationship is populated, it creates a Version object,
> but it sets all of the database column names as attributes, and the defined
> Column names are all None. Is there a way to get around that?
>
>
> well if you map to a select() then it uses the column names, as in
> http://docs.sqlalchemy.org/en/rel_0_9/orm/mapper_config.html#naming-columns-distinctly-from-attribute-namesyou
> can rename with properties={“whatever_name”: stmt.c.xyz_column}, etc.
> same thing as saying “id = Column(‘vrsn_nbr’)” on your declarative mapping.
>
>
>
>
>
>
> On Fri, Jan 31, 2014 at 6:38 PM, Michael Bayer
> wrote:
>
>> well I can get it to work for lazy loading like this:
>>
>> expr = select([func.max(Version.id)]).\
>> where(Version.object_id == Object.id).\
>> correlate_except(Version).as_scalar()
>>
>> Object.current_version = relationship(Version,
>>primaryjoin=and_(
>> expr == Version.id,
>> Version.object_id ==
>> Object.id
>> )
>>)
>>
>>
>> but for joined load, the criteria needs to fit into a LEFT OUTER JOIN ON
>> clause. Both SQLite and Postgresql reject an aggregate function in the ON
>> clause.So I didn’t really know how to get that because you have to
>> think in terms of the SQL….but then the usual approach is that you need to
>> JOIN to a subquery that has the aggregate inside of it. So I use instead
>> the pattern you see here:
>> http://docs.sqlalchemy.org/en/rel_0_9/orm/tutorial.html#using-subqueries
>> which is the subquery of “X.foo_id, func.AGGREGATE(X.id)” that then joins
>> to the parent table, and then I go with “non primary mapper”, a use case
>> that I recently added to the documentation at
>> http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#relationship-to-non-primary-mapper,
>> even though this has been around for years.
>>
>> and it looks like……
>>
>> expr = select([
>> func.max(Version.id).label("max_id"),
>> Version.object_id
>> ]).group_by(Version.object_id).alias()
>>
>> stmt = select([Version]).\
>> select_from(join(Version, expr, Version.id ==
>> expr.c.max_id)).\
>> alias()
>>
>> current_version = mapper(Version, stmt, non_primary=True)
>>
>> Object.current_version = relationship(current_version)
>>
>> I think I might have actually written a mapping like this as an example
>> back in SQLAlchemy 0.1 even, this was the goofy kind of thing I thought
>> everyone would be doing all the time.
>>
>> works with joinedload. Query is not too efficient, but is like:
>>
>> SELECT objects.id AS objects_id, anon_1.id AS anon_1_id,
>> anon_1.object_id AS anon_1_object_id
>> FROM objects LEFT OUTER JOIN (SELECT versions.id AS id,
>> versions.object_id AS object_id
>> FROM versions JOIN (SELECT max(versions.id) AS max_id,
>> versions.object_id AS object_id
>> FROM versions GROUP BY versions.object_id) AS anon_2 ON versions.id =
>> anon_2.max_id) AS anon_1 ON objects.id = anon_1.object_id
>> WHERE objects.id = %(id_1)s
>>
>>
>>
>>
>> On Jan 31, 2014, at 5:35 PM, Josh Kuhn wrote:
>>
>> I've got a two tables I'd like to create a relationship for. One is the
>> object, and another tracks versions.
>>
>> Here's a gist with the setup:
>> https://gist.github.com/deontologician/8744532
>>
>> Basically, the object doesn't have a direct reference to the current
>> version stored in the table. Instead, the current version is defined as the
>> maximum version that points to that object.
>>
>> I'd like to have a one-to-one "current_version" relationship, but this
>> has proven difficult (at
Re: [sqlalchemy] Relationships using "max"
This is pretty amazing. I get it to *almost* work. The issue seems to be
that I don't use the database column names as my model attribute names. The
db column names are really obfuscated, so it's more like:
class Version(Base):
id = Column('vrsn_nbr', Integer, primary_key=True)
date = Column('dt', DateTime)
etc..
It seems when this relationship is populated, it creates a Version object,
but it sets all of the database column names as attributes, and the defined
Column names are all None. Is there a way to get around that?
On Fri, Jan 31, 2014 at 6:38 PM, Michael Bayer wrote:
> well I can get it to work for lazy loading like this:
>
> expr = select([func.max(Version.id)]).\
> where(Version.object_id == Object.id).\
> correlate_except(Version).as_scalar()
>
> Object.current_version = relationship(Version,
>primaryjoin=and_(
> expr == Version.id,
> Version.object_id ==
> Object.id
> )
>)
>
>
> but for joined load, the criteria needs to fit into a LEFT OUTER JOIN ON
> clause. Both SQLite and Postgresql reject an aggregate function in the ON
> clause.So I didn’t really know how to get that because you have to
> think in terms of the SQL….but then the usual approach is that you need to
> JOIN to a subquery that has the aggregate inside of it. So I use instead
> the pattern you see here:
> http://docs.sqlalchemy.org/en/rel_0_9/orm/tutorial.html#using-subqueries
> which is the subquery of “X.foo_id, func.AGGREGATE(X.id)” that then joins
> to the parent table, and then I go with “non primary mapper”, a use case
> that I recently added to the documentation at
> http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#relationship-to-non-primary-mapper,
> even though this has been around for years.
>
> and it looks like……
>
> expr = select([
> func.max(Version.id).label("max_id"),
> Version.object_id
> ]).group_by(Version.object_id).alias()
>
> stmt = select([Version]).\
> select_from(join(Version, expr, Version.id == expr.c.max_id)).\
> alias()
>
> current_version = mapper(Version, stmt, non_primary=True)
>
> Object.current_version = relationship(current_version)
>
> I think I might have actually written a mapping like this as an example
> back in SQLAlchemy 0.1 even, this was the goofy kind of thing I thought
> everyone would be doing all the time.
>
> works with joinedload. Query is not too efficient, but is like:
>
> SELECT objects.id AS objects_id, anon_1.id AS anon_1_id, anon_1.object_id
> AS anon_1_object_id
> FROM objects LEFT OUTER JOIN (SELECT versions.id AS id,
> versions.object_id AS object_id
> FROM versions JOIN (SELECT max(versions.id) AS max_id, versions.object_id
> AS object_id
> FROM versions GROUP BY versions.object_id) AS anon_2 ON versions.id =
> anon_2.max_id) AS anon_1 ON objects.id = anon_1.object_id
> WHERE objects.id = %(id_1)s
>
>
>
>
> On Jan 31, 2014, at 5:35 PM, Josh Kuhn wrote:
>
> I've got a two tables I'd like to create a relationship for. One is the
> object, and another tracks versions.
>
> Here's a gist with the setup:
> https://gist.github.com/deontologician/8744532
>
> Basically, the object doesn't have a direct reference to the current
> version stored in the table. Instead, the current version is defined as the
> maximum version that points to that object.
>
> I'd like to have a one-to-one "current_version" relationship, but this has
> proven difficult (at least in 0.8.4). The primary goal is to allow using
> the joinedload options to control populating the current_version field, but
> that only works when a relationship is defined and is non-dynamic.
>
> Any hints as to how to get this to work like I want?
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Relationships using "max"
I've got a two tables I'd like to create a relationship for. One is the object, and another tracks versions. Here's a gist with the setup: https://gist.github.com/deontologician/8744532 Basically, the object doesn't have a direct reference to the current version stored in the table. Instead, the current version is defined as the maximum version that points to that object. I'd like to have a one-to-one "current_version" relationship, but this has proven difficult (at least in 0.8.4). The primary goal is to allow using the joinedload options to control populating the current_version field, but that only works when a relationship is defined and is non-dynamic. Any hints as to how to get this to work like I want? -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Automatically setting fields on collection append
On Wed, Jul 3, 2013 at 7:30 PM, Michael Bayer wrote: > @event.listens_for(Gadget, "before_insert") > def before_gadget(mapper, connection, target): > target.machine_id = target.widget.machine_id > > > 2. when widget is updated, gadgets need new machine_id, here UPDATE is > probably best since the Gadget might not be part of the flush: > > @event.listens_for(Widget, "after_update") > def after_widget(mapper, connection, target): > connection.execute( > Gadget.__table__.update(). > values(machine_id=target.machine_id). > where(Gadget.widget_id == target.widget_id) > That works for me, thanks! -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Automatically setting fields on collection append
I've attempted the following:
from sqlalchemy import Column, Integer, String, ForeignKey, create_engine
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.event import listen
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.associationproxy import association_proxy
Base = declarative_base()
class Machine(Base):
__tablename__ = 'machines'
__table_args__ = {'sqlite_autoincrement': True}
machine_id = Column(Integer, primary_key=True)
widgets = relationship('Widget')
class Widget(Base):
__tablename__ = 'widgets'
__table_args__ = {'sqlite_autoincrement': True}
widget_id = Column(Integer, primary_key=True)
machine_id = Column(Integer, ForeignKey('machines.machine_id'),
nullable=False)
_gadgets = relationship('Gadget')
gadgets = association_proxy('_gadgets', 'json_repr',
creator=lambda kwargs: Gadget(**kwargs))
class Gadget(Base):
__tablename__ = 'gadgets'
__table_args__ = {'sqlite_autoincrement': True}
gadget_id = Column(Integer, primary_key=True)
widget_id = Column(Integer, ForeignKey('widgets.widget_id'),
nullable=False)
machine_id = Column(Integer, nullable=False)
a = Column(String)
b = Column(String)
c = Column(Integer)
@property
def json_repr(self):
return dict(a=self.a, b=self.b, c=self.c)
@staticmethod
def update_machine_ids(session, flush_context):
widgets = [w for w in session.new if isinstance(w, Widget)]
widgets.extend(w for w in session.dirty if isinstance(w, Widget))
print widgets
for widget in widgets:
session.query(Gadget).with_parent(widget).update(
{"machine_id": widget.machine_id})
if __name__ == '__main__':
engine = create_engine('sqlite:///:memory:', echo=False)
Base.metadata.bind = engine
Base.metadata.create_all()
Session = sessionmaker(bind=engine)
listen(Session, 'after_flush', Gadget.update_machine_ids)
session = Session()
m = Machine()
w = Widget()
session.add(m)
m.widgets.append(w)
w.gadgets.append(dict(a='1', b='2'))
session.commit()
I still get an integrity error because the after_flush happens after I've
already tried to insert the null gadgets. If I move the flush after the
widget is added to the session, but before the gadgets are, then the
query(Gadget).with_parent(widget) obviously won't find anything.
Maybe I should listen for Widget load events?
On Wed, Jul 3, 2013 at 5:07 PM, Michael Bayer wrote:
>
> On Jul 3, 2013, at 5:06 PM, Michael Bayer
> wrote:
>
> > Do the UPDATE through Session.execute() so it's within the same
> transaction.
>
> .. or even just
> query(Gadget).with_parent(some_widget).update({"machine_id":
> some_widget.machine_id}), then you can have those Gadget objects refreshed
> in memory using synchronize_session, if that's important.
>
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Automatically setting fields on collection append
I'm trying to get a certain access pattern to work and I need a bit of help: https://gist.github.com/deontologician/5922496 What I'm trying to do is use an association proxy to create a view of a collection that looks like a list of dictionaries (for serializing to json). I also want to update that collection y receiving one of these dictionaries from the user. The problem is, I have to also keep two fields in sync that the users "shouldn't see". In the example, it's widget_id and machine_id. widget_id is easy, because it's the foreign key to the owner of the collection, so sqlalchemy sets that for me. The problem is that the machine_id needs to be kept in sync as well, but it refers to an object "one level up". I've tried adding a validator to the collection under the proxy, but it only checks on append, so initially null fields that the server sets aren't handled the way SQLA handles the foreign_key field. Is there an easier way to sync up the Gadget's machine_id field with the Widget's machine_id field than just having a listener for every set event on machine_id go through the entire collection and set it on all the objects? -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] My mapped tables are missing insert, update methods
Your User class is mapped to a Table. It's not the Table itself. To get the update method, you need to access User.__table__.update On Fri, Jun 7, 2013 at 4:12 PM, Michael Nachtigal < michael.nachti...@catalinamarketing.com> wrote: > After reading the documentation, I am under the impression that my > mapped tables should have the methods insert() and update(). For example, > see here: > > > http://docs.sqlalchemy.org/en/rel_0_8/core/schema.html?highlight=update#sqlalchemy.schema.Table.update > > But my mapped tables don't have those methods; why might that be? > > >>> User > > >>> User.insert > Traceback (most recent call last): > File "", line 1, in > AttributeError: type object 'User' has no attribute 'insert' > >>> User.update > Traceback (most recent call last): > File "", line 1, in > AttributeError: type object 'User' has no attribute 'update' > > Thanks, > Mike > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. > For more options, visit https://groups.google.com/groups/opt_out. > > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
