3

I am new to Python scripting and when I was converting my shell scripts to Python for a Netezza DB call in which a stored procedure is invoked with passed arguments.Everything is working as expected and giving result same as Shell.But in one case if one parameter is null it will read that data from a Netezza table (Varchar field).While I was testing that scenario and try to print the result read from I got a weird error saying " 'ascii' codec can't encode characters in position 0-2: ordinal not in range(128)".I tried to convert the value to string but it is not working. Attaching the script for reference. Note:The script may not follow the Python standards.Open to any Suggestions for improving the code

connection

    try:
    conn_str   ="DRIVER={NetezzaSQL};SERVER="+results.host+";PORT=5480;DATABASE="+results.sugarDB+";UID="+results.username+";PWD="+results.password+""
    print conn_str
    conn_sugar = pyodbc.connect(conn_str,ansi=True)

    cur_sugar = conn_sugar.cursor()
    if (conn_sugar):
        print "Connection successful"

except Exception, e:

    print "Error while creating Netezza connection Error:",e

    sys.exit(-1)

reading data from Netezza table

try:

checking for null parameter dim list

    if str(results.dimList)=="":
        print "dimlist is  null"

        var_query="select  LP.DIMENSIONS AS DIMENSIONS from PICASO..LKP_PX_RECOMMEND_METADATA LP where LP.client_id="+results.clientID+""
        print var_query
        for row in cur_sugar.execute(var_query):
            print "line no 62"
            print row.DIMENSIONS 
        conn_sugar.commit();            
    else:
        print "dimlist is not null",results.dimList
        v=results.dimList

    cur_sugar.execute("{exec SQLTOOLKIT..UDP_PRC_GET_MEDIAPLAN_RECOMMENDATION_3004("+results.clientID+","+results.configID+","+results.jobinstanceID+",'"+results.convBegin+"','"+results.convEnd+"','"+results.jaMeta+"','"+results.sugarDB+"','"+results.dimList+"','"+results.flag+"')}")
    conn_sugar.commit();
    conn_sugar.close();
except Exception, e:
    print "procedure call failed!!! Error :",e

Error coming as

procedure call failed!!! Error : 'ascii' codec can't encode characters in position 0-2: ordinal not in range(128)

Thanks Anoop R

MikA
  • 5,184
  • 5
  • 33
  • 42
Anoop R
  • 545
  • 3
  • 8
  • 19

2 Answers2

0

The error message is saying that it cannot parse the bytes into a valid ascii string. Decode for bytes has an option for how to handle errors. You can 'ignore' or 'replace'. 'replace' will stuff a question character in where the original bytes could not be parsed into ascii.

value = b''
val_str = value.decode("ascii", 'ignore')

Think of the ordinal as the decimal number in bytes for the ascii table lookup. http://www.asciitable.com/

value = bytes([97]) # a
val_str = value.decode("ascii", "ignore")
print(val_str)
justengel
  • 6,132
  • 4
  • 26
  • 42
  • Thanks for the reply.I will try the solution given by you.I am confused why varchar field is not readable by python odbc.Attached the code for reference. – Anoop R Oct 06 '15 at 12:48
  • I tried this and it is returning same error 'ascii' codec can't encode characters in position 0-2: ordinal not in range(128) – Anoop R Oct 06 '15 at 18:19
  • @ScottMcG Share your thoughts if you have encountered with same issue – Anoop R Oct 09 '15 at 06:14
  • My guess is that there is a problem with the library you are using. conn_sugar/pyodbc may have a bug in it. If it is native python code you could dig deep in the source and try to fix it by following the links from the traceback error (don't catch the error and let it print to the console). If you develop with eclipse you can debug and try to follow the execute/commit command or you can highlight execute and hit F3 (Open Declaration) to follow where that code is and what it does. F3 may not work for some library code. – justengel Oct 09 '15 at 12:59
0

This issue could be related to UTF-8 conversion. Your result filed has non-unicode filed and that might be causing the issue. Try this solution.

pyodbc remove unicode strings

Refer: UnicodeDecodeError: 'ascii' codec can't decode byte 0xd1 in position 2: ordinal not in range(128)

A similar issue at Django area. This will give you an idea where it originates from.

Community
  • 1
  • 1
Bimal
  • 16
  • Bimal The in table definition I used Varchar not nvarchar.For Example I am storing value 'Custom1' to this field no other special characters .Anyways I already tried all decode options But that didn't solve the issue.Is this related to Netezza driver encoding technique I am not sure .In shell script we are directly fetching this column value to a variable with out any issues – Anoop R Oct 08 '15 at 02:14