pandas to MS SQL DataWarehouse (to_sql)

[dirk.biesinger]

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

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

[dirk.biesinger]

re-attaching the files

[Mike Bayer]

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.

[Dirk Biesinger]

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.

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"

> email to sqlalchemy+unsubscribe@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+unsubscribe@googlegroups.com.

[Mike Bayer]

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.

>> > 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.

[dirk.biesinger]

Mike,

I'll post two stacks:

the first one is just calling the engine.connect():

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)')

[dirk.biesinger]

And I just verified: version 1.1.13 is installed, and loaded for sure just checked via sqlalchemy.__version__

Just to be sure.

[Mike Bayer]

On Tue, Sep 12, 2017 at 1:14 PM, dirk.biesinger
<dirk.bi...@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.

[Dirk Biesinger]

when executing the engine.connect() with pool_reset_on_return=None set

I get this error stack:

2017-09-12 17:53:37,807 INFO sqlalchemy.engine.base.Engine SELECT  SERVERPROPERTY('ProductVersion')

2017-09-12 17:53:37,808 INFO sqlalchemy.engine.base.Engine ()

2017-09-12 17:53:37,849 INFO sqlalchemy.engine.base.Engine SELECT schema_name()

2017-09-12 17:53:37,850 INFO sqlalchemy.engine.base.Engine ()

2017-09-12 17:53:38,182 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1

2017-09-12 17:53:38,183 INFO sqlalchemy.engine.base.Engine ()

2017-09-12 17:53:38,230 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS NVARCHAR(60)) AS anon_1

2017-09-12 17:53:38,231 INFO sqlalchemy.engine.base.Engine ()

<ipython-input-13-a41175de1d5e> in <module>()

> email to sqlalchemy+unsubscribe@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+unsubscribe@googlegroups.com.

[Mike Bayer]

On Tue, Sep 12, 2017 at 1:56 PM, Dirk Biesinger
<dirk.bi...@gmail.com> wrote:
> when executing the engine.connect() with pool_reset_on_return=None set

OK there's a do_rollback in there that isn't controlled by this. So
let's instead use the event hook that was on the issue to force no
transactions:

from sqlalchemy import create_engine, event

engine = create_engine("mssql+pyodbc://...")


@event.listens_for(engine, "connect")
def _set_autocommit(dbapi_connection, connection_record):
dbapi_connection.autocommit = True


This is where the original poster fell silent, which tends to
correlate to, "it's fixed". (note if this person had replied to my
message, we would not have to have this long email thread now. )

I believe you can get the same result like this too:

engine = create_engine("mssql+pyodbc://...?autocommit=true")

[Dirk Biesinger]

I have this event listener already in the code (it's active).

I'll give the ?autocommit=True in the create_engine a shot as well.

>> > email to sqlalchemy+unsubscribe@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+unsubscribe@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+unsubscribe@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+unsubscribe@googlegroups.com.

[Dirk Biesinger]

nope, same result

P.S. that is why I try to always give feedback once a solution is found. There will be someone chasing the same problem. 

[Mike Bayer]

On Tue, Sep 12, 2017 at 2:54 PM, Dirk Biesinger
<dirk.bi...@gmail.com> wrote:
> nope, same result

using raw pyodbc you don't have that problem?

pyodbc would be screwing up here if it's in autocommit mode yet
connection.rollback() is doing something. please try patching as
follows:

diff --git a/lib/sqlalchemy/dialects/mssql/pyodbc.py
b/lib/sqlalchemy/dialects/mssql/pyodbc.py
index c6368f969..22adafa03 100644
--- a/lib/sqlalchemy/dialects/mssql/pyodbc.py
+++ b/lib/sqlalchemy/dialects/mssql/pyodbc.py
@@ -289,4 +289,8 @@ class MSDialect_pyodbc(PyODBCConnector, MSDialect):
version.append(n)
return tuple(version)

+ def do_rollback(self, connection):
+ pass
+
+
dialect = MSDialect_pyodbc

this is not a solution but a new feature would need to be added to the
dialect to force absolutely no rollback() calls, however pyodbc should
not need this if it's in autocommit.

[Dirk Biesinger]

No, I don't see these problems when using raw pyodbc (I have several previous queries in the same jupyter notebook to the same sql datawarehouse / database using the same user that all go thru no problem. The queries are select, insert, delete statements, so basically the spectrum.

Here's the new error stack:

2017-09-12 21:01:39,990 INFO sqlalchemy.engine.base.Engine SELECT  SERVERPROPERTY('ProductVersion')
2017-09-12 21:01:39,991 INFO sqlalchemy.engine.base.Engine ()
2017-09-12 21:01:40,032 INFO sqlalchemy.engine.base.Engine SELECT schema_name()
2017-09-12 21:01:40,033 INFO sqlalchemy.engine.base.Engine ()
2017-09-12 21:01:40,422 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2017-09-12 21:01:40,423 INFO sqlalchemy.engine.base.Engine ()
2017-09-12 21:01:40,463 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS NVARCHAR(60)) AS anon_1
2017-09-12 21:01:40,465 INFO sqlalchemy.engine.base.Engine ()

<ipython-input-17-4e94acaff5d8> in <module>()
     16 #    dbapi_connection.autocommit = True

>>> >> > email to sqlalchemy+unsubscribe@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+unsubscribe@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+unsubscribe@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+unsubscribe@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+unsubscribe@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+unsubscribe@googlegroups.com.

[Dirk Biesinger]

Your last paragraph got me thinking.

I ran into problems (that seem to be specific to the DataWarehouse) when using raw pyodbc.

I needed to specifically call the cursor.close() function, otherwise the changes got rolled back, after the connection was terminated for whatever reason.

The changes stayed as long as the connection was not closed, so it could be until the python kernel that opened the connection was shut down.

[Mike Bayer]

On Tue, Sep 12, 2017 at 5:03 PM, Dirk Biesinger
<dirk.bi...@gmail.com> wrote:
>
>
> Here's the new error stack:

no this is wrong:

/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

do_rollback() is overridden with the patch, can't be called in
default.py. Please make sure the patch applies an empty
do_rollback() method to MSDialect_pyodbc. you're definitely using
"mssql+pyodbc://" style URL right?

[Dirk Biesinger]

Mike,

for whatever reason the importlib.reload() did not actually reload the patched file.

A fresh kernel did the trick.

When using the engine.connect() function, I have a connection.

When using the df.to_sql function I get a error message related to incorrect datatypes.

I'd say this is success.

I'll fix the datatypes issue and post an update.

Thanks.

Dirk Biesinger

dirk.bi...@gmail.com  |  206.349.9769

"Simplicity is the Mastery of Complexity"

>> >>> >> > email to sqlalchemy+unsubscribe@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+unsubscribe@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+unsubscribe@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+unsubscribe@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+unsubscribe@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+unsubscribe@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+unsubscribe@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+unsubscribe@googlegroups.com.

[Dirk Biesinger]

Actually Mike,

the error is with the create table statement that gets executed by sqlalchemy.engine.base.Engine (the df.to_sql function has option if_exists='append' set):

The table that is being created has different dataformat than the one that exists.

This sql statement seems to be going thru, as the output continues afterwards.

I have to check on the datawarehouse server once I am in the office if the create table statement did result in a new table or alterations to the existing table. (I don't have the correct ip address range from home to be able to connect to the server)

...

[Message clipped]  

[Mike Bayer]

We will need to add a new flag to the SQLAlchemy dialect that
disallows any transaction calls of any kind including rollbacks on an
autocommit connection since pyodbc is being buggy here.

On Wed, Sep 13, 2017 at 11:34 AM, Dirk Biesinger

[dirk.biesinger]

Mike,

here's the error stack (I had to mask some details):

The columns (dataformats) in the create table statement are wrong. Also, this table does not have an index.

2017-09-13 15:07:50,200 INFO sqlalchemy.engine.base.Engine SELECT  SERVERPROPERTY('ProductVersion')

2017-09-13 15:07:50,202 INFO sqlalchemy.engine.base.Engine ()

2017-09-13 15:07:50,246 INFO sqlalchemy.engine.base.Engine SELECT schema_name()

2017-09-13 15:07:50,247 INFO sqlalchemy.engine.base.Engine ()

2017-09-13 15:07:50,522 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1

2017-09-13 15:07:50,522 INFO sqlalchemy.engine.base.Engine ()

2017-09-13 15:07:50,562 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS NVARCHAR(60)) AS anon_1

2017-09-13 15:07:50,563 INFO sqlalchemy.engine.base.Engine ()

2017-09-13 15:07:50,604 INFO sqlalchemy.engine.base.Engine SELECT [INFORMATION_SCHEMA].[COLUMNS].[TABLE_SCHEMA], [INFORMATION_SCHEMA].[COLUMNS].[TABLE_NAME], [INFORMATION_SCHEMA].[COLUMNS].[COLUMN_NAME], [INFORMATION_SCHEMA].[COLUMNS].[IS_NULLABLE], [INFORMATION_SCHEMA].[COLUMNS].[DATA_TYPE], [INFORMATION_SCHEMA].[COLUMNS].[ORDINAL_POSITION], [INFORMATION_SCHEMA].[COLUMNS].[CHARACTER_MAXIMUM_LENGTH], [INFORMATION_SCHEMA].[COLUMNS].[NUMERIC_PRECISION], [INFORMATION_SCHEMA].[COLUMNS].[NUMERIC_SCALE], [INFORMATION_SCHEMA].[COLUMNS].[COLUMN_DEFAULT], [INFORMATION_SCHEMA].[COLUMNS].[COLLATION_NAME] 

FROM [INFORMATION_SCHEMA].[COLUMNS] 

WHERE [INFORMATION_SCHEMA].[COLUMNS].[TABLE_NAME] = CAST(? AS NVARCHAR(max)) AND [INFORMATION_SCHEMA].[COLUMNS].[TABLE_SCHEMA] = CAST(? AS NVARCHAR(max))

2017-09-13 15:07:50,605 INFO sqlalchemy.engine.base.Engine ('dbo.MSODS_DSI', 'dbo')

2017-09-13 15:07:52,151 INFO sqlalchemy.engine.base.Engine 

CREATE TABLE [dbo.MSODS_DSI] (

[a] DATETIME NULL, 

[b] VARCHAR(max) NULL, 

[c] VARCHAR(max) NULL, 

[d] VARCHAR(max) NULL, 

[e] VARCHAR(max) NULL, 

[f] FLOAT(53) NULL, 

[g] FLOAT(53) NULL

)

2017-09-13 15:07:52,152 INFO sqlalchemy.engine.base.Engine ()

2017-09-13 15:07:54,374 INFO sqlalchemy.engine.base.Engine ROLLBACK

---------------------------------------------------------------------------

ProgrammingError                          Traceback (most recent call last)

/home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)

   1181                         parameters,

-> 1182                         context)

   1183         except BaseException as e:

/home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/engine/default.py in do_execute(self, cursor, statement, parameters, context)

    469     def do_execute(self, cursor, statement, parameters, context=None):

--> 470         cursor.execute(statement, parameters)

    471 

ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]The statement failed. Column 'b' has a data type that cannot participate in a columnstore index.\r\nOperation cancelled by user. (35343) (SQLExecDirectW)")

The above exception was the direct cause of the following exception:

ProgrammingError                          Traceback (most recent call last)

<ipython-input-16-290e9c1020c9> 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)

    596                     "'{0}' is not valid for if_exists".format(self.if_exists))

    597         else:

--> 598             self._execute_create()

    599 

    600     def insert_statement(self):

/home/saravji/anaconda3/lib/python3.6/site-packages/pandas/io/sql.py in _execute_create(self)

    581         # Inserting table into database, add to MetaData object

    582         self.table = self.table.tometadata(self.pd_sql.meta)

--> 583         self.table.create()

    584 

    585     def create(self):

/home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/sql/schema.py in create(self, bind, checkfirst)

    754         bind._run_visitor(ddl.SchemaGenerator,

    755                           self,

--> 756                           checkfirst=checkfirst)

    757 

    758     def drop(self, bind=None, checkfirst=False):

/home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/engine/base.py in _run_visitor(self, visitorcallable, element, connection, **kwargs)

   1927                      connection=None, **kwargs):

   1928         with self._optional_conn_ctx_manager(connection) as conn:

-> 1929             conn._run_visitor(visitorcallable, element, **kwargs)

   1930 

   1931     class _trans_ctx(object):

/home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/engine/base.py in _run_visitor(self, visitorcallable, element, **kwargs)

   1536     def _run_visitor(self, visitorcallable, element, **kwargs):

   1537         visitorcallable(self.dialect, self,

-> 1538                         **kwargs).traverse_single(element)

   1539 

   1540 

/home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/sql/visitors.py in traverse_single(self, obj, **kw)

    119             meth = getattr(v, "visit_%s" % obj.__visit_name__, None)

    120             if meth:

--> 121                 return meth(obj, **kw)

    122 

    123     def iterate(self, obj):

/home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/sql/ddl.py in visit_table(self, table, create_ok, include_foreign_key_constraints, _is_metadata_operation)

    765             CreateTable(

    766                 table,

--> 767                 include_foreign_key_constraints=include_foreign_key_constraints

    768             ))

    769 

/home/saravji/anaconda3/lib/python3.6/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/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/sql/ddl.py in _execute_on_connection(self, connection, multiparams, params)

     66 

     67     def _execute_on_connection(self, connection, multiparams, params):

---> 68         return connection._execute_ddl(self, multiparams, params)

     69 

     70     def execute(self, bind=None, target=None):

/home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/engine/base.py in _execute_ddl(self, ddl, multiparams, params)

   1000             compiled,

   1001             None,

-> 1002             compiled

   1003         )

   1004         if self._has_events or self.engine._has_events:

/home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)

   1187                 parameters,

   1188                 cursor,

-> 1189                 context)

   1190 

   1191         if self._has_events or self.engine._has_events:

/home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/engine/base.py in _handle_dbapi_exception(self, e, statement, parameters, cursor, context)

   1400                 util.raise_from_cause(

   1401                     sqlalchemy_exception,

-> 1402                     exc_info

   1403                 )

   1404             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 _execute_context(self, dialect, constructor, statement, parameters, *args)

   1180                         statement,

   1181                         parameters,

-> 1182                         context)

   1183         except BaseException as e:

   1184             self._handle_dbapi_exception(

/home/saravji/anaconda3/lib/python3.6/site-packages/sqlalchemy/engine/default.py in do_execute(self, cursor, statement, parameters, context)

    468 

    469     def do_execute(self, cursor, statement, parameters, context=None):

--> 470         cursor.execute(statement, parameters)

    471 

    472     def do_execute_no_params(self, cursor, statement, context=None):

ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]The statement failed. Column 'b' has a data type that cannot participate in a columnstore index.\r\nOperation cancelled by user. (35343) (SQLExecDirectW)") [SQL: '\nCREATE TABLE [dbo.MSODS_DSI] (\n\t[a] DATETIME NULL, \n\t[b] VARCHAR(max) NULL, \n\t[c] VARCHAR(max) NULL, \n\t[d] VARCHAR(max) NULL, \n\t[e] VARCHAR(max) NULL, \n\t[f] FLOAT(53) NULL, \n\t[g] FLOAT(53) NULL\n)\n\n']

On Monday, September 11, 2017 at 3:34:47 PM UTC-7, dirk.biesinger wrote:

[Mike Bayer]

OK so....don't use VARCHAR(max)? What datatype would you like? We
have most of them and you can make new ones too.

[dirk.biesinger]

I don't get why the table is getting created in the first place. A table with this name exists, and the option "if_exists='append'" should append the dataframe to the existing table.

There should not be a dropping of the table (which I have not seen) nor creation of the table.

And in case of creating the table, I think it should be possible to define the length of the field, so varchar([variable_to_be_submitted]).

In my case I expect this particular table to grow to several hundred million rows, so assigned storage space is a factor.

the existing table was created like this:

CREATE TABLE dbo.DSI

(

a datetime

b varchar(10) null,

c varchar(100) null,

d varchar(10) null,

e varchar(100) null,

f decimal (8,6) null,

g decimal (8,6) null

)

 

If I read and understand the error stack correct, the cause is the create table statement, which I would very strongly hope to cause an error, as the table exists.

Should the create table statement not be omitted if the option "if_exists='append'" option is set?

[Mike Bayer]

This is all on the Pandas side so you'd need to talk to them.

[dirk.biesinger]

Got ya,

so we could solve the issue on the sqlalchemy end with the alteration of the pyodbc.py file.

I assume you'll include this in the next release?

The issue with creating a table when the option "if_exists='append'" is set in the df.to_sql() call, is a pandas problem.

Thank you for your help.

Best,

DB

[Mike Bayer]

On Wed, Sep 13, 2017 at 3:14 PM, dirk.biesinger
<dirk.bi...@gmail.com> wrote:
> Got ya,
>
> so we could solve the issue on the sqlalchemy end with the alteration of the
> pyodbc.py file.
> I assume you'll include this in the next release?

um.

can you just confirm for me this makes the error?


connection = pyodbc.connect(....)
connection.autocommit = 0
connection.rollback()

[dirk.biesinger]

I have the 'patched' pyodbc.py file active.

Executing your code snippet does NOT produce an error or any output for that matter.

[Mike Bayer]

On Wed, Sep 13, 2017 at 3:27 PM, dirk.biesinger
<dirk.bi...@gmail.com> wrote:
> I have the 'patched' pyodbc.py file active.
> Executing your code snippet does NOT produce an error or any output for that
> matter.
>
>
> On Wednesday, September 13, 2017 at 12:22:30 PM UTC-7, Mike Bayer wrote:
>>
>> On Wed, Sep 13, 2017 at 3:14 PM, dirk.biesinger
>> <dirk.bi...@gmail.com> wrote:
>> > Got ya,
>> >
>> > so we could solve the issue on the sqlalchemy end with the alteration of
>> > the
>> > pyodbc.py file.
>> > I assume you'll include this in the next release?
>>
>> um.
>>
>> can you just confirm for me this makes the error?
>>
>>
>> connection = pyodbc.connect(....)
>> connection.autocommit = 0
>> connection.rollback()

try it like this:

connection = pyodbc.connect(....)
connection.autocommit = 0

cursor = connection.cursor()
cursor.execute("SELECT 1")
cursor.close()

[dirk.biesinger]

using

connection = pyodbc.connect(....)

connection.autocommit = 0

cursor = connection.cursor()

cursor.execute([proper sql statement that references a table])

rows = corsor.fetchall()

print(rows)

cursor.close()

gives me the output out of the table that I expect.

So if you were wondering if a raw pyodbc connection works, this is confirmed.

[Mike Bayer]

On Wed, Sep 13, 2017 at 3:58 PM, dirk.biesinger
<dirk.bi...@gmail.com> wrote:
> using
>
> connection = pyodbc.connect(....)
> connection.autocommit = 0
> cursor = connection.cursor()
> cursor.execute([proper sql statement that references a table])
> rows = corsor.fetchall()
> print(rows)
> cursor.close()
>
> gives me the output out of the table that I expect.
> So if you were wondering if a raw pyodbc connection works, this is
> confirmed.

did you call:

connection.rollback()

the stack traces you have given me indicate this method cannot be
called else Azure raises an error. This must be illustrated as
definitely the problem, and not a side effect of something else.

This is why this would go a lot quicker if someone had a *blank* azure
database on a cloud node somewhere for me to log into. I don't need
your customer data.

[dirk.biesinger]

I hear your pain,

unfortunately we have to have very tight security on our server instances; I only can log into the server when I am in the office, I can't log in with my work laptop from home. (I have a not authorized ip address there)

If it were a possible, I would have given you access to a blank area.

as for the rollback call, 

adding it after the print command, does not change anything.

When I insert it between cursor.execute() and rows = cursor.fetchall(), I get an error as expected.

[Mike Bayer]

On Wed, Sep 13, 2017 at 4:29 PM, dirk.biesinger
<dirk.bi...@gmail.com> wrote:
>
> as for the rollback call,
> adding it after the print command, does not change anything.
> When I insert it between cursor.execute() and rows = cursor.fetchall(), I
> get an error as expected.

oh that is very interesting.

[dirk.biesinger]

"oh that is very interesting." he says and then it's getting eerily quiet.

I guess Mike and Dilly are somewhere in the depth of code and docs...

[Mike Bayer]

On Wed, Sep 13, 2017 at 6:13 PM, dirk.biesinger
<dirk.bi...@gmail.com> wrote:
> "oh that is very interesting." he says and then it's getting eerily quiet.
> I guess Mike and Dilly are somewhere in the depth of code and docs...

unfortunately not, azure seems to offer free trials if you are willing
to give them a credit card number so I can perhaps eventually get
around to working with that but at the moment it would be preferable
if someone wants to work on an azure variant of the SQL Server
dialect. It shouldn't be hard but the various glitches need to be
understood for anything to be committed.

[dirk.biesinger]

yeah, I ran into some 'nice' 'features' in this project.

the azure datawarehouse does not behave or work like a typical old-school sql server.

There might be some potential there, just not sure how many projects would be using the datawarehouse....

[Mike Bayer]

There is definitely interest in this platform and we will need to support it.  It's just it falls under the category of a subscriber database like Redhift or google whatever it was called so needs some folks with access to get it working and possibly support an external overlay project - sqlalchemy-redshift is the current example.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscribe@googlegroups.com.

[Jingting Lu]

Hi Dirk,

Happy to report that there are more projects using dw.  I have the same issues here.  Using Azure SQL DW at the moment and building a serverless function app that reads and sends data back to the SQL DW.

Did you eventually find a solution other than looping through the dataframe?

Cheers,

Nicole

>> >> >> >> >> >> > obj.__visit_name__, <b

[mjp...@lixar.com]

Any solution for this?

[Mike Bayer]

for "dm_exec_sessions" ? that's an old SQLAlchemy bug that was fixed
long ago. see https://github.com/sqlalchemy/sqlalchemy/issues/3994
please upgrade.

> Confidentiality Note: This email may contain confidential and/or private information.
> If you received this email in error please delete and notify sender.

[Mark Pearl]

No the error related to this:

sqlalchemy 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)') (Background on this error at: http://sqlalche.me/e/f405)

--

Mark Pearl Senior Data Developer | Data & New Markets

613.722.0688

mjp...@lixar.com

PREMIER AI & DATA COMPANY

End-to-End Development . IoT Intelligence . Cloud Computing

W: lixar.com | T: 613.722.0688

START YOUR DATA JOURNEY TODAY | CONTACT US

        

[Mike Bayer]

Add ?autocommit=true to your pyodbc connection string