将变量分配给SQLite元组时出现问题 [英] Problems assigning variables to SQLite Tuples

问题描述

首先，如果这看起来很简单，我深表歉意，我对python来说还是个新手，而对SQlite来说却很新。我也为文字墙表示歉意，但是我从该站点获得了很多帮助，也许其他人也可以从该问题中学到东西。

First off, I apologize if this seems elementary, I am fairly new to python and very new to SQlite. I also apologize for the wall of text, but I have gotten so much help from this site, that maybe someone else can learn from this issue as well.

我有一个SQLite数据库 levelData.db ，其中有两个表名为 Counters_Daily & Counters_Total 。每个表都有三行对应于诸如用水量之类的数据，因此每日表有一个名为 dailyWater 的行，而总表有其自己的名为 totalWater 我正在尝试编写一个脚本，该脚本最终将获取 Counters_Daily 中的数据并将其添加到 Counters_Total 。

I have one SQLite database levelData.dbwith two tables called Counters_Daily & Counters_Total. Each table has three rows corresponding to data from things like water usage, so the daily table has a row called dailyWater and the total table has it's own row called totalWater I am trying to write a script which, at the end of the day will take the data that is in the Counters_Daily and add them to the Counters_Total.

我尝试着解决这个问题，这是我能想到的最基本的解决方案是将数据转换回变量，以便我可以对其进行隐蔽，然后将其替换为数据库。这对于我开始将数据放入数据库非常有用。这是我的工作代码示例：

I've tried wrapping my head around this and the most elementary solution I can come up with is to convert the data back to variables so that I can covert them and then replace them in the database. This has worked for me to get data into the database to start with. Here is an example of my working code:

def waterCounter(waterUsed):
    conn = sqlite3.connect('levelData.db')
    c = conn.cursor()
    c.execute ('SELECT * FROM Counters_Daily')
    originalData = int(c.fetchone()[1])
    updatedData = originalData + waterUsed
    c.execute ("UPDATE Counters_Daily SET DailyWater = (?)", (updatedData,))
    conn.commit()
    c.close()
    conn.close()

当我尝试更新<$ c $时，我的问题就变成了c> Counters_Total 数据库。如前所述，我想将每日数据添加到总数据中。所以我想我会提取数据并将其分配给一个变量（类似于上面的脚本），但是在拆开这些元组时遇到了问题，这就是我的问题所在。这是我的代码：

My problem then becomes when I try to update the Counters_Total database. As I mentioned, I want to add the daily data to the total data. So I figured I would extract the data and assign it to a variable (similar to the script above), but I'm having a problem unpacking those tuples and this is where my problem comes in. Here is my code:

def showData():
    conn = sqlite3.connect('levelData.db')
    c = conn.cursor()
    print "getting started"
    c.execute('SELECT * FROM Counters_Total')
    totalCounters = c.fetchall() #Assigns the list to a variable
    totalData = tuple(totalCounters) #Converts the list to tuples
    print "totalCounters:"
    print totalCounters
    print type(totalCounters)
    print "TotalData:"
    print totalData
    print type(totalData)
    (totalWater, totalLight, totalAir) = totalData #Assigns variables to each one of the tuples
    c.close()
    conn.close()

这是我得到的输出：

getting started
totalCounters:
[(50.0, 51.0, 52.0)]
<type 'list'>
TotalData:
((50.0, 51.0, 52.0),)
<type 'tuple'>
Traceback (most recent call last):
  File "databaseWorking.py", line 129, in <module>
    showData()
  File "databaseWorking.py", line 47, in showData
    (totalWater, totalLight, totalAir) = totalData
ValueError: need more than 1 value to unpack

对我来说有趣的是，看起来元组转换在哪里出错了，因为它列出了元组为（（（50.0，51.0，52.0），）），我不知道为什么最后还要加一个逗号。无论如何，那就是我被困住的地方。感谢您可以推荐的任何帮助或建议。

What is interesting to me is that it looks like the tuple conversion is where things are going awry because it lists the tuples as ((50.0, 51.0, 52.0),) and I have no idea why there is an extra comma at the end. Anyway, that is where I am stuck. I appreciate any help or advice you can recommend.

编辑

谢谢同时向@Setop和@timgeb寻求帮助和说明。我能够使用此代码，现在一切都变得轻松了：

Thank you to both @Setop and @timgeb for their help and explanations. I was able to use this code and now everything is copacetic:

def showData2():
    conn = sqlite3.connect('levelData.db')
    c = conn.cursor()
    print "getting started"
    c.execute('SELECT * FROM Counters_Total')
    totalCounters = c.fetchall() #Assigns the list to a variable
    (totalWater, totalLight, totalAir) = tuple(totalCounters[0])
    print "totalCounters:"
    print totalCounters
    print type(totalCounters)
    print "totalWater:"
    print totalWater
    print "totalLight:"
    print totalLight
    print "totalWater:"
    print totalAir
    c.close()
    conn.close()

推荐答案

fetchall 返回一个元组列表，看起来像是

fetchall returns a list of tuples, something that looks like

totalCounters = [(1, 2, 3), (4, 5, 6)]

当您执行 totalData = tuple（totalCounters）时，不要将列表转换为元组 S ，而是将 the 列表转换为 a 元组（除非您要修改该数据结构，否则您不会这样做确实会带来很大的变化）：

When you do totalData = tuple(totalCounters) you do not convert the list to tupleS, you convert the list to a tuple (and unless you want to modify that datastructure, you are not really changing much by doing that):

>>> totalData = tuple(totalCounters)
>>> totalData
((1, 2, 3), (4, 5, 6))

所以现在您有了一个元组。您的实际示例（（50.0，51.0，52.0），）是相似的，但是外部元组仅包含一个元素-因此，您有一个包含单个元组的元组。

So now you have a tuple of tuples. Your actual example ((50.0, 51.0, 52.0),) is similar, but the outer tuple only contains one element - so you have a tuple containing a single tuple.

当您执行 var1，var2，var3 = some_iterable 时，Python期望在右侧使用三个元素进行迭代并将名称 var1 ， var2 和 var3 分配给第一个，第二个和第三个元素。

When you do var1, var2, var3 = some_iterable Python expects an iterable with three elements on the right hand side and assigns the names var1, var2 and var3 to the first, second and third element respectively.

问题在于（（50.0，51.0，52.0），）只有一个元素，元组（50.0，51.0，52.0），这就是为什么会出错的原因。

The problem is that ((50.0, 51.0, 52.0),) only has one element, the tuple (50.0, 51.0, 52.0) and that's why you get your error.

您要么需要从元组/元组列表中选择一个特定的三元素元组就可以拆包

You either need to choose a specific three-element tuple from your tuple/list of tuples for unpacking

>>> a, b, c = totalData[0]
>>> a
1
>>> b
2
>>> c
3

或在元组上循环（仅当您可以有多个时才有用元组中的元组），然后像这样打开它们的包装：

or loop over the tuples (only useful if you can have more than one tuple in your iterable) and unpack each of them like this:

>>> for a, b, c in totalData:
...     print a, b, c
... 
1 2 3
4 5 6

最后，如果您使用的是Python 3，则有一个方便的功能，称为扩展可迭代解包，可让您使用通配符名称来捕获所有剩余元素-如果您不知道要解包的可迭代元素包含多少元素，这很有用。演示：

Lastly, if you are using Python 3, there's a handy feature called extended iterable unpacking that lets you use a wildcard name that catches all "remaining" elements - which is useful if you don't know how many elements the iterable you want to unpack contains. Demo:

>>> first, *middle, last = [1,2,3,4,5]
>>> first
1
>>> middle
[2, 3, 4]
>>> last
5
>>> 
>>> head, *tail = [1,2,3,4,5]
>>> head
1
>>> tail
[2, 3, 4, 5]

这篇关于将变量分配给SQLite元组时出现问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！