10

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?

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
userbb
  • 121
  • 1
  • 5
  • Try [setting `autocommit = True`](http://stackoverflow.com/a/1064149/61305). – Aaron Bertrand Feb 02 '17 at 15:33
  • I just tried. It doesnot work.. – userbb Feb 02 '17 at 15:36
  • tried true as well. It fails – userbb Feb 02 '17 at 15:38
  • 1
    Then you're going to have to show your exact code. Related questions [here](http://stackoverflow.com/questions/30797319/sqlalchemy-mssql-create-database-statement-not-allowed-within-multi-statement-tr) and [here](http://stackoverflow.com/questions/27440711/unable-to-create-database-via-pyodbc-connection). – Aaron Bertrand Feb 02 '17 at 15:38
  • my code is as below: – userbb Feb 02 '17 at 15:44
  • 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() – userbb Feb 02 '17 at 15:46
  • Thanks Aaron Bertrand. after making autocommit =true. it started working. I was not setting it correctly. Thanks a lot – userbb Feb 02 '17 at 16:14

1 Answers1

22

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")
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418