Re: [sqlalchemy] How can I tell SA that a mutable attribute has changed?
Hi, Chris. Have you checked http://docs.sqlalchemy.org/en/latest/orm/extensions/mutable.html ? Regards, Tate -Original Message- From: Chris Withers ch...@simplistix.co.uk Sender: sqlalchemy@googlegroups.com Date: Fri, 24 Feb 2012 10:49:46 To: sqlalchemy@googlegroups.com Reply-To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] How can I tell SA that a mutable attribute has changed? Hi All, I have a JSON field that's value is usually a dictionary: class MyModel(Base): config = Column(JSONType(255)) (Does SA have a JSONType now? we're using a homebrew one, that may be where the problem lies...) So, if I do: obj = session.query(MyModel).one() obj.config['foo'] += 1 session.commit() ...on my dev machine, the change gets persisted. Of course, on production machines, it does not :-( The change I seem to need is: config = dict(obj.config) config['foo'] += 1 obj.config = config Is there a better way? cheers, Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk -- 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] on delete cascade
Hi, jo. At first, try with cascade option 'all, delete' (note that dropped 'delete-orphan'). Second, I've ever been your situation before, maybe -Original Message- From: jo jose.soa...@sferacarta.com Sender: sqlalchemy@googlegroups.com Date: Fri, 24 Feb 2012 11:41:48 To: sasqlalchemy@googlegroups.com Reply-To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] on delete cascade Hi all, I'm trying to delete cascade a linked row without success. Could anyone give me some help? This is my mapper: mapper(Azienda, tbl['azienda'], properties = { 'anagrafica': relation(Anagrafica, cascade='all, delete, delete-orphan'), }) the table Azienda has a foreign key (not null) linked to table Anagrafica. When I try to delete a row from Azienda I would like remove also the linked row in table Anagrafica... and I supposed it was done by the instruction on properties: 'anagrafica': relation(Anagrafica, cascade='all, delete, delete-orphan')... but it doesn't work... record = Azienda.get( 867 ) session.delete(record) IntegrityError: ('(IntegrityError) update or delete on table anagrafica violates foreign key constraint azienda_id_anagrafica_fkey on table azienda DETAIL: Key (id)=(313836) is still referenced from table azienda. , bound method Controller.save of sicer.BASE.controller.anagraficaAlta.azienda.Controller object at 0x8fde590) 'DELETE FROM anagrafica WHERE anagrafica.id = %(id)s' {'id': 313836} Thanks for any help j -- 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] on delete cascade
an certain anagrafica has another parent which conflicts delete-orphan. The anagrafica's relationship may has done by just setting anagrafica.azienda_pk (not by azienda.anagrafica_set.append ) (Sorry about that separated mail...my mistake.) Regards, Tate -Original Message- From: Tate Kim insight...@gmail.com Date: Fri, 24 Feb 2012 17:56:44 To: sqlalchemy@googlegroups.com Reply-To: insight...@gmail.com Subject: Re: [sqlalchemy] on delete cascade Hi, jo. At first, try with cascade option 'all, delete' (note that dropped 'delete-orphan'). Second, I've ever been your situation before, maybe -Original Message- From: jo jose.soa...@sferacarta.com Sender: sqlalchemy@googlegroups.com Date: Fri, 24 Feb 2012 11:41:48 To: sasqlalchemy@googlegroups.com Reply-To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] on delete cascade Hi all, I'm trying to delete cascade a linked row without success. Could anyone give me some help? This is my mapper: mapper(Azienda, tbl['azienda'], properties = { 'anagrafica': relation(Anagrafica, cascade='all, delete, delete-orphan'), }) the table Azienda has a foreign key (not null) linked to table Anagrafica. When I try to delete a row from Azienda I would like remove also the linked row in table Anagrafica... and I supposed it was done by the instruction on properties: 'anagrafica': relation(Anagrafica, cascade='all, delete, delete-orphan')... but it doesn't work... record = Azienda.get( 867 ) session.delete(record) IntegrityError: ('(IntegrityError) update or delete on table anagrafica violates foreign key constraint azienda_id_anagrafica_fkey on table azienda DETAIL: Key (id)=(313836) is still referenced from table azienda. , bound method Controller.save of sicer.BASE.controller.anagraficaAlta.azienda.Controller object at 0x8fde590) 'DELETE FROM anagrafica WHERE anagrafica.id = %(id)s' {'id': 313836} Thanks for any help j -- 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: Bug in old-style mutable type? (was Re: [sqlalchemy] How can I tell SA that a mutable attribute has changed?)
Opps I didn't know that subject has been changed. FYI, in 0.6.x, I used to play with class JSONType(MutableType, TypeDecorator) pretty well. But 0.7.x, I'm not sure it works as well as 0.6.x... Regards, Tate -Original Message- From: Chris Withers ch...@simplistix.co.uk Sender: sqlalchemy@googlegroups.com Date: Fri, 24 Feb 2012 11:39:39 To: sqlalchemy@googlegroups.com Reply-To: sqlalchemy@googlegroups.com Subject: Bug in old-style mutable type? (was Re: [sqlalchemy] How can I tell SA that a mutable attribute has changed?) On 24/02/2012 10:49, Chris Withers wrote: (Does SA have a JSONType now? we're using a homebrew one, that may be where the problem lies...) For reference, I've included the code for this type at the end of this email, it uses MutableType, so the following should work: obj = session.query(MyModel).one() obj.config['foo'] += 1 session.commit() ...on my dev machine, the change gets persisted. Of course, on production machines, it does not :-( Are there known bugs in this? I see there's now a new way in 0.7, but that means you have to know the type of the object stored in the column rather than just any json-serializable value being okay. cheers, Chris PS: from sqlalchemy import String from sqlalchemy.types import MutableType, TypeDecorator import simplejson class JSONType(MutableType, TypeDecorator): Column type which stores Python objects in JSON form. PickleType applies simplejson's ``dumps()`` to incoming objects, and ``loads()`` on the way out, allowing simple Python objects to be stored as a serialized text field. As it extends MutableType, changes in mutable objects will be detected and handled correctly. It is safe to use this column type to store lists, dicts and other mutable types. impl = String def process_bind_param(self, value, _): if value is None: return None return self._dumps(value) def process_result_value(self, value, _): if value is None: return None return self._loads(value) def copy_value(self, value): return self._loads(self._dumps(value)) def is_mutable(self): return True def _loads(self, strvalue): return simplejson.loads(str(strvalue), use_decimal=True) def _dumps(self, value): return simplejson.dumps(value, use_decimal=True) -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk -- 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] Overload Query Object
Hi, Have you checked the __iter__ method ? -Original Message- From: Christian Démolis christiandemo...@gmail.com Sender: sqlalchemy@googlegroups.com Date: Tue, 31 Jan 2012 17:39:54 To: sqlalchemysqlalchemy@googlegroups.com Reply-To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] Overload Query Object Hi Michael, i overload class Query in my script. i have 4 ways to obtain query's results. 1/ session.query(model.x).all() 2/ session.query(model.x).first() 3/ session.query(model.x).one() 4/ for e in session.query(model.x): print e in case 1,2,3, i know which method is used What method is used in case 4 ? Thanks in advance. Chris class Query(Query): def __init__(self, *arg, **kw): self._populate_existing = True super(Query, self).__init__(*arg, **kw) def all(self): print all, threading.current_thread() param.lock_bdd.acquire() global session try: x = super(Query, self).all() except exc2.OperationalError: import common common.alerte(L'écriture a échoué. Retentez l'action, Erreur MySQL) session.rollback() except exc2.StatementError: import common common.alerte(L'écriture a échoué. Retentez l'action, Erreur MySQL) session.rollback() except: raise param.lock_bdd.release() print /all, threading.current_thread() return x def one(self): print one, threading.current_thread() param.lock_bdd.acquire() global session try: x = super(Query, self).one() except exc2.OperationalError: import common common.alerte(L'écriture a échoué. Retentez l'action, Erreur MySQL) session.rollback() except exc2.StatementError: import common common.alerte(L'écriture a échoué. Retentez l'action, Erreur MySQL) session.rollback() except: raise param.lock_bdd.release() print /one, threading.current_thread() return x def first(self): print first, threading.current_thread() param.lock_bdd.acquire() global session try: x = super(Query, self).first() except exc2.OperationalError: import common common.alerte(L'écriture a échoué. Retentez l'action, Erreur MySQL) session.rollback() except exc2.StatementError: import common common.alerte(L'écriture a échoué. Retentez l'action, Erreur MySQL) session.rollback() except: raise param.lock_bdd.release() print /first, threading.current_thread() return x -- 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] Passing additional arguments to event listeners ?
How about functools.partial ? As far as I know, functools.partial will simply do this. Usually, I import this when I need to make an argument-less function equip extra arguments. Best regards, Tate -Original Message- From: Michael Bayer mike...@zzzcomputing.com Sender: sqlalchemy@googlegroups.com Date: Thu, 1 Dec 2011 19:52:15 To: sqlalchemy@googlegroups.com Reply-To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Passing additional arguments to event listeners ? On Dec 1, 2011, at 7:24 PM, Łukasz Czuja wrote: Hi, I do not see anywhere in the docs a way to pass custom attributes to event listeners: event.listen(cls, 'before_insert', before_insert_listener, arg1, arg2, kwarg1 = 'value', kwarg2 = 'value2') so that the before_insert_listener can have mixed signature: def before_insert_listener(mapper, connection, target, arg1, *args, **kwargs): the only other solution would be to store extra processing information on the 'target' itself. Should be reasonable if there is not another way to pass around arguments. Should I open a ticket then? this kind of pollutes the API with kwargs that might be needed for the listen() function itself someday, these are external use cases that are easily handled in Python: def before_insert_listener(arg1, arg2, k1='value', k2='value'): def before_insert(mapper, conn, target): ... body return before_insert event.listen(cls, 'before_insert', before_insert_listener(arg1, arg2, k1='x', k2='y')) -- 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] Passing additional arguments to event listeners ?
Yes, I think also lambda can be good one. Though lambda reduces an effort to type a predefined function, functools.partial is a bit familiar to me. Best regards, Tate -Original Message- From: Jackson, Cameron cameron.jack...@thalesgroup.com.au Sender: sqlalchemy@googlegroups.com Date: Fri, 2 Dec 2011 12:44:26 To: sqlalchemy@googlegroups.comsqlalchemy@googlegroups.com Reply-To: sqlalchemy@googlegroups.com Subject: RE: [sqlalchemy] Passing additional arguments to event listeners ? Not sure if this helps or not, but how about using a lambda that that calls your function with the arguments you want? This is the solution I've been using for passing arguments to wxPython event handlers. This tutorial might help: http://wiki.wxpython.org/Passing%20Arguments%20to%20Callbacks -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of Tate Kim Sent: Friday, 2 December 2011 12:33 PM To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Passing additional arguments to event listeners ? How about functools.partial ? As far as I know, functools.partial will simply do this. Usually, I import this when I need to make an argument-less function equip extra arguments. Best regards, Tate -Original Message- From: Michael Bayer mike...@zzzcomputing.com Sender: sqlalchemy@googlegroups.com Date: Thu, 1 Dec 2011 19:52:15 To: sqlalchemy@googlegroups.com Reply-To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Passing additional arguments to event listeners ? On Dec 1, 2011, at 7:24 PM, Łukasz Czuja wrote: Hi, I do not see anywhere in the docs a way to pass custom attributes to event listeners: event.listen(cls, 'before_insert', before_insert_listener, arg1, arg2, kwarg1 = 'value', kwarg2 = 'value2') so that the before_insert_listener can have mixed signature: def before_insert_listener(mapper, connection, target, arg1, *args, **kwargs): the only other solution would be to store extra processing information on the 'target' itself. Should be reasonable if there is not another way to pass around arguments. Should I open a ticket then? this kind of pollutes the API with kwargs that might be needed for the listen() function itself someday, these are external use cases that are easily handled in Python: def before_insert_listener(arg1, arg2, k1='value', k2='value'): def before_insert(mapper, conn, target): ... body return before_insert event.listen(cls, 'before_insert', before_insert_listener(arg1, arg2, k1='x', k2='y')) -- 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. - DISCLAIMER: This e-mail transmission and any documents, files and previous e-mail messages attached to it are private and confidential. They may contain proprietary or copyright material or information that is subject to legal professional privilege. They are for the use of the intended recipient only. Any unauthorised viewing, use, disclosure, copying, alteration, storage or distribution of, or reliance on, this message is strictly prohibited. No part may be reproduced, adapted or transmitted without the written permission of the owner. If you have received this transmission in error, or are not an authorised recipient, please immediately notify the sender by return email, delete this message and all copies from your e-mail system, and destroy any printed copies. Receipt by anyone other than the intended recipient should not be deemed a waiver of any privilege or protection. Thales Australia does not warrant or represent that this e-mail or any documents, files and previous e-mail messages attached are error or virus free. - -- 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
Re: [sqlalchemy] implementation of periodic task or time triggered update
Thank you for the answer. I think I need to find another way. -Original Message- From: Michael Bayer mike...@zzzcomputing.com Sender: sqlalchemy@googlegroups.com Date: Tue, 29 Nov 2011 11:40:20 To: sqlalchemy@googlegroups.com Reply-To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] implementation of periodic task or time triggered update On Nov 29, 2011, at 3:51 AM, Tate Kim wrote: Hello everyone, I think this question may be for the database side, but i 'd like to post here if you don't mind. I have a certain periodic task that bult updates a couple of columns has expired in License table. ( It doesn't matter what it is. ) In this situation, time accuracy was really important to me so i was running the task per one minute. The code is like this. # call in every one minute with something like MQ session.query( License ).filter( License.expired_at func.now() ).update({ License.type: 'basic' }) But, if I want to modify this period down to 1 sec, as you know performance is gonna be really bad. I don't think you mean performance you mean accuracy. It depends on what clock you're synchronizing here on - if you can emit the statements every second from the app side, you could possibly use timestamps from the app end. If the app and database clocks aren't perfectly synchronized, maybe the app could just subtract app time - DB time and add that in as a fudge factor. But yeah I haven't ever had to get sub-second accuracy on something like this so I don't know if that really works. If License.expired_at were not datetime field, i would try with sqlalchemy's event api at the orm attribute's changing point... I'm not really understanding how the datatype of expired_at has any bearing on a particular Python calling style. -- 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.
[sqlalchemy] implementation of periodic task or time triggered update
Hello everyone, I think this question may be for the database side, but i 'd like to post here if you don't mind. I have a certain periodic task that bult updates a couple of columns has expired in License table. ( It doesn't matter what it is. ) In this situation, time accuracy was really important to me so i was running the task per one minute. The code is like this. # call in every one minute with something like MQ session.query( License ).filter( License.expired_at func.now() ).update({ License.type: 'basic' }) But, if I want to modify this period down to 1 sec, as you know performance is gonna be really bad. If License.expired_at were not datetime field, i would try with sqlalchemy's event api at the orm attribute's changing point... I think the problem is nothing is aware of the time goes by and even Postgresql has not a time triggered feature. Am I approached in wrong way ? I'm searching for the best practice of this situation. Any thoughts will be great help, thank you. Regards, Tate -- 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] how can i detect fields that cause an exception ?
hi, I'm using sqlalchemy 0.7.3 and postgresql 9 with psycopg2 and python 2.7. When I put wrong type of data(ex: 'some string' into Integer field), I get DataError exception in commit phase. This is so expected result, but i think it would be great to get more specific data which can tell model's field name that causes an exception. I looked up DataError's field such as 'orig', 'params', 'statement', but i could not figure it out with this. How can i achieve this ? Regards, Tate -- 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] how can i detect fields that cause an exception ?
I've got an idea from your answer. Thank you. -Original Message- From: Michael Bayer mike...@zzzcomputing.com Sender: sqlalchemy@googlegroups.com Date: Tue, 1 Nov 2011 11:15:03 To: sqlalchemy@googlegroups.com Reply-To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] how can i detect fields that cause an exception ? DataError is a psycopg2 exception. psycopg2 in turn is doing what PG's client API provides for them. So if you wanted those messages to name the column that's the target of an INSERT or UPDATE you'd have to look into improving psycopg2 and/or how Postgresql's client system works. Normally, for debugging, you'd examine the SQL statement in the error as well as the bound parameters.It's usually pretty obvious which field is wrong. To catch the exception when the field name is known, can be done in the ORM on a field by field basis with @validates, http://www.sqlalchemy.org/docs/orm/mapper_config.html#simple-validators , or more across the board using attribute-level event listeners. But you'd have to design the data validation scheme manually, it's a lot of effort. A TypeDecorator can also be used to validate data but that exception would also occur in a context where the column name is not present - as types can be used in any arbitrary expression. On Nov 1, 2011, at 2:42 AM, Tate Kim wrote: hi, I'm using sqlalchemy 0.7.3 and postgresql 9 with psycopg2 and python 2.7. When I put wrong type of data(ex: 'some string' into Integer field), I get DataError exception in commit phase. This is so expected result, but i think it would be great to get more specific data which can tell model's field name that causes an exception. I looked up DataError's field such as 'orig', 'params', 'statement', but i could not figure it out with this. How can i achieve this ? Regards, Tate -- 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.