Python: sqlite update with multiple parameters

[Caraka]

I am having trouble structuring an sqlite update statement that has multiple parameters. The following code does not raise an exception, but nor does it update my table. After extensive googling and searching Daniweb, I think I'm looking right at the blindingly obvious and missing it.
I have a list of tuples, PVitems which when iterated (as at the bottom of my code) looks like this:
(21, '1570600')
(10, '1979203')
(7, '1979202')
(6, '1979201')
The first integer is the PV count while the string is a meshblock number. All my meshblocks are handled as strings because many start with zeros. Some code:

conn = sqlite3.connect(db)
g = conn.cursor()
for pv in PVitems:
    try:
        g.execute('UPDATE meshblock_1107 SET etv_1107=? WHERE meshblock_06=?', (pv[0:2]))
    except:
        print("I dropped the ball:")
g.close()
for i in range(4):
    print(PVitems[i])

I have tried various formulations for the parameters at the end which either raise an exception or execute while failing to update. I have also tried splitting each parameter into individual tuples without joy. Any ideas what I am missing?

[woooee]

You have 2 ?s and one variable. Also, you should use a dictionary as it is more secure.

#update if meshblock_06=='21'
g.execute('UPDATE meshblock_1107 SET etv_1107==:update_1 WHERE meshblock_06==:select_1', {'update_1':pv[0:2], 'select_1':'21'})

[Caraka]

Thanks woooee. I've been down this road without success, using:

for pv in PVitems:
    pv0 = pv[0]
    pv1 = pv[1]
    try:
        g.execute("UPDATE meshblock_1107 SET etv_1107 ==:pv0 WHERE meshblock_06 ==:pv1", {"pv0":pv0, "pv1":pv1})
    except:
        print("I dropped the ball:")

The change was that in my earlier tries I did not have a == but a =. Nevertheless, like all my previous attempts, the statement executes without exception but does not update the table. From my googling and examples on the net, I believe that (pv[0:2]) is an iterable tuple. I didn't get any missing argument exceptions using it.

Because I am not having any exceptions raised, I keep coming back to the notion that my WHERE statement simply isn't true. However, when I run the statement at a command prompt (with the meshblock id variable '1570600' cut from the tuple examples above), the update works perfectly.

OH - and I like your suggestion of using a dictionary. In fact, PVitems is a list of tuples drawn from a dictionary because I ahve no idea how I would use a dictionary in an sql statement. Way too novice for that just yet. Any links you could send my way?

[woooee]

Did you commit() afterwards? All of my links are pretty old http://eringary.com/python/pysqlite.html

[Caraka]

Did you commit() afterwards? All of my links are pretty old http://eringary.com/python/pysqlite.html

Ahhh, dearest woooee. I said in my opening thread that I was missing something blindingly obvious - like plugging the computer in before trying to turn it on!

I will test both the code snippets that I have posted here - with a commit()!!! I will then come back and confirm the one(s) that work and call it solved.

Thank you for asking the blindingly obvious question. <blush> As a service manager with RTFM posted proudly above my desk, I had this one coming!

[woooee]

The solutions to all problems are blindingly obvious __after__ you know what it is. Also, you can check the database to make sure that there are pv0 recs with a SELECT * and a fetchall. If the resulting list has a length, then there are records.

[Caraka]

For completeness, I tried several of my 'failed' versions of the sql statement:

conn = sqlite3.connect(db)
g = conn.cursor()
for pv in PVitems:
    try:
        g.execute('UPDATE housecount_06 SET etv_1107=? WHERE meshblock_06=?', (pv[0:2]))
    except:
        print("I dropped the ball:")
conn.commit()
g.close()

My original code works, with a commit(). (table name changed to protect the innocent)

My second dictionary code snippet also works with a commit():

conn = sqlite3.connect(db)
g = conn.cursor()
for pv in PVitems:
    pv0 = pv[0]
    pv1 = pv[1]
    try:
        g.execute("UPDATE housecount_06 SET etv_1107 ==:pv0 WHERE meshblock_06 ==:pv1", {"pv0":pv0, "pv1":pv1})
    except:
        print("I dropped the ball:")
conn.commit()
g.close()

Thanks for your support Woooee!