SQL.fetchall() fails with the SQLState 24000 (Invalid cursor state) with Python

Question

Currently I connect successfully to a database and then I execute some sql statements but when I try to retrieve the query results I encounter the following raise ProgrammingError(state,err_text) pypyodbc.ProgrammingError: ('24000', '[24000] [Microsoft][ODBC SQL Server Driver]Invalid cursor state').

This is my code:

# Connection to MSSQL
import pypyodbc

connection_live_db = pypyodbc.connect(driver="{SQL Server}", server="xxx.xxx.xxx.xxx", uid="test",
                                                  pwd="xxxx", Trusted_Connection="No")

credit_hold_co = connection_live_db.cursor()

# read the sql file in buffer and close
read_sql_file = open('C:\\Users\\userX\\Documents\\Scripts\\credit_hold_co.sql','r')
sql_file = read_sql_file.read()
read_sql_file.close()

# split each sql statement by ;
sqlCommands = sql_file.split(';')

results_sql = ""

# iterate over each command and execute it
for command in sqlCommands:
    try:
        credit_hold_co.execute(command)
    except ValueError:
        print(command)
    # retrieve results
    results = results + str(credit_hold_co.fetchall())


# close sql connection
credit_hold_co.close()

This is an example of the sql commands that I am trying to execute:

-- credit_hold_co
if OBJECT_ID('tempdb..#Credit_Hold_CO') is not NULL
drop table #Credit_Hold_CO;

create table #Credit_Hold_CO
    ([co_num] varchar(30),
     [credit_hold] char(1),
     [credit_hold_reason] char(5),
     ['Type of credit hold reason'] varchar(20),
     [credit_hold_date] datetime );

insert into #Credit_Hold_CO([co_num],[credit_hold],[credit_hold_reason],['Type of credit hold reason'],[credit_hold_date])
  select distinct co_num, credit_hold, credit_hold_reason, 
   (case 
       when credit_hold_reason = 'PD' then 'Past due payments'
       when credit_hold_reason = 'BR' then 'Bankruptcy'
    end) as 'Type of credit hold reason',
 credit_hold_date
 FROM [Database].[dbo].[co] where credit_hold = '1';

select * from #Credit_Hold_CO
order by orig_site;

drop table #Credit_Hold_CO;

I have found some links to the current problem such as pypyodbc invalid cursor name and SQL statement invalid cursor state 24000 and both suggest to create another cursor to avoid the results of the first cursor to get invalidated but no more details were provided.

Questions and answers updated on July 17th @13:31 MT:

How can I retrieve correctly the results of my sql statements? See answer from below.
Do I strongly need another cursor or is there a better approach to solve this problem? No, another cursor was not the solution but getting rid of the temp tables from the SQL query.

['Type of credit hold reason'] varchar(20), is not valid, not sure if that's just an example but you can't have quotes in the name — sniperd
– sniperd, Commented Jul 17, 2018 at 17:17
Running ['Type of credit hold reason'] varchar(20) works fine in the query. — abautista
– abautista, Commented Jul 17, 2018 at 17:19
No worries about that, thanks for trying to give some clues about what might be wrong. — abautista
– abautista, Commented Jul 17, 2018 at 17:21
I have some code that does pretty much exactly what you are looking to do. Not enough space to put it in as a comment though. May I suggest running: credit_hold_co = connection_live_db.cursor() and closing it in the while loop? Which sounds like your 2. item :) — sniperd
– sniperd, Commented Jul 17, 2018 at 17:25

abautista · Accepted Answer · 2018-07-17 19:55:21Z

Alejandro BR and I continued our extensive comments into StackOverflow chat. After some debugging it was two fold:

-The file should be of encoding ANSI or if was UTF-8 read it in as such: open("your location","r", encoding="utf-8", errors="replace")

-Simplify the Python in the following way to get it off the ground, then proceed to loop it. Need a cursor for each SELECT

Find below the updated query:

# Connection to MSSQL
import pypyodbc

connection_live_db = pypyodbc.connect(driver="{SQL Server}", server="xxx.xxx.xxx.xxx", uid="test",
                                              pwd="xxxx", Trusted_Connection="No")

credit_hold_co = connection_live_db.cursor()

# read the .txt file that contains the query 
# Avoid strange characters by saving the file in ANSI
# or use the following code from below if you save in UTF-8
read_sql_file = open('C:\\Users\\userX\\Documents\\Scripts\\credit_hold_co.txt','r', encoding="utf-8", errors="replace")
sql_file = read_sql_file.read()
read_sql_file.close()

# Execute the file that contains the sql query 
credit_hold_customer_orders.execute(sql_file) 

# store the results in variable
results = credit_hold_customer_orders.fetchall() 

# close cursor
credit_hold_customer_orders.close() 
print(results)

Also, we had to update the sql query from the txt file:

select distinct co_num, credit_hold, credit_hold_reason, 
   (case 
       when credit_hold_reason = 'PD' then 'Past due payments'
       when credit_hold_reason = 'BR' then 'Bankruptcy'
    end) as 'Type of credit hold reason',
 credit_hold_date
 FROM [Database1].[dbo].[co] where credit_hold = '1'

union all

select distinct co_num, credit_hold, credit_hold_reason, 
   (case 
       when credit_hold_reason = 'PD' then 'Past due payments'
       when credit_hold_reason = 'BR' then 'Bankruptcy'
    end) as 'Type of credit hold reason',
 credit_hold_date
 FROM [Database2].[dbo].[co] where credit_hold = '1'

Collectives™ on Stack Overflow

SQL.fetchall() fails with the SQLState 24000 (Invalid cursor state) with Python

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related