your database does not support transactions. try adding
?autocommit=true to your database URL:
sqlalchemy.engine.create_engine("mssql+pyodbc:///?autocommit=true&odbc_connect=%s"
% params)
let me know if that works
On Wed, Jan 23, 2019 at 3:58 AM Massi <[email protected]> wrote:
>
> I'm trying to connect to an Azure SQL Server via SQLAchemy (1.2.5), but I'm
> getting the following error regardless of the driver that I use:
>
> Traceback (most recent call last):
> File "C:\Users\user1\Desktop\test.py", line 27, in <module>
> res = engine.connect().execute(q)
> File "C:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line 2102,
> in connect
> return self._connection_cls(self, **kwargs)
> File "C:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line 90, in
> __init__
> if connection is not None else engine.raw_connection()
> File "C:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line 2188,
> in raw_connection
> self.pool.unique_connection, _connection)
> File "C:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line 2162,
> in _wrap_pool_connect
> e, dialect, self)
> File "C:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line 1476,
> in _handle_dbapi_exception_noconnection
> exc_info
> File "C:\Python27\lib\site-packages\sqlalchemy\util\compat.py", line 203,
> in raise_from_cause
> reraise(type(exception), exception, tb=exc_tb, cause=cause)
> File "C:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line 2158,
> in _wrap_pool_connect
> return fn()
> File "C:\Python27\lib\site-packages\sqlalchemy\pool.py", line 345, in
> unique_connection
> return _ConnectionFairy._checkout(self)
> File "C:\Python27\lib\site-packages\sqlalchemy\pool.py", line 784, in
> _checkout
> fairy = _ConnectionRecord.checkout(pool)
> File "C:\Python27\lib\site-packages\sqlalchemy\pool.py", line 532, in
> checkout
> rec = pool._do_get()
> File "C:\Python27\lib\site-packages\sqlalchemy\pool.py", line 1189, in
> _do_get
> self._dec_overflow()
> File "C:\Python27\lib\site-packages\sqlalchemy\util\langhelpers.py", line
> 66, in __exit__
> compat.reraise(exc_type, exc_value, exc_tb)
> File "C:\Python27\lib\site-packages\sqlalchemy\pool.py", line 1186, in
> _do_get
> return self._create_connection()
> File "C:\Python27\lib\site-packages\sqlalchemy\pool.py", line 350, in
> _create_connection
> return _ConnectionRecord(self)
> File "C:\Python27\lib\site-packages\sqlalchemy\pool.py", line 477, in
> __init__
> self.__connect(first_connect_check=True)
> File "C:\Python27\lib\site-packages\sqlalchemy\pool.py", line 677, in
> __connect
> exec_once(self.connection, self)
> File "C:\Python27\lib\site-packages\sqlalchemy\event\attr.py", line 274, in
> exec_once
> self(*args, **kw)
> File "C:\Python27\lib\site-packages\sqlalchemy\event\attr.py", line 284, in
> __call__
> fn(*args, **kw)
> File "C:\Python27\lib\site-packages\sqlalchemy\util\langhelpers.py", line
> 1334, in go
> return once_fn(*arg, **kw)
> File "C:\Python27\lib\site-packages\sqlalchemy\engine\strategies.py", line
> 183, in first_connect
> dialect.initialize(c)
> File "C:\Python27\lib\site-packages\sqlalchemy\dialects\mssql\base.py",
> line 1931, in initialize
> super(MSDialect, self).initialize(connection)
> File "C:\Python27\lib\site-packages\sqlalchemy\engine\default.py", line
> 283, in initialize
> self.do_rollback(connection.connection)
> File "C:\Python27\lib\site-packages\sqlalchemy\engine\default.py", line
> 457, in do_rollback
> dbapi_connection.rollback()
> sqlalchemy.exc.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)
>
> This is the script which I use to try and read a table which generates the
> error:
>
> from sqlalchemy import *
> from datetime import datetime
> import urllib, sqlalchemy
> from urllib import quote_plus as urlquote
>
> q = """
> SELECT count(*) FROM [Products]
> """
>
> params = urllib.quote_plus("Driver={ODBC Driver 13 for SQL
> Server};Server=mydb.database.windows.net,1433;Database=mydb;Uid=myuser;Pwd=mypwd;Encrypt=yes;TrustServerCertificate=no;Connection
> Timeout=30;")
> engine = sqlalchemy.engine.create_engine("mssql+pyodbc:///?odbc_connect=%s" %
> params)
> #engine =
> create_engine("mssql+pyodbc://myuser:[email protected]/mydb?charset=utf8&driver=ODBC+Driver+13+for+SQL+Server")
> print engine
>
> res = engine.connect().execute(q)
>
> If I run the same query directly via pyodbc, everythin works fine:
>
> import pyodbc
> cnxn = pyodbc.connect("Driver={ODBC Driver 13 for SQL
> Server};Server=mydb.database.windows.net,1433;Database=mydb;Uid=myuser;Pwd=mypwd;Encrypt=yes;TrustServerCertificate=no;Connection
> Timeout=30;")
> cursor = cnxn.cursor()
> cursor.execute(q)
> row = cursor.fetchone()
> if row:
> print row
>
> The output, in this case, is this (the table is empty):
>
> (0, )
>
> Can anyone help me out on this?
>
> --
> 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 [email protected].
> To post to this group, send email to [email protected].
> 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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
