SQLite and coercing to Unicode - please help.

Steve Holden steve at holdenweb.com
Thu Sep 6 12:57:20 CEST 2007

special_dragonfly wrote:
> 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
OK, the first problem is that you appear to be obsessing about Values[0] 
when there doesn't appear to be any evidence that value in particular is 
causing the problem. The line number being reported is simply that of 
the last line in a single long statement, and the issue could be 
anywhere in that statement. Sorry, you just have to know (or guess) 
that, it's not a particularly admirable feature of Python.

In point of fact it is the None values that are causing the problem:

$ /usr/bin/python
Python 2.5.1 (r251:54863, May 18 2007, 16:56:43)
[GCC 3.4.4 (cygming special, gdc 0.12, using dmd 0.125)] on cygwin
Type "help", "copyright", "credits" or "license" for more information.
 >>> "a string"+None
Traceback (most recent call last):
   File "<stdin>", line 1, in <module>
TypeError: cannot concatenate 'str' and 'NoneType' objects
 >>> u"a string"+None
Traceback (most recent call last):
   File "<stdin>", line 1, in <module>
TypeError: coercing to Unicode: need string or buffer, NoneType found

The XML values you are retrieving will be Unicode strings, but you are 
mixing them with plain string values, hence the (somewhat unhelpful) 
error message..

Secondly, you are trying to construct a SQL UPDATE statement yourself, 
instead of parameterising it like you did your INSERT statement. This is 
also usually asking for trouble, and is vulnerable to SQL injection 
errors (use Google if you don't know what they are).

Thirdly, I'm not quite sure why you are using a list for the values, and 
another one for the Elements, when it would seems to make more sense to 
use a named variable for the former and just use the necessary string 
literals for the latter. What's the advantage of using

             Values.append(branch[0].text) # currencyID
             Values.append(" "*3)

when you could just as easily say

             branch = dom.getiterator("currencyID")
             currencyID = branch[0].text
         except (..., ..., ...): # see below
             currencyID = "   "

The usual shorthand for


would, by the way, be


Note also that your INSERT statement assumes a specific ordering to the 
columns, which is asking for trouble when the database goes into 
production, as a DBA might re-order the fields during a reorganization 
or restructuring of the data, and then your code will break.

Finally, and almost completely irrelevant to the issue at hand, all 
those "except" clauses will catch *absolutely any error* that may occur 
in your code, which is almost always a bad idea, since this behavior 
will mask certain errors that you really want to know about. The XML 
analysis code is frankly pretty horrible, but we can address that issue 
once you start to get some results.

Sorry to bash what could well be your first significant program in 
Python, but my main interest is in setting you off down the right path. 
It will save time and grief in the end. Hope this has helped.


> 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)
[other horrible code elided ...]

>         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
Steve Holden        +1 571 484 6266   +1 800 494 3119
Holden Web LLC/Ltd           http://www.holdenweb.com
Skype: holdenweb      http://del.icio.us/steve.holden
--------------- Asciimercial ------------------
Get on the web: Blog, lens and tag the Internet
Many services currently offer free registration
----------- Thank You for Reading -------------

More information about the Python-list mailing list