gpt4 book ai didi

python - 从 Python SQLite3 查询中求和数字

转载 作者:行者123 更新时间:2023-11-30 21:49:10 26 4
gpt4 key购买 nike

我想用存储在 SQLite 数据库中的数字做一些数学运算。我的查询似乎有效。我的问题是让 python 将我从数据库中查询的数字视为数字。

第一个定义从我的数据库中选择一个唯一的行。第二个定义循环遍历我数据库中的许多行,并将它们添加到我想要求和的数字列表中。

当我尝试对数字求和时出现问题。谁能指出我正确的方向?

这是我的一些代码:

def query_symbol(symbolId):
uniqueId = str(symbolId) + '@' + str(datetime.date.today())

conn = sqlite3.connect('sql/databse.db')
c = conn.cursor()
with conn:
c.execute("SELECT number FROM symbols WHERE uniqueId= ?", (uniqueId,))
return c.fetchall()

def calc(filename):
symbolIds = def_sec.load_symbolIds(filename)

print(len(symbolIds))

list = []

for symbolId in symbolIds:
data = query_symbol(symbolId)
#data = data.replace('()','')
list.append(data)
print(list)

total = sum(list)
print(total)

calc('index_symbolids')

我的错误信息是这样的:

65
[[(13506636000.0,)], [(20156784500.0,)], [(21361120000.0,)], [(4650564600.0,)], [(18572773200.0,)], [(13889340000.0,)], [(21911477100.0,)], [(19014765000.0,)], [(8592582800.0,)], [(12399850600.0,)], [(26021607500.0,)], [(17344514400.0,)], [(28396342200.0,)], [(10444843100.0,)], [(13894385900.0,)], [(26429184100.0,)], [(9193019800.0,)], [(18356516200.0,)], [(13693344800.0,)], [(39135783700.0,)], [(64988933000.0,)], [(52588381800.0,)], [(53514752300.0,)], [(8205312900.0,)], [(18563139800.0,)], [(34542681400.0,)], [(10626282600.0,)], [(14568874300.0,)], [(52083201800.0,)], [(21204153700.0,)], [(13380654000.0,)], [(24821311300.0,)], [(8232241800.0,)], [(148515191500.0,)], [(31669795700.0,)], [(97989223400.0,)], [(135145143799.0,)], [(178696200.0,)], [(9474728600.0,)], [(77661549000.0,)], [(33649778800.0,)], [(10061871500.0,)], [(23682872900.0,)], [(5196629500.0,)], [(54706667400.0,)], [(13934478600.0,)], [(5141383100.0,)], [(81343002200.0,)], [(16173162200.0,)], [(17649907400.0,)], [(32514907200.0,)], [(9783995600.0,)], [(75825589800.0,)], [(6205111500.0,)], [(53908007900.0,)], [(7615559400.0,)], [(17484345800.0,)], [(16072715900.0,)], [(53990182900.0,)], [(25798084100.0,)], [(28311485300.0,)], [(7296894200.0,)], [(19297000000.0,)], [(13271169800.0,)], [(22862203000.0,)]]
Traceback (most recent call last):
File "/Users/michael/atomProjects/calc.py", line 53, in <module>
index_calc('index_symbolids')
File "/Users/michael/atomProjects/calc.py", line 49, in calc
total = sum(list)
TypeError: unsupported operand type(s) for +: 'int' and 'list'
[Finished in 0.822s]

最佳答案

我看到了两个问题。让我们看一下您打印出来的 list:

[[(13506636000.0,)], [(20156784500.0,)],  ... ]

它告诉我的是您有一个嵌套列表列表(例如 [(13506636000.0,)])。这表示单行和单列,因为您返回 c.fetchall()。在这种情况下,您肯定知道最多返回 1 行数据,因此我们可以使用 c.fetchone() 来减少嵌套列表的数量。

接下来,看看这一行:

        data = query_symbol(symbolId)

由于 c.fetchone() 返回 (13506636000.0,),这是一个包含 1 个元素的元组,您可以使用以下技巧来提取数字:

        (data,) = query_symbol(symbolId)

综合:

def query_symbol(symbolId):
uniqueId = '{}@{}'.format(symbolId, datetime.date.today())

with sqlite3.connect('sql/database.db') as conn:
c = conn.execute(
"SELECT number FROM symbols WHERE uniqueId=?",
(uniqueId,))
return c.fetchone()

def calc(filename):
symbolIds = def_sec.load_symbolIds(filename)

print(len(symbolIds))

numbers = []

for symbolId in symbolIds:
(data, ) = query_symbol(symbolId) # instead of `data =`
numbers.append(data)

print(numbers)
total = sum(numbers)
print(total)

calc('index_symbolids')

此时,您应该会得到想要的结果。

更新

此更新解释了 (data,) = 的工作原理。比方说,如果您有一个包含 3 个元素的元组,并且想要分配给变量 abc:

(a, b, c) = (1, 2, 3)  # a=1, b=2, c=3

在实践中,我们可以去掉等号左边的括号:

a, b, c = (1, 2, 3)  # a=1, b=2, c=3

对于 1 元素元组,表示法是 (1,),而不是 (1)(即 1):

data = (1,)     # data = (1,), which is the whole tuple
(data,) = (1,) # data = 1, which is what we want
data, = (1,) # data = 1, but the syntax looks kind of wrong

回到您的问题,函数 query_symbol() 返回一个 1 元素元组,因此我们必须使用:

(data,) = query_symbol(symbolId)
data, = query_symbol(symbolId)

我个人喜欢第一个语法,因为第二个语法看起来像是错误的逗号。

关于python - 从 Python SQLite3 查询中求和数字,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/48030970/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com