[sqlalchemy] Re: session.query print sql
Thanks alot,,that was what I needed, Jon --~--~-~--~~~---~--~~ 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] Suggestions for abbreviations
I wonder why declarative_base() doesn't simply set __tablename__ to the name of the class by default (maybe translating camelcase to lowercase with underscores), similar to how it is done in SQLObject and Elixir. Also, the error message if you forget to set __tablename__ is misleading, it should mention __tablename__ in declarative usage. Another idea (not sure if it really makes sense): In the order_by clause, negative integers could be used for descending order, i.e. -2 would work like desc(2). --~--~-~--~~~---~--~~ 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: Suggestions for abbreviations
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11.01.2009 12:13 Uhr, Cito wrote: I wonder why declarative_base() doesn't simply set __tablename__ to the name of the class by default (maybe translating camelcase to lowercase with underscores). Please no implicit magic under the hood. You can have multiple mappers for the same table. Citing Guido: explicit is better than implicit. My-2-cent, Andreas -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (Darwin) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAklp16AACgkQCJIWIbr9KYzroQCgxVi70Tb7Bf+qMd5uRcrfbNtf +sEAnjRkrmhGjyt18Igv5f3RacGjDkzD =ibCE -END PGP SIGNATURE- --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~--- begin:vcard fn:Andreas Jung n:Jung;Andreas org:ZOPYX Ltd. Co. KG adr;quoted-printable:;;Charlottenstr. 37/1;T=C3=BCbingen;;72070;Germany email;internet:i...@zopyx.com title:CEO tel;work:+49-7071-793376 tel;fax:+49-7071-7936840 tel;home:+49-7071-793257 x-mozilla-html:FALSE url:www.zopyx.com version:2.1 end:vcard
[sqlalchemy] Re: Suggestions for abbreviations
Andreas Jung schrieb: Please no implicit magic under the hood. You can have multiple mappers for the same table. Citing Guido: explicit is better than implicit. You wouldn't be forced to use that magic, you could still set __tablename__ explictly. I understand your argument, but otoh beautiful is better than ugly outplays explicit is better than implicit ;-) -- Christoph --~--~-~--~~~---~--~~ 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: Suggestions for abbreviations
On Jan 11, 2009, at 6:13 AM, Cito wrote: I wonder why declarative_base() doesn't simply set __tablename__ to the name of the class by default (maybe translating camelcase to lowercase with underscores), similar to how it is done in SQLObject and Elixir. Also, the error message if you forget to set __tablename__ is misleading, it should mention __tablename__ in declarative usage. __tablename__ is optional in the sense that you could alternatively specify __table__, or if you inherit then the table is optional entirely since you're using single table inheritance. I have heard the error message is misleading so we should fix that. As far as implicit tablename, it breaks the single inheritance scenario. but also besides that I made a comment on that here: http://www.sqlalchemy.org/trac/ticket/1270#comment:2 Another idea (not sure if it really makes sense): In the order_by clause, negative integers could be used for descending order, i.e. -2 would work like desc(2). im not actually familiar with that technique ? does that mean ORDER BY 2 DESC or ORDER BY somecolumn DESC(2) ? --~--~-~--~~~---~--~~ 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] Does limit() work with update()?
Hi, I have 2 records in the database. I made an expression to update only 1 record, but all are getting updated. works=session.query(Work).filter(tnow-Work.takentimedelta(minutes=15)) .filter(Work.completed==None).limit(1).with_lockmode(mode='update').update(values) Shouldn't the above query only perform the update on the limited results of the query? Darren --~--~-~--~~~---~--~~ 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: Does limit() work with update()?
On Jan 11, 2009, at 10:44 AM, Darren Govoni wrote: Hi, I have 2 records in the database. I made an expression to update only 1 record, but all are getting updated. works=session.query(Work).filter(tnow- Work.takentimedelta(minutes=15)) .filter (Work .completed==None).limit(1).with_lockmode(mode='update').update(values) Shouldn't the above query only perform the update on the limited results of the query? query.update() just issues an UPDATE statement. UPDATE doesn't support any kind of LIMITing keywords (maybe MySQL does, but thats not SQL). The Query should really be raising an error here without emitting any SQL. --~--~-~--~~~---~--~~ 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: Suggestions for abbreviations
Michael Bayer schrieb: As far as implicit tablename, it breaks the single inheritance scenario. but also besides that I made a comment on that here: http://www.sqlalchemy.org/trac/ticket/1270#comment:2 Thanks, I hadn't seen that. Not quite sure what you mean with single inheritance scenario, though. Another idea (not sure if it really makes sense): In the order_by clause, negative integers could be used for descending order, i.e. -2 would work like desc(2). im not actually familiar with that technique ? does that mean ORDER BY 2 DESC or ORDER BY somecolumn DESC(2) ? Very simple, the former: session.query(Foo).order_by(-2) should work the same as from sqlalchemy import desc session.query(Foo).order_by(desc(2)) The idea is that it's somewhat shorter and intuitive since for names of numerical columns, you also get reverse order if you add a minus sign. -- Christoph --~--~-~--~~~---~--~~ 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: Suggestions for abbreviations
On Jan 11, 2009, at 11:06 AM, Christoph Zwerschke wrote: Michael Bayer schrieb: As far as implicit tablename, it breaks the single inheritance scenario. but also besides that I made a comment on that here: http://www.sqlalchemy.org/trac/ticket/1270#comment:2 Thanks, I hadn't seen that. Not quite sure what you mean with single inheritance scenario, though. if Bar inherits from Foo, Foo is mapped to foo_table, Bar has no table, Bar will be mapped to foo_table as well. Very simple, the former: session.query(Foo).order_by(-2) should work the same as from sqlalchemy import desc session.query(Foo).order_by(desc(2)) The idea is that it's somewhat shorter and intuitive since for names of numerical columns, you also get reverse order if you add a minus sign. oh, this is entirely news to me that you can send column positions to ORDER BY. SQLA has no awareness of that concept right now. seems like more than one way to do it at the moment...(i.e. order by columns *or* position, times use desc or negation == 4 ways). --~--~-~--~~~---~--~~ 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: Does limit() work with update()?
Thank you, So I changed my query to a select/for update. then re-added the updated rows in the transaction, then committed. works=session.query(Work).filter(tnow-Work.takentimedelta(minutes=60)). filter(Work.completed==None).limit(1).with_lockmode(mode='update').all() When I run two instances of the program, the second one will block on the query while the first is inside the transaction ('update'). BUT. the second one should return 1 row when it unblocks because the first instance only modified 1 row, leaving the other to satisfy the blockers query. It doesn't return anything when the transaction is released to the second instance. Peculiar. I re-run the second instance after that and it then is able to find the qualifying row. Is that correct behavior? Both program instances are the same code. On Sun, 2009-01-11 at 11:04 -0500, Michael Bayer wrote: On Jan 11, 2009, at 10:44 AM, Darren Govoni wrote: Hi, I have 2 records in the database. I made an expression to update only 1 record, but all are getting updated. works=session.query(Work).filter(tnow- Work.takentimedelta(minutes=15)) .filter (Work .completed==None).limit(1).with_lockmode(mode='update').update(values) Shouldn't the above query only perform the update on the limited results of the query? query.update() just issues an UPDATE statement. UPDATE doesn't support any kind of LIMITing keywords (maybe MySQL does, but thats not SQL). The Query should really be raising an error here without emitting any SQL. --~--~-~--~~~---~--~~ 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: Does limit() work with update()?
On Jan 11, 2009, at 11:18 AM, Darren Govoni wrote: Thank you, So I changed my query to a select/for update. then re-added the updated rows in the transaction, then committed. works=session.query(Work).filter(tnow- Work.takentimedelta(minutes=60)). filter (Work.completed==None).limit(1).with_lockmode(mode='update').all() When I run two instances of the program, the second one will block on the query while the first is inside the transaction ('update'). BUT. the second one should return 1 row when it unblocks because the first instance only modified 1 row, leaving the other to satisfy the blockers query. It doesn't return anything when the transaction is released to the second instance. Peculiar. I re-run the second instance after that and it then is able to find the qualifying row. Is that correct behavior? Both program instances are the same code. what I'm not sure about here is if you are expecting the UPDATE to return the number of rows actually modified, which again is a MySQL only thing, or the number of rows actually matched. I'm also not sure if you are updating the rows in such a way that they won't match after they're updated. So I only have a hazy view of the actual operation. But from what I'm reading the behavior doesn't sound correct. Check the SQL log output of both applications which should illustrate the full conversation. --~--~-~--~~~---~--~~ 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: Does limit() work with update()?
Sorry for the haze. I'm using PostgreSQL and am checking their docs on isolation to see that it is consistent with SA. but here is a simplified example. I have 2 rows in the database with 1 column FOO. Both rows have a NULL value for column FOO. Two programs, A and B. They are the same program. I have a query that: selects for update, all rows with FOO=NULL, limit 1. This will block all other processes attempting the same query. A goes first and selects for update. B thus blocks. Both are looking for 1 row, FOO=NULL. A query returns 1 row. Inside the transaction. A updates that row to FOO=A. B is still waiting. Only 1 row is updated, because 1 is returned from the select. I use session.add(row) to simply re-add the mapped object after changing the value. A commits its change to 1 row. The second row is still FOO=NULL. B unblocks. B returns 0 results. A, B exit. Re-run B. B finds 1 row where FOO=NULL and sets FOO=B. B exits. Does that help clarify? thank you. On Sun, 2009-01-11 at 11:33 -0500, Michael Bayer wrote: On Jan 11, 2009, at 11:18 AM, Darren Govoni wrote: Thank you, So I changed my query to a select/for update. then re-added the updated rows in the transaction, then committed. works=session.query(Work).filter(tnow- Work.takentimedelta(minutes=60)). filter (Work.completed==None).limit(1).with_lockmode(mode='update').all() When I run two instances of the program, the second one will block on the query while the first is inside the transaction ('update'). BUT. the second one should return 1 row when it unblocks because the first instance only modified 1 row, leaving the other to satisfy the blockers query. It doesn't return anything when the transaction is released to the second instance. Peculiar. I re-run the second instance after that and it then is able to find the qualifying row. Is that correct behavior? Both program instances are the same code. what I'm not sure about here is if you are expecting the UPDATE to return the number of rows actually modified, which again is a MySQL only thing, or the number of rows actually matched. I'm also not sure if you are updating the rows in such a way that they won't match after they're updated. So I only have a hazy view of the actual operation. But from what I'm reading the behavior doesn't sound correct. Check the SQL log output of both applications which should illustrate the full conversation. --~--~-~--~~~---~--~~ 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: Suggestions for abbreviations
Michael Bayer schrieb: if Bar inherits from Foo, Foo is mapped to foo_table, Bar has no table, Bar will be mapped to foo_table as well. In that case, no implicit name should be set or course. It should only be set if a name cannot be figured out otherwise. oh, this is entirely news to me that you can send column positions to ORDER BY. SQLA has no awareness of that concept right now. seems like more than one way to do it at the moment...(i.e. order by columns *or* position, times use desc or negation == 4 ways). Using column positions is a standard SQL feature AFAIK (i.e. positive indices; using negative indices for reverse order was my idea only) and it also seems to works perfectly well with SQLAlchemy :) Since the only one way principle is broken here anyway, I thought adding another shortcut will not harm... -- Christoph --~--~-~--~~~---~--~~ 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: Does limit() work with update()?
On Jan 11, 2009, at 11:40 AM, Darren Govoni wrote: Sorry for the haze. I'm using PostgreSQL and am checking their docs on isolation to see that it is consistent with SA. but here is a simplified example. I have 2 rows in the database with 1 column FOO. Both rows have a NULL value for column FOO. Two programs, A and B. They are the same program. I have a query that: selects for update, all rows with FOO=NULL, limit 1. This will block all other processes attempting the same query. A goes first and selects for update. B thus blocks. Both are looking for 1 row, FOO=NULL. A query returns 1 row. Inside the transaction. A updates that row to FOO=A. B is still waiting. Only 1 row is updated, because 1 is returned from the select. I use session.add(row) to simply re-add the mapped object after changing the value. A commits its change to 1 row. The second row is still FOO=NULL. B unblocks. B returns 0 results. A, B exit. Re-run B. B finds 1 row where FOO=NULL and sets FOO=B. B exits. Does that help clarify? yeah I understood all that (except for the session.add(row) part, which doesn't seem related). And no, it does not sound correct, in that when B is unblocked into the transaction, it should see the same thing as if it had just been run completely after the A transaction were committed. in any case you should view the SQL logs (echo=True) to see what the conversation is saying. If that all looks as expected, open up two postgres consoles and try the same conversation manually. --~--~-~--~~~---~--~~ 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: Does limit() work with update()?
Hehe. I just did it what you suggested and it doesn't work there either. If I leave off the limit 1, it produces expected results. So at least SA is consistent with PG at the moment, even if PG is broken. Going to post on their list now, about this. Thanks. On Sun, 2009-01-11 at 11:51 -0500, Michael Bayer wrote: On Jan 11, 2009, at 11:40 AM, Darren Govoni wrote: Sorry for the haze. I'm using PostgreSQL and am checking their docs on isolation to see that it is consistent with SA. but here is a simplified example. I have 2 rows in the database with 1 column FOO. Both rows have a NULL value for column FOO. Two programs, A and B. They are the same program. I have a query that: selects for update, all rows with FOO=NULL, limit 1. This will block all other processes attempting the same query. A goes first and selects for update. B thus blocks. Both are looking for 1 row, FOO=NULL. A query returns 1 row. Inside the transaction. A updates that row to FOO=A. B is still waiting. Only 1 row is updated, because 1 is returned from the select. I use session.add(row) to simply re-add the mapped object after changing the value. A commits its change to 1 row. The second row is still FOO=NULL. B unblocks. B returns 0 results. A, B exit. Re-run B. B finds 1 row where FOO=NULL and sets FOO=B. B exits. Does that help clarify? yeah I understood all that (except for the session.add(row) part, which doesn't seem related). And no, it does not sound correct, in that when B is unblocked into the transaction, it should see the same thing as if it had just been run completely after the A transaction were committed. in any case you should view the SQL logs (echo=True) to see what the conversation is saying. If that all looks as expected, open up two postgres consoles and try the same conversation manually. --~--~-~--~~~---~--~~ 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: Suggestions for abbreviations
On Jan 11, 2009, at 11:41 AM, Christoph Zwerschke wrote: Michael Bayer schrieb: if Bar inherits from Foo, Foo is mapped to foo_table, Bar has no table, Bar will be mapped to foo_table as well. In that case, no implicit name should be set or course. It should only be set if a name cannot be figured out otherwise. a lot of people have complained about the message, and it is this: ArgumentError: Mapper 'Mapper|User|None' does not have a mapped_table specified. (Are you using the return value of table.create()? It no longer has a return value.) That's a really old error message, and I can see how its less than perfect so I've just changed it and added a declarative specific message which supercedes it, so the issue is resolved. But I don't see how the old message is *confusing*. It seems obvious that its saying no table is present to be mapped, what else could it possibly mean ? --~--~-~--~~~---~--~~ 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: Suggestions for abbreviations
Michael Bayer schrieb: ArgumentError: Mapper 'Mapper|User|None' does not have a mapped_table specified. (Are you using the return value of table.create()? It no longer has a return value.) That's a really old error message, and I can see how its less than perfect so I've just changed it and added a declarative specific message which supercedes it, so the issue is resolved. But I don't see how the old message is *confusing*. It seems obvious that its saying no table is present to be mapped, what else could it possibly mean ? Thanks. Well, the message was not really confusing, only misleading in that it says you need to specify mapped_table where you actually need to specify __table__ or __tablename___. -- Christoph --~--~-~--~~~---~--~~ 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: Self-Referential Mapping with Base?
Hi MikeCo, I tried your example, and got this error from SA 0.5. ValueError: need more than 0 values to unpack Darren On Fri, 2009-01-09 at 20:33 -0800, MikeCo wrote: from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.ext.declarative import declarative_base dbname = '' dburl = 'sqlite:///%s' % dbname # set up environment eng = create_engine(dburl, echo=False) meta = MetaData(bind=eng) Base = declarative_base(metadata=meta) Session = sessionmaker() # D self referential class D(Base): __tablename__ = 'D' id = Column(Integer, primary_key=True) id_d = Column(Integer, ForeignKey('D.id')) name = Column(Text) child = relation('D', backref=backref('parent', remote_side='D.id')) def __repr__(self): return '--D (id:%s-id_d:%s) %s --' % (self.id, self.id_d, self.name) def initdb(): print '# create the database' meta.drop_all(checkfirst=True) meta.create_all() sess = Session() # insert some data for self-reference relationship for ddata in ('d-one', 'd-two'): dobj = D(name=ddata) sess.add(dobj) dobj2 = D(name=ddata+'-child1') dobj.child.append(dobj2) dobj2 = D(name=ddata+'-child2') dobj.child.append(dobj2) dobj3 = D(name=ddata+'-child2'+'-child3') dobj2.child.append(dobj3) sess.commit() sess.close() def printobj(obj, indent=''): # recursive list print '%s%s' % (indent, obj) if hasattr(obj, 'child'): for ch in obj.child: printobj(ch, indent=indent+'') def listd(): # retrieve and list sess = Session() print '# D-D' query = sess.query(D).filter(D.id_d==None).order_by(D.name) for dobj in query: printobj(dobj) sess.close() def tst3(): # do stuff with self referential class D sess = Session() print #switch parentage of name 'd-two-child1' to 'd-one' print '# before' listd() child = sess.query(D).filter(D.name=='d-two-child1').one() parent = sess.query(D).filter(D.name=='d-one').one() eng.echo=True child.parent = parent child.name += ' MOVED' sess.commit() eng.echo=False print '# after' listd() initdb() tst3() --~--~-~--~~~---~--~~ 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: Self-Referential Mapping with Base?
0.5.0 final ? On Jan 11, 2009, at 12:21 PM, Darren Govoni wrote: Hi MikeCo, I tried your example, and got this error from SA 0.5. ValueError: need more than 0 values to unpack Darren On Fri, 2009-01-09 at 20:33 -0800, MikeCo wrote: from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.ext.declarative import declarative_base dbname = '' dburl = 'sqlite:///%s' % dbname # set up environment eng = create_engine(dburl, echo=False) meta = MetaData(bind=eng) Base = declarative_base(metadata=meta) Session = sessionmaker() # D self referential class D(Base): __tablename__ = 'D' id = Column(Integer, primary_key=True) id_d = Column(Integer, ForeignKey('D.id')) name = Column(Text) child = relation('D', backref=backref('parent', remote_side='D.id')) def __repr__(self): return '--D (id:%s-id_d:%s) %s --' % (self.id, self.id_d, self.name) def initdb(): print '# create the database' meta.drop_all(checkfirst=True) meta.create_all() sess = Session() # insert some data for self-reference relationship for ddata in ('d-one', 'd-two'): dobj = D(name=ddata) sess.add(dobj) dobj2 = D(name=ddata+'-child1') dobj.child.append(dobj2) dobj2 = D(name=ddata+'-child2') dobj.child.append(dobj2) dobj3 = D(name=ddata+'-child2'+'-child3') dobj2.child.append(dobj3) sess.commit() sess.close() def printobj(obj, indent=''): # recursive list print '%s%s' % (indent, obj) if hasattr(obj, 'child'): for ch in obj.child: printobj(ch, indent=indent+'') def listd(): # retrieve and list sess = Session() print '# D-D' query = sess.query(D).filter(D.id_d==None).order_by(D.name) for dobj in query: printobj(dobj) sess.close() def tst3(): # do stuff with self referential class D sess = Session() print #switch parentage of name 'd-two-child1' to 'd-one' print '# before' listd() child = sess.query(D).filter(D.name=='d-two-child1').one() parent = sess.query(D).filter(D.name=='d-one').one() eng.echo=True child.parent = parent child.name += ' MOVED' sess.commit() eng.echo=False print '# after' listd() initdb() tst3() --~--~-~--~~~---~--~~ 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: Self-Referential Mapping with Base?
Ahhh, looks like I'm on rc4. Let me try final and see. On Sun, 2009-01-11 at 13:10 -0500, Michael Bayer wrote: 0.5.0 final ? On Jan 11, 2009, at 12:21 PM, Darren Govoni wrote: Hi MikeCo, I tried your example, and got this error from SA 0.5. ValueError: need more than 0 values to unpack Darren On Fri, 2009-01-09 at 20:33 -0800, MikeCo wrote: from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.ext.declarative import declarative_base dbname = '' dburl = 'sqlite:///%s' % dbname # set up environment eng = create_engine(dburl, echo=False) meta = MetaData(bind=eng) Base = declarative_base(metadata=meta) Session = sessionmaker() # D self referential class D(Base): __tablename__ = 'D' id = Column(Integer, primary_key=True) id_d = Column(Integer, ForeignKey('D.id')) name = Column(Text) child = relation('D', backref=backref('parent', remote_side='D.id')) def __repr__(self): return '--D (id:%s-id_d:%s) %s --' % (self.id, self.id_d, self.name) def initdb(): print '# create the database' meta.drop_all(checkfirst=True) meta.create_all() sess = Session() # insert some data for self-reference relationship for ddata in ('d-one', 'd-two'): dobj = D(name=ddata) sess.add(dobj) dobj2 = D(name=ddata+'-child1') dobj.child.append(dobj2) dobj2 = D(name=ddata+'-child2') dobj.child.append(dobj2) dobj3 = D(name=ddata+'-child2'+'-child3') dobj2.child.append(dobj3) sess.commit() sess.close() def printobj(obj, indent=''): # recursive list print '%s%s' % (indent, obj) if hasattr(obj, 'child'): for ch in obj.child: printobj(ch, indent=indent+'') def listd(): # retrieve and list sess = Session() print '# D-D' query = sess.query(D).filter(D.id_d==None).order_by(D.name) for dobj in query: printobj(dobj) sess.close() def tst3(): # do stuff with self referential class D sess = Session() print #switch parentage of name 'd-two-child1' to 'd-one' print '# before' listd() child = sess.query(D).filter(D.name=='d-two-child1').one() parent = sess.query(D).filter(D.name=='d-one').one() eng.echo=True child.parent = parent child.name += ' MOVED' sess.commit() eng.echo=False print '# after' listd() initdb() tst3() --~--~-~--~~~---~--~~ 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: MS SQL wrapper for pymssql DBAPI broken in 0.5.0
On Sat, Jan 10, 2009 at 9:54 PM, Michael Trier mtr...@gmail.com wrote: 1. Override do_begin so that it creates a cursor and then executes on the cursor: def do_begin(self, connection): cursor = connection.cursor() cursor.execute(SET IMPLICIT_TRANSACTIONS OFF) cursor.execute(BEGIN TRANSACTION) this would be appropriate since connection doesn't have an execute() method in DBAPI. Yeah I didn't even realize that pyodbc supports execute at the connection level especially since it is undocumented. I guess in my mind I thought I was dealing with the cursor at that point. So I should change this anyway even for pyodbc so we keep in line with the API. I just tested the change and it works fine. 2. Revert to the old behavior by doing: def do_begin(self, connection): pass This would only affect pymssql. Option 2 results in the greatest number of passed tests for straight orm usage, but causes the ever persistent hanging when working with transactional tests. This was the reason for the introduction of those statements (plus visitpoints). I want Mike Bayer to confirm that's the direction he wants to go in, before I proceed. This has been corrected in http://www.sqlalchemy.org/trac/changeset/5641. I actually did both things, since using the cursor is the right thing to do. Michael Trier http://blog.michaeltrier.com/ http://thisweekindjango.com/ --~--~-~--~~~---~--~~ 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: MS SQL wrapper for pymssql DBAPI broken in 0.5.0
This has been corrected in http://www.sqlalchemy.org/trac/changeset/5641 . I actually did both things, since using the cursor is the right thing to do. Thats awesome, thanks so much for your time and help with this! TH --~--~-~--~~~---~--~~ 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 to walk through a query result
I can not find the answer looking through the 0.5 doc for my problem. query = session.query(db.SomeClass).filter(something).order_by(anorder) # get the first row row = query.first() # display a dialog to the user # one of the possible actions is to press a button which should show the next row in the query row = query.???() How could I use fetchone() on this? Or is there another/correcter way to do this type of thing ins SA? 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: how to walk through a query result
On Jan 11, 2009, at 4:51 PM, Werner F. Bruhin wrote: I can not find the answer looking through the 0.5 doc for my problem. query = session.query(db.SomeClass).filter(something).order_by(anorder) # get the first row row = query.first() # display a dialog to the user # one of the possible actions is to press a button which should show the next row in the query row = query.???() How could I use fetchone() on this? Or is there another/correcter way to do this type of thing ins SA? the query itself is an iterator, so you could say result = iter(query); row = result.next() . now, if you are looking for the results from the *server* to be non- buffered, that requires something more (namely yield_per()), but it has a lot of caveats so I would not use it unless you absolutely know what you're doing. --~--~-~--~~~---~--~~ 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] Announcing Datahub 0.7
http://lucasmanual.com/mywiki/DataHub *Datahub is a tool that allows faster download/crawl, parse, load, and visualize of data. It achieves this by allowing you to divide each step into its own work folders. In each work folder you get a sample files that you can start coding. *Datahub is for people who found some interesting data source for them, they want to download it, parse it, load it into database, provide some documentation, and visualize it. Datahub will speed up the process by creating folder for each of these actions. You will create all the programs from our base default template and move on to analyzing the data in no time. How to get started?: Datahub is a python based tool and here is how to run it. **Create python virtualenviroment: virtualenv --no-site-packages datahubENV source datahubENV/bin/activate **How to get it: wget http://launchpad.net/datahub/trunk/0.7/+download/datahub-0.7.tar.gz tar -xzvf datahub-0.7.tar.gz ** Install it: cd datahub-0.7/ python setup.py install **Create you project using datahub default templates: paster create --list-templates paster create -t datahub ** Where do I start: Above commands created a project skeleton that has 4 folders: crawl (sample code to download via wget or harvestman), parse (here is where you parse raw data), load (here is where you load the data into database using sqlalchemy or a tool of your choice), hdf5 (convert to hdf5 if you don't want to use database), wiki (provide some documentation) This is a first release, so feedback is appreciated. Give it a try if you have some interesting data to deal with. Thanks, Lucas --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---