Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

I'm trying to create a SQL Server database using pyodbc.

import pyodbc 
server = 'AMR112\NAMED1' 
database = 'msdb' 
username = '' 
password = 'mypassword' 
abcd='yes' 
ghi='False' 
#driver = '{/usr/local/lib/libtdsodbc.so}' #for linux of windows 
driver= '{ODBC Driver 13 for SQL Server}' 
cnxn = pyodbc.connect('DRIVER='+driver+';PORT=1433;SERVER='+server+??';PORT=1443;DATABASE??='+database+';UID='+??username+';PWD='+ password+';trusted_connection='+ abcd+'; autocommit='+ ghi) cursor = cnxn.cursor() 
cursor.execute("create database dbafgh") 
row = cursor.fetchone() 
if row: 
    print row 
cursor.close()

It fails with this error

CREATE DATABASE statement not allowed within multi-statement transaction

It fails because the .execute method starts a transaction and CREATE DATABASE cannot be run within a transaction.

So is there any other way to execute a CREATE DATABASE command using python?

like image 287
userbb Avatar asked Feb 02 '17 15:02

userbb


People also ask

What is Autocommit in Pyodbc?

The AUTOCOMMIT connection attribute controls whether INSERT, ALTER, COPY and other data-manipulation statements are automatically committed after they complete. By default, AUTOCOMMIT is enabled—all statements are committed after they execute.

What is the alternative to Pyodbc?

Top Alternatives to pyodbcPython wrapper for the Cloudflare v4 API. The AWS SDK for Python. Powerful data structures for data analysis, time series, and statistics. Pytest: simple powerful testing with Python.

What is multi statement transaction SQL?

A multi-statement transaction lets you perform mutating operations, such as inserting or deleting rows on one or more tables, and either commit or roll back the changes atomically. Uses for multi-statement transactions include: Performing DML mutations on multiple tables as a single transaction.

What is DSN in Pyodbc?

More Information. It is the name that applications use to request a connection to an ODBC Data Source. In other words, it is a symbolic name that represents the ODBC connection. It stores the connection details like database name, directory, database driver, UserID, password, etc.


1 Answers

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")
like image 54
Gord Thompson Avatar answered Sep 20 '22 22:09

Gord Thompson