pyodbc error creating tables in Azure SQL Data WareHouse

Question

import pyodbc

sql = 'CREATE TABLE TestTable (TestID int,  TestDate datetime)'

con = pyodbc.connect(SQL Data WareHouse)
con.execute(sql)
con.commit()
con.close()

I got following error:

Traceback (most recent call last):
  File "sqlTest.py", line 24, in <module>
    con.execute(sql)
pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]111212;Operation cannot be performed within a transaction. (111212) (SQLExecDirectW)')

I checked that I can use INSERT and DELETE query by above code but I can't use CREATE and TRUNCATE query. I checked having permission for CREATE and TRUNCATE by using SSMS too.

Is it possible to create a table with pyodbc in the Azure SQL Data WareHouse?

Answer 1

I can use CREATE and TRUNCATE query by using autocommit. Following code works fine.

import pyodbc

sql = 'CREATE TABLE TestTable (TestID int,  TestDate datetime)'

con = pyodbc.connect(SQL Data WareHouse)
con.autocommit = True
con.execute(sql)
con.close()