Request for auto commit option on the connection

[keitherskine]

I just wanted to add this issue as a placeholder for an "auto commit" option. It would be very handy to have an option on the connect function to tell the database to commit after every SQL statement (i.e. by setting SQL_ATTR_AUTOCOMMIT). This would save users from having to explicitly call commit() after every call to execute. The default would be not to implicitly commit, to comply with PEP249, but it would be good to be able to set this attribute when needed. Better still it would be good to be able to change this attribute dynamically, e.g. switch on "auto-commit" on the connection after the connection is established and then switch it off again later, all without dropping the connection. I appreciate this attribute and behaviour may not be supported by all RDBMS's, but this is possible and does work with MS SQL Server.

For examples of existing implementations, see pyodbc and MySQL.

[MathMagique]

Since PEP 249 suggests that driver implementers can implement an custom interface method to turn autocommit back on, I don't have any rational points against it besides the odd "explicit is better than implicit". Internally, turbodbc already sets the SQL_ATTR_AUTOCOMMIT property to disable autocommit behavior, so it should be easy to switch it on again.

[MathMagique]

I really need to set up proper documentation to explain all the new tweaks and switches ;-).

[keitherskine]

Thanks, Michael. Just in case it wasn't obvious what I was talking about in my original message, here's a concrete example of the way auto-commit could be used in code:

# create a connection with auto-commit switched on (i.e. not the default of False)
conn = turbodbc.connect(dsn='...', autocommit=True)
crsr = conn.cursor()
# the following sql statement would not need to be explicitly committed
crsr.execute("INSERT INTO TABLE ...")
# it should still be possible to check auto-commit on the connection when necessary
if not conn.autocommit:
    conn.commit()
# dynamically change the auto-commit setting
conn.autocommit = False
# the following sql statement would now need to be explicitly committed
crsr.execute("INSERT INTO TABLE ...")
conn.commit()

Personally, I do use code like this with pyodbc. I also set autocommit=True almost every time I create a connection.

I would create a PR for this hopefully simple change but, although I like to think of myself as a decent Python programmer, C++ is beyond me! So many thanks for going to the trouble of creating this module and maintaining it. I can see myself using it in the future.

[keitherskine]

Excellent! Many thanks indeed, @MathMagique . This is a big help. I use autocommit all the time (both True and False). This may be a relatively small update but it's a very welcome one.

[MathMagique]

I am always pleased to help :-)