DBSession.connection().execute(...) not working

[Sanjay]

Hi,

I have a simple controller method like this:

@expose()
def insert_a_record(self):
DBSession.connection().execute(foo_table.insert({'user_id':2,
'org_id': 2}))
return "Successfu!"

This controller returns successfully, but in the database the record
is not inserted. Unable to understand why. Need help.

thanks,
Sanjay

[Glauco]

Sanjay ha scritto:

What's "DBSession" ?

If it isn't the session useg by TG than you must explicit the commit()

Gla

[Diez B. Roggisch]

Sanjay schrieb:

This looks like the problems I had a month or two ago:

http://www.nabble.com/Bug-in-zope.sqlalchemy-td22685504.html

It eventually led to me removing zope.transaction-handling from our
application stack, as even with the described solution it didn't solve
the problem for us.

Others have claimed that things work for them - dunno about that.

Diez

[Sanjay]

> > @expose()
> > def insert_a_record(self):
> >     DBSession.connection().execute(foo_table.insert({'user_id':2,
> > 'org_id': 2}))
> >     return "Successfu!"
>
> > This controller returns successfully, but in the database the record
> > is not inserted. Unable to understand why. Need help.

> What's "DBSession" ?
>
> If it isn't the session useg by TG than you must explicit the commit()

It is the DBSession used by TG. That is, in the model/__init__.py, I
have the lines:

maker = sessionmaker(autoflush=True, autocommit=False,
extension=ZopeTransactionExtension())
DBSession = scoped_session(maker)

and then, I have imported this in the controller:

from myproject.model import DBSession

In fact, I tried DBSession.commit() but got this error:

AssertionError: Transaction must be committed using the transaction
manager

[Sanjay]

> > I have a simple controller method like this:
>
> > @expose()
> > def insert_a_record(self):
> >     DBSession.connection().execute(foo_table.insert({'user_id':2,
> > 'org_id': 2}))
> >     return "Successfu!"
>
> > This controller returns successfully, but in the database the record
> > is not inserted. Unable to understand why. Need help.
>
> This looks like the problems I had a month or two ago:
>
> http://www.nabble.com/Bug-in-zope.sqlalchemy-td22685504.html
>
> It eventually led to me removing zope.transaction-handling from our
> application stack, as even with the described solution it didn't solve
> the problem for us.

It is not working for me either, where as in TG 1.0 similar case was
working perfectly. Would look for a solution in TG 2.0. Meanwhile, I
am a beginner and would request some information on how to remove
zope.transaction-handling from the application stack and what are the
side effects.

thanks,
Sanjay

[Christoph Zwerschke]

Diez B. Roggisch schrieb:

> http://www.nabble.com/Bug-in-zope.sqlalchemy-td22685504.html
>
> It eventually led to me removing zope.transaction-handling from our
> application stack, as even with the described solution it didn't solve
> the problem for us.
>
> Others have claimed that things work for them - dunno about that.

It works if you use the session for bulk deletes or updates. But it does
not work if you do inserts that way past the session.

-- Christoph

[Christoph Zwerschke]

Sanjay schrieb:

> @expose()
> def insert_a_record(self):
> DBSession.connection().execute(foo_table.insert({'user_id':2,
> 'org_id': 2}))
> return "Successfu!"

The problem here is that you're actually not using the db session here,
but doing things past the db session.

The right and much simpler way is to add your data records to the
session using DBSession.add().

In this case, your table looks like an association table between a User
and Org class. Usually the User class then has an orgs attribute which
is a list, and vice versa (see how it is implemented in the auth model).
You would then simply append the org to the user.orgs list.

-- Christoph

[Sanjay]

I am using dynamic_loader, .i.e. user.orgs is a query object and not a
list:

orgs = dynamic_loader('Org', secondary=foo_table)

Curious to get some suggestions in this case.

thanks,
Sanjay

[Diez B. Roggisch]

What does "past the session" mean here? Or better yet, how does one do the
bulk-*-stuff?

Diez

[Diez B. Roggisch]

What happens if you leave out the connection()-call? Directly using execute on
the session should be supported (and thus use the session)

Diez

[Christoph Zwerschke]

Diez B. Roggisch schrieb:

> What does "past the session" mean here?

What I meant is that you're executing an sql statement bypassing the
session. The session is not affected by that.

> Or better yet, how does one do the bulk-*-stuff?

For instance, you can do a bulk delete like this:

DBSession.query(User).filter(User.is_spammer == True).delete()

But that works only with deletes and updates, not with inserts.

-- Christoph

[Christoph Zwerschke]

Diez B. Roggisch schrieb:

> What happens if you leave out the connection()-call? Directly using execute on
> the session should be supported (and thus use the session)

This is only a shortcut and should not make any difference. It works,
but the problem is that zope.sqlalchemy does not notice that data was
modified and therefore does not commit the transaction.

It should works as soon as you add any other statement that marks the
session as dirty, like this dummy "bulk" delete:

@expose()
def insert_a_record(self):
DBSession.execute(foo_table.insert({'user_id':2, 'org_id': 2}))
DBSession.query(User).filter(User.user_id == None).delete()
return "Successful!"

-- Christoph

[Christoph Zwerschke]

Sanjay schrieb:

> I am using dynamic_loader, .i.e. user.orgs is a query object and not a
> list:
>
> orgs = dynamic_loader('Org', secondary=foo_table)
>
> Curious to get some suggestions in this case.

That works as well, you can append to that object as if it was a list
(since it's not a normal Query object but an "AppenderQuery" object).

-- Christoph

[Sanjay]

> @expose()
> def insert_a_record(self):
>    DBSession.execute(foo_table.insert({'user_id':2, 'org_id': 2}))
>    DBSession.query(User).filter(User.user_id == None).delete()
>    return "Successful!"

Thanks a lot! The idea of making the session dirty works.

Although the dummy line gave me the error "ArgumentError: Only
deletion via a single table query is currently supported," I did some
other thing to make the session dirty and it worked.

Sanjay

[Sanjay]

> > orgs = dynamic_loader('Org', secondary=foo_table)
>
> > Curious to get some suggestions in this case.
>
> That works as well, you can append to that object as if it was a list
> (since it's not a normal Query object but an "AppenderQuery" object).

Oh! Thanks a lot!.

Sanjay

[Sanjay]

However, I had to make the session dirty for this too.

Sanjay

[Christoph Zwerschke]

Sanjay schrieb:

Should not be necessary - it works for me without any dirty hacks.

In a quickstarted project I changed Permission.groups to
groups = dynamic_loader(Group, secondary=group_permission_table)
and then the following just worked:

@expose()
def insert_a_record(self):
p = DBSession.query(Permission).get(1)
g = DBSession.query(Group).get(1)
p.groups.append(g)
return "Successful!"

Tested with SA 0.5.4 and zope.sa 0.4.

-- Christoph

[Sanjay]

> Should not be necessary - it works for me without any dirty hacks.
>
> In a quickstarted project I changed Permission.groups to
> groups = dynamic_loader(Group, secondary=group_permission_table)
> and then the following just worked:
>
>     @expose()
>     def insert_a_record(self):
>         p = DBSession.query(Permission).get(1)
>         g = DBSession.query(Group).get(1)
>         p.groups.append(g)
>         return "Successful!"
>
> Tested with SA 0.5.4 and zope.sa 0.4.

I had tested it with SA 0.5.1 etc., the default that comes with TG2
rc1, and dirtying was necessary. Will try with TG latest version...

thanks,
Sanjay