blocks|key|2949725|text|一旦IntegrityError被引发，无论您是否捕获到错误，您正在工作的会话都将失效。当第二条错误消息指示您To+begin+a+new+transaction+with+this+Session,+first+issue+Session.rollback().继续使用会话时，您需要发出一个session.rollback()|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2949726|我不能确定，但我猜您或您的web框架正在尝试继续使用以某种方式引发IntegrityError的会话。我建议您在捕获异常后或在handle_elegantly函数中发出session.rollback()。|2949727|如果你运行下面的代码，你就会明白我的意思：|2949728|from+sqlalchemy+import+types
from+sqlalchemy+import+exc
from+sqlalchemy+import+create_engine
from+sqlalchemy.schema+import+Column
from+zope.sqlalchemy+import+ZopeTransactionExtension
from+sqlalchemy.ext.declarative+import+declarative_base
from+sqlalchemy.orm+import+sessionmaker,+scoped_session

Base+=+declarative_base()


class+User(Base):
++++__tablename__+=+'user'
++++name+=+Column(types.String,+primary_key=True)


def+handle_elegantly(name):
++++session+=+DBSession()
++++session.add(User(name=name))
++++session.flush()
++++print+'Exception+elegantly+handled!!\n'


def+pretend_view(request):
++++"""Pretend+view+in+a+Pyramid+application+using+pyramid_tm"""
++++session+=+DBSession()
++++user+=+User()
++++print+'\n-------Here+we+rollback+before+continuing+-------'
++++try:
++++++++session.add(user)
++++++++session.flush()
++++except+exc.IntegrityError:
++++++++session.rollback()
++++++++handle_elegantly('This+will+run+fine')

++++print+'\n-------+Here+we+do+not,+and+this+will+error+-------'
++++try:
++++++++session.add(user)
++++++++session.flush()
++++except+exc.IntegrityError:
++++++++handle_elegantly('Exception+will+be+raised')


if+__name__+==+'__main__':
++++engine+=+create_engine('sqlite://')
++++global+DBSession
++++DBSession+=+scoped_session(
++++++++sessionmaker(extension=ZopeTransactionExtension()))
++++DBSession.configure(bind=engine)
++++Base.metadata.bind+=+engine
++++Base.metadata.create_all()
++++pretend_view("dummy+request")|code-block|syntax|javascript|2949729|entityMap^0|2|E|1J|25|43|I|0|1R|G|2C|I|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]|$9|T|A|U|B|C]|$9|V|A|W|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|X|8|@$9|Y|A|Z|B|C]|$9|10|A|11|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|12|8|@]|D|@]|E|$]]|$1|J|3|K|5|L|7|13|8|@]|D|@]|E|$M|N]]|$1|O|3|-4|5|6|7|14|8|@]|D|@]|E|$]]]|P|$]]

As soon as the <code>IntegrityError</code> is raised, regardless of whether or not you've caught the error, the session you were working in is invalidated. As the second error message is instructing you, <code>To begin a new transaction with this Session, first issue Session.rollback().</code>, to continue using the session you'll need to issue a <code>session.rollback()</code>

I cannot say for sure, but I am guessing you or your web framework is attempting to continue using the session which raised the IntegrityError in some way. I recommend you issue a <code>session.rollback()</code> either after you catch the exception or in your <code>handle_elegantly</code> function. 

If you run the below you'll see what I mean:

<pre><code>from sqlalchemy import types
from sqlalchemy import exc
from sqlalchemy import create_engine
from sqlalchemy.schema import Column
from zope.sqlalchemy import ZopeTransactionExtension
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, scoped_session

Base = declarative_base()


class User(Base):
 __tablename__ = 'user'
 name = Column(types.String, primary_key=True)


def handle_elegantly(name):
 session = DBSession()
 session.add(User(name=name))
 session.flush()
 print 'Exception elegantly handled!!\n'


def pretend_view(request):
 """Pretend view in a Pyramid application using pyramid_tm"""
 session = DBSession()
 user = User()
 print '\n-------Here we rollback before continuing -------'
 try:
 session.add(user)
 session.flush()
 except exc.IntegrityError:
 session.rollback()
 handle_elegantly('This will run fine')

 print '\n------- Here we do not, and this will error -------'
 try:
 session.add(user)
 session.flush()
 except exc.IntegrityError:
 handle_elegantly('Exception will be raised')


if __name__ == '__main__':
 engine = create_engine('sqlite://')
 global DBSession
 DBSession = scoped_session(
 sessionmaker(extension=ZopeTransactionExtension()))
 DBSession.configure(bind=engine)
 Base.metadata.bind = engine
 Base.metadata.create_all()
 pretend_view("dummy request")
</code></pre>

blocks|key|152353|text|我在我的Flask应用程序中也有同样的需求，我像下面这样处理它，它可以工作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|152354|from+flask+import+Flask
from+flask_sqlalchemy+import+SQLAlchemy
from+sqlalchemy+import+exc

db+=+SQLAlchemy(Flask(__name__))

try:
+++++db.session.add(resource)
+++++return+db.session.commit()
except+exc.IntegrityError:
+++++db.session.rollback()|code-block|syntax|javascript|152355|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I have the same need in my Flask application, I handle it like below and it works:

<pre><code>from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import exc

db = SQLAlchemy(Flask(__name__))

try:
 db.session.add(resource)
 return db.session.commit()
except exc.IntegrityError:
 db.session.rollback()
</code></pre>

blocks|key|2737024|text|SQLALCHEMY_COMMIT_ON_TEARDOWN+=+False|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|2737025|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>SQLALCHEMY_COMMIT_ON_TEARDOWN = False
</code></pre>

blocks|key|153735|text|捕获错误后，必须添加session.rollback()：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|153736|try:
++++session.flush()
except+IntegrityError:
++++session.rollback()|code-block|syntax|javascript|153737|entityMap^0|A|I|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

You have to add <code>session.rollback()</code> after you catch an error:
<pre><code>try:
 session.flush()
except IntegrityError:
 session.rollback()
</code></pre>

Try as I might, I can't seem to catch the sqlalchemy IntegrityError correctly:

<pre><code>from sqlalchemy import exc

try:
 insert_record()
except exc.IntegrityError, exc:
 print exc # this is never called
 handle_elegantly() # this is never called
</code></pre>

As what one might expect:

<pre><code>IntegrityError: (IntegrityError) insert or update on table "my_table" 
 violates foreign key constraint "my_table_some_column_fkey"
</code></pre>

I've tried to explicitly:

<pre><code>from sqlalchemy.exc import IntegrityError
</code></pre>

UPDATE:

I found something that seems to fit what's happening here, where Integrity Error isn't thrown until the session is flushed to the db, and after the <code>try</code>/<code>except</code>blocks have been executed: <a href="https://stackoverflow.com/questions/11313935/trying-to-catch-integrity-error-with-sqlalchemy">Trying to catch integrity error with SQLAlchemy</a>

However, adding <code>session.flush()</code> in the <code>try</code> block yields an <code>InvalidRequestError</code>:

<pre><code>ERROR:root:This Session's transaction has been rolled back due to a previous 
 exception during flush. To begin a new transaction with this Session, 
 first issue Session.rollback(). 
 Original exception was: (IntegrityError)
</code></pre>

cannot catch SQLAlchemy IntegrityError

数据库

尽管我可能会尝试，但我似乎不能正确地捕获sqlalchemy IntegrityError：from sqlalchemy import exctry:    insert_record()except exc.IntegrityError, exc:    print exc # this is never call...

问无法捕获SQLAlchemy IntegrityError
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问无法捕获SQLAlchemy IntegrityErrorEN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问无法捕获SQLAlchemy IntegrityError
EN