[Tutor] sqlite3 Python25 parameter binding problem with UPDATEplease help

[aivars]

Hello again,

Finally I managed to get it working!

I had to remove [year] braces from the second argument - year. The
reason I used [] was the posts we exchanged recently (7th of November)
about CGI script where it was recommended by Kent that I put [] around
string argument:

Quote
The second argument to execute() is a *sequence* of parameter values.
A string is a sequence of characters, so by passing a plain string you
are saying that each character of the string is a parameter to the
SQL.

Try adding braces around the parameter to make  list:
cur.execute("insert into test (name) values (?)", [sPath])

Kent
Unquote

Now the year argument is string so I thought I should do the same
again but it is not working.
The reason is of course is that I am new to python and there are so
many things to learn so the question really is why in this case there
were no [] or () necessary?

Thanks in advance

Aivars



2008/12/2 aivars <aivars868 at gmail.com>:
> Interestingly, when simply testing like this:
>
> import sqlite3, sys
> sPATH=r'e:\pythonexamples\aivars2.db'
> oCon=sqlite3.connect(sPATH)
>
> cur=oCon.cursor()
>
> oCon.execute("""UPDATE rezerve SET latusaldo = ? WHERE gads = ?
> """,(6000.0,'2006'))
>
> oCon.commit()
>
>
> it works. Therefore I am stuck since it looks like there is something
> wrong in below function.
>
> Thanks,
>
> Aivars
>
>
> 2008/12/2 aivars <aivars868 at gmail.com>:
>> Alan,
>> Thanks.
>>
>> Ok I should have been more precise and give more code
>> There is what I do:
>>
>> def starpiba(year, month, day):
>>    datumsno=str(year+'-01-01') #beginning of year date
>>    datumsuz=str(year+'-'+month+'-'+day) #period end date
>>
>>    result = (atlikumiBeiguKurss(year, month, day)-
>> atlikumiDienasKurss(year, month, day))
>>    ##print result
>>    oCon=sqlite3.connect(sPATH)
>>    if result<=0:
>>        print abs(result)
>>        oCon.execute("UPDATE rezerve SET latusaldo =? where gads
>> =?;",(result, [year]))
>>        oCon.commit()
>>    else:
>>        ##print 'aivars'
>>        oCon.execute("UPDATE rezerve SET latusaldo =? where gads
>> =?;",(result, [year]))
>>
>> Please bear in mind I am a noob in Python and I write spaggeti code.
>>
>> There is a table in my sqlite database called rezerve which shows the
>> breakdown of a consolidated reserves item in a consolidated balance
>> sheet report (if that says anything to you) by years. For the previous
>> years the numbers does not change since they are already reported but
>> for the current year it may change month by month until reported.
>> Therefore, I wanted to update the numbers for the current year.
>>
>> The above python function works OK.
>>
>> Thanks
>>
>> Aivars
>>
>>
>> 2008/12/2 Alan Gauld <alan.gauld at btinternet.com>:
>>>
>>> "aivars" <aivars868 at gmail.com> wrote
>>>
>>>> oCon.execute("UPDATE rezerve SET latusaldo =? where gads =?;",(result,
>>>> [year]))
>>>> oCon.commit()
>>>>
>>>> it throws me the error:
>>>> sqlite3.InterfaceError: error binding parameter 1 probably unsupported
>>>> type
>>>
>>> I assume its the [year] value it objects to.
>>> I'm not sure what you expect SQLite to do with a list as a value, it does
>>> not support a list type field.
>>>
>>>> All works OK when using INSERT with the same parameters.
>>>
>>> Are you sure? You can insert a list into a field?
>>>
>>>> Maybe I should as this question on sqlite list?
>>>
>>> Perhaps but I think it is the list parameter that it doesn't like.
>>> Unless I misunderstand the syntax.
>>>
>>> --
>>> Alan Gauld
>>> Author of the Learn to Program web site
>>> http://www.freenetpages.co.uk/hp/alan.gauld
>>>
>>> _______________________________________________
>>> Tutor maillist  -  Tutor at python.org
>>> http://mail.python.org/mailman/listinfo/tutor
>>>
>>
>