Sqlalchemy MSSQL creates database expression which is not allowed in multi-operation transaction
Using sqlalchemy 1.0.4, python 3.4.3 and pyodbc 3.0.10.
I want to create a raw sql database for 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 creating the script but didn't do anything.
UPDATE
If I install the driver ?driver=SQL+SERVER
, it works !?
source to share
There seem to be several ways to do this, with the key being an auto-commit. I couldn't just switch to SQL SERVER because it seems to be causing apache issues. If you just want to use pyobbc 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 a 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 am attaching the file, but I am guessing it works without it as well.
source to share