Pymssql and autocommit

[Thorsten Kampe]

Hi,

is it possible to enable autocommit for a MSSQL
connection with Pymssql? The feature is available in
Pymssql since 2014. PyODBC - the default MSSQL driver -
supports autoconnect in the connection string.

I have been unsuccessful trying to enable autocommit in
the connection string or as a keyword for
`create_engine` (`isolation_level = 'AUTOCOMMIT'` or
`autocommit = True`).

Thorsten

[Mike Bayer]

the pyodbc connector does accept 'autocommit' as a query parameter
which will be coerced to boolean, so this should work:

create_engine("mssql+pyodbc://scott:tiger@dsn?autocommit=true")


Beyond that, we like to support AUTOCOMMIT as an isolation level if it
is available from the DBAPI as a flag we can set upon the Connection
object itself after it's been created. Here's what such a commit
looks like if you had the ability to propose an initial pull request:
https://bitbucket.org/zzzeek/sqlalchemy/commits/a87b3c21


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

[Thorsten Kampe]

* Mike Bayer (Sun, 27 Aug 2017 11:35:27 -0400)

>
> On Sun, Aug 27, 2017 at 9:52 AM, Thorsten Kampe
> <thor...@thorstenkampe.de> wrote:
> > Hi,
> >
> > is it possible to enable autocommit for a MSSQL
> > connection with Pymssql? The feature is available in
> > Pymssql since 2014. PyODBC - the default MSSQL driver -
> > supports autoconnect in the connection string.
> >
> > I have been unsuccessful trying to enable autocommit in
> > the connection string or as a keyword for
> > `create_engine` (`isolation_level = 'AUTOCOMMIT'` or
> > `autocommit = True`).
>
> the pyodbc connector does accept 'autocommit' as a query parameter
> which will be coerced to boolean, so this should work:
>
> create_engine("mssql+pyodbc://scott:tiger@dsn?autocommit=true")

Thanks for the quick answer. My question was
specifically about the PyMSSQL driver (not the PyODBC
driver).

The reason why I'm asking is that I would like to
connect from Linux.

PyODBC works fine on Windows. On Linux I simply don't
know what to use instead of...

driver=SQL+Server+Native+Client+11.0

...in the connection string when using a "Hostname-
based connection".

Thorsten

[Mike Bayer]

You use the ODBC connector that comes with FreeTDS in conjunction with UnixODBC.   That said, pymssql probably works a little better on linux so you can set this autocommit flag using a connect hook (http://docs.sqlalchemy.org/en/latest/core/events.html?highlight=connect#sqlalchemy.events.PoolEvents.connect) or we can accept a PR for a real feature addition.

Thorsten

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

good news, SQL Server now supports an easy to use linux container
which means I can SQL Server to my CI setup, which means I need it to
be able to CREATE DATABASE which means I need generalized
per-connection "AUTOCOMMIT" support for pyodbc and pymssql. I'll be
adding this soon.

> email to sqlalchemy+...@googlegroups.com.