Sqlite update don't working right - python

EDIT: after some test i found out that it don't was the addpoint method that failed.

I'm working on a small game to a irc bot. This method will update the score in database called 'score', the are only two players. It's a sqlite database. It's mostly the update sql that ain't working right.

Thanks

def addpointo(phenny, id, msg, dude):
 try:
  for row in c.execute("select score from score where id = '0'"):
   for bow in c.execute("select score from score where id = '1'"):
 开发者_如何学C   if int(row[0]) == 3:
     phenny.say("Winner is " + dude)
     clear("score") # clear db
     clear("sap") # clear db
    elif int(bow[0]) == 3:
     phenny.say("Winner is " + dude)
     clear("score") # clear db
     clear("sap") # clear db
    else:
     phenny.say(msg)
     s = c.execute("select score from score where id=?", id)
     a = int(s.fetchone()[0]) + 1
     print a
     c.execute("update score SET score =? where id =?", (a, id)) #here i got some prolem
     conn.commit()
 except Exception:
  phenny.say("Error in score. Try to run '.sap clear-score' and/or '.sap clear-sap'")
  pass

and this is the way i created the score db

def createscore():
 if not (checkdb("score") is True):
  c.execute('''create table score (id int, score int)''')
  c.execute('insert into score values (0, 0)')
  conn.commit()
  c.execute('insert into score values (1, 0)')
  conn.commit()

error message: parameters are of unsupported type

Although the original author has most likely moved on, I figured I'd leave an answer here for future Googler's (like me ^_^).

I think what's happening here is that the following error...

ValueError: parameters are of unsupported type

... is actually coming from the following line (contrary to what the author said).

s = c.execute("select score from score where id=?", id)

The problem here is that Cursor.execute accepts the query string as the first parameter (which he has right), but a list, tuple, or dict as the second parameter. In this case, he needs to wrap that id in a tuple or list, like this:

s = c.execute("select score from score where id=?", (id,))

A list or tuple can be used with positional arguments (which is when you use a question mark ? as the placeholder). You can also use a dict and :key for named arguments, as follows:

s = c.execute("select score from score where id=:id", {"id": id})

There is an error in your last select

This

s = c.execute("select score from score where id='id'")

must be written as

s = c.execute("select score from score where id=?", id)

You have another serious issue with your code assuming 'c' is a cursor. SQLite cursors get the next result row one at a time (ie each time through the for loop) rather than all in advance. If you reuse a cursor then it replaces the current query with a new one. For example this code will only run through the loop once:

for row in c.execute("select * from score"):
   for dummy in c.execute("select 3"):
      print row, dummy

Your solutions include:

• Add .fetchall() on the end: c.execute("select * from score").fetchall() which gets all the rows up front rather than one at a time.

• Use different cursors so the iteration through each one doesn't affect the others

• Make a new cursor - replace c.execute("...") with conn.cursor().execute("...") Recent versions of pysqlite let you do conn.execute("...") which is effectively doing that above behind the scenes.

Cursors are very cheap so do not try to conserve them - use as many as you want - and you won't have errors like this.

In general it is also a good idea to be very careful reusing iterators and modifying what you are iterating over within the same series of loops. Various classes behave in varying ways so it is best to assume they do not like it unless shown otherwise.