PyODBC return error, but why?
I am trying to execute a T-SQL query in two cases (I am using Win7, python 3.2, MS SQL Server express 2008, pyodbc for python 3.2):
-case 1:
using MS SQL Server Management Studio I am trying to execute the query:
USE master;CREATE DATABASE Sales ON (NAME = Sales_dat,FILENAME = 'C:\saledat.mdf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ) LOG ON ( NAME = Sales_log, FILENAME = 'C:\salelog.ldf', SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB );
it returns a successful result
-case 2:
import pyodbc
cxnn=pyodbc.connect('DSN=SERVER;UID=sa;PWD=password')
cur=cxnn.cursor()
cur.execute("USE master;CREATE DATABASE Sales ON (NAME = Sales_dat,FILENAME = 'C:\saledat.mdf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ) LOG ON ( NAME = Sales_log, FILENAME = 'C:\salelog.ldf', SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB );")
cxnn.commit()
but after running the code i get the error
pyodbc.Error: ('HY000', 'The driver did not supply an error!')
If I run the code without
cxnn.commit()
I am not getting any error. But why?
+3
source to share
2 answers
For reasons I don't fully understand, setting autocommit to true seems to fix the problem. Note that dropping backslashes is still required.
Autocomment can be set in two ways:
cxnn=pyodbc.connect('DSN=SERVER;UID=sa;PWD=password', autocommit=True)
Or:
cxnn=pyodbc.connect('DSN=SERVER;UID=sa;PWD=password')
cxnn.autocommit = True
After that, cursor creation and query execution should proceed as expected:
cur=cxnn.cursor()
cur.execute("USE master;CREATE DATABASE Sales ON (NAME=Sales_dat, FILENAME='C:\\saledat.mdf', SIZE=10, MAXSIZE=50, FILEGROWTH=5) LOG ON (NAME=Sales_log, FILENAME='C:\\salelog.ldf', SIZE=5MB, MAXSIZE=25MB, FILEGROWTH=5MB );")
+3
source to share