2

It's late, I'm tired - I hope I am just making a stupid mistake but I can't seem to see it. Can anyone see what I may be doing wrong? If I run the SQL on SQL Server Management studio, it works fine. I can run other SQL statements against the cursor, just not this one.

Thanks in advance

System:

Windows 7, Python 3.4, SQLServer 2008, pypyodbc

Query:

IF EXISTS(SELECT * FROM sys.databases WHERE name='{MyDBName}')
BEGIN
 ALTER DATABASE {MyDBName} SET SINGLE_USER WITH ROLLBACK IMMEDIATE; 
 DROP DATABASE {MyDBName}; 
END

CODE:

cursor.execute(query)
cursor.commit()

EXCEPTION:

ex = {IndexError} list index out of range
SteveJ
  • 3,034
  • 2
  • 27
  • 47

1 Answers1

4

For posterity sake, I finally found my way through it and will share in case anyone reads this later:

It turns out that I need the autocommit property set to true when making the connection:

auto_commit = true
with pypyodbc.connect(cnx, auto_commit) as connection:
     cursor = connection.cursor()              

     cursor.execute(query)
     cursor.commit()

Note also that I change my query to just:

DROP DATABASE {MyDBName};

However, that alone didn't fix it without the auto-commit.

I hope that is helpful to someone, time for bed : )

SteveJ
  • 3,034
  • 2
  • 27
  • 47
  • 1
    Not sure if this is a difference with the latest version or a typo in the answer, but for anyone else dealing with this, I had to use `autocommit=True` (note the lack of underscore). `auto_commit=True` threw up concatenation errors, and the parameter without `=True` also didn't work. – gramblestown Mar 22 '16 at 14:01
  • 1
    @Aujury Thanks, my answer wasn't clear. I had a variable named 'auto_commit'. I should have included that in the code. I've edited it now - thanks for correcting me. – SteveJ Mar 22 '16 at 20:14