Hello everyone,
I encountered a surprising behavior of the postres specific insert when
using UniqueConstraint.
A test code :
|
fromsqlalchemy importColumn,Integer,String,UniqueConstraint,create_engine
fromsqlalchemy.dialects.postgresql importinsert aspg_insert
fromsqlalchemy.ext.declarative importdeclarative_base
Base=declarative_base()
classTestClass(Base):
__tablename__ ='test'
id_ =Column('id_',Integer,primary_key=True)
a =Column('a',Integer)
b =Column('b',String)
__table_args__ =(UniqueConstraint('a','b',name='const'),)
engine =create_engine('mysql+mysqldb://db_user:db_pswd@db_host/db_name')
TestClass.metadata.create_all(engine)
query
=pg_insert(TestClass).values(a=1,b='b').on_conflict_do_nothing(constraint='const')
engine.execute(query)
engine.execute(query)
|
The exception returned :
|
---------------------------------------------------------------------------
AttributeError Traceback(most recent call last)
/home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py
inconnection(self)
345 try:
-->346 returnself.__connection
347 exceptAttributeError:
AttributeError:'Connection'objecthas noattribute '_Connection__connection'
Duringhandling of the above exception,another exception occurred:
ResourceClosedError Traceback(most recent call last)
/home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py
in_commit_impl(self,autocommit)
722 exceptBaseExceptionase:
-->723 self._handle_dbapi_exception(e,None,None,None,None)
724 finally:
/home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py
in_handle_dbapi_exception(self,e,statement,parameters,cursor,context)
1396 else:
->1397 util.reraise(*exc_info)
1398
/home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/util/compat.py
inreraise(tp,value,tb,cause)
186 raisevalue.with_traceback(tb)
-->187 raisevalue
188
/home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py
in_commit_impl(self,autocommit)
720 try:
-->721 self.engine.dialect.do_commit(self.connection)
722 exceptBaseExceptionase:
/home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py
inconnection(self)
350 exceptBaseExceptionase:
-->351 self._handle_dbapi_exception(e,None,None,None,None)
352
/home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py
in_handle_dbapi_exception(self,e,statement,parameters,cursor,context)
1396 else:
->1397 util.reraise(*exc_info)
1398
/home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/util/compat.py
inreraise(tp,value,tb,cause)
186 raisevalue.with_traceback(tb)
-->187 raisevalue
188
/home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py
inconnection(self)
348 try:
-->349 returnself._revalidate_connection()
350 exceptBaseExceptionase:
/home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py
in_revalidate_connection(self)
428 returnself.__connection
-->429 raiseexc.ResourceClosedError("This Connection is closed")
430
ResourceClosedError:ThisConnectionisclosed
Duringhandling of the above exception,another exception occurred:
AttributeError Traceback(most recent call last)
/home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py
inconnection(self)
345 try:
-->346 returnself.__connection
347 exceptAttributeError:
AttributeError:'Connection'objecthas noattribute '_Connection__connection'
Duringhandling of the above exception,another exception occurred:
ResourceClosedError Traceback(most recent call last)
<ipython-input-2-02fbc1f7d6d4>in<module>()
14TestClass.metadata.create_all(engine)
15query
=pg_insert(TestClass).values(a=1,b='b').on_conflict_do_nothing(constraint='const1')
--->16engine.execute(query)
17engine.execute(query)
/home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py
inexecute(self,statement,*multiparams,**params)
2054
2055 connection =self.contextual_connect(close_with_result=True)
->2056 returnconnection.execute(statement,*multiparams,**params)
2057
2058 defscalar(self,statement,*multiparams,**params):
/home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py
inexecute(self,object,*multiparams,**params)
943 raiseexc.ObjectNotExecutableError(object)
944 else:
-->945 returnmeth(self,multiparams,params)
946
947 def_execute_function(self,func,multiparams,params):
/home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/sql/elements.py
in_execute_on_connection(self,connection,multiparams,params)
261 def_execute_on_connection(self,connection,multiparams,params):
262 ifself.supports_execution:
-->263
returnconnection._execute_clauseelement(self,multiparams,params)
264 else:
265 raiseexc.ObjectNotExecutableError(self)
/home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py
in_execute_clauseelement(self,elem,multiparams,params)
1051 compiled_sql,
1052 distilled_params,
->1053 compiled_sql,distilled_params
1054 )
1055 ifself._has_events orself.engine._has_events:
/home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py
in_execute_context(self,dialect,constructor,statement,parameters,*args)
1207
1208 ifcontext.should_autocommit andself._root.__transaction
isNone:
->1209 self._root._commit_impl(autocommit=True)
1210
1211 ifresult._soft_closed andself.should_close_with_result:
/home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py
in_commit_impl(self,autocommit)
724 finally:
725 ifnotself.__invalid and\
-->726 self.connection._reset_agent isself.__transaction:
727 self.connection._reset_agent =None
728 self.__transaction =None
/home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py
inconnection(self)
349 returnself._revalidate_connection()
350 exceptBaseExceptionase:
-->351 self._handle_dbapi_exception(e,None,None,None,None)
352
353 defget_isolation_level(self):
/home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py
in_handle_dbapi_exception(self,e,statement,parameters,cursor,context)
1395 )
1396 else:
->1397 util.reraise(*exc_info)
1398
1399 finally:
/home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/util/compat.py
inreraise(tp,value,tb,cause)
185 ifvalue.__traceback__ isnottb:
186 raisevalue.with_traceback(tb)
-->187 raisevalue
188
189else:
/home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py
inconnection(self)
347 exceptAttributeError:
348 try:
-->349 returnself._revalidate_connection()
350 exceptBaseExceptionase:
351 self._handle_dbapi_exception(e,None,None,None,None)
/home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py
in_revalidate_connection(self)
427 self.__invalid =False
428 returnself.__connection
-->429 raiseexc.ResourceClosedError("This Connection is closed")
430
431 @property
ResourceClosedError:ThisConnectionisclose
|
I guess I did something wrong, but the error message seems completely
unrelated to the operation (A RessourceClosed error ?).
It is to note that it is the second execution of the query (or the first
execution on a table with the line from query already present) that
raise this exception.
The exception is not raised when we put a value to id_ (for example
values(a=1, b='b', id=1) ).
It was done with sqlalchemy 1.1.8 and postgres 9.6.2.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and
Verifiable Example. See http://stackoverflow.com/help/mcve for a full
description.
---
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 sqlalchemy+unsubscr...@googlegroups.com
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
