17

I have a username which I must change in numerous (up to ~25) tables. (Yeah, I know.) An atomic transaction seems to be the way to go for this sort of thing. However, I do not know how to do this with pyodbc. I've seen various tutorials on atomic transactions before, but have never used them.

The setup: Windows platform, Python 2.6, pyodbc, Microsoft SQL 2005. I've used pyodbc for single SQL statements, but no compound statements or transactions.

Best practices for SQL seem to suggest that creating a stored procedure is excellent for this. My fears about doing a stored procedure are as follows, in order of increasing importance: 1) I have never written a stored procedure. 2) I heard that pyodbc does not return results from stored procedures as of yet. 3) This is most definitely Not My Database. It's vendor-supplied, vendor-updated, and so forth.

So, what's the best way to go about this?

2 Answers2

27

By its documentation, pyodbc does support transactions, but only if the odbc driver support it. Furthermore, as pyodbc is compliant with PEP 249, data is stored only when a manual commit is done.
This means that you have to explicitely commit() the transaction, or rollback() the entire transaction.

Note that pyodbc also support autocommit feature, and in that case you cannot have any transaction.
By default, autocommit is off, but your codebase might have tuerned it on. You should check the connection, when it is performed

cnxn = pyodbc.connect(cstring, autocommit=True)

Alternatively, you can also explicitely turn off the autocommit mode with

cnxn.autocommit = False

but this might have quite a big impact on your system.

Note: you can get more information on the autocommit mode of pyodbc on its wiki

rob
  • 36,896
  • 2
  • 55
  • 65
  • autocommit is off by default per PEP 249: "Note that if the database supports an auto-commit feature, this must be initially off." – Gord Thompson Nov 13 '18 at 13:30
  • 1
    I spent a day trying to figure out why I was getting this error: `pyodbc.ProgrammingError: ('25000', '[25000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1. (266) (SQLExecDirectW)')` Any ROLLBACK within your stored procedure causes this unless you manually commit, or set autocommit=True. – Nick Allan Mar 02 '22 at 09:47
  • To be honest, that seems a bug in the stored procedure, or in the design. I mean, either you are in a transaction, either you don't care. Using `autocommit=True` means that you don't care about transaction -- which is fine, but then I __believe__ it should be explicitly documented. – rob Mar 03 '22 at 10:04
-9

I don't think pyodbc has any specific support for transactions. You need to send the SQL command to start/commit/rollback transactions.

Lennart Regebro
  • 167,292
  • 41
  • 224
  • 251