CODEWITHSUNDEEP
pythonsqlsql-serverdatabasepyodbc
dCoder
dCoder

Reputation: 549

Not able to create database using stored procedure pyodbc SQL SERVER

I am trying to call a stored procedure that creates a Database from pyodbc

The Following is a minimal example of the code

import pyodbc
conn = pyodbc.connect("Driver={SQL Server};Server=SERVERNAME;Trusted_Connection=yes;")
cursor=conn.cursor()
cursor.execute("""\
    DECLARE @RC int
    DECLARE @ClientName varchar(255)
    SET @ClientName = 'Test'
    EXECUTE @RC = [DB_NAME].[SCHEMA].[sp_NAME] @ClientName
""")
conn.commit()

The code should ideally create a Database named 'Test' in the SQL Server Instance. This does not produce any error. But the database is not created.

Running 

DECLARE @RC int
DECLARE @ClientName varchar(255)
SET @ClientName = 'Test'
EXECUTE @RC = [DB_NAME].[SCHEMA].[sp_NAME] @ClientName

From SSMS seems to create the database. Already referred a couple of questions including this one.

Upvotes: 3

Views: 413

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123654

The Python DB API 2.0 specifies that, by default, connections should be opened with autocommit disabled. However, many databases require that DDL statements not be executed within a transaction. Attempts to execute DDL statements with autocommit disabled can lead to errors or unexpected results.

Therefore, it is safer to ensure that DDL statements are executed on a connection where autocommit is enabled. That can be accomplished by setting autocommit=True when opening the connection ...

cnxn = pyodbc.connect(connection_string, autocommit=True)

... or by enabling autocommit on an existing connection ...

cnxn = pyodbc.connect(connection_string)
# ...
cnxn.autocommit = True

Upvotes: 2

Related Questions