CODEWITHSUNDEEP
pythonsql-serversqlalchemyturbodbc
Nithin Sai
Nithin Sai

Reputation: 23

Create a database with sqlalchemy-turbodbc on MSSQL Server

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

Upvotes: 0

Views: 862

Answers (2)

Nithin Sai
Nithin Sai

Reputation: 23

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")

Upvotes: 0

Max
Max

Reputation: 7100

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))

Upvotes: 0

Related Questions