Hello everyone,
I encountered a surprising behavior of the postres specific insert when
using UniqueConstraint.
A test code :
from sqlalchemy import Column, Integer, String, UniqueConstraint,
create_engine
from sqlalchemy.dialects.postgresql import insert as pg_insert
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class TestClass(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
in connection(self)
345 try:
--> 346 return self.__connection
347 except AttributeError:
AttributeError: 'Connection' object has no attribute
'_Connection__connection'
During handling 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 except BaseException as e:
--> 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
in reraise(tp, value, tb, cause)
186 raise value.with_traceback(tb)
--> 187 raise value
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 except BaseException as e:
/home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py
in connection(self)
350 except BaseException as e:
--> 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
in reraise(tp, value, tb, cause)
186 raise value.with_traceback(tb)
--> 187 raise value
188
/home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py
in connection(self)
348 try:
--> 349 return self._revalidate_connection()
350 except BaseException as e:
/home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py
in _revalidate_connection(self)
428 return self.__connection
--> 429 raise exc.ResourceClosedError("This Connection is closed")
430
ResourceClosedError: This Connection is closed
During handling 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
in connection(self)
345 try:
--> 346 return self.__connection
347 except AttributeError:
AttributeError: 'Connection' object has no attribute
'_Connection__connection'
During handling of the above exception, another exception occurred:
ResourceClosedError Traceback (most recent call last)
<ipython-input-2-02fbc1f7d6d4> in <module>()
14 TestClass.metadata.create_all(engine)
15 query = pg_insert(TestClass).values(a=1, b='b').
on_conflict_do_nothing(constraint='const1')
---> 16 engine.execute(query)
17 engine.execute(query)
/home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py
in execute(self, statement, *multiparams, **params)
2054
2055 connection = self.contextual_connect(close_with_result=True)
-> 2056 return connection.execute(statement, *multiparams, **params)
2057
2058 def scalar(self, statement, *multiparams, **params):
/home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py
in execute(self, object, *multiparams, **params)
943 raise exc.ObjectNotExecutableError(object)
944 else:
--> 945 return meth(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 if self.supports_execution:
--> 263 return connection._execute_clauseelement(self,
multiparams, params)
264 else:
265 raise exc.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 if self._has_events or self.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 if context.should_autocommit and self._root.__transaction is
None:
-> 1209 self._root._commit_impl(autocommit=True)
1210
1211 if result._soft_closed and self.should_close_with_result:
/home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py
in _commit_impl(self, autocommit)
724 finally:
725 if not self.__invalid and \
--> 726 self.connection._reset_agent is self.
__transaction:
727 self.connection._reset_agent = None
728 self.__transaction = None
/home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py
in connection(self)
349 return self._revalidate_connection()
350 except BaseException as e:
--> 351 self._handle_dbapi_exception(e, None, None, None,
None)
352
353 def get_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
in reraise(tp, value, tb, cause)
185 if value.__traceback__ is not tb:
186 raise value.with_traceback(tb)
--> 187 raise value
188
189 else:
/home/test/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py
in connection(self)
347 except AttributeError:
348 try:
--> 349 return self._revalidate_connection()
350 except BaseException as e:
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 return self.__connection
--> 429 raise exc.ResourceClosedError("This Connection is closed")
430
431 @property
ResourceClosedError: This Connection is close
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.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
