SQLite and coercing to Unicode - please help.

Discussion in 'Python' started by special_dragonfly, Sep 6, 2007.

  1. 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
    special_dragonfly, Sep 6, 2007
    #1
    1. Advertising

  2. special_dragonfly

    Steve Holden Guest

    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

    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, Sep 6, 2007
    #2
    1. Advertising

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



    "Steve Holden" <> wrote in message
    news:...
    > 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
    >
    > 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, Sep 6, 2007
    #3
  4. special_dragonfly

    Steve Holden Guest

    special_dragonfly wrote:
    > 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 -------------
    Steve Holden, Sep 6, 2007
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Stuart Forsyth
    Replies:
    1
    Views:
    4,912
    vincent wehren
    Nov 13, 2003
  2. Randall Parker
    Replies:
    2
    Views:
    806
    Fredrik Lundh
    Mar 22, 2006
  3. Max Erickson
    Replies:
    0
    Views:
    1,258
    Max Erickson
    Jun 21, 2006
  4. Fredrik Lundh
    Replies:
    0
    Views:
    765
    Fredrik Lundh
    Jun 21, 2006
  5. Jon Bowlas
    Replies:
    6
    Views:
    20,083
    Jon Bowlas
    Jul 27, 2006
Loading...

Share This Page