[sqlalchemy] Re: insert defaults
Thanks. I might have figured out a solution. I can get it to assign func.compressed to the column attribute, but I can't get it to load the text uncompressed now. class BaseStruct(object): def _set_text(self, text): self.compressed = func.compress(text) def _get_text(self): return select([uncompress(compressed)]) text = property(_get_text, _set_text) class Matrix(BaseStruct, Base): __tablename__ = 'matrix' __table_args__ = ( {'autoload':True} ) compressed = deferred(Column(Binary())) text = BaseStruct.text s = Session() m = s.query(Matrix).get(1) m.compressed read-only buffer for 0x1e67000, size -1, offset 0 at 0x12e7680 m.text='Blah' m.compressed sqlalchemy.sql.expression.Function at 0x12e76b0; compress So after assigning the text... I get what I want. The column mapped attribute is assigned func.compress(text). But I also need to uncompress the field to read it sometimes. s = Session() m = s.query(Matrix).get(7) m.text sqlalchemy.sql.expression.Function at 0x1392cf0; uncompress In the past I assigned Matrix.text=column_property(select([uncompress(compressed)]),deferred=True) I could probably write a little SqlSoup to do this select statement, but is there a way I can integrate this all well? The way I'm doing this feels very sloppy. SqlAlchemy is powerful, but can be very complicated. On Mar 5, 8:30 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Mar 5, 2010, at 6:44 PM, patrick wrote: Well it's something between the two. The instance variable compressed will always be NULL when adding or updating an instance, but I want it to infer a value from another instance variable. When inserting... the value of 'compressed' in the sql statement needs to be the raw SQL string COMPRESS('%s') % instance.text. It isn't simply running it through a function... it's dynamically creating the column value from the object's instance variables upon insert or update. MySQL has some funky compression function so the insert value for the column has to be raw sql. so do a before_insert() mapper extension and set the attribute as needed to func.compressed(instance.text). Or do the same at the object level, i.e user sets myobject.foo, foo is a descriptor-enabled method which then sets myobject.bar = func.compressed(foo) or whatever. the technique here ishttp://www.sqlalchemy.org/docs/session.html#embedding-sql-insert-upda On Mar 4, 3:30 pm, Michael Bayer mike...@zzzcomputing.com wrote: patrick wrote: Hey, I'm trying to create dynamic defaults for columns ala http:// www.sqlalchemy.org/docs/metadata.html#context-sensitive-default-funct MySQL has COMPRESS and UNCOMPRESS functions that I'm trying to leverage. I don't want to compress with python's zlib because I have legacy tables that were compressed using MySQL (which has a weird non- standard zip header and body), and I need to interface with them. Anyway, during an insert or update, I want to grab the 'text' variable from the instance object and insert it into the database like: COMPRESS(the text value). Obviously context.current_parameters is not the appropriate object, but I can't figure out if it's possible to access the instance being inserted/updated. are you trying to create a *default* value for an INSERT/UPDATE when NULL would otherwise be passed, or are you trying to run all incoming/outgoing data through a SQL function ? those are two completely separate topics. def compress_text(context): return COMPRESS('%s') % context.current_parameters['text'] class Tree(BaseStruct, Base): __tablename__ = 'tree' __table_args__ = ( {'autoload':True} ) compressed = deferred(Column(Binary(),default=compress_text,default=compress_text,onupdate=compress_text)) text = column_property(select([UNCOMPRESS(compressed)]),deferred=True) Is this possible with 0.5.7? -- 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. -- 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 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 sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at
Re: [sqlalchemy] Re: insert defaults
patrick wrote: In the past I assigned Matrix.text=column_property(select([uncompress(compressed)]),deferred=True) I could probably write a little SqlSoup to do this select statement, but is there a way I can integrate this all well? The way I'm doing this feels very sloppy. SqlAlchemy is powerful, but can be very complicated. you should map an attribute directly to column_property(uncompress(table.c.compressed)). if you want it only to fire when you read it, use deferred() instead of column_property(). On Mar 5, 8:30 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Mar 5, 2010, at 6:44 PM, patrick wrote: Well it's something between the two. The instance variable compressed will always be NULL when adding or updating an instance, but I want it to infer a value from another instance variable. When inserting... the value of 'compressed' in the sql statement needs to be the raw SQL string COMPRESS('%s') % instance.text. It isn't simply running it through a function... it's dynamically creating the column value from the object's instance variables upon insert or update. MySQL has some funky compression function so the insert value for the column has to be raw sql. so do a before_insert() mapper extension and set the attribute as needed to func.compressed(instance.text). Or do the same at the object level, i.e user sets myobject.foo, foo is a descriptor-enabled method which then sets myobject.bar = func.compressed(foo) or whatever. the technique here ishttp://www.sqlalchemy.org/docs/session.html#embedding-sql-insert-upda On Mar 4, 3:30 pm, Michael Bayer mike...@zzzcomputing.com wrote: patrick wrote: Hey, I'm trying to create dynamic defaults for columns ala http:// www.sqlalchemy.org/docs/metadata.html#context-sensitive-default-funct MySQL has COMPRESS and UNCOMPRESS functions that I'm trying to leverage. I don't want to compress with python's zlib because I have legacy tables that were compressed using MySQL (which has a weird non- standard zip header and body), and I need to interface with them. Anyway, during an insert or update, I want to grab the 'text' variable from the instance object and insert it into the database like: COMPRESS(the text value). Obviously context.current_parameters is not the appropriate object, but I can't figure out if it's possible to access the instance being inserted/updated. are you trying to create a *default* value for an INSERT/UPDATE when NULL would otherwise be passed, or are you trying to run all incoming/outgoing data through a SQL function ? those are two completely separate topics. def compress_text(context): return COMPRESS('%s') % context.current_parameters['text'] class Tree(BaseStruct, Base): __tablename__ = 'tree' __table_args__ = ( {'autoload':True} ) compressed = deferred(Column(Binary(),default=compress_text,default=compress_text,onupdate=compress_text)) text = column_property(select([UNCOMPRESS(compressed)]),deferred=True) Is this possible with 0.5.7? -- 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. -- 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 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 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. -- 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: insert defaults
Yes, but then my descriptor is washed away. I'm trying to make it like a column property on 'get' and a descriptor enabled property on 'set'. On Mar 25, 3:27 pm, Michael Bayer mike...@zzzcomputing.com wrote: patrick wrote: In the past I assigned Matrix.text=column_property(select([uncompress(compressed)]),deferred=True) I could probably write a little SqlSoup to do this select statement, but is there a way I can integrate this all well? The way I'm doing this feels very sloppy. SqlAlchemy is powerful, but can be very complicated. you should map an attribute directly to column_property(uncompress(table.c.compressed)). if you want it only to fire when you read it, use deferred() instead of column_property(). On Mar 5, 8:30 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Mar 5, 2010, at 6:44 PM, patrick wrote: Well it's something between the two. The instance variable compressed will always be NULL when adding or updating an instance, but I want it to infer a value from another instance variable. When inserting... the value of 'compressed' in the sql statement needs to be the raw SQL string COMPRESS('%s') % instance.text. It isn't simply running it through a function... it's dynamically creating the column value from the object's instance variables upon insert or update. MySQL has some funky compression function so the insert value for the column has to be raw sql. so do a before_insert() mapper extension and set the attribute as needed to func.compressed(instance.text). Or do the same at the object level, i.e user sets myobject.foo, foo is a descriptor-enabled method which then sets myobject.bar = func.compressed(foo) or whatever. the technique here ishttp://www.sqlalchemy.org/docs/session.html#embedding-sql-insert-upda On Mar 4, 3:30 pm, Michael Bayer mike...@zzzcomputing.com wrote: patrick wrote: Hey, I'm trying to create dynamic defaults for columns ala http:// www.sqlalchemy.org/docs/metadata.html#context-sensitive-default-funct MySQL has COMPRESS and UNCOMPRESS functions that I'm trying to leverage. I don't want to compress with python's zlib because I have legacy tables that were compressed using MySQL (which has a weird non- standard zip header and body), and I need to interface with them. Anyway, during an insert or update, I want to grab the 'text' variable from the instance object and insert it into the database like: COMPRESS(the text value). Obviously context.current_parameters is not the appropriate object, but I can't figure out if it's possible to access the instance being inserted/updated. are you trying to create a *default* value for an INSERT/UPDATE when NULL would otherwise be passed, or are you trying to run all incoming/outgoing data through a SQL function ? those are two completely separate topics. def compress_text(context): return COMPRESS('%s') % context.current_parameters['text'] class Tree(BaseStruct, Base): __tablename__ = 'tree' __table_args__ = ( {'autoload':True} ) compressed = deferred(Column(Binary(),default=compress_text,default=compress_text,onupdate=compress_text)) text = column_property(select([UNCOMPRESS(compressed)]),deferred=True) Is this possible with 0.5.7? -- 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. -- 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 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 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. -- 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: insert defaults
patrick wrote: Yes, but then my descriptor is washed away. I'm trying to make it like a column property on 'get' and a descriptor enabled property on 'set'. here's an example of a full round trip of data going in through a func.() and out through a func.() - use this recipe: from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() engine = create_engine('sqlite://', echo=True) class Foo(Base): __tablename__ = 'foo' id = Column(Integer, primary_key=True) data = Column(String) _some_uppercase_thing = column_property(func.lower(data)) def _get_some_uppercase_thing(self): return self._some_uppercase_thing def _set_some_uppercase_thing(self, data): self.data = func.upper(data) some_uppercase_thing = synonym( '_some_uppercase_thing', descriptor=property( _get_some_uppercase_thing, _set_some_uppercase_thing ) ) Base.metadata.create_all(engine) sess = sessionmaker(engine)() sess.add_all([ Foo(some_uppercase_thing=value 1), Foo(some_uppercase_thing=value 2), Foo(some_uppercase_thing=value 3), Foo(some_uppercase_thing=value 4), ]) sess.commit() assert sess.query(Foo.some_uppercase_thing).all() == [ (value 1, ), (value 2, ), (value 3, ), (value 4, ), ] assert sess.query(Foo.some_uppercase_thing).filter(Foo.some_uppercase_thing == 'value 2').all() == [ (value 2, ), ] assert sess.execute(select([Foo.__table__.c.data])).fetchall() == [ (VALUE 1, ), (VALUE 2, ), (VALUE 3, ), (VALUE 4, ), ] On Mar 25, 3:27 pm, Michael Bayer mike...@zzzcomputing.com wrote: patrick wrote: In the past I assigned Matrix.text=column_property(select([uncompress(compressed)]),deferred=True) I could probably write a little SqlSoup to do this select statement, but is there a way I can integrate this all well? The way I'm doing this feels very sloppy. SqlAlchemy is powerful, but can be very complicated. you should map an attribute directly to column_property(uncompress(table.c.compressed)). if you want it only to fire when you read it, use deferred() instead of column_property(). On Mar 5, 8:30 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Mar 5, 2010, at 6:44 PM, patrick wrote: Well it's something between the two. The instance variable compressed will always be NULL when adding or updating an instance, but I want it to infer a value from another instance variable. When inserting... the value of 'compressed' in the sql statement needs to be the raw SQL string COMPRESS('%s') % instance.text. It isn't simply running it through a function... it's dynamically creating the column value from the object's instance variables upon insert or update. MySQL has some funky compression function so the insert value for the column has to be raw sql. so do a before_insert() mapper extension and set the attribute as needed to func.compressed(instance.text). Or do the same at the object level, i.e user sets myobject.foo, foo is a descriptor-enabled method which then sets myobject.bar = func.compressed(foo) or whatever. the technique here ishttp://www.sqlalchemy.org/docs/session.html#embedding-sql-insert-upda On Mar 4, 3:30 pm, Michael Bayer mike...@zzzcomputing.com wrote: patrick wrote: Hey, I'm trying to create dynamic defaults for columns ala http:// www.sqlalchemy.org/docs/metadata.html#context-sensitive-default-funct MySQL has COMPRESS and UNCOMPRESS functions that I'm trying to leverage. I don't want to compress with python's zlib because I have legacy tables that were compressed using MySQL (which has a weird non- standard zip header and body), and I need to interface with them. Anyway, during an insert or update, I want to grab the 'text' variable from the instance object and insert it into the database like: COMPRESS(the text value). Obviously context.current_parameters is not the appropriate object, but I can't figure out if it's possible to access the instance being inserted/updated. are you trying to create a *default* value for an INSERT/UPDATE when NULL would otherwise be passed, or are you trying to run all incoming/outgoing data through a SQL function ? those are two completely separate topics. def compress_text(context): return COMPRESS('%s') % context.current_parameters['text'] class Tree(BaseStruct, Base): __tablename__ = 'tree' __table_args__ = ( {'autoload':True} ) compressed = deferred(Column(Binary(),default=compress_text,default=compress_text,onupdate=compress_text)) text =
[sqlalchemy] Re: insert defaults
Well it's something between the two. The instance variable compressed will always be NULL when adding or updating an instance, but I want it to infer a value from another instance variable. When inserting... the value of 'compressed' in the sql statement needs to be the raw SQL string COMPRESS('%s') % instance.text. It isn't simply running it through a function... it's dynamically creating the column value from the object's instance variables upon insert or update. MySQL has some funky compression function so the insert value for the column has to be raw sql. On Mar 4, 3:30 pm, Michael Bayer mike...@zzzcomputing.com wrote: patrick wrote: Hey, I'm trying to create dynamic defaults for columns ala http:// www.sqlalchemy.org/docs/metadata.html#context-sensitive-default-funct MySQL has COMPRESS and UNCOMPRESS functions that I'm trying to leverage. I don't want to compress with python's zlib because I have legacy tables that were compressed using MySQL (which has a weird non- standard zip header and body), and I need to interface with them. Anyway, during an insert or update, I want to grab the 'text' variable from the instance object and insert it into the database like: COMPRESS(the text value). Obviously context.current_parameters is not the appropriate object, but I can't figure out if it's possible to access the instance being inserted/updated. are you trying to create a *default* value for an INSERT/UPDATE when NULL would otherwise be passed, or are you trying to run all incoming/outgoing data through a SQL function ? those are two completely separate topics. def compress_text(context): return COMPRESS('%s') % context.current_parameters['text'] class Tree(BaseStruct, Base): __tablename__ = 'tree' __table_args__ = ( {'autoload':True} ) compressed = deferred(Column(Binary(),default=compress_text,default=compress_text,onupdate=compress_text)) text = column_property(select([UNCOMPRESS(compressed)]),deferred=True) Is this possible with 0.5.7? -- 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. -- 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: insert defaults
On Mar 5, 2010, at 6:44 PM, patrick wrote: Well it's something between the two. The instance variable compressed will always be NULL when adding or updating an instance, but I want it to infer a value from another instance variable. When inserting... the value of 'compressed' in the sql statement needs to be the raw SQL string COMPRESS('%s') % instance.text. It isn't simply running it through a function... it's dynamically creating the column value from the object's instance variables upon insert or update. MySQL has some funky compression function so the insert value for the column has to be raw sql. so do a before_insert() mapper extension and set the attribute as needed to func.compressed(instance.text). Or do the same at the object level, i.e user sets myobject.foo, foo is a descriptor-enabled method which then sets myobject.bar = func.compressed(foo) or whatever. the technique here is http://www.sqlalchemy.org/docs/session.html#embedding-sql-insert-update-expressions-into-a-flush . On Mar 4, 3:30 pm, Michael Bayer mike...@zzzcomputing.com wrote: patrick wrote: Hey, I'm trying to create dynamic defaults for columns ala http:// www.sqlalchemy.org/docs/metadata.html#context-sensitive-default-funct MySQL has COMPRESS and UNCOMPRESS functions that I'm trying to leverage. I don't want to compress with python's zlib because I have legacy tables that were compressed using MySQL (which has a weird non- standard zip header and body), and I need to interface with them. Anyway, during an insert or update, I want to grab the 'text' variable from the instance object and insert it into the database like: COMPRESS(the text value). Obviously context.current_parameters is not the appropriate object, but I can't figure out if it's possible to access the instance being inserted/updated. are you trying to create a *default* value for an INSERT/UPDATE when NULL would otherwise be passed, or are you trying to run all incoming/outgoing data through a SQL function ? those are two completely separate topics. def compress_text(context): return COMPRESS('%s') % context.current_parameters['text'] class Tree(BaseStruct, Base): __tablename__ = 'tree' __table_args__ = ( {'autoload':True} ) compressed = deferred(Column(Binary(),default=compress_text,default=compress_text,onupdate=compress_text)) text = column_property(select([UNCOMPRESS(compressed)]),deferred=True) Is this possible with 0.5.7? -- 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. -- 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. -- 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.