SQLite and coercing to Unicode - please help.

[special_dragonfly]

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

 

[Steve Holden]

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.Traceback (most recent call last):
Traceback (most recent call last):

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

try:
branch=dom.getiterator(Elements[1])
Values.append(branch[0].text) # currencyID
except:
Values.append(" "*3)

when you could just as easily say

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

The usual shorthand for


(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])

would, by the way, be

tuple(Values)

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.

regards
Steve

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 -------------

 

[special_dragonfly]

That helped immensely Steve thank you. You're right, this is my first really
big project ever really, not just in Python.
Just to clarify, my UPDATE statement instead of looking like this:

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]+"'"
cursor.execute(longstring)

should instead look more like this:
cursor.execute('UPDATE SecB SET
(?,?,?,?,?,?,?,?,?,?,?,?,?,?)',tuple(Values[1:])) ?

The Elements list was from a time when it looked less pretty than it does
now, where I iterated through it and didn't catch errors at all.

Thank you again for your help and when it's finished and working I'll repost
it online somewhere for ideas on how to optimise it slightly more!
Dominic

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.Traceback (most recent call last):
Traceback (most recent call last):

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

try:
branch=dom.getiterator(Elements[1])
Values.append(branch[0].text) # currencyID
except:
Values.append(" "*3)

when you could just as easily say

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

The usual shorthand for


(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])

would, by the way, be

tuple(Values)

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.

regards
Steve

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 -------------

 

[Steve Holden]

That helped immensely Steve thank you. You're right, this is my first really
big project ever really, not just in Python.
Just to clarify, my UPDATE statement instead of looking like this:

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]+"'"
cursor.execute(longstring)

should instead look more like this:
cursor.execute('UPDATE SecB SET
(?,?,?,?,?,?,?,?,?,?,?,?,?,?)',tuple(Values[1:])) ?

Closer. It really needs to be more like

cursor.execute("""UPDATE SecB
SET currencyCode=?, issuerName=?, instrName=?,
...
openOrClosed=?
WHERE secBoardId=?""", data)

In this case, since the secBoardID is used as the last parameter, data
really needs to be se at

data = tuple(Values[1:] + Values[:1])

But of course it would probably be easier to assemble Values in the
right order in the first place. You will still need to turn it into a
tuple, however, for cursor.execute().

The Elements list was from a time when it looked less pretty than it does
now, where I iterated through it and didn't catch errors at all.

Thank you again for your help and when it's finished and working I'll repost
it online somewhere for ideas on how to optimise it slightly more!
Dominic

Good one!

regards
Steve
--
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 -------------