November 16, 2021

Ordinal not in range

Have you ever encountered an error where the "Ordinal not in range?"

Ordinal Not In Range

Sample Output:

UnicodeEncodeError: 'latin-1' codec can't encode characters in position 13821-13822: ordinal not in range(256)

According to various StackExachange comments, this error is thrown because Python can't encode some characters in a non Ascii string.

For me, this particular error occurred because I had bad data in my MySQL database - there was no codec checking done on INSERT. Apparently, PHP didn't have a problem handling the 'latin-1' codec - but using Python is now an issue.

Quick Easy Solution

My quick and easy solution is to have MySQL convert the text to ASCII. This is easy to do in-code:

cur.execute( "select date, CONVERT(teaserText USING ASCII), CONVERT(myTextData USING ASCII) from Order .... "")

Here's what happens: The MySQL CONVERT utility will convert the text in the teaserText and myTextData columns on the fly. The process is seemless and adds very little overhead while Python runs the query - your miles may vary if you have a complex database query.

Use Views

A better solution would be to create a view that would automatically convert the data, this way you can fix other columns if they cause issues:

CREATE OR REPLACE
ALGORITHM = UNDEFINED VIEW `order_vw` AS
select
    date_format(`w`.`orderdate`, '%M %e, %Y') AS `date`, 
   convert(`w`.`teaserText`
        using ascii) AS `teaserText`,
    convert(`w`.`myTextData`
        using ascii) AS `myTextData`,
    `c`.`name` AS `item`,
from
    ((`order` `w`)
where
    (`w`.`weblog` <= curdate())
order by
    `w`.`weblog`

Add your Comments

Feel free to leave a comment about this post.

- Feel Free to add HTML to your comment!