date:20110312

Re: [sqlalchemy] Is there a way to generate this UPDATE statement?

2011-03-12 Thread Michael Bayer


On Mar 13, 2011, at 1:23 AM, Michael Bayer wrote:

> 
> ORM:
> 
>   
> sess.query(Item).update(price=sess.query(Month.price).filter(Item.id==Month.id).as_scalar(),
>  False)

try again:

sess.query(Item).update({'price':sess.query(Month.price).filter(Item.id==Month.id).as_scalar()},
 False)

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Re: Has anyone already got a collection_class mod that uses defaultdict?

2011-03-12 Thread Michael Bayer


On Mar 10, 2011, at 6:20 PM, Eric Ongerth wrote:

> So, jek, if you're listening, or anyone else -- is there an already
> existing, working implementation of a Dict of Lists or Dict of Sets
> collection class?

the association_proxy is always there to flatten the "object" in the middle of 
two relationships typically along an association - so just 
A->relationship->dict of B->relationship->set of C, association proxy from A to 
B.cs.



> 
> 
> On Mar 10, 1:55 pm, Eric Ongerth  wrote:
>> Ach, I did it again... proceeded as if column_mapped_collection and
>> attribute_mapped_collection provide collection classes that are dicts
>> of lists.  No, they're just dicts of scalar values!
>> 
>> Again and again I need dicts of lists.  They seem to really fit the
>> data that I tend to work with; A's related to C's indexed by B's (the
>> B typically indicating the *type* of relationship between the A and
>> the C).
>> 
>> Eventually I need to work my own implementation of dict-of-lists and
>> dict-of-dicts collection classes up to full strength so I can share
>> them as a recipe or extension or something.
>> 
>> On Mar 10, 1:02 pm, Eric Ongerth  wrote:
>> 
>>> I make occasional use of alternate collection classes for my
>>> sqlalchemy relationships.  For example, column_mapped_collection.  The
>>> existing collection classes in sqlalchemy.orm.collections work really
>>> well for me; I have a lot of relational data that very naturally
>>> belongs in dicts rather than lists because it makes sense as one thing
>>> indexed or mapped by another.
>> 
>>> I find that I often need to add an item to one of these dict-mapped
>>> collections and would rather not have to check whether the key already
>>> exists in the dict.  So I'm doing, essentially:
>>> collection.setdefault(key, []).append(value).
>> 
>>> As a matter of syntactic sugar (which alternative collection classes
>>> already are, anyway) I would like to have collection classes backed by
>>> dicts with the capability of python's defaultdict, so that I could
>>> write more simply, and with equivalent results:
>>> collection[key].append(value)
>> 
>>> I understand how to create my own collection class which will behave
>>> as desired.  My question is, has anyone already got code for this that
>>> I could borrow rather than reinvent the wheel?  Thanks in advance.  --
>>> Eric
> 
> -- 
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> To unsubscribe from this group, send email to 
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at 
> http://groups.google.com/group/sqlalchemy?hl=en.
> 

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Is there a way to generate this UPDATE statement?

2011-03-12 Thread Michael Bayer


On Mar 12, 2011, at 11:41 PM, Darren Yin wrote:

> The MySQL documentation has the following example syntax for multi-table 
> UPDATES:
> 
> UPDATE items, month SET items.price=month.price
> WHERE items.id=month.id;
> 
> Is there a way to generate that via SQLAlchemy? The closest thing to it I 
> could find in the documentation was the correlated update, but that doesn't 
> generate quite the same thing. Right now I'm just constructing the MySQL 
> statement by hand and executing, but I'd like to do it through SQLAlchemy if 
> I could.

MySQL's syntax is not standard SQL.   The usual way to do this is to use a 
correlated subquery:

UPDATE items set price=(select price from month where items.id=month.id)

SQLAlchemy:


items.update().values(price=select([month.c.price]).where(items.c.id==month.c.id).as_scalar())

ORM:


sess.query(Item).update(price=sess.query(Month.price).filter(Item.id==Month.id).as_scalar(),
 False)



> 
> -- 
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> To unsubscribe from this group, send email to 
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at 
> http://groups.google.com/group/sqlalchemy?hl=en.

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Is there a way to generate this UPDATE statement?

2011-03-12 Thread Darren Yin

The MySQL documentation has the
followingexample
syntax for multi-table UPDATES:

UPDATE items, month SET items.price=month.price
WHERE items.id=month.id;


Is there a way to generate that via SQLAlchemy? The closest thing to it I
could find in the documentation was the correlated
update,
but that doesn't generate quite the same thing. Right now I'm just
constructing the MySQL statement by hand and executing, but I'd like to do
it through SQLAlchemy if I could.

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] InvalidRequestError

2011-03-12 Thread Michael Bayer


On Mar 11, 2011, at 7:51 PM, AJAY PATTNI wrote:

> We use sqlalchemy 0.4.6 with Elixir 0.5.2 and zope.sqlalchemy
> 
> Every once a while we get this error(see below):
> In a previous thread somebody said, just 'rollback your session when
> the exception is raised'?
> 
> We use a global session as follows:
> 
> __session__ =
> scoped_session(sessionmaker(twophase=False,transactional=True,autoflush=True,extension=ZopeTransactionExtension()))
> 
> All the rest of the transactional machinery is handled by Elixiry
> Entity object.
> 
> So my question - how can we rollback in this situation?
> 
> We tried to do __session__.remove() which seems to work but then zope
> seems to have a problem after it completes the request.


A description of what this error means from the perspective of SQLAlchemy only, 
not that of zope.sqlalchemy for which you should consult their mailing list, is 
here:


http://www.sqlalchemy.org/trac/wiki/FAQ#Thetransactionisinactiveduetoarollbackinasubtransaction

However, note that version 0.4.6 of SQLAlchemy is extremely old, and the 
mechanics of session and transaction have been largely reworked since then.   
The basic idea that a rollback needs to occur is consistent in that version, 
however.

Its very likely that some artifact of zope.sqlalchemy is involved in the 
production of your error here.


> ==
> 2011-03-11T10:41:22 ERROR Zope.SiteErrorLog
> http://dragon.initiatesystems.com:8080/ati/txm_inject_orm
> Traceback (innermost last):
>  Module ZPublisher.Publish, line 121, in publish
>  Module Zope2.App.startup, line 238, in commit
>  Module transaction._manager, line 89, in commit
>  Module transaction._transaction, line 329, in commit
>  Module transaction._transaction, line 446, in _commitResources
>  Module zope.sqlalchemy.datamanager, line 76, in tpc_vote
>  Module sqlalchemy.orm.session, line 263, in commit
>  Module sqlalchemy.orm.session, line 176, in _assert_is_open
> InvalidRequestError: The transaction is closed
> =
> 
> Appreciate any help I can get on this.
> 
> 
> Mar 4 12:56:23 pbrk4.den05.accenx.com [err]
> https://acm.accenx.com/mapsrvcluster/ati/acm/tdm/group/19150/broker/25150/site/705077435/processMsg#012Traceback
> (innermost last):#012 Module ZPublisher.Publish, line 115, in
> publish#012 Module ZPublisher.mapply, line 88, in mapply#012 Module
> ZPublisher.Publish, line 41, in call_object#012 Module
> Shared.DC.Scripts.Bindings, line 313, in __call__#012 Module
> Shared.DC.Scripts.Bindings, line 350, in _bindAndExec#012 Module
> Products.PythonScripts.PythonScript, line 326, in _exec#012 Module
> None, line 90, in processMsg#012 -  processMsg used for /ati/acm/tdm/group/19150/broker/25150/site/
> 705077435>#012 - Line 90#012 Module Shared.DC.Scripts.Bindings, line
> 313, in __call__#012 Module Shared.DC.Scripts.Bindings, line 350, in
> _bindAndExec#012 Module Products.PythonScripts.PythonScript, line 326,
> in _exec#012 Module None, line 131, in translate#012 -  at /ati/acm/tdm/group/19150/broker/25150/site/705077435/outbound/
> translate>#012 - Line 131#012 Module Shared.DC.Scripts.Bindings, line
> 313, in __call__#012 Module Shared.DC.Scripts.Bindings, line 350, in
> _bindAndExec#012 Module Products.PythonScripts.PythonScript, line 326,
> in _exec#012 Module None, line 99, in callTxm#012 -  ati/acm/tdm/group/19150/broker/25150/site/705077435/outbound/
> callTxm>#012 - Line 99#012 Module Shared.DC.Scripts.Bindings, line
> 313, in __call__#012 Module Shared.DC.Scripts.Bindings, line 350, in
> _bindAndExec#012 Module Products.PythonScripts.PythonScript, line 326,
> in _exec#012 Module None, line 44, in txm_ampiWrapperOrm#012 -
>  BrokerMapFunctions/TxmFunctions/txm_ampiWrapperOrm>#012 - Line 44#012
> Module Shared.DC.Scripts.Bindings, line 313, in __call__#012 Module
> Shared.DC.Scripts.Bindings, line 350, in _bindAndExec#012 Module
> Products.PythonScripts.PythonScript, line 326, in _exec#012 Module
> None, line 22, in getTransactionTypes#012 -  tdm/group/19150/broker/25150/BrokerMapFunctions/TxmFunctions/
> getTransactionTypes>#012 - Line 22#012 Module Products.txm.txm, line
> 537, in txmQuery#012 Module Products.txm.txm_functions, line 139, in
> __init__#012 Module elixir.entity, line 854, in get_by#012 Module
> sqlalchemy.orm.query, line 907, in first#012 Module
> sqlalchemy.orm.query, line 935, in __iter__#012 Module
> sqlalchemy.orm.session, line 768, in _autoflush#012 Module
> sqlalchemy.orm.session, line 786, in flush#012 Module
> sqlalchemy.orm.unitofwork, line 230, in flush#012 Module
> sqlalchemy.orm.session, line 499, in begin#012 Module
> sqlalchemy.orm.session, line 178, in _begin#012 Module
> sqlalchemy.orm.session, line 166, in
> _assert_is_active#012InvalidRequestError: The transaction is inactive
> due to a rollback in a subtransaction and should be closed
> =
> 
> -- 
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To

Re: [sqlalchemy] Commit many queries at once

2011-03-12 Thread Michael Bayer


On Mar 11, 2011, at 6:41 PM, eddy wrote:

> Like in session we commit all changes in one commit is there any way
> we can execute different queries at one.
> 
> for eg
> q1 = some.table update.query1
> q2 = some.table.update.query2
> 
> instead of updating table one at a time like running
> q1.execute()
> q2.execute()
> 
> can i add these to a session and commit all at once ??


if you have a Session, using Session.execute() with a statement object will 
emit the SQL within the transaction of that session:


http://www.sqlalchemy.org/docs/orm/session.html#using-sql-expressions-with-sessions

if you aren't using the ORM or a Session, Connection/Transaction achieves this 
result:

http://www.sqlalchemy.org/docs/core/connections.html#using-transactions

> 
> thanks in advance
> 
> -- 
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> To unsubscribe from this group, send email to 
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at 
> http://groups.google.com/group/sqlalchemy?hl=en.
> 

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] InvalidRequestError

2011-03-12 Thread AJAY PATTNI

We use sqlalchemy 0.4.6 with Elixir 0.5.2 and zope.sqlalchemy

Every once a while we get this error(see below):
In a previous thread somebody said, just 'rollback your session when
the exception is raised'?

We use a global session as follows:

__session__ =
scoped_session(sessionmaker(twophase=False,transactional=True,autoflush=True,extension=ZopeTransactionExtension()))

All the rest of the transactional machinery is handled by Elixiry
Entity object.

So my question - how can we rollback in this situation?

We tried to do __session__.remove() which seems to work but then zope
seems to have a problem after it completes the request.
==
2011-03-11T10:41:22 ERROR Zope.SiteErrorLog
http://dragon.initiatesystems.com:8080/ati/txm_inject_orm
Traceback (innermost last):
  Module ZPublisher.Publish, line 121, in publish
  Module Zope2.App.startup, line 238, in commit
  Module transaction._manager, line 89, in commit
  Module transaction._transaction, line 329, in commit
  Module transaction._transaction, line 446, in _commitResources
  Module zope.sqlalchemy.datamanager, line 76, in tpc_vote
  Module sqlalchemy.orm.session, line 263, in commit
  Module sqlalchemy.orm.session, line 176, in _assert_is_open
InvalidRequestError: The transaction is closed
=

Appreciate any help I can get on this.


Mar 4 12:56:23 pbrk4.den05.accenx.com [err]
https://acm.accenx.com/mapsrvcluster/ati/acm/tdm/group/19150/broker/25150/site/705077435/processMsg#012Traceback
(innermost last):#012 Module ZPublisher.Publish, line 115, in
publish#012 Module ZPublisher.mapply, line 88, in mapply#012 Module
ZPublisher.Publish, line 41, in call_object#012 Module
Shared.DC.Scripts.Bindings, line 313, in __call__#012 Module
Shared.DC.Scripts.Bindings, line 350, in _bindAndExec#012 Module
Products.PythonScripts.PythonScript, line 326, in _exec#012 Module
None, line 90, in processMsg#012 - #012 - Line 90#012 Module Shared.DC.Scripts.Bindings, line
313, in __call__#012 Module Shared.DC.Scripts.Bindings, line 350, in
_bindAndExec#012 Module Products.PythonScripts.PythonScript, line 326,
in _exec#012 Module None, line 131, in translate#012 - #012 - Line 131#012 Module Shared.DC.Scripts.Bindings, line
313, in __call__#012 Module Shared.DC.Scripts.Bindings, line 350, in
_bindAndExec#012 Module Products.PythonScripts.PythonScript, line 326,
in _exec#012 Module None, line 99, in callTxm#012 - #012 - Line 99#012 Module Shared.DC.Scripts.Bindings, line
313, in __call__#012 Module Shared.DC.Scripts.Bindings, line 350, in
_bindAndExec#012 Module Products.PythonScripts.PythonScript, line 326,
in _exec#012 Module None, line 44, in txm_ampiWrapperOrm#012 -
#012 - Line 44#012
Module Shared.DC.Scripts.Bindings, line 313, in __call__#012 Module
Shared.DC.Scripts.Bindings, line 350, in _bindAndExec#012 Module
Products.PythonScripts.PythonScript, line 326, in _exec#012 Module
None, line 22, in getTransactionTypes#012 - #012 - Line 22#012 Module Products.txm.txm, line
537, in txmQuery#012 Module Products.txm.txm_functions, line 139, in
__init__#012 Module elixir.entity, line 854, in get_by#012 Module
sqlalchemy.orm.query, line 907, in first#012 Module
sqlalchemy.orm.query, line 935, in __iter__#012 Module
sqlalchemy.orm.session, line 768, in _autoflush#012 Module
sqlalchemy.orm.session, line 786, in flush#012 Module
sqlalchemy.orm.unitofwork, line 230, in flush#012 Module
sqlalchemy.orm.session, line 499, in begin#012 Module
sqlalchemy.orm.session, line 178, in _begin#012 Module
sqlalchemy.orm.session, line 166, in
_assert_is_active#012InvalidRequestError: The transaction is inactive
due to a rollback in a subtransaction and should be closed
=

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Is there a way to generate this UPDATE statement?

Re: [sqlalchemy] Re: Has anyone already got a collection_class mod that uses defaultdict?

Re: [sqlalchemy] Is there a way to generate this UPDATE statement?

[sqlalchemy] Is there a way to generate this UPDATE statement?

Re: [sqlalchemy] InvalidRequestError

Re: [sqlalchemy] Commit many queries at once

[sqlalchemy] InvalidRequestError

7 matches

Site Navigation

Mail list logo

Footer information