Python -- list index out of range -- on a simple SELECT statement?

I'm new to Python, so maybe I'm making a newbie mistake.  But this doesn't seem the kind of error I should get in this circumstance.

On a very simple SELECT statement, I'm getting a "list index out of range" error.

        sql = """
            begin tran
            -- Several update statements are in this block.

            commit tran"""
        sql = sql.format(tablename=self.tablename, **self.mappings)
        #print(sql)
        self.cursor.execute(sql, (self.catalog_id,self.catalog_id,self.catalog_id,))

        
        self.cursor.execute("SELECT CaptionText, DisplayOrder FROM dbo.SizeOrder where CaptionText is not NULL and len(CaptionText)>0")  #This is the line that breaks!
        
        size_order = {row[0].lower(): row[1] for row in self.cursor}

Open in new window


It's the next to last row that breaks.  It's not doing any funny formatting.  I don't do any of the substitutions in this problem query. When run directly against the DB, it returns over 300 records.

The trace output definitely implicates that line of code.  I really suspected the next line b/c it involves indexes.  But a return after the execute helped nothing.
Traceback (most recent call last):
  File "import.py", line 782, in <module>
    main(sys.argv)
  File "import.py", line 167, in main
    db.transform_catalog((len(args)>4 and args[4] == "--skipimages") or (len(args)>5 and args[5] == "--skipimages") )
  File "import.py", line 235, in transform_catalog
    self.do_transform(skipImages)
  File "import.py", line 264, in do_transform
    self.insert_size_types()
  File "import.py", line 501, in insert_size_types
    self.cursor.execute("SELECT CaptionText, DisplayOrder FROM dbo.SizeOrder  where CaptionText is not NULL and len(Capt
ionText)>0")
  File "C:\Python33\lib\site-packages\pypyodbc.py", line 1449, in execute
    self._free_stmt(SQL_CLOSE)
  File "C:\Python33\lib\site-packages\pypyodbc.py", line 1971, in _free_stmt
    check_success(self, ret)
  File "C:\Python33\lib\site-packages\pypyodbc.py", line 986, in check_success
    ctrl_err(SQL_HANDLE_STMT, ODBC_obj.stmt_h, ret, ODBC_obj.ansi)
  File "C:\Python33\lib\site-packages\pypyodbc.py", line 951, in ctrl_err
    state = err_list[0][0]
IndexError: list index out of range

Open in new window


What could I be doing wrong?
LVL 32
Daniel WilsonAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark BradyPrincipal Data EngineerCommented:
That's a good question. You could try changing the len(CaptionText)>0 to where CaptionText != ''
and see if that helps.
Daniel WilsonAuthor Commented:
Well, the query works.  But ... here are versions I've tried with the same result:

SELECT CaptionText, DisplayOrder FROM dbo.SizeOrder where CaptionText is not NULL and len(CaptionText)>0
SELECT CaptionText, DisplayOrder FROM dbo.SizeOrder
SELECT CaptionText, DisplayOrder FROM dbo.SizeOrder  where CaptionText is not NULL and CaptionText != ''
SELECT CaptionText, DisplayOrder FROM dbo.SizeOrder  where CaptionText is not NULL and CaptionText != '' and DisplayOrder <=15

The data returned by the queries with a WHERE clause are very clean.
Daniel WilsonAuthor Commented:
The previous query contained an error causing the connection object to be in a bad state.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Daniel WilsonAuthor Commented:
I found my error.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Python

From novice to tech pro — start learning today.