Create a database with sqlalchemy-turbodbc on MSSQL Server

Question

I am trying to create a database on MSSQL server with sqlalchemy and turbodbc.

Below is my code

from sqlalchemy import create_engine
import turbodbc

username = ''
password = ''
server = ''
database = ''
driver= 'ODBC+Driver+17+for+SQL+Server'

con = create_engine(f"mssql+turbodbc://{username}:{password}@{server}:1433/{database}?driver={driver}")

con.execute(f"create database newdb")

Error

DatabaseError: (turbodbc.exceptions.DatabaseError) ODBC error
state: 42000
native error code: 226
message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]CREATE DATABASE statement not allowed within multi-statement transaction.
[SQL: create database sai]
(Background on this error at: http://sqlalche.me/e/4xp6)

Can somebody help?. I am looking to create a database only by using the modules: SQLALCHEMY, Turbodbc or psycopg2. Due to some restrictions.

Thanks

score 0 · Answer 1 · answered Sep 24 '19 at 11:54

0

From the CREATE DATABASE docs.

The CREATE DATABASE statement must run in autocommit mode (the default transaction management mode) and is not allowed in an explicit or implicit transaction.

In SqlAlchemy you can set autocommit passing it as parameter like this:

engine.execute(text("SELECT my_mutating_procedure()").execution_options(autocommit=True))

answered Sep 24 '19 at 11:54

Max

6,821
3
43
59

I am still having the same error. I guess `.execution_options(autocommit=True)` is not working for some reason – Nithin Sai Sep 24 '19 at 12:21

score 0 · Answer 2 · answered Sep 24 '19 at 12:36

Got the solution. This worked for me

import turbodbc


connection = turbodbc.connect(
                                driver=driver , server=server , database=database,
                                uid=username , pwd=password,
                            )
connection.autocommit =True
cur = connection.cursor()
cur.execute("create database newdb")

Create a database with sqlalchemy-turbodbc on MSSQL Server

2 Answers2