[sqlalchemy] Re: Versioning
Sam schrieb: One of the things I really liked about sqlobject was its versioning plug-in. ( http://www.sqlobject.org/Versioning.html ) Is there anything similar for sqlalchemy? A google search turned up versioned ( http://elixir.ematia.de/apidocs/elixir.ext.versioned.html ) This is a plug-in for elixir, but I'm not using elixir. Could I make it work without having to use elixir? Are there other alternatives? Maybe sqlalchmey.migrate is what you are looking for? Diez --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] ProgrammingError invalidates Session
Hi, I've got a table with a unique-constraint. And I've got an automatic transaction management via decorator in place. Now it can happen that the violation of that constraint occurs - then a ProgrammingError is raised. As the violation is non-fatal, I try to catch it, and continue. The problem is that the session has been invalidated, and my decorator fails when trying to commit. Is there any chance to prevent this invalidation from happening? Documention didn't indicate a solution. Probably an Extension helps? Diez --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Adjacency List + Alternate Join Conditions == ???
The main reason why I wan't to use relations is eagerloading, because it is the only way, as far as I can see, to retrieve data from DB from several objects in one request. I don't wan't each of my 50 objects requests the DB to fill its own neighbors; but fill them all in one request. Eagerloading does that that's why I want relations... Maybe today that demand doesn't make any sense, but last time I used DB, it was much more efficient to issue 1 big request rather than 50 small ones. On Sep 11, 8:24 pm, Michael Bayer [EMAIL PROTECTED] wrote: On Sep 11, 2008, at 2:04 PM, GustaV wrote: Ok, another thing on the subject: It looks like that does not work before a commit. Even a flush doesn't help: t1 = Tile(id=1) t2 = Tile(id=2) t3 = Tile(id=3) t4 = Tile(id=4) session.add_all([t1, t2, t3, t4]) session.flush() assert t2.neighbors == [t1] FAIL I'd really like to use it before even a flush! :) well the flush is needed since you're making use of the database to calculate what members are part of the collection. Assuming you haven't already accessed t2.neighbors, it should lazyload the items the first time you hit it. Otherwise you could just say Session.expire(t2, [neighbors]). Other expiry methods apply, i.e. Session.expire_all(), Session.expire(t2), Session.commit() etc. Another option here is to do away with relation() altogether. This would greatly simplify the whole thing: class Tile(object): [EMAIL PROTECTED] def neighbors(self): return object_session(self).query(Tile).filter(Tile.idself.id).all() --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Versioning
On Friday 12 September 2008 05:37:35 Sam wrote: One of the things I really liked about sqlobject was its versioning plug-in. ( http://www.sqlobject.org/Versioning.html ) Is there anything similar for sqlalchemy? A google search turned up versioned ( http://elixir.ematia.de/apidocs/elixir.ext.versioned.html ) This is a plug-in for elixir, but I'm not using elixir. Could I make it work without having to use elixir? Are there other alternatives? i have a bitemporal-versions mixin but it isn't that simple and API-finished as the above. http://dbcook.svn.sourceforge.net/viewvc/dbcook/trunk/dbcook/misc/timed/ --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Versioning
On Fri, Sep 12, 2008 at 4:37 AM, Sam [EMAIL PROTECTED] wrote: One of the things I really liked about sqlobject was its versioning plug-in. ( http://www.sqlobject.org/Versioning.html ) Is there anything similar for sqlalchemy? A google search turned up versioned ( http://elixir.ematia.de/apidocs/elixir.ext.versioned.html ) This is a plug-in for elixir, but I'm not using elixir. Could I make it work without having to use elixir? Are there other alternatives? Not sure what's it worth but this might be what you are looking for: http://www.okfn.org/vdm/ -- Gaëtan de Menten http://openhex.org --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Replacing a Firebird view with orm.query.join
I have a view in an FB db with basically does this CREATE VIEW VCBOOK( CB_CELLARBOOKID, CBV_CBVINTAGEID, CBB_CBBOTTLEID ) AS select cb.cellarbookid, cbv.cbvintageid, cbb.cbbottleid, from cellarbook cb left outer join cbvintage cbv on cb.cellarbookid = cbv.fk_cellarbookid left outer join cbbottle cbb on cbv.cbvintageid = cbb.fk_cbvintageid The actual view has many more columns but I think the above is enough to show what I would like to accomplish. In the application I then use it e.g. like this: query(db.Vcbook).all() Now as the cbv_cbvintageid and cbb_cbbottleid can be Null I get None back. I thought lets get rid of this view and just use joins, but I can not figure it out. Doing this: wine = session.query(db.Cellarbook).outerjoin([db.Cbvintage, db.Cbbottle]).all() Only gives me 5 rows, i.e. only the one from db.Cellarbook. Can anyone push me in the right direction on this? Werner P.S. I am on 0.5rc1 --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: ProgrammingError and Catching an Exception
As the session is handled by TurboGears, I tried the following: from turbogears.database import session try: MyDBLog( myfield='A too long string ' ) session.commit() session.begin() except Exception, e: log.error(Exception occurred with database logging: %s % e) It seems to work - however I'm not sure of the performance cost of committing twice per web request. Eoghan On Sep 11, 6:53 pm, David Gardner [EMAIL PROTECTED] wrote: Depending on how your code is layed out you could wrap your session.flush() with a try:/catch I do something like this: session.begin() try: ... session.flush() session.commit() except: session.rollback() Eoghan Murray wrote: Hi, I've the following which generates an insert: try: MyDBLog( myfield='A too long string ' ) except Exception, e: log.error(Exception occurred with database logging: %s % e) Unfortunately, 'myfield' is (for example) a string of only length 10, so the session fails with ProgrammingError: (ProgrammingError) value too long for type character varying(10) This error occurs at session commit time, so my 'except' clause above is useless. As 'MyDBLog' is not critical, I want to be able to ignore/log any kind of Exception which the 'try' block above ultimately generates. Is there a way to do this? Thanks! Eoghan -- David Gardner Pipeline Tools Programmer, Sid the Science Kid Jim Henson Creature Shop (323) 802-1717 [EMAIL PROTECTED] --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Replacing a Firebird view with orm.query.join
Still not there, but another question/problem below:
Werner F. Bruhin wrote:
I have a view in an FB db with basically does this
CREATE VIEW VCBOOK(
CB_CELLARBOOKID,
CBV_CBVINTAGEID,
CBB_CBBOTTLEID
)
AS
select cb.cellarbookid, cbv.cbvintageid, cbb.cbbottleid,
from cellarbook cb
left outer join cbvintage cbv on cb.cellarbookid = cbv.fk_cellarbookid
left outer join cbbottle cbb on cbv.cbvintageid = cbb.fk_cbvintageid
The actual view has many more columns but I think the above is enough to
show what I would like to accomplish.
In the application I then use it e.g. like this:
query(db.Vcbook).all()
Now as the cbv_cbvintageid and cbb_cbbottleid can be Null I get None back.
I thought lets get rid of this view and just use joins, but I can not
figure it out.
Doing this:
wine = session.query(db.Cellarbook).outerjoin([db.Cbvintage,
db.Cbbottle]).all()
Only gives me 5 rows, i.e. only the one from db.Cellarbook.
Can anyone push me in the right direction on this?
Werner
P.S.
I am on 0.5rc1
j1 = sao.outerjoin(db.Cellarbook, db.Cbvintage)
print j1
j2 = sao.outerjoin(db.Cbvintage, db.Cbbottle)
print j2
j3 = sao.outerjoin(db.Cellarbook, db.Cbvintage).outerjoin(db.Cbvintage,
db.Cbbottle)
print j3
j1 and j2 produce a join clause, but on j3 I get the following exception:
Traceback (most recent call last):
File saTest.py, line 66, in module
print j3
File
c:\python25\lib\site-packages\sqlalchemy-0.5.0rc1-py2.5.egg\sqlalchemy\sql\expression.py,
line 1157, in __str__
return unicode(self.compile()).encode('ascii', 'backslashreplace')
File
c:\python25\lib\site-packages\sqlalchemy-0.5.0rc1-py2.5.egg\sqlalchemy\sql\expression.py,
line 1153, in compile
compiler.compile()
File
c:\python25\lib\site-packages\sqlalchemy-0.5.0rc1-py2.5.egg\sqlalchemy\sql\compiler.py,
line 175, in compile
self.string = self.process(self.statement)
File
c:\python25\lib\site-packages\sqlalchemy-0.5.0rc1-py2.5.egg\sqlalchemy\sql\compiler.py,
line 183, in process
return meth(obj, **kwargs)
File
c:\python25\lib\site-packages\sqlalchemy-0.5.0rc1-py2.5.egg\sqlalchemy\sql\compiler.py,
line 597, in visit_join
self.process(join.right, asfrom=True) + ON +
self.process(join.onclause))
File
c:\python25\lib\site-packages\sqlalchemy-0.5.0rc1-py2.5.egg\sqlalchemy\sql\compiler.py,
line 181, in process
meth = getattr(self, visit_%s % obj.__visit_name__, None)
AttributeError: type object 'Cbbottle' has no attribute '__visit_name__'
What is the best/most efficient way of doing multiple joins with SA.orm?
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Replacing a Firebird view with orm.query.join
Werner F. Bruhin wrote:
...
j3 = sao.outerjoin(db.Cellarbook, db.Cbvintage).outerjoin(db.Cbvintage,
db.Cbbottle)
print j3
j1 and j2 produce a join clause, but on j3 I get the following exception:
I different exception if I actually try to use j3:
I do this:
wine = session.query(db.Cellarbook)
j3 = sao.outerjoin(db.Cellarbook, db.Cbvintage).outerjoin(db.Cbvintage,
db.Cbbottle)
wine = wine.select_from(j3)
Then I get this:
Traceback (most recent call last):
File saTest.py, line 76, in module
print wine
File
c:\python25\lib\site-packages\sqlalchemy-0.5.0rc1-py2.5.egg\sqlalchemy\orm\query.py,
line 1565, in __str__
return str(self._compile_context().statement)
File
c:\python25\lib\site-packages\sqlalchemy-0.5.0rc1-py2.5.egg\sqlalchemy\sql\expression.py,
line 1157, in __str__
return unicode(self.compile()).encode('ascii', 'backslashreplace')
File
c:\python25\lib\site-packages\sqlalchemy-0.5.0rc1-py2.5.egg\sqlalchemy\sql\expression.py,
line 1153, in compile
compiler.compile()
File
c:\python25\lib\site-packages\sqlalchemy-0.5.0rc1-py2.5.egg\sqlalchemy\sql\compiler.py,
line 175, in compile
self.string = self.process(self.statement)
File
c:\python25\lib\site-packages\sqlalchemy-0.5.0rc1-py2.5.egg\sqlalchemy\sql\compiler.py,
line 183, in process
return meth(obj, **kwargs)
File
c:\python25\lib\site-packages\sqlalchemy-0.5.0rc1-py2.5.egg\sqlalchemy\sql\compiler.py,
line 498, in visit_select
correlate_froms = set(sql._from_objects(*froms))
File
c:\python25\lib\site-packages\sqlalchemy-0.5.0rc1-py2.5.egg\sqlalchemy\sql\expression.py,
line 886, in _from_objects
return itertools.chain(*[element._get_from_objects(**kwargs) for
element in elements])
File
c:\python25\lib\site-packages\sqlalchemy-0.5.0rc1-py2.5.egg\sqlalchemy\sql\expression.py,
line 2412, in _get_from_objects
return [self] + self.onclause._get_from_objects(**modifiers) +
self.left._get_from_objects(**modifiers) +
self.right._get_from_objects(**modifiers)
AttributeError: type object 'Cbbottle' has no attribute '_get_from_objects'
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: ProgrammingError invalidates Session
On Sep 12, 2008, at 3:48 AM, Diez B. Roggisch wrote: Hi, I've got a table with a unique-constraint. And I've got an automatic transaction management via decorator in place. Now it can happen that the violation of that constraint occurs - then a ProgrammingError is raised. As the violation is non-fatal, I try to catch it, and continue. The problem is that the session has been invalidated, and my decorator fails when trying to commit. Is there any chance to prevent this invalidation from happening? Documention didn't indicate a solution. Probably an Extension helps? any errors raised during flush() will result in a rollback(). If you're using 0.5, the Session recovers gracefully from a rollback so you can try again, but you have to perform the entire contents of the transaction again. The bookkeeping which flush() performs does not currently support partial progress, so it cant issue half of its SQL, fail, and then pick up again where it left off when you try again - the session's state is updated after the flush() completes. So the current way to issue a flush(), catch an error, and continue in the same transaction is to use begin_nested() to issue a SAVEPOINT which the flush() will roll back to. In the bigger picture, SQLAlchemy encourages programming practices that don't rely upon exception throws from the database to determine database state, favoring explicit SELECT operations beforehand (typically via Query and lazyload operations) as needed. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: any effort getting sqlalchemy to work on ironpython is going on ?
On Sep 11, 2008, at 11:01 PM, sakesun wrote:
I need sqlalchemy to work on ironpython (1.2 or 2.0b)
sqlalchemy fail on ironpython even with simple use case
like create simple Table definition.
from sqlalchemy import Table, Column, Integer, String, MetaData,
ForeignKey
metadata = MetaData()
users_table = Table('users', metadata,
... Column('id', Integer, primary_key=True),
... Column('name', String),
... Column('fullname', String),
... Column('password', String)
... )
Traceback (most recent call last):
File stdin, line 1, in module
IndexError: Index was outside the bounds of the array.
wow, thats pretty bad.
I wonder if the question is more, is any effort underway getting
IronPython to work like Python ?
SQLAlchemy does work with Jython and really didnt need any changes
other than database dialect stuff. So its not like we're performing
any black magic with arrays in our Table object.
I am curious what the issue is above, but also I think the agenda with
IronPython is that folks would be calling out to all Microsoft
libraries to do everything, like Linq. So I'm not aware of any effort
to get SQLA going on IronPython but it would definitely be much
appreciated if there were.
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Replacing a Firebird view with orm.query.join
On Sep 12, 2008, at 9:42 AM, Werner F. Bruhin wrote: j1 = sao.outerjoin(db.Cellarbook, db.Cbvintage) print j1 j2 = sao.outerjoin(db.Cbvintage, db.Cbbottle) print j2 j3 = sao.outerjoin(db.Cellarbook, db.Cbvintage).outerjoin(db.Cbvintage, db.Cbbottle) print j3 j1 and j2 produce a join clause, but on j3 I get the following exception: File c:\python25\lib\site-packages\sqlalchemy-0.5.0rc1-py2.5.egg \sqlalchemy\sql\compiler.py, line 181, in process meth = getattr(self, visit_%s % obj.__visit_name__, None) AttributeError: type object 'Cbbottle' has no attribute '__visit_name__' What is the best/most efficient way of doing multiple joins with SA.orm? if CellarBook, Cbvintage, etc. are mapped classes, the join and outerjoin functions you must be using are from sqlalchemy.orm import join, outerjoin. those are aware of ORM mapped classes whereas sqlalchemy.sql.expression.join/outerjoin are not. You can use the outerjoin() attached to Qeury for the whole thing, i.e.: query(Class1).outerjoin(Class2, Class3) if the ON condition is required: query(Class1).outerjoin((Class2, Class1.foo==Class2.bar), (Class3, Class3.bar==Class2.foo)) --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Replacing a Firebird view with orm.query.join
Michael, Michael Bayer wrote: ... if CellarBook, Cbvintage, etc. are mapped classes, the join and outerjoin functions you must be using are from sqlalchemy.orm import join, outerjoin. those are aware of ORM mapped classes whereas sqlalchemy.sql.expression.join/outerjoin are not. You can use the outerjoin() attached to Qeury for the whole thing, i.e.: query(Class1).outerjoin(Class2, Class3) if the ON condition is required: query(Class1).outerjoin((Class2, Class1.foo==Class2.bar), (Class3, Class3.bar==Class2.foo)) O.K. that looks easy, and I tried this before but I don't get the result I am looking for. wines = session.query(db.Cellarbook).outerjoin(db.Cbvintage, db.Cbbottle) print wines # if I use this sql select in my db ide I get 8 rows for wine in wines.all(): print wine print '\n' If I use the generated SQL I get 8 rows, but in my for loop above I only get 5. i.e. I get the following: (note the integer at the end is the dbCellarbook.primarykey, so this duplicated rows have more then one row in cbvintage and possible in cbbottle). Glen Elgin Virgin OakGlen Elgin Virgin Oak141 Ardbeg RenaissanceArdbeg Renaissance142 Ch. St. GeorgesCh. St. Georges144 Ch. St. GeorgesCh. St. Georges144 Ch. St. GeorgesCh. St. Georges144 Goldwater EsslinGoldwater Esslin, Merlot145 Goldwater EsslinGoldwater Esslin, Merlot145 Goldwater ZellGoldwater Zell146 Maybe I am asking the question incorrectly. In other words: db.Cellarbook (a wine) - relates (oneToMany) to db.Cbvintage (zero or more vintages) - which in turn relates (oneToMany) to db.Cbbottle (zero or more bottle sizes) What do I need to do to get the 8 rows in my for loop? Can I do this with just a query or do I need to look into other things. As always thanks a lot for your help 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Replacing a Firebird view with orm.query.join
On Sep 12, 2008, at 11:43 AM, Werner F. Bruhin wrote: Michael, Michael Bayer wrote: ... if CellarBook, Cbvintage, etc. are mapped classes, the join and outerjoin functions you must be using are from sqlalchemy.orm import join, outerjoin. those are aware of ORM mapped classes whereas sqlalchemy.sql.expression.join/outerjoin are not. You can use the outerjoin() attached to Qeury for the whole thing, i.e.: query(Class1).outerjoin(Class2, Class3) if the ON condition is required: query(Class1).outerjoin((Class2, Class1.foo==Class2.bar), (Class3, Class3.bar==Class2.foo)) O.K. that looks easy, and I tried this before but I don't get the result I am looking for. wines = session.query(db.Cellarbook).outerjoin(db.Cbvintage, db.Cbbottle) print wines # if I use this sql select in my db ide I get 8 rows for wine in wines.all(): print wine print '\n' If I use the generated SQL I get 8 rows, but in my for loop above I only get 5. i.e. I get the following: (note the integer at the end is the dbCellarbook.primarykey, so this duplicated rows have more then one row in cbvintage and possible in cbbottle). Glen Elgin Virgin OakGlen Elgin Virgin Oak141 Ardbeg RenaissanceArdbeg Renaissance142 Ch. St. GeorgesCh. St. Georges144 Ch. St. GeorgesCh. St. Georges144 Ch. St. GeorgesCh. St. Georges144 Goldwater EsslinGoldwater Esslin, Merlot145 Goldwater EsslinGoldwater Esslin, Merlot145 Goldwater ZellGoldwater Zell146 Maybe I am asking the question incorrectly. In other words: db.Cellarbook (a wine) - relates (oneToMany) to db.Cbvintage (zero or more vintages) - which in turn relates (oneToMany) to db.Cbbottle (zero or more bottle sizes) What do I need to do to get the 8 rows in my for loop? Can I do this with just a query or do I need to look into other things. thats actually working correctly. When you say sess.query(SomeClass), the Query will load rows each representing a distinct SomeClass instance based on the primary key. The outerjoins used result in the same SomeClass primary key being repeated, but since those aren't being returned, query() returns just the five unique SomeClass instances. Now, if you actually want to get back objects for the outerjoins, youd say somehting like: sess.query(SomeClass, SomeOtherClass, SomeThirdClass) this will disable the uniquing logic used for a single class and return tuples containining instances of the above three classes, exactly corresponding to the full result of the outerjoin. Primary keys which aren't fulfilled for the joined classes will correspoind to an object value of None. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Replacing a Firebird view with orm.query.join
Michael Bayer wrote: ... Now, if you actually want to get back objects for the outerjoins, youd say somehting like: sess.query(SomeClass, SomeOtherClass, SomeThirdClass) On Firebird when I do this I get a cross join (according to the Helen Borrie book) which in my case gives me 280 rows instead of the 8 :-( . this will disable the uniquing logic used for a single class and return tuples containining instances of the above three classes, exactly corresponding to the full result of the outerjoin. Primary keys which aren't fulfilled for the joined classes will correspoind to an object value of None. I got what I call a work around. I keep the Firebird view and to a outerjoin on it. Gives me the result I want but I can not get rid of the view - not a big deal. 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Replacing a Firebird view with orm.query.join
On Sep 12, 2008, at 12:49 PM, Werner F. Bruhin wrote: Michael Bayer wrote: ... Now, if you actually want to get back objects for the outerjoins, youd say somehting like: sess.query(SomeClass, SomeOtherClass, SomeThirdClass) On Firebird when I do this I get a cross join (according to the Helen Borrie book) which in my case gives me 280 rows instead of the 8 :-( . what is the SQL being emitted ? --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Replacing a Firebird view with orm.query.join
Michael Bayer wrote: On Sep 12, 2008, at 12:49 PM, Werner F. Bruhin wrote: Michael Bayer wrote: ... Now, if you actually want to get back objects for the outerjoins, youd say somehting like: sess.query(SomeClass, SomeOtherClass, SomeThirdClass) On Firebird when I do this I get a cross join (according to the Helen Borrie book) which in my case gives me 280 rows instead of the 8 :-( . what is the SQL being emitted ? SELECT cellarbook.winenames AS cellarbook_winenames, cellarbook.namesandvar AS cellarbook_namesandvar, cellarbook.cellarbookid AS cellarbook_cellarbookid, cellarbook.winename AS cellarbook_winename, cellarbook.winename2 AS cellarbook_winename2, cellarbook.barrique AS cellarbook_barrique, cellarbook.externalref AS cellarbook_externalref, cellarbook.created AS cellarbook_created, cellarbook.updated AS cellarbook_updated, cellarbook.fk_countryid AS cellarbook_fk_countryid, cellarbook.fk_regionid AS cellarbook_fk_regionid, cellarbook.fk_subregionid AS cellarbook_fk_subregionid, cellarbook.fk_vineyardid AS cellarbook_fk_vineyardid, cellarbook.fk_supplierid AS cellarbook_fk_supplierid, cellarbook.fk_producerid AS cellarbook_fk_producerid, cellarbook.fk_distillerid AS cellarbook_fk_distillerid, cellarbook.fk_qualityid AS cellarbook_fk_qualityid, cellarbook.fk_drinktypeid AS cellarbook_fk_drinktypeid, cellarbook.notes AS cellarbook_notes, cellarbook.fk_winefamid AS cellarbook_fk_winefamid, cellarbook.variety AS cellarbook_variety, cbvintage.cbvintageid AS cbvintage_cbvintageid, cbvintage.vintage AS cbvintage_vintage, cbvintage.created AS cbvintage_created, cbvintage.updated AS cbvintage_updated, cbvintage.notes AS cbvintage_notes, cbvintage.alcohol AS cbvintage_alcohol, cbvintage.avgscore AS cbvintage_avgscore, cbvintage.avgscore2 AS cbvintage_avgscore2, cbvintage.fk_cellarbookid AS cbvintage_fk_cellarbookid, cbvintage.fk_wineinfoid AS cbvintage_fk_wineinfoid, cbvintage.fk_spirinfoid AS cbvintage_fk_spirinfoid, cbbottle.quantityonhand AS cbbottle_quantityonhand, cbbottle.purchasevalueonhand AS cbbottle_purchasevalueonhand, cbbottle.currentvalueonhand AS cbbottle_currentvalueonhand, cbbottle.cbbottleid AS cbbottle_cbbottleid, cbbottle.maturityfirst AS cbbottle_maturityfirst, cbbottle.maturitybest AS cbbottle_maturitybest, cbbottle.maturitypast AS cbbottle_maturitypast, cbbottle.storagelocation AS cbbottle_storagelocation, cbbottle.quantitypurchased AS cbbottle_quantitypurchased, cbbottle.quantityconsumed AS cbbottle_quantityconsumed, cbbottle.lastpurchaseprice AS cbbottle_lastpurchaseprice, cbbottle.avgpurchaseprice AS cbbottle_avgpurchaseprice, cbbottle.currentvalue AS cbbottle_currentvalue, cbbottle.isactive AS cbbottle_isactive, cbbottle.printlabel AS cbbottle_printlabel, cbbottle.remarks AS cbbottle_remarks, cbbottle.created AS cbbottle_created, cbbottle.updated AS cbbottle_updated, cbbottle.fk_cbvintageid AS cbbottle_fk_cbvintageid, cbbottle.fk_containerid AS cbbottle_fk_containerid, cbbottle.fk_cellarid AS cbbottle_fk_cellarid, cbbottle.fk_sealtypeid AS cbbottle_fk_sealtypeid, cbbottle.barcode AS cbbottle_barcode, cbbottle.minqoh AS cbbottle_minqoh, cbbottle.inetrefcode AS cbbottle_inetrefcode, cbbottle.inetrefsource AS cbbottle_inetrefsource FROM cellarbook, cbvintage, cbbottle 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Replacing a Firebird view with orm.query.join
On Sep 12, 2008, at 1:09 PM, Werner F. Bruhin wrote: Michael Bayer wrote: On Sep 12, 2008, at 12:49 PM, Werner F. Bruhin wrote: Michael Bayer wrote: ... Now, if you actually want to get back objects for the outerjoins, youd say somehting like: sess.query(SomeClass, SomeOtherClass, SomeThirdClass) On Firebird when I do this I get a cross join (according to the Helen Borrie book) which in my case gives me 280 rows instead of the 8 :- ( . what is the SQL being emitted ? OK, more specifically, this is how to do the query: sess.query(SomeClass, SomeOtherClass, SomeThirdClass).outerjoin((SomeOtherClass, SomeClass.foo==SomeOtherClass.bar), (SomeThirdClass, SomeOtherClass.foo==SomeThirdClass.bar)) if firebird can't do OUTER JOIN, then that's a different story. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Replacing a Firebird view with orm.query.join
Michael, Michael Bayer wrote: ... OK, more specifically, this is how to do the query: sess.query(SomeClass, SomeOtherClass, SomeThirdClass).outerjoin((SomeOtherClass, SomeClass.foo==SomeOtherClass.bar), (SomeThirdClass, SomeOtherClass.foo==SomeThirdClass.bar)) if firebird can't do OUTER JOIN, then that's a different story. That did the trick and is also a lot faster then using the view which is even better. Actually it was a little simplar as SA figured out the onclause, i.e.: wines = session.query(db.Cellarbook, db.Cbvintage, db.Cbbottle).outerjoin(db.Cbvintage).outerjoin(db.Cbbottle) Thanks for being patient with me 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: any effort getting sqlalchemy to work on ironpython is going on ?
On Fri, 12 Sep 2008 11:11:50 -0400
Michael Bayer [EMAIL PROTECTED] wrote:
On Sep 11, 2008, at 11:01 PM, sakesun wrote:
I need sqlalchemy to work on ironpython (1.2 or 2.0b)
sqlalchemy fail on ironpython even with simple use case
like create simple Table definition.
from sqlalchemy import Table, Column, Integer, String,
MetaData, ForeignKey
metadata = MetaData()
users_table = Table('users', metadata,
... Column('id', Integer, primary_key=True),
... Column('name', String),
... Column('fullname', String),
... Column('password', String)
... )
Traceback (most recent call last):
File stdin, line 1, in module
IndexError: Index was outside the bounds of the array.
wow, thats pretty bad.
This traceback seems to be either incomplete, or the interactive
interpreter doesn't like the way it was typed, since it appears to be
saying the error is in something on the console input. I might try this
and see, the console editing in IronPython is kinda spartan and quirky.
SA doesn't do *that* much black magic. It might be just enough to expose
a bug in IronPython, or a false dependency in SA, but I seriously doubt
it would involve the list subscript operator. :)
I wonder if the question is more, is any effort underway getting
IronPython to work like Python ?
The last time I played with IronPython, it was 1.1 (I think) and the
language itself is quite complete. The only strangeness with the
language proper is that unicode and str primitives are one in the same,
you have to use bytes to get an unencoded string (which is probably fine
as CPython 3.0 is going to mandate this also. I just wonder if they will
do it *that* way).
Also, the class implementation needs a little looking at, as there seems
to be no old-style classes, you get a new-style class without inheriting
from object (or anything else).
These I discovered when I tweaked PyYAML to run on IronPython. It
depended on str and unicode being differentiable, and it's RTTI would
puke because of this. Also, codec was absent; see below.
SQLAlchemy does work with Jython and really didnt need any changes
other than database dialect stuff. So its not like we're performing
any black magic with arrays in our Table object.
Since SA runs on Jython it should be relatively free of false
dependencies on interpreter implementation details (such as GC behavior
and datatype details). That probably goes a good way towards making it
work on IronPython.
I am curious what the issue is above, but also I think the agenda
with IronPython is that folks would be calling out to all Microsoft
libraries to do everything, like Linq.
I don't know enough about Linq to say whether it would be able to be
molded into a pythonic interface for IronPython. I tend to doubt it
because of that language integrated part, but there might be a
meta-interface to whatever sorts of things Linq manipulates. There's
probably also value in using python libraries one is already familiar
with, for people such as me (a Python guy who may want to run on .NET,
as opposed to a .NET guy who wants to code in Python)
Also, I think IronPython and IronRuby are trying to stay compatible with
Mono too. I say that because dynamic languages on the CLR are being
touted as major hotness when combined with Silverlight, and Moonlight is
a Microsoft endorsed Silverlight implementation built on Mono.
So I'm not aware of any
effort to get SQLA going on IronPython but it would definitely be
much appreciated if there were.
It would be pretty cool :)
The biggest gaps in IronPython last time I used it were Python standard
library support, which they appear to be working on. For instance, the
codec module and (i think) the pickle module weren't present at all,
although the former was available with slightly altered/incomplete
semantics as _codec.
I think the big hold-up on the libraries is that the IronPython guys
seem to be thoughtfully integrating the CLR and Python features
together, and I guess that takes some time. For instance, there is
(was?) a bug to allow Python classes implementing a pickle interface to
be able to be serialized with CLR's System.Runtime.Serialization, and
[Serializable] CLR classes to be picklable.
Anyway, just some thoughts on the matter...
-Kyle
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
