On Tue, Sep 12, 2017 at 1:14 PM, dirk.biesinger <dirk.biesin...@gmail.com> wrote: > Mike, > > I'll post two stacks: > the first one is just calling the engine.connect():
yup different error than before. Add pool_reset_on_return=None to your create_engine: e = create_engine(...., pool_reset_on_return=None) then try again. > the second one (look for a row of #####) is when calling the df.to_sql() > function. > > 2017-09-12 17:08:23,572 INFO sqlalchemy.engine.base.Engine SELECT > SERVERPROPERTY('ProductVersion') > 2017-09-12 17:08:23,573 INFO sqlalchemy.engine.base.Engine () > 2017-09-12 17:08:23,627 INFO sqlalchemy.engine.base.Engine SELECT > schema_name() > 2017-09-12 17:08:23,627 INFO sqlalchemy.engine.base.Engine () > 2017-09-12 17:08:24,056 INFO sqlalchemy.engine.base.Engine SELECT CAST('test > plain returns' AS VARCHAR(60)) AS anon_1 > 2017-09-12 17:08:24,057 INFO sqlalchemy.engine.base.Engine () > 2017-09-12 17:08:24,117 INFO sqlalchemy.engine.base.Engine SELECT CAST('test > unicode returns' AS NVARCHAR(60)) AS anon_1 > 2017-09-12 17:08:24,117 INFO sqlalchemy.engine.base.Engine () > --------------------------------------------------------------------------- > Empty Traceback (most recent call last) > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/pool.py in > _do_get(self) > 1121 wait = use_overflow and self._overflow >= > self._max_overflow > -> 1122 return self._pool.get(wait, self._timeout) > 1123 except sqla_queue.Empty: > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/util/queue.py > in get(self, block, timeout) > 144 if self._empty(): > --> 145 raise Empty > 146 elif timeout is None: > > Empty: > > During handling of the above exception, another exception occurred: > > ProgrammingError Traceback (most recent call last) > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/engine/base.py > in _wrap_pool_connect(self, fn, connection) > 2146 try: > -> 2147 return fn() > 2148 except dialect.dbapi.Error as e: > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/pool.py in > unique_connection(self) > 327 """ > --> 328 return _ConnectionFairy._checkout(self) > 329 > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/pool.py in > _checkout(cls, pool, threadconns, fairy) > 765 if not fairy: > --> 766 fairy = _ConnectionRecord.checkout(pool) > 767 > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/pool.py in > checkout(cls, pool) > 515 def checkout(cls, pool): > --> 516 rec = pool._do_get() > 517 try: > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/pool.py in > _do_get(self) > 1137 with util.safe_reraise(): > -> 1138 self._dec_overflow() > 1139 else: > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py > in __exit__(self, type_, value, traceback) > 65 if not self.warn_only: > ---> 66 compat.reraise(exc_type, exc_value, exc_tb) > 67 else: > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/util/compat.py > in reraise(tp, value, tb, cause) > 186 raise value.with_traceback(tb) > --> 187 raise value > 188 > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/pool.py in > _do_get(self) > 1134 try: > -> 1135 return self._create_connection() > 1136 except: > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/pool.py in > _create_connection(self) > 332 > --> 333 return _ConnectionRecord(self) > 334 > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/pool.py in > __init__(self, pool, connect) > 460 if connect: > --> 461 self.__connect(first_connect_check=True) > 462 self.finalize_callback = deque() > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/pool.py in > __connect(self, first_connect_check) > 660 for_modify(pool.dispatch).\ > --> 661 exec_once(self.connection, self) > 662 if pool.dispatch.connect: > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/event/attr.py > in exec_once(self, *args, **kw) > 245 try: > --> 246 self(*args, **kw) > 247 finally: > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/event/attr.py > in __call__(self, *args, **kw) > 255 for fn in self.listeners: > --> 256 fn(*args, **kw) > 257 > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py > in go(*arg, **kw) > 1330 once_fn = once.pop() > -> 1331 return once_fn(*arg, **kw) > 1332 > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/engine/strategies.py > in first_connect(dbapi_connection, connection_record) > 180 c._execution_options = util.immutabledict() > --> 181 dialect.initialize(c) > 182 event.listen(pool, 'first_connect', first_connect, > once=True) > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/connectors/pyodbc.py > in initialize(self, connection) > 164 # run other initialization which asks for user name, etc. > --> 165 super(PyODBCConnector, self).initialize(connection) > 166 > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/dialects/mssql/base.py > in initialize(self, connection) > 1741 def initialize(self, connection): > -> 1742 super(MSDialect, self).initialize(connection) > 1743 self._setup_version_attributes() > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/engine/default.py > in initialize(self, connection) > 265 > --> 266 self.do_rollback(connection.connection) > 267 > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/engine/default.py > in do_rollback(self, dbapi_connection) > 439 def do_rollback(self, dbapi_connection): > --> 440 dbapi_connection.rollback() > 441 > > ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Driver 13 for SQL > Server][SQL Server]111214;An attempt to complete a transaction has failed. > No corresponding transaction found. (111214) (SQLEndTran)') > > The above exception was the direct cause of the following exception: > > ProgrammingError Traceback (most recent call last) > <ipython-input-10-bd43083eb2bb> in <module>() > 16 dbapi_connection.autocommit = True > 17 #cnxn = pyodbc.connect(connection_str) > ---> 18 engn.connect() > 19 #df.to_sql(tbl_server_out, engn, if_exists='append', index=False) > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/engine/base.py > in connect(self, **kwargs) > 2089 """ > 2090 > -> 2091 return self._connection_cls(self, **kwargs) > 2092 > 2093 def contextual_connect(self, close_with_result=False, **kwargs): > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/engine/base.py > in __init__(self, engine, connection, close_with_result, _branch_from, > _execution_options, _dispatch, _has_events) > 88 else: > 89 self.__connection = connection \ > ---> 90 if connection is not None else > engine.raw_connection() > 91 self.__transaction = None > 92 self.__savepoint_seq = 0 > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/engine/base.py > in raw_connection(self, _connection) > 2175 """ > 2176 return self._wrap_pool_connect( > -> 2177 self.pool.unique_connection, _connection) > 2178 > 2179 > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/engine/base.py > in _wrap_pool_connect(self, fn, connection) > 2149 if connection is None: > 2150 Connection._handle_dbapi_exception_noconnection( > -> 2151 e, dialect, self) > 2152 else: > 2153 util.reraise(*sys.exc_info()) > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/engine/base.py > in _handle_dbapi_exception_noconnection(cls, e, dialect, engine) > 1463 util.raise_from_cause( > 1464 sqlalchemy_exception, > -> 1465 exc_info > 1466 ) > 1467 else: > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/util/compat.py > in raise_from_cause(exception, exc_info) > 201 exc_type, exc_value, exc_tb = exc_info > 202 cause = exc_value if exc_value is not exception else None > --> 203 reraise(type(exception), exception, tb=exc_tb, cause=cause) > 204 > 205 if py3k: > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/util/compat.py > in reraise(tp, value, tb, cause) > 184 value.__cause__ = cause > 185 if value.__traceback__ is not tb: > --> 186 raise value.with_traceback(tb) > 187 raise value > 188 > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/engine/base.py > in _wrap_pool_connect(self, fn, connection) > 2145 dialect = self.dialect > 2146 try: > -> 2147 return fn() > 2148 except dialect.dbapi.Error as e: > 2149 if connection is None: > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/pool.py in > unique_connection(self) > 326 > 327 """ > --> 328 return _ConnectionFairy._checkout(self) > 329 > 330 def _create_connection(self): > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/pool.py in > _checkout(cls, pool, threadconns, fairy) > 764 def _checkout(cls, pool, threadconns=None, fairy=None): > 765 if not fairy: > --> 766 fairy = _ConnectionRecord.checkout(pool) > 767 > 768 fairy._pool = pool > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/pool.py in > checkout(cls, pool) > 514 @classmethod > 515 def checkout(cls, pool): > --> 516 rec = pool._do_get() > 517 try: > 518 dbapi_connection = rec.get_connection() > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/pool.py in > _do_get(self) > 1136 except: > 1137 with util.safe_reraise(): > -> 1138 self._dec_overflow() > 1139 else: > 1140 return self._do_get() > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py > in __exit__(self, type_, value, traceback) > 64 self._exc_info = None # remove potential circular > references > 65 if not self.warn_only: > ---> 66 compat.reraise(exc_type, exc_value, exc_tb) > 67 else: > 68 if not compat.py3k and self._exc_info and > self._exc_info[1]: > > /home/saravji/anaconda3/lib/python3.6/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/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/pool.py in > _do_get(self) > 1133 if self._inc_overflow(): > 1134 try: > -> 1135 return self._create_connection() > 1136 except: > 1137 with util.safe_reraise(): > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/pool.py in > _create_connection(self) > 331 """Called by subclasses to create a new ConnectionRecord.""" > 332 > --> 333 return _ConnectionRecord(self) > 334 > 335 def _invalidate(self, connection, exception=None): > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/pool.py in > __init__(self, pool, connect) > 459 self.__pool = pool > 460 if connect: > --> 461 self.__connect(first_connect_check=True) > 462 self.finalize_callback = deque() > 463 > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/pool.py in > __connect(self, first_connect_check) > 659 pool.dispatch.first_connect.\ > 660 for_modify(pool.dispatch).\ > --> 661 exec_once(self.connection, self) > 662 if pool.dispatch.connect: > 663 pool.dispatch.connect(self.connection, self) > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/event/attr.py > in exec_once(self, *args, **kw) > 244 if not self._exec_once: > 245 try: > --> 246 self(*args, **kw) > 247 finally: > 248 self._exec_once = True > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/event/attr.py > in __call__(self, *args, **kw) > 254 fn(*args, **kw) > 255 for fn in self.listeners: > --> 256 fn(*args, **kw) > 257 > 258 def __len__(self): > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py > in go(*arg, **kw) > 1329 if once: > 1330 once_fn = once.pop() > -> 1331 return once_fn(*arg, **kw) > 1332 > 1333 return go > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/engine/strategies.py > in first_connect(dbapi_connection, connection_record) > 179 _has_events=False) > 180 c._execution_options = util.immutabledict() > --> 181 dialect.initialize(c) > 182 event.listen(pool, 'first_connect', first_connect, > once=True) > 183 > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/connectors/pyodbc.py > in initialize(self, connection) > 163 > 164 # run other initialization which asks for user name, etc. > --> 165 super(PyODBCConnector, self).initialize(connection) > 166 > 167 def _dbapi_version(self): > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/dialects/mssql/base.py > in initialize(self, connection) > 1740 > 1741 def initialize(self, connection): > -> 1742 super(MSDialect, self).initialize(connection) > 1743 self._setup_version_attributes() > 1744 > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/engine/default.py > in initialize(self, connection) > 264 self._description_decoder = self.description_encoding = > None > 265 > --> 266 self.do_rollback(connection.connection) > 267 > 268 def on_connect(self): > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/engine/default.py > in do_rollback(self, dbapi_connection) > 438 > 439 def do_rollback(self, dbapi_connection): > --> 440 dbapi_connection.rollback() > 441 > 442 def do_commit(self, dbapi_connection): > > ProgrammingError: (pyodbc.ProgrammingError) ('42000', '[42000] > [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]111214;An attempt to > complete a transaction has failed. No corresponding transaction found. > (111214) (SQLEndTran)') > > In [ ]: > > ################################################################# > > > 2017-09-12 17:12:13,839 INFO sqlalchemy.engine.base.Engine SELECT > SERVERPROPERTY('ProductVersion') > 2017-09-12 17:12:13,840 INFO sqlalchemy.engine.base.Engine () > 2017-09-12 17:12:13,881 INFO sqlalchemy.engine.base.Engine SELECT > schema_name() > 2017-09-12 17:12:13,882 INFO sqlalchemy.engine.base.Engine () > 2017-09-12 17:12:14,195 INFO sqlalchemy.engine.base.Engine SELECT CAST('test > plain returns' AS VARCHAR(60)) AS anon_1 > 2017-09-12 17:12:14,196 INFO sqlalchemy.engine.base.Engine () > 2017-09-12 17:12:14,238 INFO sqlalchemy.engine.base.Engine SELECT CAST('test > unicode returns' AS NVARCHAR(60)) AS anon_1 > 2017-09-12 17:12:14,239 INFO sqlalchemy.engine.base.Engine () > --------------------------------------------------------------------------- > Empty Traceback (most recent call last) > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/pool.py in > _do_get(self) > 1121 wait = use_overflow and self._overflow >= > self._max_overflow > -> 1122 return self._pool.get(wait, self._timeout) > 1123 except sqla_queue.Empty: > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/util/queue.py > in get(self, block, timeout) > 144 if self._empty(): > --> 145 raise Empty > 146 elif timeout is None: > > Empty: > > During handling of the above exception, another exception occurred: > > ProgrammingError Traceback (most recent call last) > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/engine/base.py > in _wrap_pool_connect(self, fn, connection) > 2146 try: > -> 2147 return fn() > 2148 except dialect.dbapi.Error as e: > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/pool.py in > connect(self) > 386 if not self._use_threadlocal: > --> 387 return _ConnectionFairy._checkout(self) > 388 > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/pool.py in > _checkout(cls, pool, threadconns, fairy) > 765 if not fairy: > --> 766 fairy = _ConnectionRecord.checkout(pool) > 767 > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/pool.py in > checkout(cls, pool) > 515 def checkout(cls, pool): > --> 516 rec = pool._do_get() > 517 try: > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/pool.py in > _do_get(self) > 1137 with util.safe_reraise(): > -> 1138 self._dec_overflow() > 1139 else: > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py > in __exit__(self, type_, value, traceback) > 65 if not self.warn_only: > ---> 66 compat.reraise(exc_type, exc_value, exc_tb) > 67 else: > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/util/compat.py > in reraise(tp, value, tb, cause) > 186 raise value.with_traceback(tb) > --> 187 raise value > 188 > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/pool.py in > _do_get(self) > 1134 try: > -> 1135 return self._create_connection() > 1136 except: > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/pool.py in > _create_connection(self) > 332 > --> 333 return _ConnectionRecord(self) > 334 > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/pool.py in > __init__(self, pool, connect) > 460 if connect: > --> 461 self.__connect(first_connect_check=True) > 462 self.finalize_callback = deque() > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/pool.py in > __connect(self, first_connect_check) > 660 for_modify(pool.dispatch).\ > --> 661 exec_once(self.connection, self) > 662 if pool.dispatch.connect: > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/event/attr.py > in exec_once(self, *args, **kw) > 245 try: > --> 246 self(*args, **kw) > 247 finally: > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/event/attr.py > in __call__(self, *args, **kw) > 255 for fn in self.listeners: > --> 256 fn(*args, **kw) > 257 > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py > in go(*arg, **kw) > 1330 once_fn = once.pop() > -> 1331 return once_fn(*arg, **kw) > 1332 > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/engine/strategies.py > in first_connect(dbapi_connection, connection_record) > 180 c._execution_options = util.immutabledict() > --> 181 dialect.initialize(c) > 182 event.listen(pool, 'first_connect', first_connect, > once=True) > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/connectors/pyodbc.py > in initialize(self, connection) > 164 # run other initialization which asks for user name, etc. > --> 165 super(PyODBCConnector, self).initialize(connection) > 166 > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/dialects/mssql/base.py > in initialize(self, connection) > 1741 def initialize(self, connection): > -> 1742 super(MSDialect, self).initialize(connection) > 1743 self._setup_version_attributes() > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/engine/default.py > in initialize(self, connection) > 265 > --> 266 self.do_rollback(connection.connection) > 267 > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/engine/default.py > in do_rollback(self, dbapi_connection) > 439 def do_rollback(self, dbapi_connection): > --> 440 dbapi_connection.rollback() > 441 > > ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Driver 13 for SQL > Server][SQL Server]111214;An attempt to complete a transaction has failed. > No corresponding transaction found. (111214) (SQLEndTran)') > > The above exception was the direct cause of the following exception: > > ProgrammingError Traceback (most recent call last) > <ipython-input-11-f3810ddb1488> in <module>() > 17 #cnxn = pyodbc.connect(connection_str) > 18 #engn.connect() > ---> 19 df.to_sql(tbl_server_out, engn, if_exists='append', index=False) > > /home/saravji/anaconda3/lib/python3.6/site-packages/pandas/core/generic.py > in to_sql(self, name, con, flavor, schema, if_exists, index, index_label, > chunksize, dtype) > 1343 sql.to_sql(self, name, con, flavor=flavor, schema=schema, > 1344 if_exists=if_exists, index=index, > index_label=index_label, > -> 1345 chunksize=chunksize, dtype=dtype) > 1346 > 1347 def to_pickle(self, path, compression='infer'): > > /home/saravji/anaconda3/lib/python3.6/site-packages/pandas/io/sql.py in > to_sql(frame, name, con, flavor, schema, if_exists, index, index_label, > chunksize, dtype) > 469 pandas_sql.to_sql(frame, name, if_exists=if_exists, index=index, > 470 index_label=index_label, schema=schema, > --> 471 chunksize=chunksize, dtype=dtype) > 472 > 473 > > /home/saravji/anaconda3/lib/python3.6/site-packages/pandas/io/sql.py in > to_sql(self, frame, name, if_exists, index, index_label, schema, chunksize, > dtype) > 1148 if_exists=if_exists, > index_label=index_label, > 1149 schema=schema, dtype=dtype) > -> 1150 table.create() > 1151 table.insert(chunksize) > 1152 if (not name.isdigit() and not name.islower()): > > /home/saravji/anaconda3/lib/python3.6/site-packages/pandas/io/sql.py in > create(self) > 584 > 585 def create(self): > --> 586 if self.exists(): > 587 if self.if_exists == 'fail': > 588 raise ValueError("Table '%s' already exists." % > self.name) > > /home/saravji/anaconda3/lib/python3.6/site-packages/pandas/io/sql.py in > exists(self) > 572 > 573 def exists(self): > --> 574 return self.pd_sql.has_table(self.name, self.schema) > 575 > 576 def sql_schema(self): > > /home/saravji/anaconda3/lib/python3.6/site-packages/pandas/io/sql.py in > has_table(self, name, schema) > 1176 self.connectable.dialect.has_table, > 1177 name, > -> 1178 schema or self.meta.schema, > 1179 ) > 1180 > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/engine/base.py > in run_callable(self, callable_, *args, **kwargs) > 2042 > 2043 """ > -> 2044 with self.contextual_connect() as conn: > 2045 return conn.run_callable(callable_, *args, **kwargs) > 2046 > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/engine/base.py > in contextual_connect(self, close_with_result, **kwargs) > 2110 return self._connection_cls( > 2111 self, > -> 2112 self._wrap_pool_connect(self.pool.connect, None), > 2113 close_with_result=close_with_result, > 2114 **kwargs) > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/engine/base.py > in _wrap_pool_connect(self, fn, connection) > 2149 if connection is None: > 2150 Connection._handle_dbapi_exception_noconnection( > -> 2151 e, dialect, self) > 2152 else: > 2153 util.reraise(*sys.exc_info()) > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/engine/base.py > in _handle_dbapi_exception_noconnection(cls, e, dialect, engine) > 1463 util.raise_from_cause( > 1464 sqlalchemy_exception, > -> 1465 exc_info > 1466 ) > 1467 else: > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/util/compat.py > in raise_from_cause(exception, exc_info) > 201 exc_type, exc_value, exc_tb = exc_info > 202 cause = exc_value if exc_value is not exception else None > --> 203 reraise(type(exception), exception, tb=exc_tb, cause=cause) > 204 > 205 if py3k: > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/util/compat.py > in reraise(tp, value, tb, cause) > 184 value.__cause__ = cause > 185 if value.__traceback__ is not tb: > --> 186 raise value.with_traceback(tb) > 187 raise value > 188 > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/engine/base.py > in _wrap_pool_connect(self, fn, connection) > 2145 dialect = self.dialect > 2146 try: > -> 2147 return fn() > 2148 except dialect.dbapi.Error as e: > 2149 if connection is None: > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/pool.py in > connect(self) > 385 """ > 386 if not self._use_threadlocal: > --> 387 return _ConnectionFairy._checkout(self) > 388 > 389 try: > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/pool.py in > _checkout(cls, pool, threadconns, fairy) > 764 def _checkout(cls, pool, threadconns=None, fairy=None): > 765 if not fairy: > --> 766 fairy = _ConnectionRecord.checkout(pool) > 767 > 768 fairy._pool = pool > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/pool.py in > checkout(cls, pool) > 514 @classmethod > 515 def checkout(cls, pool): > --> 516 rec = pool._do_get() > 517 try: > 518 dbapi_connection = rec.get_connection() > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/pool.py in > _do_get(self) > 1136 except: > 1137 with util.safe_reraise(): > -> 1138 self._dec_overflow() > 1139 else: > 1140 return self._do_get() > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py > in __exit__(self, type_, value, traceback) > 64 self._exc_info = None # remove potential circular > references > 65 if not self.warn_only: > ---> 66 compat.reraise(exc_type, exc_value, exc_tb) > 67 else: > 68 if not compat.py3k and self._exc_info and > self._exc_info[1]: > > /home/saravji/anaconda3/lib/python3.6/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/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/pool.py in > _do_get(self) > 1133 if self._inc_overflow(): > 1134 try: > -> 1135 return self._create_connection() > 1136 except: > 1137 with util.safe_reraise(): > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/pool.py in > _create_connection(self) > 331 """Called by subclasses to create a new ConnectionRecord.""" > 332 > --> 333 return _ConnectionRecord(self) > 334 > 335 def _invalidate(self, connection, exception=None): > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/pool.py in > __init__(self, pool, connect) > 459 self.__pool = pool > 460 if connect: > --> 461 self.__connect(first_connect_check=True) > 462 self.finalize_callback = deque() > 463 > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/pool.py in > __connect(self, first_connect_check) > 659 pool.dispatch.first_connect.\ > 660 for_modify(pool.dispatch).\ > --> 661 exec_once(self.connection, self) > 662 if pool.dispatch.connect: > 663 pool.dispatch.connect(self.connection, self) > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/event/attr.py > in exec_once(self, *args, **kw) > 244 if not self._exec_once: > 245 try: > --> 246 self(*args, **kw) > 247 finally: > 248 self._exec_once = True > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/event/attr.py > in __call__(self, *args, **kw) > 254 fn(*args, **kw) > 255 for fn in self.listeners: > --> 256 fn(*args, **kw) > 257 > 258 def __len__(self): > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py > in go(*arg, **kw) > 1329 if once: > 1330 once_fn = once.pop() > -> 1331 return once_fn(*arg, **kw) > 1332 > 1333 return go > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/engine/strategies.py > in first_connect(dbapi_connection, connection_record) > 179 _has_events=False) > 180 c._execution_options = util.immutabledict() > --> 181 dialect.initialize(c) > 182 event.listen(pool, 'first_connect', first_connect, > once=True) > 183 > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/connectors/pyodbc.py > in initialize(self, connection) > 163 > 164 # run other initialization which asks for user name, etc. > --> 165 super(PyODBCConnector, self).initialize(connection) > 166 > 167 def _dbapi_version(self): > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/dialects/mssql/base.py > in initialize(self, connection) > 1740 > 1741 def initialize(self, connection): > -> 1742 super(MSDialect, self).initialize(connection) > 1743 self._setup_version_attributes() > 1744 > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/engine/default.py > in initialize(self, connection) > 264 self._description_decoder = self.description_encoding = > None > 265 > --> 266 self.do_rollback(connection.connection) > 267 > 268 def on_connect(self): > > /home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/engine/default.py > in do_rollback(self, dbapi_connection) > 438 > 439 def do_rollback(self, dbapi_connection): > --> 440 dbapi_connection.rollback() > 441 > 442 def do_commit(self, dbapi_connection): > > ProgrammingError: (pyodbc.ProgrammingError) ('42000', '[42000] > [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]111214;An attempt to > complete a transaction has failed. No corresponding transaction found. > (111214) (SQLEndTran)') > > > > > > On Tuesday, September 12, 2017 at 6:31:58 AM UTC-7, Mike Bayer wrote: >> >> On Mon, Sep 11, 2017 at 9:00 PM, Dirk Biesinger >> <dirk.bi...@gmail.com> wrote: >> > Mike, >> > >> > I have upgraded to 1.1.11 (specifically) after posting and have still >> > seen >> > the same error. I also used the event override you posted in issue >> > #3994. >> > Since these experiments I have upgraded to 1.1.13 and the issues >> > persist. >> >> I"m skeptical that either this is the same error and not a different >> one, or this is still the older version of the code somehow. >> >> Can you please provide the complete stack trace? no need for it to >> be an attachment. >> >> > >> > Unfortunately I can't give you permission to our database instance >> > (customer >> > policies) but am able to cooperate as much as possible. >> > Like said in my post, I need to write multiple pandas dataframes with >> > millions of rows. Not looking really forward to doing this with my own >> > function. ;-) >> > >> > >> > >> > Dirk Biesinger >> > >> > dirk.bi...@gmail.com | 206.349.9769 >> > >> > "Simplicity is the Mastery of Complexity" >> > >> > On Mon, Sep 11, 2017 at 4:45 PM, Mike Bayer <mik...@zzzcomputing.com> >> > wrote: >> >> >> >> On Mon, Sep 11, 2017 at 6:34 PM, dirk.biesinger >> >> <dirk.bi...@gmail.com> wrote: >> >> > I am encountering errors when trying to use the pd.to_sql function to >> >> > write >> >> > a dataframe to MS SQL Data Warehouse. >> >> > The connection works when NOT using sqlalchemy engines. >> >> > I can read dataframes as well as row-by-row via select statements >> >> > when I >> >> > use >> >> > pyodbc connections >> >> > I can write data via insert statements (as well as delete data) when >> >> > using >> >> > pyodbc. >> >> > However, when I try to connect using a sqlalchemy engine I run into a >> >> > string >> >> > of error messages starting with: >> >> > >> >> > ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] >> >> > [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Catalog view >> >> > 'dm_exec_sessions' is not supported in this version. (104385) >> >> > (SQLExecDirectW)") >> >> > >> >> > >> >> > I have searched online, and this exact error seems to have been >> >> > reported >> >> > / >> >> > evaluated in May of this year as issue #3994: >> >> > >> >> > >> >> > >> >> > >> >> > https://bitbucket.org/zzzeek/sqlalchemy/issues/3994/azure-sql-datawarehouse-basic >> >> >> >> the issue originally reported there is the one you are having, it was >> >> resolved as of 1.1.11. I have closed the issue as I would need new >> >> issues opened to deal with the subsequent issues that user was having. >> >> If you upgrade to 1.1.11, you should no longer receive an error >> >> about dm_exec_sessions because this error is caught and the next view, >> >> sys.dm_pdw_nodes_exec_sessions, is used. There are likely problems >> >> beyond that error message but they should not be that message. >> >> please open new issues to describe these error messages and keep in >> >> mind it is unlikely I can fix them unless someone gives me access to >> >> this database. >> >> >> >> >> >> > >> >> > >> >> > I could not find a solution to this, and I'd really dislike to do a >> >> > line-wise or blob insert statement (I'm working with multiple >> >> > datasets >> >> > that >> >> > each has a few million rows, so execution time is a consideration, >> >> > although >> >> > the result sets I'm getting are more like in the 100k lines area >> >> > each.) >> >> > >> >> > >> >> > I get the same error messages even when I replace the pd.to_sql >> >> > command >> >> > with >> >> > a simple engine.connect() >> >> > >> >> > >> >> > Enclosed my installed packages (packages.list) >> >> > >> >> > Enclosed the full traceback (traceback.txt) >> >> > >> >> > >> >> > This is the code I'm using: >> >> > >> >> > connection_string = >> >> > >> >> > >> >> > "mssql+pyodbc://<username>:<password>@<sqlhost>.database.windows.net:<port>/<database>?driver=ODBC+Driver+13+for+SQL+Server" >> >> > engn = sqlalchemy.engine.create_engine(connection_string, echo=True) >> >> > engn.connect() >> >> > >> >> > >> >> > I'm very well aware that MS SQL DataWarehouse behaves a bit >> >> > different, >> >> > so >> >> > I'm open for some experimenting to get this issue narrowed down. >> >> > >> >> > In case it matters: I'm running an ubuntu 16.04 VM on azure with >> >> > jupyter >> >> > notebook server and python 3.6.1. >> >> > >> >> > Best, >> >> > >> >> > DB >> >> > >> >> > -- >> >> > 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+...@googlegroups.com. >> >> > To post to this group, send email to sqlal...@googlegroups.com. >> >> > Visit this group at https://groups.google.com/group/sqlalchemy. >> >> > For more options, visit https://groups.google.com/d/optout. >> >> >> >> -- >> >> 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+...@googlegroups.com. >> >> To post to this group, send email to sqlal...@googlegroups.com. >> >> Visit this group at https://groups.google.com/group/sqlalchemy. >> >> For more options, visit https://groups.google.com/d/optout. >> > >> > >> > -- >> > 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+...@googlegroups.com. >> > To post to this group, send email to sqlal...@googlegroups.com. >> > Visit this group at https://groups.google.com/group/sqlalchemy. >> > For more options, visit https://groups.google.com/d/optout. > > -- > 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. -- 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.