PyODBC return error, but why?

Question

I try execute T-SQL query in two cases (i use Win7, python 3.2, MS SQL Server express 2008,pyodbc for python 3.2):

-case 1:

using MS SQL Server Management Studio i try to execute 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 return 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 code i receive error

 pyodbc.Error: ('HY000', 'The driver did not supply an error!')

If i run the code without

cxnn.commit()

i have receive no error. But why?

Talvalin · Accepted Answer · 2013-01-29 10:49:41Z

4

For reasons I don't quite understand, setting autocommit to be true seems to fix the issue. Please note that escaping the backslashes is still required.

Autocommit 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

Once that's done, creating the cursor and executing the query should then run 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 );")

edited Jan 29, 2013 at 10:49

answered Jan 29, 2013 at 7:49

Talvalin

7,9072 gold badges33 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Vardan Arakelyan Over a year ago

No, i try your string (with escape backslashes), but again receive error pyodbc.Error: ('HY000', 'The driver did not supply an error!'). And if i run the code without commit() all is work without error. But without commit i can not save changes to DB

Talvalin Over a year ago

Have you been able to get this code to work using autocommit?

Vardan Arakelyan Over a year ago

yes, i confirm, it work with autocommit, thanks. But why it works only with autocommit?

Talvalin Over a year ago

That is a good question. Perhaps it would be worth asking the PyODBC dev?

PeterChen Over a year ago

I have no idea why but this works for me also in 11 years later. I am using pyodbc 5.0.1, pandas 2.2.1.

Vito · Accepted Answer · 2018-04-03 15:33:32Z

0

In my case I simply needed to upgrade pyodbc for it to work.

pip install --upgrade pyodbc

answered Apr 3, 2018 at 15:33

Vito

1,7582 gold badges20 silver badges34 bronze badges

Collectives™ on Stack Overflow

PyODBC return error, but why?

2 Answers 2

5 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Linked

Related