SQLite and coercing to Unicode - please help.

special_dragonfly Dominic at PLEASEASK.co.uk
Thu Sep 6 12:01:49 CEST 2007


Hello!
First, the problem: the program below falls over with the following error:
TypeError: coercing to Unicode: need string or buffer, NoneType found.
and gives the following line:
"' WHERE secBoardId='"+Values[0]+"'"
My first thought was that Values[0] was containing nothing at all, that 
would allow a NoneType to be found, but it has data in that position of the 
list - you'll see below.
So I thought that Values[0] was 'losing' its type somewhere, so I checked it 
just by having the program print out type(Values[0]), it says it's a 
string - also seen below.
So given it's not a NoneType and it's a string, I don't understand why I'm 
getting the error. I tried forcing it to a particular type (I think that's 
the right word), so I put lines like:
Values[0]=unicode(Values[0]) or
WHERE ... ='"str(Values[0]+"'"
but neither worked.
I've put my code below, I'm sorry it's so long, I've commented the line 
where it's falling over.
If it's useful, I am using Python 2.5.1, ElementTree 1.2.6 and pySQLite 
2.3.5
If this is a silly mistake ( one where RTFM is a valid response, can you 
point me at the place where I can get the answer please?), otherwise any 
help is greatly appreciated as I'm out of ideas. :(
Dominic


The output:
At top of function: GBP/PLUS-ofn-GB00B12T7004
Values list contains: ['GBP/PLUS-ofn-GB00B12T7004', u'GBP', u'GB00B12T7004 
', u'All Star Minerals plc                   ', 'ASMO', 'DE', 'PLUS', 
u'B12T700', u'        ', u'A', None, None, None, '000000000000000000', '0', 
'0']

Type of Values[0] is: <type 'str'>

The code:
def dealwithSecBRep(text_buffer):
    # text_buffer contains a sequential string of xml
    Elements=['secBoardId','currencyId','issuerId','secName','secShortName','secClassId',\
              'sectorId','isin','issueDate','inheritedState','bidPrice','offerPrice','midPrice',\
              'standardMarketSize','openPrice','closePrice']
    Values=[]
    dom=get_a_document(text_buffer) # this function returns an xml document.
    branch=dom.getiterator(Elements[0])
    Values.append(GetUniqueId(branch[0])) # Combo of secCode and secBoardId
    print "At top of function:",Values[0]
    sql=cursor.execute('SELECT*FROM SecB WHERE 
secBoardId='+"'"+Values[0]+"'").fetchall()
    SQL2=sql
    flag=0
    if len(sql)>0:
        #Prior database exists
        try:
            branch=dom.getiterator(Elements[1])
            Values.append(branch[0].text) # currencyID
        except:
            value=GetFromDB('currencyCode',text_buffer)
            Values.append(value)
        try:
            branch=dom.getiterator(Elements[2])
            Values.append(branch[0].text) # issuerName
        except:
            value=GetFromDB('issuerName',text_buffer)
            Values.append(value)
        try:
            branch=dom.getiterator(Elements[3])
            Values.append(branch[0].text) # Tradable Instrument Name
        except:
            value=GetFromDB('Tradable Instrument Name',text_buffer)
            Values.append(value)
        try:
            branch=dom.getiterator(Elements[4])
            Values.append(branch[0].text) # Tradable Instrument Short Name
        except:
            value=GetFromDB('Tradable Instrument Short Name',text_buffer)
            Values.append(value)
        try:
            branch=dom.getiterator(Elements[5])
            Values.append(branch[0].text) # Tradable Instrument Type
        except:
            value=GetFromDB('Tradable Instrument Type',text_buffer)
            Values.append(value)
        try:
            branch=dom.getiterator(Elements[6])
            Values.append(branch[0].text) # SectorCode
        except:
            Values.append('PLUS') # SectorCode if one does not naturally 
exist
        try:
            branch=dom.getiterator(Elements[7])
            value=str(branch[0].text)
            Values.append(value[4:11]) # SEDOL
        except:
            value=GetFromDB('SEDOL Code',text_buffer)
            Values.append(value)
        try:
            branch=dom.getiterator(Elements[8])
            Values.append(branch[0].text) # Date
        except:
            value=GetFromDB('Tradable Instrument Effective 
Date',text_buffer)
            Values.append(value)
        try:
            branch=dom.getiterator(Elements[9])
            Values.append(branch[0].text) # inherited State
        except:
            value=GetFromDB('inheritedState',text_buffer)
            Values.append(value)
        try:
            branch=dom.getiterator(Elements[10])
            Values.append(branch[0].text) # bidPrice if available
        except:
            Values.append("0"*18) # bidPrice if not available
        try:
            branch=dom.getiterator(Elements[11])
            Values.append(branch[0].text) # offerPrice if available
        except:
            Values.append("0"*18) # offerPrice if not available
        try:
            branch=dom.getiterator(Elements[12])
            Values.append(branch[0].text) # midPrice if available
        except:
            Values.append("0"*18) # midPrice if not available
        try:
            branch=dom.getiterator(Elements[13])
            Values.append(branch[0].text) # standardMarketSize if available
        except:
            Values.append("0"*18) # standardMarketSize if not available
        try:
            branch=dom.getiterator(Elements[14])
            Values.append(branch[0].text) # Stock Open
        except:
            Values.append("0") # Stock Close
        try:
            branch=dom.getiterator(Elements[15])
            Values.append(branch[0].text) # Stock Close
        except:
            Values.append("0") # Stock Open
        flag=0
    else:
        #Fill missing fields with blanks.
        try:
            branch=dom.getiterator(Elements[1])
            Values.append(branch[0].text) # currencyID
        except:
            Values.append(" "*3)
        try:
            branch=dom.getiterator(Elements[2])
            Values.append(branch[0].text) # issuerName
        except:
            Values.append(" "*35)
        try:
            branch=dom.getiterator(Elements[3])
            Values.append(branch[0].text) # Tradable Instrument Name
        except:
            Values.append(" "*40)
        try:
            branch=dom.getiterator(Elements[4])
            Values.append(branch[0].text) # Tradable Instrument Short Name
        except:
            Values.append(" "*15)
        try:
            branch=dom.getiterator(Elements[5])
            Values.append(branch[0].text) # Tradable Instrument Type
        except:
            Values.append("  ") #It's length 2
        try:
            branch=dom.getiterator(Elements[6])
            Values.append(branch[0].text) # SectorCode
        except:
            Values.append('PLUS') # SectorCode if one does not naturally 
exist
        try:
            branch=dom.getiterator(Elements[7])
            value=str(branch[0].text)
            Values.append(value[4:11]) # SEDOL
        except:
            Values.append(" "*7)
        try:
            branch=dom.getiterator(Elements[8])
            Values.append(branch[0].text) # Date
        except:
            Values.append(" "*8)
        try:
            branch=dom.getiterator(Elements[9])
            Values.append(branch[0].text) # inherited State
        except:
            Values.append('A') # Means Active
        try:
            branch=dom.getiterator(Elements[10])
            Values.append(branch[0].text) # bidPrice if available
        except:
            Values.append("0"*18) # bidPrice if not available
        try:
            branch=dom.getiterator(Elements[11])
            Values.append(branch[0].text) # offerPrice if available
        except:
            Values.append("0"*18) # offerPrice if not available
        try:
            branch=dom.getiterator(Elements[12])
            Values.append(branch[0].text) # midPrice if available
        except:
            Values.append("0"*18) # midPrice if not available
        try:
            branch=dom.getiterator(Elements[13])
            value=string.ljust(string.rjust(branch[0][0].text,10,"0"),18,"0")
            Values.append(value) # standardMarketSize if available
        except:
            Values.append("0"*18) # standardMarketSize if not available
        print "Values currenctly:",Values
        try:
            branch=dom.getiterator(Elements[14])
            value=string.ljust(string.rjust(branch[0][0].text,10,"0"),18,"0")
            Values.append(branch[0].text) # Stock Open
        except:
            Values.append("0") # Stock Close
        try:
            branch=dom.getiterator(Elements[15])
            value=string.ljust(string.rjust(branch[0][0].text,10,"0"),18,"0")
            Values.append(branch[0].text) # Stock Close
        except:
            Values.append("0") # Stock Open
        flag=1
    if flag==0:
        print "Values list contains:",Values,"\n"
        print "Type of Values[0] is:",type(Values[0]),"\n"
        longstring="UPDATE SecB SET 
currencyCode='"+Values[1]+"',issuerName='"+Values[2]+"',instrName='"+Values[3]+\
                    "',instrShortName='"+Values[4]+"',instrType='"+Values[5]+"',secCode='"+Values[6]+\
                    "',SEDOL='"+Values[7]+"',Date='"+Values[8]+"',SuspendedState='"+Values[9]+\
                    "',bidPrice='"+Values[10]+"',offerPrice='"+Values[11]+"',midPrice='"+Values[12]+\
                    "',standardMarketSize='"+Values[13]+"',openOrClosed='"+Values[14]+\
                    "' WHERE secBoardId='"+Values[0]+"'" # This is the line 
it falls over on.
        cursor.execute(longstring)
        connection.commit()
        currentStatus=SQL2[0][13] #current Stock Status
        if currentStatus==1: # Open
            if Values[14]!='0': # xml says open
                oldStart("<secBoardRep>",text_buffer) #5SE
            elif Values[15]!='0': # xml says closed
                oldStart("<ClosingRep>",text_buffer)
            elif Values[14]==0 and Values[15]==0: # neither openPrice nor 
closePrice exist in xml
                oldStart("<secBoardRep2>",text_buffer) # just 5ER and 5IS
        else: # currently Closed
            if Values[14]!='0': #xml says open
                oldStart("<OpeningRep>",text_buffer) # 5PR
            else: #xml says closed
                oldStart("<secBoard2Rep>",text_buffer) # 5ER,5IS
    else:
        cursor.execute('INSERT INTO SecB VALUES 
(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',\
                       (Values[0],Values[1],Values[2],Values[3],Values[4],Values[5],\
                        Values[6],Values[7],Values[8],Values[9],Values[10],Values[11],\
                        Values[12],Values[13],Values[14]))
        connection.commit()
        if Values[14]!='0': # xml says open
            test=dom.getiterator('openPrice')
            oldStart("<secBoard2Rep>",text_buffer) # 5ER, 5IS
            oldStart("<OpeningRep>",text_buffer) # 5PR
        else: # xml says closed, or neither options exist.
            oldStart("<secBoard2Rep>",text_buffer) # 5ER, 5IS





More information about the Python-list mailing list