3

I tried retrieving data from a Microsoft SQL database using pypyodbc 1.3.3 with Python 3.5 on Windows but got a pypyodbc.ProgrammingError '[24000] [Microsoft] [SQL Server Native Client 11.0] Invalid cursor state' using the following code:

import pypyodbc
conn = pypyodbc.connect(r'DRIVER={SQL Server Native Client 11.0};SERVER=server;DATABASE=database;UID=uid;PWD=pwd')
cursor = conn.cursor()
sql = '''USE database;
SELECT R0
FROM table;'''
cursor.execute(sql)
results = cursor.fetchone()
print(results)

The SQL works in Microsoft SQL Server Management Studio, the connection and executing worked in another script i wrote to insert into the same database and also works if i remove

results = cursor.fetchone()

So far I tried cursor.fetchone(), cursor.fetchall() and list(cursor) but all produced the same result which leads me to believe that the command itself isn't the problem. According to this microsoft site it means that there isn't an open cursor, but I can get it's description, so from my understanding there has to be.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
Lapuranebar
  • 31
  • 1
  • 3
  • 1
    Remove the `USE database` statement and try again. – sisanared Sep 23 '16 at 15:58
  • Yes agree with SSNR. My understanding is Microsoft lets you change databases. But Python does not let you. You have to connect to the correct database for what you want to work on first, and not change in the sql code. I believe it is a security issue where Python uses more secure methods than MS. – M T Head Sep 23 '16 at 16:53
  • Actually, just change the SELECT statement to something like `SELECT R0 FROM database.dbo.table;`. This is what multipart naming is for in SQL Server - .. is the format to reference a table in any DB, no matter which DB you are connected to. – Laughing Vergil Jun 02 '17 at 15:30

2 Answers2

1

I had a similar issue. I was able to resolve this by removing the "USE Database" statement.

You already connected to your db here:

conn = pypyodbc.connect(r'DRIVER={SQL Server Native Client 11.0};SERVER=server;DATABASE=database;UID=uid;PWD=pwd')
Daniel Long
  • 1,162
  • 14
  • 30
1

It's not a matter of being unable to execute a USE ... statement at all, it's just that we cannot do that as part of a multi-statement batch. So, this will not work ...

crsr.execute("""\
USE master;
SELECT TOP 2 name FROM sys.tables ORDER BY name;
""")
rows = crsr.fetchall()  # error

... but this will work fine

crsr.execute("USE master")
crsr.execute("SELECT TOP 2 name FROM sys.tables ORDER BY name")
rows = crsr.fetchall()

(Tested with both pypyodbc 1.3.4 and pyodbc 4.0.21)

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418