Re: [sqlalchemy] server_default argument in Column(DateTime, server_default = ...)
func.now() will translate into the appropriate construct for a given backend, so server_default=func.now() will do it. On Oct 15, 2010, at 9:01 PM, Domingo Aguilera wrote: HI, I wonder if there's a way to use server_default in Column ( DateTime, server_default = ...) to make a database agnostic current timestamp as default . Cheers. -- 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] String formatting issues with connection.execute(sql_str)
On Oct 15, 2010, at 10:20 AM, William Furnass wrote: Trying to follow the SQLA tutorial at http://mapfish.org/doc/tutorials/sqlalchemy.html but find that I get stuck at the first hurdle (the Engine API bit) due to a string formatting problem: sqlalchemy.exc.ProgrammingError: (ProgrammingError) syntax error at or near , LINE 1: INSERT INTO users(username, password) VALUES ( ?, ? ) ^ 'INSERT INTO users(username, password) VALUES ( ?, ? )' (u'bob', u'bobpwd') Getting rid of the question marks and doing string interpolation in the typical way works just fine: connection.execute(INSERT INTO users(username, password) VALUES ( '%s', '%s' )' % (u'bob', u'bobpwd')) but I'd rather make use of SQLAs interpolation mechanism so as to guard against SQL injection. Any ideas as to where I could be going wrong? SQLA 0.6.3 Python 2.5 Connection to a UTF8-encoded Postgres 8.4.4 database. that tutorial is hardwiring a particular bindparam style into a statement, known as qmark, or question marks for binds. It is not supported by Psycopg2. The psycopg2 format would be : connection.execute( INSERT INTO users (username, password) VALUES (%(uname)s, %(passwd)s); , uname=foo, passwd=bar ) if you want to use a straight string and not worry about DBAPIs strange decision to allow any random subset of six types of bindparam formats, use text(): connection.execute( text( INSERT INTO users (username, password) VALUES (:uname, :passwd); ), uname=foo, passwd=bar ) as soon as you get into normal expression language usage with table metadata you don't really deal with these distinctions anymore. -- 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] orm object, before after
On 8/24/2010 9:47 PM, Michael Bayer wrote: Michael Hipp wrote: How do I make a copy of an orm object such that modifications to the copy do not affect the original? The mapped object has a member _sa_instance_state that you basically don't want to transfer to your new object.You want it to have its own _sa_instance_state and this comes from calling the plain constructor, which the copy module, if that's what you're using, does not use. You also want to set attributes normally, not populating __dict__ directly. So just basically don't use the copy module. x = MyObject() for a in dir(myoldobject): if not a.startswith('_'): setattr(x, a, getattr(myoldobject, a)) Resurrecting an old thread ... I'm just now getting around to try this but I'm finding out it doesn't really work like I'd hoped. As soon as 'getattr' hits a column with a ForeignKey it immediately tries to autoflush INSERT 'x'. But 'x' is only half-baked and not ready to be saved. In fact, I don't ever want to save 'x', and I especially don't want to INSERT it. It would be a duplicate of 'myoldobject'. Is there a way to copy an orm object and tell it don't ever save this I just want to keep it around to look at? Alternatively I can just copy all the attributes to a dict(), but that's a bit messy. Thanks for all your help, Michael -- 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] orm object, before after
On Oct 16, 2010, at 1:02 PM, Michael Hipp wrote: On 8/24/2010 9:47 PM, Michael Bayer wrote: Michael Hipp wrote: How do I make a copy of an orm object such that modifications to the copy do not affect the original? The mapped object has a member _sa_instance_state that you basically don't want to transfer to your new object.You want it to have its own _sa_instance_state and this comes from calling the plain constructor, which the copy module, if that's what you're using, does not use. You also want to set attributes normally, not populating __dict__ directly. So just basically don't use the copy module. x = MyObject() for a in dir(myoldobject): if not a.startswith('_'): setattr(x, a, getattr(myoldobject, a)) Resurrecting an old thread ... I'm just now getting around to try this but I'm finding out it doesn't really work like I'd hoped. As soon as 'getattr' hits a column with a ForeignKey it immediately tries to autoflush INSERT 'x'. But 'x' is only half-baked and not ready to be saved. In fact, I don't ever want to save 'x', and I especially don't want to INSERT it. It would be a duplicate of 'myoldobject'. Is there a way to copy an orm object and tell it don't ever save this I just want to keep it around to look at? Alternatively I can just copy all the attributes to a dict(), but that's a bit messy. dont put it in the Session. Thanks for all your help, Michael -- 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] orm object, before after
On 10/16/2010 12:52 PM, Michael Bayer wrote: On Oct 16, 2010, at 1:02 PM, Michael Hipp wrote: On 8/24/2010 9:47 PM, Michael Bayer wrote: Michael Hipp wrote: How do I make a copy of an orm object such that modifications to the copy do not affect the original? The mapped object has a member _sa_instance_state that you basically don't want to transfer to your new object.You want it to have its own _sa_instance_state and this comes from calling the plain constructor, which the copy module, if that's what you're using, does not use. You also want to set attributes normally, not populating __dict__ directly. So just basically don't use the copy module. x = MyObject() for a in dir(myoldobject): if not a.startswith('_'): setattr(x, a, getattr(myoldobject, a)) Resurrecting an old thread ... I'm just now getting around to try this but I'm finding out it doesn't really work like I'd hoped. As soon as 'getattr' hits a column with a ForeignKey it immediately tries to autoflush INSERT 'x'. But 'x' is only half-baked and not ready to be saved. In fact, I don't ever want to save 'x', and I especially don't want to INSERT it. It would be a duplicate of 'myoldobject'. Is there a way to copy an orm object and tell it don't ever save this I just want to keep it around to look at? Alternatively I can just copy all the attributes to a dict(), but that's a bit messy. dont put it in the Session. That makes sense. But how do I not do that? As in your example code above I'm not adding it to a session, at least not intentionally. Thanks, Michael -- 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] orm object, before after
On Oct 16, 2010, at 2:03 PM, Michael Hipp wrote: On 10/16/2010 12:52 PM, Michael Bayer wrote: On Oct 16, 2010, at 1:02 PM, Michael Hipp wrote: On 8/24/2010 9:47 PM, Michael Bayer wrote: Michael Hipp wrote: How do I make a copy of an orm object such that modifications to the copy do not affect the original? The mapped object has a member _sa_instance_state that you basically don't want to transfer to your new object.You want it to have its own _sa_instance_state and this comes from calling the plain constructor, which the copy module, if that's what you're using, does not use. You also want to set attributes normally, not populating __dict__ directly. So just basically don't use the copy module. x = MyObject() for a in dir(myoldobject): if not a.startswith('_'): setattr(x, a, getattr(myoldobject, a)) Resurrecting an old thread ... I'm just now getting around to try this but I'm finding out it doesn't really work like I'd hoped. As soon as 'getattr' hits a column with a ForeignKey it immediately tries to autoflush INSERT 'x'. But 'x' is only half-baked and not ready to be saved. In fact, I don't ever want to save 'x', and I especially don't want to INSERT it. It would be a duplicate of 'myoldobject'. Is there a way to copy an orm object and tell it don't ever save this I just want to keep it around to look at? Alternatively I can just copy all the attributes to a dict(), but that's a bit messy. dont put it in the Session. That makes sense. But how do I not do that? As in your example code above I'm not adding it to a session, at least not intentionally. don't call Session.add(). the code above doesn't add to a session unless you're using some special extensions like Session.mapper. Thanks, Michael -- 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] orm object, before after
On 10/16/2010 1:55 PM, Michael Bayer wrote: On Oct 16, 2010, at 2:03 PM, Michael Hipp wrote: On 10/16/2010 12:52 PM, Michael Bayer wrote: On Oct 16, 2010, at 1:02 PM, Michael Hipp wrote: On 8/24/2010 9:47 PM, Michael Bayer wrote: Michael Hipp wrote: How do I make a copy of an orm object such that modifications to the copy do not affect the original? The mapped object has a member _sa_instance_state that you basically don't want to transfer to your new object.You want it to have its own _sa_instance_state and this comes from calling the plain constructor, which the copy module, if that's what you're using, does not use. You also want to set attributes normally, not populating __dict__ directly. So just basically don't use the copy module. x = MyObject() for a in dir(myoldobject): if not a.startswith('_'): setattr(x, a, getattr(myoldobject, a)) Resurrecting an old thread ... I'm just now getting around to try this but I'm finding out it doesn't really work like I'd hoped. As soon as 'getattr' hits a column with a ForeignKey it immediately tries to autoflush INSERT 'x'. But 'x' is only half-baked and not ready to be saved. In fact, I don't ever want to save 'x', and I especially don't want to INSERT it. It would be a duplicate of 'myoldobject'. Is there a way to copy an orm object and tell it don't ever save this I just want to keep it around to look at? Alternatively I can just copy all the attributes to a dict(), but that's a bit messy. dont put it in the Session. That makes sense. But how do I not do that? As in your example code above I'm not adding it to a session, at least not intentionally. don't call Session.add(). the code above doesn't add to a session unless you're using some special extensions like Session.mapper. As shown exactly in the code above, I do *not* call Session.add(). It appears to be doing the add when it hits the ForeignKey field. Then it not only does an add it also attempts an INSERT (which thankfully fails since the object is not ready to be saved). Michael -- 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] Query help
Table structure: A(id, name) B(id, name, A_id) C(id, name, B_id) one-to-many A-B and B-C I'm trying to use session.query() to select all rows of A such that none of the joined B rows have any joined C rows. A: (1,'A1') (2,'A2') B: (1,'B1',1) (2,'B2',1) (3,'B3',2) C: (1,'C1',1) So, it would return (2,'A2'). Any help would be appreciated. -- 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] Query help
On 10/16/2010 03:31 PM, rake wrote: Table structure: A(id, name) B(id, name, A_id) C(id, name, B_id) one-to-many A-B and B-C I'm trying to use session.query() to select all rows of A such that none of the joined B rows have any joined C rows. A: (1,'A1') (2,'A2') B: (1,'B1',1) (2,'B2',1) (3,'B3',2) C: (1,'C1',1) So, it would return (2,'A2'). Any help would be appreciated. Assuming you have relations A.bs and B.cs: q = session.query(A) q = q.filter(~A.bs.any(B.cs.any()) which would translate roughly into this SQL: SELECT A columns FROM A WHERE NOT EXISTS ( SELECT 1 FROM B WHERE B.a_id = A.id AND EXISTS ( SELECT 1 FROM C WHERE C.b_id = B.id)) -Conor -- 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] orm object, before after
On Oct 16, 2010, at 4:02 PM, Michael Hipp wrote: On 10/16/2010 1:55 PM, Michael Bayer wrote: On Oct 16, 2010, at 2:03 PM, Michael Hipp wrote: On 10/16/2010 12:52 PM, Michael Bayer wrote: On Oct 16, 2010, at 1:02 PM, Michael Hipp wrote: On 8/24/2010 9:47 PM, Michael Bayer wrote: Michael Hipp wrote: How do I make a copy of an orm object such that modifications to the copy do not affect the original? The mapped object has a member _sa_instance_state that you basically don't want to transfer to your new object.You want it to have its own _sa_instance_state and this comes from calling the plain constructor, which the copy module, if that's what you're using, does not use. You also want to set attributes normally, not populating __dict__ directly. So just basically don't use the copy module. x = MyObject() for a in dir(myoldobject): if not a.startswith('_'): setattr(x, a, getattr(myoldobject, a)) Resurrecting an old thread ... I'm just now getting around to try this but I'm finding out it doesn't really work like I'd hoped. As soon as 'getattr' hits a column with a ForeignKey it immediately tries to autoflush INSERT 'x'. But 'x' is only half-baked and not ready to be saved. In fact, I don't ever want to save 'x', and I especially don't want to INSERT it. It would be a duplicate of 'myoldobject'. Is there a way to copy an orm object and tell it don't ever save this I just want to keep it around to look at? Alternatively I can just copy all the attributes to a dict(), but that's a bit messy. dont put it in the Session. That makes sense. But how do I not do that? As in your example code above I'm not adding it to a session, at least not intentionally. don't call Session.add(). the code above doesn't add to a session unless you're using some special extensions like Session.mapper. As shown exactly in the code above, I do *not* call Session.add(). It appears to be doing the add when it hits the ForeignKey field. Then it not only does an add it also attempts an INSERT (which thankfully fails since the object is not ready to be saved). you mean a relationship(). Yeah, if you do this: x = MyObject() foo_bar = Session.query(FooBar).first() x.some_related = foo_bar where some_related has a backref to collection_of_myobject, the cascade will put it in to that session which FooBar belongs. This behavior is configurable via the cascade argument on relationship(), and even more so using the new cascade_backrefs flag available in 0.6.5.But also you need to decide what you are really trying to do here, if you even want FooBar populated on your non-persisted object. -- 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.