"CREATE ... statement not allowed within multi-statement transaction" when using pyodbc

Solution 1:

When establishing a connection, pyodbc defaults to autocommit=False in accordance with Python's DB-API spec. Therefore when the first SQL statement is executed, ODBC begins a database transaction that remains in effect until the Python code does a .commit() or a .rollback() on the connection.

SQL Server does not allow CREATE DATABASE to be executed within such a transaction, so we need to have the connection in autocommit mode before issuing such statements. That can be accomplished when the connection is opened ...

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

... or by switching to autocommit mode if the connection is already established:

conn = pyodbc.connect(conn_str)  # autocommit=False by default
# ...
conn.autocommit = True
conn.execute("CREATE DATABASE MyNewDatabase")