sqlalchemy MSSQL create database statement not allowed within multi-statement transaction

Question

Using sqlalchemy 1.0.4, python 3.4.3 and pyodbc 3.0.10.

I want to create a database with raw sql for the MS SQL server.

c = "mssql+pyodbc://sa:admin11$$@PROIMT01\SQLEXPRESS/master?driver=SQL+Server+Native+Client+11.0"
e = create_engine(c,echo=True)
con = e.connect()

con.execute("CREATE DATABASE xb;")
con.close()

But the above code returns an error "create database statement not allowed within multi-statement transaction".

I tried con.execute("commit") before the creation script, but it does nothing.

UPDATE
If i set the driver ?driver=SQL+SERVER it works!?

Answer 1

There seem to be a couple ways to do this, with the key being auto commit. I was unable to just switch to SQL SERVER because it seems to cause issues with apache. If you just want to use pyodbc you can do:

import pyodbc
conn =pyodbc.connect("driver={SQL Server Native Client 11.0};server=databaseBName; database=master; trusted_connection=yes;", autocommit=True)
curr = conn.execute("CREATE DATABASE databaseName ON (FILENAME='E:\\SQL\\path_to_file.mdf') FOR ATTACH;")
curr.close()

You can also use sqlalchemy to create the connection with:

import sqlalchemy
engine = sqlalchemy.create_engine("mssql+pyodbc://serverName/master?driver=SQL Server Native Client 11.0;", connect_args = {'autocommit':True})
engine.execute("CREATE DATABASE databaseName ON (FILENAME='E:\\SQL\\path_to_file.mdf') FOR ATTACH;")

I have been attaching by file but I assume it also works without.