Sqlite更新无法正常工作-python-Sqliteupdatedon'tworkingright-python

26  

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

虽然原作者最有可能继续前进，但我想我会在这里为未来的Googler留下答案（就像我^ _ ^）。

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

我想这里发生的是以下错误......

ValueError: parameters are of unsupported type

ValueError：参数属于不受支持的类型

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

这里的问题是Cursor.execute接受查询字符串作为第一个参数（他有权），但是列表，元组或dict作为第二个参数。在这种情况下，他需要将该id包装在元组或列表中，如下所示：

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:

列表或元组可以与位置参数一起使用（当您使用问号？作为占位符时）。您还可以使用dict和：key作为命名参数，如下所示：

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

2  

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)

2  

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:

假设'c'是游标，你的代码还有另一个严重问题。 SQLite游标一次获得一个下一个结果行（即每次通过for循环），而不是提前完成。如果重用游标，则会用新的查询替换当前查询。例如，此代码仅运行一次循环：

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.

  在末尾添加.fetchall（）：c.execute（“select * from score”）。fetchall（）获取前面的所有行而不是一次获取一行。

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

  创建一个新游标 - 用conn.cursor（）替换c.execute（“...”）。execute（“...”）pysqlite的最新版本让你做conn.execute（“...”）这是在幕后有效地做到这一点。

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.

一般来说，重新使用迭代器并修改在同一系列循环中迭代的内容也是一个好主意。各种类的行为方式各不相同，因此除非另有说明，否则最好假设它们不喜欢它。