On Wed, Feb 6, 2013 at 11:19 AM, Simon King <[email protected]> wrote:
> On Wed, Feb 6, 2013 at 2:05 AM, Jonathan Vanasco <[email protected]>
> wrote:
>> I use SqlAlchemy in a Pyramid app. All my models, connections, etc
>> are within and set up by Pyramid.
>>
>> I'm trying to do a maintenance script, and am a bit confused.
>>
>> In my script, thanks to a bootstraped commandline Pyramid feature, i
>> have a Session which can query objects. great.
>>
>> results = dbSession.query( model.core.Useraccount )\
>> .filter(\
>> model.core.Useraccount.last_login.op("IS")(None)
>> )\
>> .all()
>>
>> here's my problem. i want to do execute a command like this:
>>
>>
>> stmt = model.core.Useraccount.update()\
>> .where( model.core.Useraccount.id.in_( list_of_uids ) )\
>> .values( last_login = 'now()' )
>> connection.execute(stmt)
>>
>> I'm not actually updating the last_login field. just using this as an
>> example.
>>
>> So here are my questions:
>>
>> 1. can i get the connection out of a scoped session ?
>> 2. i'm getting an error that Useraccount doesn't have an 'update'
>> method.
>>
>> i based the example above on this
>> http://docs.sqlalchemy.org/en/rel_0_8/core/tutorial.html#correlated-updates
>>
>> I think this is all based on a portion of SqlAlchemy that my
>> environment doesn't have access to (in that everything is set up for
>> scoped sessions, not sql expressions). If so, is there another way to
>> execute an update query ?
>>
>
> The Session has "connection()" and "execute()" methods which will
> probably do what you want:
>
> http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#using-sql-expressions-with-sessions
>
Sorry, I missed your second question: Useraccount is a mapped class,
whereas the update() method you are trying to use exists on Table
objects. If you are using Declarative for your classes, the underlying
Table is accessible via Useraccount.__table__.
ua_table = Useraccount.__table__
stmt = (ua_table.update()
.where(ua_table.c.id.in_(list_of_uids))
.values(last_login=sa.func.now())
dbSession.execute(stmt)
The Query class also has an update() method which you could use as an
alternative:
http://docs.sqlalchemy.org/en/rel_0_8/orm/query.html#sqlalchemy.orm.query.Query.update
I can't see an example in the docs, but I think it would look
something like this:
nmatches = (dbSession.query( model.core.Useraccount )
.filter(model.core.Useraccount.id.in_(list_of_uids))
.update({'last_login': sa.func.now()}))
Simon
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
