[sqlalchemy] Building a query with if(type = purchase, value, 0)
Hi Folks, After an hour or so of browsing the net and documentations, getting increasingly frustrated and being about to just - nah, I didn't really consider throwing sqlalchemy out of my project. But still. Anyway, I'm trying to make a select-query which would send something like this to postgres: SELECT sum(if(type = purchase, value, 0)), user_id FROM transactions GROUP BY user_id But no matter how hard I try, I can't find something that works like just being able to do sql.func.if( ... ) when I do that, I get the obvious syntax error. Any help is much appreciated. Wouter --~--~-~--~~~---~--~~ 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] moving an object
Hi all,
I wonder if SA can handle this use case:
An Account can contain Entries ordered by 'position' attribute.
mapper(Account, table_accounts, properties = dict(
entries = relation(Entry, lazy=True, collection_class=ordering_list
('position'),
order_by=[table_entries.c.position],
passive_deletes='all', cascade='save-update',
backref=backref('account', lazy=False),
),
))
I'd like to move an entry from accountA to accountB and let SA remove
the link between the entry and accountA:
entry = accountA.entries[0]
insort_right(accountB.entries, entry)
assert not entry in accountA.entries# false, entry is still in
accountA
It is possible?
Thank you,
jean-philippe
--~--~-~--~~~---~--~~
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: Building a query with if(type = purchase, value, 0)
Thanks for your suggestion, but no such luck. From the sql error I get I gathered that that just creates an actual if_ function.. On Apr 5, 9:08 pm, a...@svilendobrev.com wrote: try with func.if_() On Sunday 05 April 2009 21:19:29 Wouter van Vliet wrote: Hi Folks, After an hour or so of browsing the net and documentations, getting increasingly frustrated and being about to just - nah, I didn't really consider throwing sqlalchemy out of my project. But still. Anyway, I'm trying to make a select-query which would send something like this to postgres: SELECT sum(if(type = purchase, value, 0)), user_id FROM transactions GROUP BY user_id But no matter how hard I try, I can't find something that works like just being able to do sql.func.if( ... ) when I do that, I get the obvious syntax error. Any help is much appreciated. Wouter --~--~-~--~~~---~--~~ 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: session close - whatfor?
Michael Bayer schrieb: the close() will remove any objects left in the session and may help with unit tests in that the subsequent tests aren't interfered with by objects remaining from the previous test. Where is the difference between a process running several tests and one answering several http requests via mod_wsgi? The code to set up the session is this: DBSession = scoped_session(sessionmaker( autoflush=True, autocommit=False, )) Then code running DB-stuff is wrapped into begin/(commit/rollback) via a decorator. This is the same for my tests as well as the production system. So if the close() is needed for tests, I presume it's needed for the production code as well? but its better to use a distinct Session for each test and keep things simple. I keep hearing about Turbogears not allowing complete control over sessions and I'm getting annoyed by this feature. Actually I removed the TG-session-facilities because of other problems, and then the troubles with the not-closed session began. But you have a point, the current way of dealing with SA-transactions in TG2 isn't satisfactory. 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: moving an object
jean-philippe dutreve wrote:
Hi all,
I wonder if SA can handle this use case:
An Account can contain Entries ordered by 'position' attribute.
mapper(Account, table_accounts, properties = dict(
entries = relation(Entry, lazy=True, collection_class=ordering_list
('position'),
order_by=[table_entries.c.position],
passive_deletes='all', cascade='save-update',
backref=backref('account', lazy=False),
),
))
I'd like to move an entry from accountA to accountB and let SA remove
the link between the entry and accountA:
entry = accountA.entries[0]
insort_right(accountB.entries, entry)
assert not entry in accountA.entries# false, entry is still in
accountA
It is possible?
Try removing the entry from accountA:
entry = accountA.pop(0)
...
Also beware that bisect insort has a bug that prevents it from working
properly with list subclasses like ordering_list (or any SA list-based
collection). I think it's fixed in Python 3.0, not sure if the fix was
backported to 2.x.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Building a query with if(type = purchase, value, 0)
And then I realised that I was barking up the wrong tree. Too used to mysql's if(cond,true_val,else_val) syntax I didn't even check if postgressql even know the same thing. Newsflash; it doesn't. Instead, it has the 'case' expression. To be found in the sqlalchemy.sql.expressions module. On Apr 5, 9:03 pm, Wouter van Vliet wou...@interpotential.com wrote: Thanks for your suggestion, but no such luck. From the sql error I get I gathered that that just creates an actual if_ function.. On Apr 5, 9:08 pm, a...@svilendobrev.com wrote: try with func.if_() On Sunday 05 April 2009 21:19:29 Wouter van Vliet wrote: Hi Folks, After an hour or so of browsing the net and documentations, getting increasingly frustrated and being about to just - nah, I didn't really consider throwing sqlalchemy out of my project. But still. Anyway, I'm trying to make a select-query which would send something like this to postgres: SELECT sum(if(type = purchase, value, 0)), user_id FROM transactions GROUP BY user_id But no matter how hard I try, I can't find something that works like just being able to do sql.func.if( ... ) when I do that, I get the obvious syntax error. Any help is much appreciated. Wouter --~--~-~--~~~---~--~~ 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] SA and python 26
I have been working with Python 2.5 and SQLAlchemy. I recently upgraded to Python 2.6 on my Windows machine and I receive the following message: D:\Python26\lib\site-packages\sqlalchemy-0.5.3-py2.6.egg\sqlalchemy\databases\ms sql.py:977: DeprecationWarning: object.__new__() takes no parameters return super(MSSQLDialect, cls).__new__(cls, *args, **kwargs) I have a script that creates my tables and inserts data. The tables get created however they are empty. Here is some example code that does not work: from sqlalchemy.orm import * from db import * session = getsession() new_serv = server(SERVER = test) session.add(new_serv) session.flush() --~--~-~--~~~---~--~~ 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: Building a query with if(type = purchase, value, 0)
i think you're looking for CASE. We have a case() construct which creates this. On Apr 5, 2009, at 3:03 PM, Wouter van Vliet wrote: Thanks for your suggestion, but no such luck. From the sql error I get I gathered that that just creates an actual if_ function.. On Apr 5, 9:08 pm, a...@svilendobrev.com wrote: try with func.if_() On Sunday 05 April 2009 21:19:29 Wouter van Vliet wrote: Hi Folks, After an hour or so of browsing the net and documentations, getting increasingly frustrated and being about to just - nah, I didn't really consider throwing sqlalchemy out of my project. But still. Anyway, I'm trying to make a select-query which would send something like this to postgres: SELECT sum(if(type = purchase, value, 0)), user_id FROM transactions GROUP BY user_id But no matter how hard I try, I can't find something that works like just being able to do sql.func.if( ... ) when I do that, I get the obvious syntax error. Any help is much appreciated. Wouter --~--~-~--~~~---~--~~ 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: SA and python 26
this is not an error, it is only a warning.There should be no difference in behavior between py2.5 and 2.6. The code you have below may not necessarily create any data if you didn't commit your transaction. On Apr 5, 2009, at 6:05 PM, Michael Mileusnich wrote: I have been working with Python 2.5 and SQLAlchemy. I recently upgraded to Python 2.6 on my Windows machine and I receive the following message: D:\Python26\lib\site-packages\sqlalchemy-0.5.3-py2.6.egg\sqlalchemy \databases\ms sql.py:977: DeprecationWarning: object.__new__() takes no parameters return super(MSSQLDialect, cls).__new__(cls, *args, **kwargs) I have a script that creates my tables and inserts data. The tables get created however they are empty. Here is some example code that does not work: from sqlalchemy.orm import * from db import * session = getsession() new_serv = server(SERVER = test) session.add(new_serv) session.flush() --~--~-~--~~~---~--~~ 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: session close - whatfor?
On Apr 5, 2009, at 3:34 PM, Diez B. Roggisch wrote: Michael Bayer schrieb: the close() will remove any objects left in the session and may help with unit tests in that the subsequent tests aren't interfered with by objects remaining from the previous test. Where is the difference between a process running several tests and one answering several http requests via mod_wsgi? The code to set up the session is this: DBSession = scoped_session(sessionmaker( autoflush=True, autocommit=False, )) Then code running DB-stuff is wrapped into begin/(commit/rollback) via a decorator. This is the same for my tests as well as the production system. So if the close() is needed for tests, I presume it's needed for the production code as well? not at all, when you commit/rollback, everything in the session is expired (assuming you're on 0.5).Doing a remove() at the end of a request is a good way to ensure nothing is around from the previous request but in theory its not needed. But again, I've no idea what TG does in this regard. --~--~-~--~~~---~--~~ 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: SA and python 26
The code used to work in 2.5. A simple add and flush was all I needed to do. I tried to use a commit but that did not work either. Am I missing something here? On Sun, Apr 5, 2009 at 4:58 PM, Michael Bayer mike...@zzzcomputing.comwrote: this is not an error, it is only a warning.There should be no difference in behavior between py2.5 and 2.6. The code you have below may not necessarily create any data if you didn't commit your transaction. On Apr 5, 2009, at 6:05 PM, Michael Mileusnich wrote: I have been working with Python 2.5 and SQLAlchemy. I recently upgraded to Python 2.6 on my Windows machine and I receive the following message: D:\Python26\lib\site-packages\sqlalchemy-0.5.3-py2.6.egg\sqlalchemy \databases\ms sql.py:977: DeprecationWarning: object.__new__() takes no parameters return super(MSSQLDialect, cls).__new__(cls, *args, **kwargs) I have a script that creates my tables and inserts data. The tables get created however they are empty. Here is some example code that does not work: from sqlalchemy.orm import * from db import * session = getsession() new_serv = server(SERVER = test) session.add(new_serv) session.flush() --~--~-~--~~~---~--~~ 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: SA and python 26
youd have to provide us a test case. to my knowledge our tests pass with MS on python 2.6. On Apr 5, 2009, at 7:59 PM, Michael Mileusnich wrote: The code used to work in 2.5. A simple add and flush was all I needed to do. I tried to use a commit but that did not work either. Am I missing something here? On Sun, Apr 5, 2009 at 4:58 PM, Michael Bayer mike...@zzzcomputing.com wrote: this is not an error, it is only a warning.There should be no difference in behavior between py2.5 and 2.6. The code you have below may not necessarily create any data if you didn't commit your transaction. On Apr 5, 2009, at 6:05 PM, Michael Mileusnich wrote: I have been working with Python 2.5 and SQLAlchemy. I recently upgraded to Python 2.6 on my Windows machine and I receive the following message: D:\Python26\lib\site-packages\sqlalchemy-0.5.3-py2.6.egg\sqlalchemy \databases\ms sql.py:977: DeprecationWarning: object.__new__() takes no parameters return super(MSSQLDialect, cls).__new__(cls, *args, **kwargs) I have a script that creates my tables and inserts data. The tables get created however they are empty. Here is some example code that does not work: from sqlalchemy.orm import * from db import * session = getsession() new_serv = server(SERVER = test) session.add(new_serv) session.flush() --~--~-~--~~~---~--~~ 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: SA and python 26
and i would also suggest investigating if the DBAPI you're using is somehow different than the one you've used with 2.5, or unsupported on py2.6. On Apr 5, 2009, at 7:59 PM, Michael Mileusnich wrote: The code used to work in 2.5. A simple add and flush was all I needed to do. I tried to use a commit but that did not work either. Am I missing something here? On Sun, Apr 5, 2009 at 4:58 PM, Michael Bayer mike...@zzzcomputing.com wrote: this is not an error, it is only a warning.There should be no difference in behavior between py2.5 and 2.6. The code you have below may not necessarily create any data if you didn't commit your transaction. On Apr 5, 2009, at 6:05 PM, Michael Mileusnich wrote: I have been working with Python 2.5 and SQLAlchemy. I recently upgraded to Python 2.6 on my Windows machine and I receive the following message: D:\Python26\lib\site-packages\sqlalchemy-0.5.3-py2.6.egg\sqlalchemy \databases\ms sql.py:977: DeprecationWarning: object.__new__() takes no parameters return super(MSSQLDialect, cls).__new__(cls, *args, **kwargs) I have a script that creates my tables and inserts data. The tables get created however they are empty. Here is some example code that does not work: from sqlalchemy.orm import * from db import * session = getsession() new_serv = server(SERVER = test) session.add(new_serv) session.flush() --~--~-~--~~~---~--~~ 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: SA and python 26
On Sun, Apr 5, 2009 at 9:46 PM, Michael Bayer mike...@zzzcomputing.comwrote: and i would also suggest investigating if the DBAPI you're using is somehow different than the one you've used with 2.5, or unsupported on py2.6. On Apr 5, 2009, at 7:59 PM, Michael Mileusnich wrote: The code used to work in 2.5. A simple add and flush was all I needed to do. I tried to use a commit but that did not work either. Am I missing something here? On Sun, Apr 5, 2009 at 4:58 PM, Michael Bayer mike...@zzzcomputing.comwrote: this is not an error, it is only a warning.There should be no difference in behavior between py2.5 and 2.6. The code you have below may not necessarily create any data if you didn't commit your transaction. On Apr 5, 2009, at 6:05 PM, Michael Mileusnich wrote: I have been working with Python 2.5 and SQLAlchemy. I recently upgraded to Python 2.6 on my Windows machine and I receive the following message: D:\Python26\lib\site-packages\sqlalchemy-0.5.3-py2.6.egg\sqlalchemy \databases\ms sql.py:977: DeprecationWarning: object.__new__() takes no parameters return super(MSSQLDialect, cls).__new__(cls, *args, **kwargs) I have a script that creates my tables and inserts data. The tables get created however they are empty. Here is some example code that does not work: from sqlalchemy.orm import * from db import * session = getsession() new_serv = server(SERVER = test) session.add(new_serv) session.flush() I'm using the latest pyodbc with 2.6.1 version of python and all tests run fine. If you provide a test script I can dig into it. -- 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: SA and python 26
I tried to the same py scripts with sqllite and that works fine. The issues seems to be with pyodbc however I am using the version from the site. http://code.google.com/p/pyodbc/downloads/detail?name=pyodbc-2.1.3.win32-py2.6.execan=2q= My db.py file has all my tables and mappers in it and the columns are defined there and not in the classes. here is my engine/session stuff: engine = create_engine(connection) metadata = MetaData(engine) Session = scoped_session(sessionmaker(bind=engine, autoflush=False, autocommit=True)) On Sun, Apr 5, 2009 at 10:31 PM, Michael Trier mtr...@gmail.com wrote: On Sun, Apr 5, 2009 at 9:46 PM, Michael Bayer mike...@zzzcomputing.comwrote: and i would also suggest investigating if the DBAPI you're using is somehow different than the one you've used with 2.5, or unsupported on py2.6. On Apr 5, 2009, at 7:59 PM, Michael Mileusnich wrote: The code used to work in 2.5. A simple add and flush was all I needed to do. I tried to use a commit but that did not work either. Am I missing something here? On Sun, Apr 5, 2009 at 4:58 PM, Michael Bayer mike...@zzzcomputing.comwrote: this is not an error, it is only a warning.There should be no difference in behavior between py2.5 and 2.6. The code you have below may not necessarily create any data if you didn't commit your transaction. On Apr 5, 2009, at 6:05 PM, Michael Mileusnich wrote: I have been working with Python 2.5 and SQLAlchemy. I recently upgraded to Python 2.6 on my Windows machine and I receive the following message: D:\Python26\lib\site-packages\sqlalchemy-0.5.3-py2.6.egg\sqlalchemy \databases\ms sql.py:977: DeprecationWarning: object.__new__() takes no parameters return super(MSSQLDialect, cls).__new__(cls, *args, **kwargs) I have a script that creates my tables and inserts data. The tables get created however they are empty. Here is some example code that does not work: from sqlalchemy.orm import * from db import * session = getsession() new_serv = server(SERVER = test) session.add(new_serv) session.flush() I'm using the latest pyodbc with 2.6.1 version of python and all tests run fine. If you provide a test script I can dig into it. -- 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 -~--~~~~--~~--~--~---
