Readonly connection with mssql and pyodbc

Question

I'm having some trouble with trying to create a read-only connection and not sure if it's a bug or my error.

Pyodbc's documentation indicates that it is possible to create a readonly connection. See https://mkleehammer.github.io/pyodbc/api-module.html

When running the following though I receive no errors and it runs as if the READONLY keyword was not present at all (the update goes through).

import pyodbc

readonly_conn_str = "DRIVER={SQL Server Native Client 10.0};SERVER=...;DATABASE=...;UID=...;PWD=...;READONLY=True;"
conn = pyodbc.connect(readonly_conn_str)
cursor = conn.cursor()
result = cursor.execute(update_query)
cursor.commit()
print(result.rowcount)

If I try using function keywords I get the same thing.

...
conn = pyodbc.connect(conn_str, readonly=True)
...

Answer 1

When we invoke pyodbc.connect with readonly=True, pyodbc dutifully calls the ODBC function

ret = SQLSetConnectAttr(cnxn->hdbc, SQL_ATTR_ACCESS_MODE, (SQLPOINTER)SQL_MODE_READ_ONLY, 0);

SQL_MODE_READ_ONLY is one of the standard ODBC SQL_ATTR_ACCESS_MODE attributes. How that actually affects the behaviour of the ODBC connection is up to the ODBC driver. As stated in the ODBC documentation:

SQL_MODE_READ_ONLY is used by the driver or data source as an indicator that the connection is not required to support SQL statements that cause updates to occur. This mode can be used to optimize locking strategies, transaction management, or other areas as appropriate to the driver or data source. The driver is not required to prevent such statements from being submitted to the data source. The behavior of the driver and data source when asked to process SQL statements that are not read-only during a read-only connection is implementation-defined.

In other words, pyodbc has passed the "read only" attribute to the ODBC driver. It is up to the driver to decide whether it should be interpreted as a hint, a hard limitation, or something to be simply ignored.