[sqlalchemy] Oracle 8i supports RETURNING clause (but sqla running 8i doesn't)
I'm connecting to a legacy database, some tables that I need to map in
sqla have no primary key. I've created views for such tables that map
the ROWID as the primary key. SQLAlchemy does a RETURNING clause when
I am connected to a more recent Oracle database, but not when
connected to 8i.
I've pasted the exact same code sqla compiled for newer oracle into an
8i session and it works fine:
{{{
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
SQL show release
release 801070400
}}}
{{{
...(set up variables in sqlplus)...
SQL INSERT INTO artransarchive (customerid, companyid, orderid,
adjinvoice, transactioncodeid, status, paymenttypeid, postdate,
amount, sourcesiteid, artype) VALUES
(:customerid, :companyid, :orderid, :adjinvoice, :transactioncodeid, :status,
:paymenttypeid, :postdate, :amount, :sourcesiteid, :artype)
RETURNING artransarchive.artransarchiveid INTO :ret_0
2 ;
1 row created.
SQL print ret_0
RET_0
-
zbAADAAARwDAAZ
SQL show release
release 801070400
SQL
}}}
Are there other problems that restricted the use of RETURNING with
oracle 8i, or was it believed to not be supported? (Note that I don't
believe Oracle 8.0 supports it... I read it was implemented in 8i)
Kent
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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] Oracle 8i supports RETURNING clause (but sqla running 8i doesn't)
On Aug 14, 2010, at 9:34 AM, Kent wrote:
I'm connecting to a legacy database, some tables that I need to map in
sqla have no primary key. I've created views for such tables that map
the ROWID as the primary key. SQLAlchemy does a RETURNING clause when
I am connected to a more recent Oracle database, but not when
connected to 8i.
I've pasted the exact same code sqla compiled for newer oracle into an
8i session and it works fine:
{{{
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
SQL show release
release 801070400
}}}
{{{
...(set up variables in sqlplus)...
SQL INSERT INTO artransarchive (customerid, companyid, orderid,
adjinvoice, transactioncodeid, status, paymenttypeid, postdate,
amount, sourcesiteid, artype) VALUES
(:customerid, :companyid, :orderid, :adjinvoice, :transactioncodeid, :status,
:paymenttypeid, :postdate, :amount, :sourcesiteid, :artype)
RETURNING artransarchive.artransarchiveid INTO :ret_0
2 ;
1 row created.
SQL print ret_0
RET_0
-
zbAADAAARwDAAZ
SQL show release
release 801070400
SQL
}}}
Are there other problems that restricted the use of RETURNING with
oracle 8i, or was it believed to not be supported? (Note that I don't
believe Oracle 8.0 supports it... I read it was implemented in 8i)
I probably went off of this:
http://www.lattimore.id.au/2006/04/06/oracle-returning-clause/ which says its
as of 10g.If we can get confirmation somewhere of when RETURNING was
introduced we can lower the auto-returning-on-insert threshhold; though its
not clear that its really a better method to use with oracle, so perhaps here
since you have some special use we would allow it to be enabled manually on any
version.
though if you're mapping to a view, these are writeable views ? i thought
those were only in myth.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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: SQLite: Rolling back DDL requests
On Aug 13, 2010, at 10:22 PM, Peter Hansen wrote:
On Jun 24, 1:23 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Jun 24, 2010, at 12:35 PM, Torsten Landschoff wrote:
That's what I thought but it does not cure my problem.
e.raw_connect().isolation_levelis in fact None, but the rollback is not
done anyway. :-(
its passing it through.dont know what else we can do there
I ran into this myself today and after struggling for a few hours I
came across this thread. I then ended up creating a test case to
reproduce the problem and it suggests the problem stems from some
behaviour of transactions or of the engine.base.Connection class. I
don't quite know what to make of it yet, but I think it shows that the
effect of passing it through is being counteracted by something
else. I ran this on Python 2.6.5 with SA 0.6.3 on Windows.
Postgresql and MS-SQL support transactional DDL, and I use this feature all the
time with SQLAlchemy which does nothing special to support them.
In addition, isolation_level=None with pysqlite disables the DBAPIs entire
transactional system. SQLAlchemy relies upon this system to handle proper
transactional behavior. Per their documentation, some statements will fail if
executed in a transaction - SQLAlchemy does not want to be involved in
reorganizing how the DBAPI wants to approach things, emitting manual 'begin'
and 'commit' strings, etc.
So I consider this a pysqlite bug, and they should offer a mode by which there
is no implicit commit for CREATE TABLE.
See http://docs.python.org/library/sqlite3.html#controlling-transactions
'''Test showing unexpected behaviour with sqlalchemy and sqlite3 and
isolation_level settings. The test creates one table outside of
a transaction (to detect potential problems with the test not
executing
properly) then creates a second table inside a transaction which it
immediately rolls back.
test01() fails basically as expected, since the sqlite3 DBAPI layer
appears to need isolation_level==None to properly roll back DDL
statements.
test02() succeeds because isolation_level==None now. This test and
test01()
both use a connection from engine.raw_connection(), which is a
sqlalchemy.pool._ConnectionFairy() object.
test03() tries again with isolation_level==None but using a
transaction
created from a connection returned by engine.connect(), which is a
sqlalchemy.engine.base.Connection() object. This test fails in spite
of the isolation_level setting.
'''
import unittest
from sqlalchemy import create_engine
DBPATH = 'sqlite://'
DDL = 'create table %s (id integer primary key)'
class TestCase(unittest.TestCase):
def setup(self, **kwargs):
self.engine = create_engine(DBPATH, **kwargs)
self.engine.execute(DDL % 'foo')
def rollback_raw(self):
conn = self.engine.raw_connection()
try:
conn.execute('begin')
conn.execute(DDL % 'bar')
raise ValueError
except ValueError:
conn.rollback()
def rollback_txn(self):
conn = self.engine.connect()
try:
txn = conn.begin()
conn.execute(DDL % 'bar')
raise ValueError
except ValueError:
txn.rollback()
def get_table_names(self):
conn = self.engine.raw_connection()
return [x[0] for x in conn.execute('select name from
sqlite_master')]
def test01(self):
'''use engine with default isolation_level'''
self.setup()
self.rollback_raw()
self.assertEqual(['foo'], self.get_table_names())
def test02(self):
'''use raw_connection with isolation_level None'''
self.setup(connect_args={'isolation_level': None})
self.rollback_raw()
self.assertEqual(['foo'], self.get_table_names())
def test03(self):
'''use transaction with isolation_level None'''
self.setup(connect_args={'isolation_level': None})
self.rollback_txn()
self.assertEqual(['foo'], self.get_table_names())
if __name__ == '__main__':
unittest.main()
--
Peter Hansen
Engenuity Corporation
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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 sqlalch...@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] Oracle 8i supports RETURNING clause (but sqla running 8i doesn't)
Not a myth, I'm using them (via sqla). Simple views (just one table)
oracle figures out writes to all alone. For more complex views (joins
of tables), you can use an INSTEAD OF trigger to write to the view
(just pl/sql trigger where you tell Oracle what you want to happen when
someone inserts/updates/deletes data in your view)... also using them,
not a myth.
Anyway, here is documentation from oracle.com, so at least we know 8.1.7
supported it:
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/state11c.htm#2066261
(I think they added fancier bulk returns and the like in later versions...)
Kent
On 8/14/2010 10:42 AM, Michael Bayer wrote:
On Aug 14, 2010, at 9:34 AM, Kent wrote:
I'm connecting to a legacy database, some tables that I need to map in
sqla have no primary key. I've created views for such tables that map
the ROWID as the primary key. SQLAlchemy does a RETURNING clause when
I am connected to a more recent Oracle database, but not when
connected to 8i.
I've pasted the exact same code sqla compiled for newer oracle into an
8i session and it works fine:
{{{
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
SQL show release
release 801070400
}}}
{{{
...(set up variables in sqlplus)...
SQL INSERT INTO artransarchive (customerid, companyid, orderid,
adjinvoice, transactioncodeid, status, paymenttypeid, postdate,
amount, sourcesiteid, artype) VALUES
(:customerid, :companyid, :orderid, :adjinvoice, :transactioncodeid, :status,
:paymenttypeid, :postdate, :amount, :sourcesiteid, :artype)
RETURNING artransarchive.artransarchiveid INTO :ret_0
2 ;
1 row created.
SQL print ret_0
RET_0
-
zbAADAAARwDAAZ
SQL show release
release 801070400
SQL
}}}
Are there other problems that restricted the use of RETURNING with
oracle 8i, or was it believed to not be supported? (Note that I don't
believe Oracle 8.0 supports it... I read it was implemented in 8i)
I probably went off of
this:http://www.lattimore.id.au/2006/04/06/oracle-returning-clause/ which says its as of
10g.If we can get confirmation somewhere of when RETURNING was introduced we can
lower the auto-returning-on-insert threshhold; though its not clear that its
really a better method to use with oracle, so perhaps here since you have some special
use we would allow it to be enabled manually on any version.
though if you're mapping to a view, these are writeable views ? i thought
those were only in myth.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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: SQLite: Rolling back DDL requests
On Aug 14, 11:07 am, Michael Bayer mike...@zzzcomputing.com wrote: In addition, isolation_level=None with pysqlite disables the DBAPIs entire transactional system. SQLAlchemy relies upon this system to handle proper transactional behavior. So I consider this a pysqlite bug, and they should offer a mode by which there is no implicit commit for CREATE TABLE. Michael, I don't doubt that you're right, but the puzzling thing (for me and, I think, Torsen) is that (if you set echo='debug') you see a BEGIN and ROLLBACK statement apparently being issued through the DBAPI layer, and if there's an implicit COMMIT going on we don't actually see it and don't understand why isolation_level=None isn't preventing it as it apparently does in the other case (test02). I suspect this is because with the logging on, we aren't actually seeing the operations performed, but merely sqlalchemy's report of what it is about to ask for. If the DBAPI is doing something under the covers, we don't know what and therefore can't find a workaround. To be clear, in test02 I believe we're effectively telling sqlite3 BEGIN; CREATE TABLE ...; ROLLBACK, and in test03 (with the same isolation_level setting) that's exactly what the logging reports is happening, yet the behaviour is different. (I was hoping to put some debugging in the DBAPI layer but unfortunately that's a C extension so harder to deal with. Maybe there's also some feature in sqlite3 itself (not the Python module) which can be configured for debug purposes to show what's really happening here.) -- Peter Hansen Engenuity Corporation -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] DetachedInstanceError
I'm obviously missing some key concept as regards the management of sessions. This seemingly simple usage fails: def get_new(): sess = Session() new = Something() # new orm object sess.add(new) sess.commit() sess.close() return new new = get_new() # request a new Something print new print new.id Those last 2 print lines throw: DetachedInstanceError: Instance Something at 0x2873ed0 is not bound to a Session; attribute refresh operation cannot proceed I seem to keep butting heads with the session needing to be a global eternal thing (opposite what the docs recommend). I could create another session and add 'new' to it, but that seems like a lot of boilerplate when all I wanted to do was get a bit of info from the returned object. Can someone explain how this is supposed to be done? Thanks, Michael -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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: SQLite: Rolling back DDL requests
On Aug 14, 2010, at 12:04 PM, Peter Hansen wrote: On Aug 14, 11:07 am, Michael Bayer mike...@zzzcomputing.com wrote: In addition, isolation_level=None with pysqlite disables the DBAPIs entire transactional system. SQLAlchemy relies upon this system to handle proper transactional behavior. So I consider this a pysqlite bug, and they should offer a mode by which there is no implicit commit for CREATE TABLE. Michael, I don't doubt that you're right, but the puzzling thing (for me and, I think, Torsen) is that (if you set echo='debug') you see a BEGIN and ROLLBACK statement apparently being issued through the DBAPI layer, The BEGIN is just a logging message emitted by SQLAlchemy. We don't ever send that string, and DBAPI has no begin() - this is a critical aspect of DBAPI that confuses everyone - it doesn't support ad-hoc transaction creation. There always a transaction going on as far as DBAPI is concerned, its just that the transaction might not matter if the connection has disabled transactions. ROLLBACK is a message we emit when we call connection.rollback(). and if there's an implicit COMMIT going on we don't actually see according to sqlite3's docs, there is, we of course can't see it unless you got sqlite3 to log its interactions. it and don't understand why isolation_level=None isn't preventing it as it apparently does in the other case (test02). test02 would appear to succeed because you are manipulating the transaction directly by emitting 'begin'. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] DetachedInstanceError
On Aug 14, 2010, at 12:53 PM, Michael Hipp wrote: I'm obviously missing some key concept as regards the management of sessions. This seemingly simple usage fails: def get_new(): sess = Session() new = Something() # new orm object sess.add(new) sess.commit() sess.close() return new new = get_new() # request a new Something print new print new.id Those last 2 print lines throw: DetachedInstanceError: Instance Something at 0x2873ed0 is not bound to a Session; attribute refresh operation cannot proceed I seem to keep butting heads with the session needing to be a global eternal thing (opposite what the docs recommend). hehno, the session is completely ad hoc. What you're missing is that the objects associated with the session should also in most situations be treated as ad-hoc - they represent the state of data within a particular transaction. If you use them outside of a transaction, and not associated with a session that would otherwise have the ability to associate them with a transaction, they are considered to be detached. detached is described at: http://www.sqlalchemy.org/docs/session.html#quickie-intro-to-object-states Where you'll note that expired attributes cannot be loaded back from the database. Why are they expired ? Let's look at commit(): http://www.sqlalchemy.org/docs/session.html#committing Second paragraph. Another behavior of commit() is that by default it expires the state of all instances present after the commit is complete. Why does it do this ? Well, when we have a detached object, and there's no transaction going on (i.e. no connection that can query the DB), we know nothing about what is in the database at that point, so all state on the object is expired. After all, if it had id =12, but some other transaction has deleted row 12, that object is invalid. Without a transaction associated, it would be wrong for us to tell you otherwise. Because we don't know. Now lets assume you don't like this behavior, and your application is just set of operations at a time and nobody else is updating your row (assumptions SQLAlchemy has chosen not to make). Fine. Turn off expire_on_commit.Then when you detach your objects, all their attributes are still present, and you can access them freely. So what if we made this the default. What kinds of complaints, which btw we never get anymore, would we have then ? Well, we'd have (and we had, all the time) this complaint: sess1 = Session() x1 = sess1.query(X).first() x1.foo = 'bar' sess1.commit() sess2 = Session() x2 = sess2.query(X).first() x2.foo = 'bat' sess2.commit() # x1 is still present in the Session's identity map x1 = sess1.query(X).first() assert x1.foo == 'bat' # ugh SQLALCHEMY IS BROKEN ! so we default to the more transaction hugging behavior by default - where the error message you get is at least very straightforward, instead of a subtle effect like this one. Can someone explain how this is supposed to be done? When you work with mapped objects, you're working with your database. A Session() should be in place and a transaction is in progress.Its only if you want to store mapped objects in some kind of offline cache, or pass them to other usage contexts, that you'd want to keep detached objects around. And when you go to use a detached object, you put it back into a context where it again is a proxy to some ongoing database operation, i.e. put it in the session for the current operation - often this transfer of state is done via merge(), so that if the destination session already has the object in question present, it will reconcile the incoming state with what it already has. The load=False setting of merge() prevents the usage of a SELECT from loading existing state, if you are working with long term immutable data and don't want the extra SELECT emitted. Alternatively, if you really want to pass around detached objects and make use of their detached state, even though that state may be stale or even deleted vs. what's in the database, you can disable expire_on_commit - if you are making ad-hoc sessions for usage in single functions, just pass it to that specific session constructor. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] DetachedInstanceError
Michael Thanks for taking the time to formulate a very thorough answer. (Now if I can make my understanding be as thorough.) If you could suffer me one more question ... it appears there are two* ways to handle this inside a method that may not know where it's called from. def alternative1(thing): sess = Session() sess.merge(thing) thing.name = Foo sess.commit() sess.close() def alternative2(thing, sess=None): if sess is None: sess = Session() sess.merge(thing) thing.name = Foo sess.commit() # incomplete, must do sess.close() Am I getting anywhere close? Can either one be said to be better? Again, thanks. Michael * For now, I'm taking it as an article of faith that I should stay away from expire_on_commit at least until I better understand the implications. On 8/14/2010 12:38 PM, Michael Bayer wrote: On Aug 14, 2010, at 12:53 PM, Michael Hipp wrote: I'm obviously missing some key concept as regards the management of sessions. This seemingly simple usage fails: def get_new(): sess = Session() new = Something() # new orm object sess.add(new) sess.commit() sess.close() return new new = get_new() # request a new Something print new print new.id Those last 2 print lines throw: DetachedInstanceError: Instance Something at 0x2873ed0 is not bound to a Session; attribute refresh operation cannot proceed I seem to keep butting heads with the session needing to be a global eternal thing (opposite what the docs recommend). hehno, the session is completely ad hoc. What you're missing is that the objects associated with the session should also in most situations be treated as ad-hoc - they represent the state of data within a particular transaction. If you use them outside of a transaction, and not associated with a session that would otherwise have the ability to associate them with a transaction, they are considered to be detached. detached is described at: http://www.sqlalchemy.org/docs/session.html#quickie-intro-to-object-states Where you'll note that expired attributes cannot be loaded back from the database. Why are they expired ? Let's look at commit(): http://www.sqlalchemy.org/docs/session.html#committing Second paragraph. Another behavior of commit() http://www.sqlalchemy.org/docs/reference/orm/sessions.html#sqlalchemy.orm.session.Session.commit is that by default it expires the state of all instances present after the commit is complete. Why does it do this ? Well, when we have a detached object, and there's no transaction going on (i.e. no connection that can query the DB), we know nothing about what is in the database at that point, so all state on the object is expired. After all, if it had id =12, but some other transaction has deleted row 12, that object is invalid. Without a transaction associated, it would be wrong for us to tell you otherwise. Because we don't know. Now lets assume you don't like this behavior, and your application is just set of operations at a time and nobody else is updating your row (assumptions SQLAlchemy has chosen not to make). Fine. Turn off expire_on_commit. Then when you detach your objects, all their attributes are still present, and you can access them freely. So what if we made this the default. What kinds of complaints, which btw we never get anymore, would we have then ? Well, we'd have (and we had, all the time) this complaint: sess1 = Session() x1 = sess1.query(X).first() x1.foo = 'bar' sess1.commit() sess2 = Session() x2 = sess2.query(X).first() x2.foo = 'bat' sess2.commit() # x1 is still present in the Session's identity map x1 = sess1.query(X).first() assert x1.foo == 'bat' # ugh SQLALCHEMY IS BROKEN ! so we default to the more transaction hugging behavior by default - where the error message you get is at least very straightforward, instead of a subtle effect like this one. Can someone explain how this is supposed to be done? When you work with mapped objects, you're working with your database. A Session() should be in place and a transaction is in progress. Its only if you want to store mapped objects in some kind of offline cache, or pass them to other usage contexts, that you'd want to keep detached objects around. And when you go to use a detached object, you put it back into a context where it again is a proxy to some ongoing database operation, i.e. put it in the session for the current operation - often this transfer of state is done via merge(), so that if the destination session already has the object in question present, it will reconcile the incoming state with what it already has. The load=False setting of merge() prevents the usage of a SELECT from loading existing state, if you are working with long term immutable data and don't want the extra SELECT emitted. Alternatively, if you really want to pass around detached objects and make use of their detached state, even though that state may be stale or even deleted vs. what's in the database, you can
Re: [sqlalchemy] DetachedInstanceError
On Aug 14, 2010, at 3:00 PM, Michael Hipp wrote: Michael Thanks for taking the time to formulate a very thorough answer. (Now if I can make my understanding be as thorough.) If you could suffer me one more question ... it appears there are two* ways to handle this inside a method that may not know where it's called from. def alternative1(thing): sess = Session() sess.merge(thing) thing.name = Foo sess.commit() sess.close() def alternative2(thing, sess=None): if sess is None: sess = Session() sess.merge(thing) thing.name = Foo sess.commit() # incomplete, must do sess.close() Am I getting anywhere close? Can either one be said to be better? If you're looking for that approach, it is usually: from sqlalchemy.orm import object_session def foo(thing): session = object_session(thing) if not session: local_sess = Session(expire_on_commit=False) local_sess.add(thing) thing.name = 'foo' if not session: local_sess.commit() What we've done above is, if the thing is already part of a session, we don't assume to know what the state of the transaction is - we don't commit it. If it was detached, and we made our own session, then we committed it. You can also make a decorator that does the same: import decorator # pypi package @decorator def force_a_session(fn, item): session = object_session(item) if not session: local_sess = Session(expire_on_commit=False) local_sess.add(item) try: try: return fn(item) finally: if not session: local_sess.commit() except: if not session: local_sess.rollback() raise The approach above may be fine for your needs but I wouldn't encourage it. The demarcation of transaction boundaries shouldn't be an ad-hoc thing IMO and granular functions shouldn't be deciding whether or not they are setting up a transaction. Again, thanks. Michael * For now, I'm taking it as an article of faith that I should stay away from expire_on_commit at least until I better understand the implications. On 8/14/2010 12:38 PM, Michael Bayer wrote: On Aug 14, 2010, at 12:53 PM, Michael Hipp wrote: I'm obviously missing some key concept as regards the management of sessions. This seemingly simple usage fails: def get_new(): sess = Session() new = Something() # new orm object sess.add(new) sess.commit() sess.close() return new new = get_new() # request a new Something print new print new.id Those last 2 print lines throw: DetachedInstanceError: Instance Something at 0x2873ed0 is not bound to a Session; attribute refresh operation cannot proceed I seem to keep butting heads with the session needing to be a global eternal thing (opposite what the docs recommend). hehno, the session is completely ad hoc. What you're missing is that the objects associated with the session should also in most situations be treated as ad-hoc - they represent the state of data within a particular transaction. If you use them outside of a transaction, and not associated with a session that would otherwise have the ability to associate them with a transaction, they are considered to be detached. detached is described at: http://www.sqlalchemy.org/docs/session.html#quickie-intro-to-object-states Where you'll note that expired attributes cannot be loaded back from the database. Why are they expired ? Let's look at commit(): http://www.sqlalchemy.org/docs/session.html#committing Second paragraph. Another behavior of commit() http://www.sqlalchemy.org/docs/reference/orm/sessions.html#sqlalchemy.orm.session.Session.commit is that by default it expires the state of all instances present after the commit is complete. Why does it do this ? Well, when we have a detached object, and there's no transaction going on (i.e. no connection that can query the DB), we know nothing about what is in the database at that point, so all state on the object is expired. After all, if it had id =12, but some other transaction has deleted row 12, that object is invalid. Without a transaction associated, it would be wrong for us to tell you otherwise. Because we don't know. Now lets assume you don't like this behavior, and your application is just set of operations at a time and nobody else is updating your row (assumptions SQLAlchemy has chosen not to make). Fine. Turn off expire_on_commit. Then when you detach your objects, all their attributes are still present, and you can access them freely. So what if we made this the default. What kinds of complaints, which btw we never get anymore, would we have then ? Well, we'd have (and we had, all the time) this complaint: sess1 = Session() x1 = sess1.query(X).first() x1.foo = 'bar' sess1.commit() sess2 = Session() x2 = sess2.query(X).first() x2.foo = 'bat'
Re: [sqlalchemy] DetachedInstanceError
On 8/14/2010 2:29 PM, Michael Bayer wrote: The approach above may be fine for your needs but I wouldn't encourage it. The demarcation of transaction boundaries shouldn't be an ad-hoc thing IMO and granular functions shouldn't be deciding whether or not they are setting up a transaction. Thanks. Yes, I was beginning to suspect such. Makes more sense to manage the session and commit/rollback issues at the top of the call stack. I was trying too hard to not have to pass the session down in argument lists, but looks like I should. Thanks, Michael -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] DetachedInstanceError
On Aug 14, 2010, at 3:54 PM, Michael Hipp wrote: On 8/14/2010 2:29 PM, Michael Bayer wrote: The approach above may be fine for your needs but I wouldn't encourage it. The demarcation of transaction boundaries shouldn't be an ad-hoc thing IMO and granular functions shouldn't be deciding whether or not they are setting up a transaction. Thanks. Yes, I was beginning to suspect such. Makes more sense to manage the session and commit/rollback issues at the top of the call stack. I was trying too hard to not have to pass the session down in argument lists, but looks like I should. well, you can either call object_session() in the methods to get the current object's session, or set up a registry like scoped_session that you access globally - I'd try to avoid having to pass the session around too. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
