SQLite and coercing to Unicode - please help.
special_dragonfly
Dominic at PLEASEASK.co.uk
Thu Sep 6 06:01:49 EDT 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