Re: [sqlalchemy] Re: onupdate Column attribute with joined table inheritance question
btw, using the __before_update__ technique introduced some weird data corruption issues for me when my class had PickleType datamembers. For instance, # Create the Foo class to map foos_table to class Foo(object): def __before_update__(self): self.some_pickle_type self.last_edit_date = datetime.now() the sql update statement generated would set some_pickle_type to NULL, even though I hadn't modified the datamember. This doesn't matter hugely to me at the moment, because I no longer am using a table hierarchy, and hence don't need to use __before_update__ anymore, but thought I would mention it in case anyone else runs into it. sqlalchemy.__version__ '0.5.4p2' On Mon, Mar 2, 2009 at 12:43 PM, John Fries john.a.fr...@gmail.com wrote: Thanks Michael! That should have been obvious to me, but for some reason I couldn't figure it out. On Mon, Mar 2, 2009 at 11:24 AM, Michael Bayer mike...@zzzcomputing.comwrote: On Mar 2, 2009, at 2:16 PM, John Fries wrote: Hi all, I am having a problem that is identical to the one mentioned last year on this list: http://groups.google.com/group/sqlalchemy/browse_thread/thread/efd3993c94c8d162/37943cda02151f2b?lnk=gstq=onupdate+inheritance#37943cda02151f2b I have Engineer and Manager tables that are inheriting from an Employee table using joined table inheritance. When I update one of the child tables, I want the last_edited timestamp column on the Employee table to be updated as well, but it is not updating. Mr. Bayer proposes the following: from sqlalchemy.orm import mapper as _mapper from sqlalchemy.orm import MapperExtension class MyExt(MapperExtension): def before_update(self, mapper, connection, instance): if hasattr(instance, '__before_update__'): instance.__before_update__() def mapper(*args, **kw): kw['extension'] = MyExt() return _mapper(*args, **kw) just hide that code away someplace, and then any instance which defines a method called __before_update__() will have it called before update. I've done that step, but I don't understand what the next step is. I thought that I would have to implement a __before_update__ method on my Engineer and Manager classes, which would then call some method on Employee using super. However, when I update my Engineer, it doesn't even look like the __before_update__ method is being called. So I conclude that I'm doing it wrong.Which instances should implement a __before_update__ method? What should the __before_update__ method do? just implement def __before_update__() on your base Employee class. this method should set the last_edited attribute to a new value (note you can set it to func.now() to have a SQL function fire off). Engineer and Manager will have the method automatically via class inheritance. Also make sure you are using the modfied mapper() function to create your mappers. --~--~-~--~~~---~--~~ 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.comsqlalchemy%2bunsubscr...@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: which column type to use for case sensitive queries?
Thanks Michael. I'm going to use MSString (confusingly, in my non-Microsoft
database instance), unless someone suggests otherwise (say, for performance
reasons). Having case-sensitive comparisons be the default seems more
natural to me (coming from a Java/Python/C++ background).
On Sun, Sep 20, 2009 at 9:31 PM, Michael Bayer mike...@zzzcomputing.comwrote:
On Sep 20, 2009, at 11:42 PM, John wrote:
I create mytable like this:
mytable = Table('mytable', metadata, Column('mycolumn', String(512)))
class MyTable:
pass
mapper(MyTable, mytable)
But when I run session.query(MyTable).filter(MyTable.mycolumn ==
Foo), it not only return the rows that match Foo, but also the
rows that match foo.
Is there a standard solution? I see some advice to modify, not the
column at creation time, but the query at query time so as to provide
case-sensitive matching on a per query basis. Some online references
suggest setting collate = utf8_bin (but that's MS specific), or
character set to binary, etc. Other references suggest passing in a
character_set=utf-8 at the time sqlalchemy connects to the database,
so as to change the default character set (http://www.sqlalchemy.org/
docs/05/reference/dialects/mysql.html#character-sets). I am not very
familiar yet with mysql, character sets, unicode, utf-8, latin1, etc.
I am using mysql. I can run
ALTER TABLE mytable MODIFY mycolumn VARCHAR(512) COLLATE utf8_bin;
or
ALTER TABLE mytable MODIFY mycolumn VARCHAR(512) CHARACTER SET
BINARY; either of which seems to fix the problem, but I'd rather do
it from within sqlalchemy since that is how I normally create my
tables.
for MySQL we have the COLLATE operator that is good for at query
time. Here are their docs:
http://dev.mysql.com/doc/refman/5.0/en/charset-collate.html
SQLA Column objects offer a collate() method to provide this, so you
can say:
column.collate('latin1_german2_ci') == foo
If you want to issue COLLATE when you create your tables, you can use
the MSString() datatype that in the 0.5 series you can import from
sqlalchemy.databases.mysql. For example you can use a case sensitive
collation, and then for a case insensitive match use either collate()
again, or just func.lower(mycolumn) == func.lower(myvalue).
--~--~-~--~~~---~--~~
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] onupdate Column attribute with joined table inheritance question
Hi all, I am having a problem that is identical to the one mentioned last year on this list: http://groups.google.com/group/sqlalchemy/browse_thread/thread/efd3993c94c8d162/37943cda02151f2b?lnk=gstq=onupdate+inheritance#37943cda02151f2b I have Engineer and Manager tables that are inheriting from an Employee table using joined table inheritance. When I update one of the child tables, I want the last_edited timestamp column on the Employee table to be updated as well, but it is not updating. Mr. Bayer proposes the following: from sqlalchemy.orm import mapper as _mapper from sqlalchemy.orm import MapperExtension class MyExt(MapperExtension): def before_update(self, mapper, connection, instance): if hasattr(instance, '__before_update__'): instance.__before_update__() def mapper(*args, **kw): kw['extension'] = MyExt() return _mapper(*args, **kw) just hide that code away someplace, and then any instance which defines a method called __before_update__() will have it called before update. I've done that step, but I don't understand what the next step is. I thought that I would have to implement a __before_update__ method on my Engineer and Manager classes, which would then call some method on Employee using super. However, when I update my Engineer, it doesn't even look like the __before_update__ method is being called. So I conclude that I'm doing it wrong.Which instances should implement a __before_update__ method? What should the __before_update__ method do? Any advice would be extremely appreciated. Thanks, John --~--~-~--~~~---~--~~ 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: onupdate Column attribute with joined table inheritance question
Thanks Michael! That should have been obvious to me, but for some reason I couldn't figure it out. On Mon, Mar 2, 2009 at 11:24 AM, Michael Bayer mike...@zzzcomputing.comwrote: On Mar 2, 2009, at 2:16 PM, John Fries wrote: Hi all, I am having a problem that is identical to the one mentioned last year on this list: http://groups.google.com/group/sqlalchemy/browse_thread/thread/efd3993c94c8d162/37943cda02151f2b?lnk=gstq=onupdate+inheritance#37943cda02151f2b I have Engineer and Manager tables that are inheriting from an Employee table using joined table inheritance. When I update one of the child tables, I want the last_edited timestamp column on the Employee table to be updated as well, but it is not updating. Mr. Bayer proposes the following: from sqlalchemy.orm import mapper as _mapper from sqlalchemy.orm import MapperExtension class MyExt(MapperExtension): def before_update(self, mapper, connection, instance): if hasattr(instance, '__before_update__'): instance.__before_update__() def mapper(*args, **kw): kw['extension'] = MyExt() return _mapper(*args, **kw) just hide that code away someplace, and then any instance which defines a method called __before_update__() will have it called before update. I've done that step, but I don't understand what the next step is. I thought that I would have to implement a __before_update__ method on my Engineer and Manager classes, which would then call some method on Employee using super. However, when I update my Engineer, it doesn't even look like the __before_update__ method is being called. So I conclude that I'm doing it wrong.Which instances should implement a __before_update__ method? What should the __before_update__ method do? just implement def __before_update__() on your base Employee class. this method should set the last_edited attribute to a new value (note you can set it to func.now() to have a SQL function fire off). Engineer and Manager will have the method automatically via class inheritance. Also make sure you are using the modfied mapper() function to create your mappers. --~--~-~--~~~---~--~~ 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: Should create_engine() be called per-process or per-request in threaded wsgi environment?
Yes, I would also like to know what is the appropriate way to use SQLAlchemy with respect to a WSGI server. I've been using Django with SQLAlchemy (not yet supported, but the recipe here http://lethain.com/entry/2008/jul/23/replacing-django-s-orm-with-sqlalchemy/got me moving), and it's not clear how to do connection pools and all that other good stuff. On Thu, Nov 6, 2008 at 10:29 AM, Randy Syring [EMAIL PROTECTED] wrote: I am developing a WSGI based web framework with sqlalchemy. I am unclear about when create_engine() should be called. I initially thought that engine creation and metadata would be initialized per process and each thread/request would just get a new session. However, I have recently run into error messages when using sqlite with the framework in a threaded WSGI server: SQLite objects created in a thread can only be used in that same thread... That lead me to this thread: http://groups.google.com/group/pylons-discuss/browse_thread/thread/3d3009cd7421c45a/ed0f3dde401ff474?lnk=gst Can someone weigh in on this issue? What are the performance ramifications of needing to create an engine on each request as opposed to each process? Do I also need to load my meta data on each request or could I just re-bind the engine to the metadata on each request? Should I not bind the engine to the metadata at all but just bind it to the session? 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
