[sqlalchemy] Re: UserDefinedType for tuples
The most similar example to what I want to do, I think is here: http://groups.google.com/group/sqlalchemy/browse_thread/thread/81cc56d5ed693a48/58a3caa40a7daa39?lnk=gstq=TypeDecorator#58a3caa40a7daa39 where Frank is storing nutrient weights in a new type. After (re)reading the doco about custom types at: http://www.sqlalchemy.org/docs/05/reference/sqlalchemy/types.html#sqlalchemy.types.TypeDecorator it seems to me that the reason for using TypeDecorator rather than TypeEngine is something to do with this sentence: The reason that type behavior is modified using class decoration instead of subclassing is due to the way dialect specific types are used. I suppose there are some complexities with bind_params implementations across dialects. eg. http://www.sqlite.org/c3ref/bind_blob.html In Frank's example he defined two classes: Weight(Object) and WeightType(types.TypeDecorator) but it still seems more natural to me to use the subclassing of TypeEngine to create a new compound type from two(or three) FLOATs. Then I might have to write methods overriding adapt_operator and compare_values and copy(value) but at least I'd understand what I was doing and why. However as TypeDecorator seems to be the recommended way, perhaps I should go with the flow. But what about the adapt_operator, compare_values etc? Will that all somehow be taken care of automagically if I do something like this? Numpy seems to have gained quite a bit of acceptance so I want pull small objects of type np.array([x,y], np.float32) straight out of the database and use them directly. Is this possible? Is it sensible? import numpy as np class Vector(object): __slots__ = ['x', 'y'] def __init__(self, x, y): self.x = x self.y = y def __repr__(self): return np.array([x,y], np.float32) #Both Sage and Numpy seem to prefer a list eg. V([x,y]) #as the instantiation input rather than a python tuple. def __repr__(self): return [%s, %s] % (self.x, self.y) class VectorType(types.TypeDecorator): impl = types.Numeric def process_bind_param(self, vector, dialect): if vector is None: return None return [vector.x, vector.y] def process_result_value(self, vector, dialect): if x is None: return None #return Vector(x, y) #or... return np.array([vector.x, vector.y], np.float32) -- 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: Is it possible to know in advance elements that will be cascade-deleted?
With a bit of duck typing I came out with an easier solution (to my needs). The only risky thing I had to use private _props dictionary. from sqlalchemy.orm import object_mapper for name, prop in object_mapper(item_to_be_deleted)._props.iteritems(): if 'delete' in getattr(prop, 'cascade', ()): print name Thanks again you for your support On Feb 3, 6:04 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Feb 3, 2011, at 11:58 AM, neurino wrote: That is great! Just for eventual followers I fix imports: from sqlalchemy.orm import object_mapper from sqlalchemy.orm.attributes import instance_state m = object_mapper(item_to_be_deleted) for rec in m.cascade_iterator(delete, instance_state(item_to_be_deleted)): obj = rec[0] print item will be deleted !, obj Anyway there's some way to stop recursiveness after a given level (or just 1)? That's because for me deleting one ctrl_unit means deleting hundreds of `Acquisition`s with thousands of `Data` each that means a **lot** of queries and I could assume the user is smart enough to know that if he deletes an Acquisition he deletes its data too... I'm looking at cascade_iterator def source, I could hack that end enclose directly in my code, the halt_on parameter is unused, as far as I understand. Thanks for your support halt_on works, you use that, its a callable. neurino On Feb 3, 5:19 pm, Michael Bayer mike...@zzzcomputing.com wrote: you could use the mapper's cascade function from sqlalchemy.orm import object_mapper, instance_state m = object_mapper(item_to_be_deleted) for rec in m.cascade_iterator(delete, instance_state(item_to_be_deleted)): obj = rec[0] print item will be deleted !, obj On Feb 3, 2011, at 6:15 AM, neurino wrote: Can I show the user a warning like: if you delete this item also [list of other items] will be removed whichever is the item? I was using something like this: import inspect def get_items(item_to_be_deleted): get_items(item_to_be_deleted) - [(child_item_name, number_of_child_items)] return [(name, len(inst)) for (name, inst) in inspect.getmembers(item_to_be_deleted) if isinstance(inst, orm.collections.InstrumentedList)] and it worked until all relationships had cascade delete but now I have one without it and it shows in the list too while it shouldn't... Any tips? Thank you for your support neurino -- 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 athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to 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: FOR UPDATE OF
Excellent, it is working for the simpler case, but for oracle 8 (who isn't as smart when indexing) I also need it to work for subqueryload(). So the problem is that my FOR UPDATE OF is also being added for subqueryload selects. * Can I tell within the compiles context if this is for subqueryload? (Or can the Query tell?) * Are there other cases where the query is reused that I need to be careful of? I restructured this way (as you're original suggestion to fix another issue): @compiles(Select) def compile_forupdateof(select, compiler, **kw): rendered = compiler.visit_select(select, **kw) if hasattr(select, '_for_update_of'): mapper = class_mapper(select._for_update_of) name = mapper.mapped_table.name if compiler.dialect.name == 'oracle': # Oracle makes us specify the column name (for views, I guess, since it locks entire row) name += '.' + mapper.primary_key[0].name rendered = %s FOR UPDATE OF %s % (rendered, name) return rendered On Feb 3, 9:51 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Feb 3, 2011, at 9:29 PM, Kent wrote: Yeah, I wanted to apologize because my heart wants to contribute to the project (really), but I'm working overtime like mad swamped because our product is live in use now and I've got a backload of tickets to solve! I also feel my level of understanding currently is more hacking than contributing. I hope to be of more help to the project in the future. Is simpler than you had in mind a good thing or am I over simplifying and it won't work for bunch of cases? I note that the simple case is working, but something like this fails: DBSession.query(Order).for_update_of(Order).limit(10).all() since I really need to have the for update inside in this case... any advise or is this what you meant by There's not a great way to intercept the middle of the SELECT compilation with a new kind of clause in this case.? i think if it works for what you need right now, then its great. @compiles is meant to give you what you need to get out of a jam. On Feb 3, 9:07 pm, Michael Bayer mike...@zzzcomputing.com wrote: oh OK this is a little simpler than what I had in mind, you just have to add the mixin expression.Executable to your ForUpdateOf class. On Feb 3, 2011, at 9:05 PM, Kent wrote: Here is a crude outline (need to properly escape table name, etc.), of what I think might work, and it seems to render properly, but crashes with: File /home/rarch/tg2env/lib/python2.6/site-packages/ SQLAlchemy-0.6.4.2kbdev-py2.6-linux-x86_64.egg/sqlalchemy/engine/ default.py, line 353, in __init__ raise exc.ArgumentError(Not an executable clause: %s % compiled) ArgumentError: Not an executable clause: ... class MyQuery(Query): _for_update_of = None @_generative() def for_update_of(self, arg): Keep track that we want to for update of this self._for_update_of = class_mapper(arg).mapped_table.name def _compile_context(self, labels=True): context = super(MyQuery, self)._compile_context(labels) if self._for_update_of: context.statement = ForUpdateOf(context.statement, self._for_update_of) return context class ForUpdateOf(ClauseElement): def __init__(self, statement, for_update_of): self.statement = statement self.for_update_of = for_update_of @compiles(ForUpdateOf) def compile_forupdateof(element, compiler, **kw): return %s FOR UPDATE OF %s % (compiler.process(element.statement), element.for_update_of) -- 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 athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to 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] sequence incrementing
I'm connecting to an Oracle database for my work and we do replication by periodically joining tables across sites instead of a single server just in case a link in between goes down. One issue with this though is I need to generate unique keys for a single table so if the connection does go down, insertions to both tables won't cause a conflict when it's time to merge. Because the ids aren't important to me during insertion, I just use a Sequence object (since Oracle doesn't support autoincrementing). This has worked fine, but I'm now trying how to combine this with the unique keys across the two sites. I noticed the Sequence class has a start and increment tag, which I thought would work perfectly by interleaving the keys per site. For example... if site1: init = 0 else: init = 1 sequence = Sequence('id_seq',start=init,increment=2) I don't see any actual documentation for what start and increment do (http://www.sqlalchemy.org/docs/core/schema.html#sqlalchemy.schema.Sequence), so I'm just assuming they function as I'd expect, where start is the first id to try and increment is the space between the possible id's, so site 1's ids will always be % 2 == 0, while site 2's would be % 2 == 1. This doesn't seem to be what I'm getting however. In a few test cases on one site, the first id starts at 10,283 and the next one generated is 10,284. In this case it's starting at a crazy high value other than 0 and it doesn't seem to be incrementing at all. Am I using these flags incorrectly? Is this a bug in sqlalchemy? Deprecated flags? Or is this whole idea garbage and I should use a guid? Thanks. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] sequence incrementing
The sequence has to be created (meaning, the appropriate DDL statements must be executed) on the target platform with those options, or the appropriate ALTER statements emitted to ALTER the sequence's settings. So when those flags are applied to sqlalchemy.Sequence(), its assumed that you're using table.create(), metadata.create_all(), or sequence.create() to issue the CREATE SEQUENCE call. The INCREMENT BY and START WITH clauses should be emitted as of the 0.6 series of SQLAlchemy. On Feb 4, 2011, at 2:10 PM, Josh Stratton wrote: I'm connecting to an Oracle database for my work and we do replication by periodically joining tables across sites instead of a single server just in case a link in between goes down. One issue with this though is I need to generate unique keys for a single table so if the connection does go down, insertions to both tables won't cause a conflict when it's time to merge. Because the ids aren't important to me during insertion, I just use a Sequence object (since Oracle doesn't support autoincrementing). This has worked fine, but I'm now trying how to combine this with the unique keys across the two sites. I noticed the Sequence class has a start and increment tag, which I thought would work perfectly by interleaving the keys per site. For example... if site1: init = 0 else: init = 1 sequence = Sequence('id_seq',start=init,increment=2) I don't see any actual documentation for what start and increment do (http://www.sqlalchemy.org/docs/core/schema.html#sqlalchemy.schema.Sequence), so I'm just assuming they function as I'd expect, where start is the first id to try and increment is the space between the possible id's, so site 1's ids will always be % 2 == 0, while site 2's would be % 2 == 1. This doesn't seem to be what I'm getting however. In a few test cases on one site, the first id starts at 10,283 and the next one generated is 10,284. In this case it's starting at a crazy high value other than 0 and it doesn't seem to be incrementing at all. Am I using these flags incorrectly? Is this a bug in sqlalchemy? Deprecated flags? Or is this whole idea garbage and I should use a guid? Thanks. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- 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] Multilingual Model
I'd probably use INT and also get some FOREIGN KEYs in there... On Feb 2, 2011, at 11:46 AM, Madhu Alagu wrote: Hi I am looking best model for the following tables: - ---Table : groups_t - CREATE TABLE groups_t ( id BIGINT NOT NULL, code VARCHAR NOT NULL, version BIGINT NOT NULL ); - ---Table : groups_i18n_t - CREATE TABLE groups_i18n_t ( group_id BIGINT NOT NULL, lang_id BIGINT NOT NULL, description TEXT NOT NULL, version BIGINT NOT NULL ); - ---Table : groups_lookup_t - CREATE TABLE groups_lookup_t ( group_id BIGINT NOT NULL, lang_id BIGINT NOT NULL, code VARCHAR NOT NULL, description TEXT NOT NULL, lookup_text TEXT NOT NULL ); Thanks Madhu Alagu -- 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. -- 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] sequence incrementing
I'm currently passing the table the sequence object, when I create the table. I've been building the table with metadata.create_all. Calling sequnce.create() before creating the table complains of no binding to an engine or a connection. I understand this makes sense as I haven't told the sequence where to connect, but I don't see anything in the docs for connecting the sequence to the engine/connection. Is there a hidden parameter for binding? Or if I include this sequence in in the table, should that sequence be associated with the metadata object I use to make the table? sequence = Sequence('id_seq', start=siteIndex, increment=len(sites)) #sequence.create() table = Table('acounts', self.metadata, Column()) self.metadata.create_all() On Fri, Feb 4, 2011 at 1:46 PM, Michael Bayer mike...@zzzcomputing.com wrote: The sequence has to be created (meaning, the appropriate DDL statements must be executed) on the target platform with those options, or the appropriate ALTER statements emitted to ALTER the sequence's settings. So when those flags are applied to sqlalchemy.Sequence(), its assumed that you're using table.create(), metadata.create_all(), or sequence.create() to issue the CREATE SEQUENCE call. The INCREMENT BY and START WITH clauses should be emitted as of the 0.6 series of SQLAlchemy. On Feb 4, 2011, at 2:10 PM, Josh Stratton wrote: I'm connecting to an Oracle database for my work and we do replication by periodically joining tables across sites instead of a single server just in case a link in between goes down. One issue with this though is I need to generate unique keys for a single table so if the connection does go down, insertions to both tables won't cause a conflict when it's time to merge. Because the ids aren't important to me during insertion, I just use a Sequence object (since Oracle doesn't support autoincrementing). This has worked fine, but I'm now trying how to combine this with the unique keys across the two sites. I noticed the Sequence class has a start and increment tag, which I thought would work perfectly by interleaving the keys per site. For example... if site1: init = 0 else: init = 1 sequence = Sequence('id_seq',start=init,increment=2) I don't see any actual documentation for what start and increment do (http://www.sqlalchemy.org/docs/core/schema.html#sqlalchemy.schema.Sequence), so I'm just assuming they function as I'd expect, where start is the first id to try and increment is the space between the possible id's, so site 1's ids will always be % 2 == 0, while site 2's would be % 2 == 1. This doesn't seem to be what I'm getting however. In a few test cases on one site, the first id starts at 10,283 and the next one generated is 10,284. In this case it's starting at a crazy high value other than 0 and it doesn't seem to be incrementing at all. Am I using these flags incorrectly? Is this a bug in sqlalchemy? Deprecated flags? Or is this whole idea garbage and I should use a guid? Thanks. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- 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. -- 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] Multilingual Model
Hi Madhu, On 02/02/2011 17:46, Madhu Alagu wrote: Hi I am looking best model for the following tables: - ---Table : groups_t - CREATE TABLE groups_t ( id BIGINT NOT NULL, code VARCHAR NOT NULL, version BIGINT NOT NULL ); - ---Table : groups_i18n_t - CREATE TABLE groups_i18n_t ( group_id BIGINT NOT NULL, lang_id BIGINT NOT NULL, description TEXT NOT NULL, version BIGINT NOT NULL ); - ---Table : groups_lookup_t - CREATE TABLE groups_lookup_t ( group_id BIGINT NOT NULL, lang_id BIGINT NOT NULL, code VARCHAR NOT NULL, description TEXT NOT NULL, lookup_text TEXT NOT NULL ); You might want to look at these threads. i10n of data internationalization of content A few different approaches to this problem were discussed in them. Werner -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.