Re: [sqlalchemy] 0.6 and c extensions
Le dimanche 21 mars 2010 à 02:10 -0700, drakkan a écrit : Hi, a really interesting feature in sa 0.6 are the c extensions, however I think they should be implemented using ctypes so if python ctypes extension is available (default in python =2.5 and available even for 2.4) the c extensions are used as default, ctypes cannot compile your own C code. -- 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] assert_unicode=True
Le Thu, 25 Feb 2010 21:44:51 +0100, Marcin Krol mrk...@gmail.com a écrit : SAWarning: Unicode type received non-unicode bind param value 'Jane Shmoe' param[key.encode(encoding)] = processors[key](compiled_params[key]) [...] I would need this to be error rather than warning just like docs say (well I'd rather have it crash then debug than write bad values into db) To turn warnings into errors you just have to issue the right settings to the standard warnings module: http://docs.python.org/library/warnings.html Regards Antoine. -- 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: joining sessions / two phase commit
Le dimanche 14 février 2010 à 17:47 +, Chris Withers a écrit : Wichert Akkerman wrote: On 2010-2-9 09:48, Chris Withers wrote: I know that zope's transaction package aims to do just this, I wonder if anyone's used that, or anything else, with SA to solve this problem? You mean ZODB's transaction package? it's actually now just a standalone package ;-) http://pypi.python.org/pypi/transaction I've found it to be an active nuisance. For example it will forbid the use of commit() on the session, forcing you to use transaction.commit() instead. And transaction.commit() has the annoying side effect that it will also purge the session afterwards, making all your objects unusable unless you go out of your way to fetch them again manually. (unfortunately, this package is used by TurboGears by default) Regards Antoine. -- 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: joining sessions / two phase commit
Le dimanche 14 février 2010 à 19:45 +0100, Wichert Akkerman a écrit : That is by design: zope.sqlalchemy (which is really the thing you are complaining about) forces you to commit the entire transaction. This is required to coordinate transactions between multiple participants in a transaction, prevent one of them from getting out of sync. I understand this is by design, but better again would have been a flag to disable it. And transaction.commit() has the annoying side effect that it will also purge the session afterwards, making all your objects unusable unless you go out of your way to fetch them again manually. Iirc it starts a new session, making sure that you do not leak things between transactions. Well, there are situations where you'd want to leak things between transactions... If SQLAlchemy itself doesn't enforce it, I'm not sure why the transaction package thinks it should. Regards Antoine. -- 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: joining sessions / two phase commit
Le dimanche 14 février 2010 à 19:58 +0100, Wichert Akkerman a écrit : On 2/14/10 19:54 , Antoine Pitrou wrote: Le dimanche 14 février 2010 à 19:45 +0100, Wichert Akkerman a écrit : That is by design: zope.sqlalchemy (which is really the thing you are complaining about) forces you to commit the entire transaction. This is required to coordinate transactions between multiple participants in a transaction, prevent one of them from getting out of sync. I understand this is by design, but better again would have been a flag to disable it. I have never seen a request for such a flag before, which makes me suspect it is not a widely requested feature. You are more than welcome to contribute such a flag to zope.sqlalchemy though. Well the primary use for it is that session.commit() doesn't expunge the session, while transaction.commit() does. So the real fix, or improvement, would be to change or at least make configurable transaction.commit()'s behaviour. If you are trying to put blame anywhere do it in the right place: the 'transaction' package does not do this. The SQLAlchemy transaction glue in the zope.sqlalchemy package does. Oh, sorry. That doesn't really change my point, though, since we are talking about use of transaction in an SQLAlchemy context. Regards Antoine. -- 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] session commit and the GIL
Le mardi 02 février 2010 à 01:08 -0800, Eyal Gordon a écrit : Hello, I'm running sqlalchemy on python 2.4.3, with postgresql. My application is multi-threaded, commits and queries are protected by a python thread lock. I suspect that when running session.commit(), the python global interpreter lock (GIL) remains owned by this thread until the commit completes, such that other threads can not run until the commit action completes (even thread that have nothing to do with the database). Touching any Python object requires holding the GIL. The GIL can only be released when issueing a query to the database (that query can be COMMIT of course), which I suppose the various database drivers already do (if they don't, you can certainly issue a bug/feature request to them). Antoine. -- 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] any way to pre cook a monster query?
Le mercredi 27 janvier 2010 à 12:31 -0500, Michael Bayer a écrit : Or, we can generate the compiled() object, which contains the SQL string as well as a lot of important metadata about the statement used when fetching results. But this is not possible without access to a dialect and changes for every dialect - so we can key the string off of the current dialect in a dictionary. But another super edge case, some inexperienced users create new engines on every request of their application - the dictionary would grow and they'd say we have a memory leak (this has actually happened). You could use a weak key dictionary, which would remove the entry as soon as all strong references to the dialect disappear. -- 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] Table as dictionary?
Le vendredi 15 janvier 2010 à 09:28 -0800, Nelson a écrit :
Hello SQLAlchemy experts,
I'd like to view the contents of a table object as a dictionary.
Example:
s = Table('sparrow', Column('type', String(50)) , Column('weight',
Integer), ... etc)
s.type = 'African'
s.weight = 32
Then I want to see / get a dictionary:
{'type': 'African, weight: 32, ... etc}
Easy way to do that I'm missing?
s.__dict__ ?
You'll have to filter out any private attributes set by SQLAlchemy.
--
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] pylons SQLAlchemy memory
Le lundi 11 janvier 2010 à 15:55 -0800, diana a écrit : And now for a question about a completely different app (no sharding, very simple). I haven't got a sufficient response from the pylons group, so I'm trying here. The question: http://groups.google.com/group/pylons-discuss/browse_thread/thread/cb48d0ea2b084159 Well if you only want to count entries, use Query.count(), not Query.all(). It will be much more efficient, both on the DB side and on the Python side. Even if you use the entries one by one but don't need to keep them in memory afterwards, just use the iterative form (`for row in query: ...`). Regardless, you are fetching 9 objects and witnessing a 160MB increase in memory. This gives approximately 1.7KB per objects. Depending on the size and complexity of each row this is not necessarily surprising. Python will generally not be as memory-efficient as hand-tailored structures written in C, since there is a lot of genericity and flexibility in most Python datatypes. Objects in general can be quite big, because they are based on dictionaries (dict objects) which are themselves big. As for releasing memory, try to call gc.collect() after you have released the last reference to the result set. I'd be a bit surprised if SQLAlchemy created reference cycles, though -- and would be inclined to consider it a bug ;-) -- 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] Sqlalchemy and python 3
Le mardi 05 janvier 2010 à 11:32 -0500, Michael Bayer a écrit : its likely a line which needs a PY3K/PY2K directive. Or simply replace it with `NoneType = type(None)`. -- 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: Sqlalchemy and python 3
The line that tries to import NoneType from the types module. I can't tell you which one exactly, I have never read the code, but it should be trivial :-) Le mardi 05 janvier 2010 à 10:07 -0800, batok a écrit : Replace what line? In properties.py ? On Jan 5, 10:42 am, Antoine Pitrou solip...@pitrou.net wrote: Le mardi 05 janvier 2010 à 11:32 -0500, Michael Bayer a écrit : its likely a line which needs a PY3K/PY2K directive. Or simply replace it with `NoneType = type(None)`. -- 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] Python Pickle Error - Can't pickle class 'sqlalchemy.orm.session.Session'
Does anyone have any idea how I can get around this Pickling error? Different ways to make your class picklable are described here: http://docs.python.org/library/pickle.html#pickling-and-unpickling-normal-class-instances You could for example write __getstate__ and __setstate__ methods such that the session / sessionmaker objects don't get pickled when pickling your class, and are recreated on the fly when unpickling. Regards Antoine. -- 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] base classes that have nothing to do with table inheritence
Le jeudi 17 décembre 2009 à 11:05 -0500, Michael Bayer a écrit :
Chris Withers wrote:
How should I create a class like this? This isn't about table
inheritance or the like and I'm *sure* I was told an easy solution for
this specific use case before, but I can't find it for the life of me
now...
Just gave a current status on this feature three days ago:
http://groups.google.com/group/sqlalchemy/browse_thread/thread/99812e0ca1f8cc7c#
Another, not pretty, solution would be to use `exec` with a predefined
string containing the declarations...
Yet another solution is something like:
class CommonColumns(object):
# Add here your column definitions
@classmethod
def update_locals(cls, locals):
locals.update((k, v) for (k, v) in cls.__dict__.items()
if not k.startswith('_'))
class X(Base):
# Add here the custom columns
# Inject the common columns
CommonColumns.update_locals(locals())
Or even:
class CommonColumns(object):
# Add here your column definitions
@classmethod
def update_locals(cls):
locals = sys._getframe(1).f_locals
locals.update((k, v) for (k, v) in cls.__dict__.items()
if not k.startswith('_'))
class X(Base):
# Add here the custom columns
# Inject the common columns
CommonColumns.update_locals()
--
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: performance issues
Hello, just in case you're not motivated to share mappings here, I would note that an incorrect placement of a flag like remote_side on a relation() may be causing this. I would have to produce anonymized mappings, but I will do so if it's useful. What do you mean by incorrect placement of a flag like `remote_side`? I do have one (exactly one) relation with a `remote_side` flag, but the class it is defined on isn't involved in the script I have timed here. (it is on a recursive relation) yet another thought, which again reveals how much easier this would be with some sample code - if you're in a loop that is calling query(), then changing things, then around again, you may have excessive triggering of autoflush going on, which also can result in lots of _save_obj() calls. Set the autoflush flag on your Session to be false, which you can do temporarily within a particular section or across the whole session. Calling commit() will issue a flush(), or alternatively you can call flush() every few thousand new records. Ah, a very useful tip. Setting autoflush to False does speed up things quite a bit (a sample run of the script is 2x faster). I haven't found how to enable it temporarily, though. I'm using a ScopedSession instance and if I call `db_session.configure (autoflush=False)`, or set the autoflush attribute, autoflushing still happens. An example: db_session sqlalchemy.orm.scoping.ScopedSession object at 0x2e467d0 db_session.autoflush = False lg = db_session.query(Logement)[0] [ SNIP long SELECT ] lg.tel1 = abc db_session.query(Zone)[0] 2009-12-17 00:11:35,161 INFO sqlalchemy.engine.base.Engine.0x...c550 UPDATE logement SET date_modif=%s, tel1=%s WHERE logement.id_logement = %s 2009-12-17 00:11:35,162 INFO sqlalchemy.engine.base.Engine.0x...c550 [datetime.datetime(2009, 12, 17, 0, 11, 35, 161436), 'abc', 4L] 2009-12-17 00:11:35,167 INFO sqlalchemy.engine.base.Engine.0x...c550 SELECT zone.id_zone AS zone_id_zone, zone.cp AS zone_cp FROM zone LIMIT 0, 1 2009-12-17 00:11:35,168 INFO sqlalchemy.engine.base.Engine.0x...c550 [] xxx.Zone object at 0x3309150 (as you see, an UPDATE is issued before the SELECT for the query) Regards Antoine. -- 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: performance issues
I would have to produce anonymized mappings, but I will do so if it's
What do you mean by incorrect placement of a flag like
`remote_side`? I do have one (exactly one) relation with a
`remote_side` flag, but the class it is defined on isn't involved in
the script I have timed here. (it is on a recursive relation)
if its backwards versus a backref that is also present, it can create an
overly complex dependency chain.
I'm not sure I understand. Does it also apply when the backref is
created implicitly, as follows:
class OT(DeclarativeBase):
[ snip ]
id_ot = Column(Integer, autoincrement=True, primary_key=True)
id_parent = Column(Integer,
ForeignKey('ot.id_ot', use_alter=True,
name='fk_ot_parent'))
OT.parent = relation(OT, remote_side=[OT.id_ot],
backref=backref('children'))
the configure only takes effect for the next session created. so if you
said Session.remove(); Session.configure(); Session(), that would do it.
Ok, thank you.
Regards
Antoine.
--
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] performance issues
Hello,
I've got some scripts which take quite a bit of time and I wanted to
investigate why they were so long. So I profiled them using cProfile
and I was quite surprised to get the following results. In short, SQL
queries themselves take less than 10% of the total time, and most of
the time is taken by SQLAlchemy itself (you'll notice than in the 50
first CPU consumers below, there isn't a single function of my own
application).
I should point out that this particular script doesn't manipulate that
many objects (a few thousands of them perhaps), and the queries aren't
complex at all. It would be good to know whether there are well known
techniques to help reduce the overall overhead of SQLAlchemy which, in
this case, is really bewildering.
Thank you
Antoine.
61307243 function calls (59670236 primitive calls) in 343.725
CPU seconds
Ordered by: internal time
List reduced from 1369 to 50 due to restriction 50
ncalls tottime percall cumtime percall filename:lineno
(function)
23771 20.1890.001 20.1890.001 {method 'query' of
'_mysql.connection' objects}
44559019.2440.0009.2440.000 {isinstance}
17361628.7060.000 25.2370.000 /usr/lib/python2.6/site-
packages/SQLAlchemy-0.5.4p2-py2.6.egg/sqlalchemy/orm/unitofwork.py:426
(collection)
237717.5670.000 17.3960.001 /usr/lib/python2.6/site-
packages/SQLAlchemy-0.5.4p2-py2.6.egg/sqlalchemy/engine/base.py:1432
(_init_metadata)
19854566.1110.000 10.0470.000 /usr/lib/python2.6/site-
packages/SQLAlchemy-0.5.4p2-py2.6.egg/sqlalchemy/orm/unitofwork.py:342
(polymorphic_tasks)
2885265.6660.000 23.9240.000 /usr/lib/python2.6/site-
packages/SQLAlchemy-0.5.4p2-py2.6.egg/sqlalchemy/sql/compiler.py:261
(visit_label)
533484.7850.000 69.4520.001 /usr/lib/python2.6/site-
packages/SQLAlchemy-0.5.4p2-py2.6.egg/sqlalchemy/orm/mapper.py:1190
(_save_obj)
3164574.6880.0007.3440.000 /usr/lib/python2.6/site-
packages/SQLAlchemy-0.5.4p2-py2.6.egg/sqlalchemy/sql/compiler.py:278
(visit_column)
28036664.3590.0004.3590.000 {method 'get' of 'dict'
objects}
712463/238084.1820.000 61.6720.003 /usr/lib/python2.6/
site-packages/SQLAlchemy-0.5.4p2-py2.6.egg/sqlalchemy/sql/visitors.py:
49(_compiler_dispatch)
237713.5950.0003.5950.000 {method 'store_result'
of '_mysql.connection' objects}
10956733.4990.0003.4990.000 {hasattr}
712463/238083.4460.000 61.8270.003 /usr/lib/python2.6/
site-packages/SQLAlchemy-0.5.4p2-py2.6.egg/sqlalchemy/sql/compiler.py:
216(process)
136803.3740.000 43.6710.003 /usr/lib/python2.6/site-
packages/SQLAlchemy-0.5.4p2-py2.6.egg/sqlalchemy/sql/compiler.py:510
(visit_select)
100913.2580.000 13.9260.001 /usr/lib/python2.6/site-
packages/SQLAlchemy-0.5.4p2-py2.6.egg/sqlalchemy/sql/compiler.py:671
(_get_colparams)
109533.1370.0007.8800.001 /usr/lib/python2.6/site-
packages/SQLAlchemy-0.5.4p2-py2.6.egg/sqlalchemy/topological.py:162
(_sort)
2461893.0550.0008.1270.000 /usr/lib/python2.6/site-
packages/SQLAlchemy-0.5.4p2-py2.6.egg/sqlalchemy/orm/interfaces.py:592
(create_row_processor)
492482.9920.0009.4420.000 /usr/lib/python2.6/site-
packages/SQLAlchemy-0.5.4p2-py2.6.egg/sqlalchemy/orm/mapper.py:1514
(_register_dependencies)
8320762.9510.0002.9510.000 /usr/lib/python2.6/site-
packages/SQLAlchemy-0.5.4p2-py2.6.egg/sqlalchemy/orm/state.py:49(dict)
2050642.8930.000 23.3480.000 /usr/lib/python2.6/site-
packages/SQLAlchemy-0.5.4p2-py2.6.egg/sqlalchemy/orm/unitofwork.py:655
(preexecute)
6514332.8310.0007.1880.000 {method 'decode' of
'str' objects}
3486812.7710.0006.4850.000 /usr/lib/python2.6/site-
packages/SQLAlchemy-0.5.4p2-py2.6.egg/sqlalchemy/orm/interfaces.py:589
(setup)
14232032.6870.0003.6810.000 {method 'add' of 'set'
objects}
8299562.6860.0004.0010.000 /usr/lib/python2.6/site-
packages/SQLAlchemy-0.5.4p2-py2.6.egg/sqlalchemy/orm/unitofwork.py:433
(elements)
18424802.6570.0002.6570.000 {method 'append' of
'list' objects}
2534922.6370.0006.2520.000 /usr/lib/python2.6/site-
packages/SQLAlchemy-0.5.4p2-py2.6.egg/sqlalchemy/orm/attributes.py:1241
(from_attribute)
5948702.6180.0003.5820.000 /usr/lib/python2.6/site-
packages/SQLAlchemy-0.5.4p2-py2.6.egg/sqlalchemy/orm/interfaces.py:568
(__get_context_strategy)
2919822.6170.000 13.5260.000 /usr/lib/python2.6/site-
packages/SQLAlchemy-0.5.4p2-py2.6.egg/sqlalchemy/orm/state.py:87
(get_history)
7547202.6130.0006.2510.000 /usr/lib/python2.6/site-
packages/SQLAlchemy-0.5.4p2-py2.6.egg/sqlalchemy/orm/unitofwork.py:437
(polymorphic_elements)
1185942.6080.0002.6080.000 /usr/lib/python2.6/site-
[sqlalchemy] The transaction is inactive due to a rollback in a subtransaction
Hi, I'm getting the following error in one of my unit tests, while I have never opened a subtransaction. The only thing that happens in that test is that a first db_session.flush() raises an IntegrityError (this is deliberate, because I test the generation of an unique number). Then a second db_session.flush() succeeds, but for some reason the subsequent commit raises this weird error. Could someone clarify why sqlalchemy thinks I've got a subtransaction in there? == ERROR: XXX.tests.test_logement.TestLogement.test_generer_decharge -- Traceback (most recent call last): File /usr/lib/python2.6/site-packages/nose-0.10.4-py2.6.egg/nose/ case.py, line 182, in runTest self.test(*self.arg) File /home/antoine/ftth/XXX/XXX/tests/test_logement.py, line 92, in test_generer_decharge db_session.commit() File /usr/lib/python2.6/site-packages/SQLAlchemy-0.5.4p2-py2.6.egg/ sqlalchemy/orm/scoping.py, line 121, in do return getattr(self.registry(), name)(*args, **kwargs) File /usr/lib/python2.6/site-packages/SQLAlchemy-0.5.4p2-py2.6.egg/ sqlalchemy/orm/session.py, line 673, in commit self.transaction.commit() File /usr/lib/python2.6/site-packages/SQLAlchemy-0.5.4p2-py2.6.egg/ sqlalchemy/orm/session.py, line 378, in commit self._prepare_impl() File /usr/lib/python2.6/site-packages/SQLAlchemy-0.5.4p2-py2.6.egg/ sqlalchemy/orm/session.py, line 351, in _prepare_impl self._assert_is_active() File /usr/lib/python2.6/site-packages/SQLAlchemy-0.5.4p2-py2.6.egg/ sqlalchemy/orm/session.py, line 247, in _assert_is_active The transaction is inactive due to a rollback in a InvalidRequestError: The transaction is inactive due to a rollback in a subtransaction. Issue rollback() to cancel the transaction. -- --~--~-~--~~~---~--~~ 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: The transaction is inactive due to a rollback in a subtransaction
On 12 juil, 23:37, Michael Bayer mike...@zzzcomputing.com wrote: the session always does things in a transaction , as does any DBAPI connection running in the default mode of operation as according to spec. Whether or not the Session leaves the transaction open after an individual execute() or flush() operation is dependent on the autocommit setting, which defaults to False - meaning the Session remains open within a transaction as soon as its used, which remains until either rollback(), commit(), or close() is called. After any flush() which fails, session.rollback() must be called. the subtransaction is specifically the internal begin/rollback() pair issued by the failed flush() call. Start reading fromhttp://www.sqlalchemy.org/docs/05/session.html#flushing on downwards to get more detail about how this works. Ok, thanks. Then I want to ask: why the complication? It looks like a weird and difficult to grasp behaviour. Why doesn't a failed flush() leave the session in a consistent state (either rolled back or not, but not something in-between)? By the way, I solved my problem by doing the UPDATE with an ORM-less query, so that I can catch the failure without rolling back the transaction. Perhaps flush() should have an optional flag to avoid rolling back on errors? This would help the cases where potential errors are expected, and the developer knows how to correct them. --~--~-~--~~~---~--~~ 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: The transaction is inactive due to a rollback in a subtransaction
Hello, Thanks for the lengthy (!) explanation. On Jul 13, 1:05 am, Michael Bayer mike...@zzzcomputing.com wrote: Postgres in particular has operations which, once failed, the transaction is not allowed to continue: Ouch. I'll have to change my strategy, then (I run unit tests with SQLite but the production backend is PostgreSQL-based). What SQLAlchemy offers that solves both issues is support of SAVEPOINT, via begin_nested(). Using begin_nested(), you can frame an operation that may potentially fail within a transaction, and then roll back to the point before its failure while maintaining the enclosing transaction. For some reason, calling rollback() or commit() after begin_nested() doesn't work (with SQLite): [...] File /home/antoine/ftth/XXX/XXX/model/logement.py, line 154, in generer_decharge db_session.commit() File /usr/lib/python2.6/site-packages/SQLAlchemy-0.5.4p2-py2.6.egg/ sqlalchemy/orm/scoping.py, line 121, in do return getattr(self.registry(), name)(*args, **kwargs) File /usr/lib/python2.6/site-packages/SQLAlchemy-0.5.4p2-py2.6.egg/ sqlalchemy/orm/session.py, line 673, in commit self.transaction.commit() File /usr/lib/python2.6/site-packages/SQLAlchemy-0.5.4p2-py2.6.egg/ sqlalchemy/orm/session.py, line 382, in commit t[1].commit() File /usr/lib/python2.6/site-packages/SQLAlchemy-0.5.4p2-py2.6.egg/ sqlalchemy/engine/base.py, line 1036, in commit self._do_commit() File /usr/lib/python2.6/site-packages/SQLAlchemy-0.5.4p2-py2.6.egg/ sqlalchemy/engine/base.py, line 1071, in _do_commit self.connection._release_savepoint_impl(self._savepoint, self._parent) File /usr/lib/python2.6/site-packages/SQLAlchemy-0.5.4p2-py2.6.egg/ sqlalchemy/engine/base.py, line 768, in _release_savepoint_impl self.engine.dialect.do_release_savepoint(self, name) File /usr/lib/python2.6/site-packages/SQLAlchemy-0.5.4p2-py2.6.egg/ sqlalchemy/engine/default.py, line 117, in do_release_savepoint connection.execute(expression.ReleaseSavepointClause(name)) File /usr/lib/python2.6/site-packages/SQLAlchemy-0.5.4p2-py2.6.egg/ sqlalchemy/engine/base.py, line 824, in execute return Connection.executors[c](self, object, multiparams, params) File /usr/lib/python2.6/site-packages/SQLAlchemy-0.5.4p2-py2.6.egg/ sqlalchemy/engine/base.py, line 874, in _execute_clauseelement return self.__execute_context(context) File /usr/lib/python2.6/site-packages/SQLAlchemy-0.5.4p2-py2.6.egg/ sqlalchemy/engine/base.py, line 896, in __execute_context self._cursor_execute(context.cursor, context.statement, context.parameters[0], context=context) File /usr/lib/python2.6/site-packages/SQLAlchemy-0.5.4p2-py2.6.egg/ sqlalchemy/engine/base.py, line 950, in _cursor_execute self._handle_dbapi_exception(e, statement, parameters, cursor, context) File /usr/lib/python2.6/site-packages/SQLAlchemy-0.5.4p2-py2.6.egg/ sqlalchemy/engine/base.py, line 931, in _handle_dbapi_exception raise exc.DBAPIError.instance(statement, parameters, e, connection_invalidated=is_disconnect) OperationalError: (OperationalError) no such savepoint: sa_savepoint_1 u'RELEASE SAVEPOINT sa_savepoint_1' [] --~--~-~--~~~---~--~~ 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: The transaction is inactive due to a rollback in a subtransaction
On Jul 13, 1:33 am, Michael Bayer mike...@zzzcomputing.com wrote: pysqlite doesn't seem to support SAVEPOINT out of the box. Jason Kirtland found out a little bit about it here: http://itsystementwicklung.de/pipermail/list-pysqlite/2009-June/00041... . But I'm not sure how that can be integrated with SQLA unless the pysqlite dialect issued an explicit BEGIN (and I thought perhaps Jason was going to look into integrating the correct sequences into the sqlite dialect). Ok, thanks again! Given all those compatibility problems, I'll fall back on the dirty (MyISAM-ish :-)) way: first do a SELECT to see if the value already exists, otherwise UPDATE it. Regards Antoine. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
