python - Python SQLite。LIMIT 变量不会在循环中改变值

Question

我试图找出每行 SEN 学生人数的差异。（代码、输出和数据库表在底部）

我做了什么：

Finding the number of rows in a table.

Initializing the LIMIT number to 0 BEFORE the loop starts.

I then add one to LIMIT and output it as a string.

I then select the number of SEN students at LIMIT currLimitStr 

I then add one to LIMIT and output it as a string.

I then select the number of SEN students at LIMIT currLimitStr 

I then fetch this data and print it out.

The loop starts again.

每个循环递增currLimit2 有效，但由于某种原因，每次循环后都使用相同的 senRow1 和 senRow2 值。为什么会这样

countNumRows = cur.execute("SELECT count(*) FROM SEN_Table")
countNumFetch = countNumRows.fetchone()
countNumRowsTable = countNumFetch[0]
print("Number of rows in the table: " +  str(countNumRowsTable))
currLimit = 0
for x in range(0, countNumRowsTable):
    currLimit = currLimit + 1
    currLimit1Str = str(currLimit)
    senRow1 = cur.execute("SELECT SenNumber FROM SEN_Table ORDER BY SenNumber DESC limit " + currLimit1Str)
    currLimit = currLimit + 1
    currLimit2Str = str(currLimit)
    senRow2 = cur.execute("SELECT SenNumber FROM SEN_Table ORDER BY SenNumber DESC limit " + currLimit2Str)

    senRow1Num = senRow1.fetchone()[0]
    senRow2Num = senRow2.fetchone()[0]
    print(senRow1Num)
    print(senRow2Num)
    print("")
    senDiff = print(senRow1Num - senRow2Num)
    print("")

输出：

数据库表：

标签： pythonsqliteloopsfor-loopselect

Answer 1

我真的不明白你想要什么，所以我无法帮助你实现它。希望我可以帮助您理解为什么您的代码会执行它的功能。

我在您的代码中添加了一些行并用数字注释它们，#n以便在我的解释中引用它们。我的代码的输出以星号为前缀，因此您可以区分它并且不会干扰您的代码。

Cursor.execute() 返回对同一游标的引用。

因此senRow1和senRow2实际上是与断言该身份的地方所证明的对象相同的对象。#5当你获取第一行senRow1和第一行时，senrow2你实际上是在读取同一个游标的前两行，调用它cur，senRow1或者senRow2，它们都是同一事物的不同名称。

实际上执行第一个查询是没有用的，因为在对它做任何事情之前，您在同一个游标中执行第二个查询并且第一个查询的结果丢失了。

更改限制会更改行数，但不会更改第一行。

为了演示它，我在 and 中创建了两个游标，在#1and#2中执行查询#3并打印and#4中的所有行。如您所见，返回的行数随限制而变化，但无论限制如何，第一行都保持不变。#6#7

请注意，我正在打印第一个查询的结果以确保完整性，但如前所述，只有第二个查询与您的代码相关。

因此，在循环的每次迭代中，您都在查看第二个查询返回的前两行。并且这两行在每次迭代中都是相同的。

修改后的代码：

countNumRows = cur.execute("SELECT count(*) FROM SEN_Table")
countNumFetch = countNumRows.fetchone()
countNumRowsTable = countNumFetch[0]
print("Number of rows in the table: " +  str(countNumRowsTable))
currLimit = 0

cursor1 = conn.cursor()  #1
cursor2 = conn.cursor()  #2

for x in range(0, countNumRowsTable):
    currLimit = currLimit + 1
    currLimit1Str = str(currLimit)
    senRow1 = cur.execute("SELECT SenNumber FROM SEN_Table ORDER BY SenNumber DESC limit " + currLimit1Str)
    cursor1.execute("SELECT SenNumber FROM SEN_Table ORDER BY SenNumber "
                    "DESC limit " + currLimit1Str)  #3

    currLimit = currLimit + 1
    currLimit2Str = str(currLimit)
    senRow2 = cur.execute("SELECT SenNumber FROM SEN_Table ORDER BY SenNumber DESC limit " + currLimit2Str)
    cursor2.execute("SELECT SenNumber FROM SEN_Table ORDER BY SenNumber "
                    "DESC limit " + currLimit2Str)  #4

    assert(senRow1 is senRow2)  #5
    print('* cursor1: {}'.format(cursor1.fetchall()))  #6
    print('* cursor2: {}'.format(cursor2.fetchall()))  #7

    senRow1Num = senRow1.fetchone()[0]  # the 1st row of cur, senRow1
                                        # or senRow2 (they are the same)
    senRow2Num = senRow2.fetchone()[0]  # the 2nd row of cur, senRow1 or
                                        # senRow2 (they are the same)
    print(senRow1Num)
    print(senRow2Num)
    print("")
    senDiff = print(senRow1Num - senRow2Num)
    print("")

输出：

Number of rows in the table: 5
* cursor1: [(4,)]
* cursor2: [(4,), (3,)]
4
3

1

* cursor1: [(4,), (3,), (3,)]
* cursor2: [(4,), (3,), (3,), (2,)]
4
3

1

* cursor1: [(4,), (3,), (3,), (2,), (1,)]
* cursor2: [(4,), (3,), (3,), (2,), (1,)]
4
3

1

* cursor1: [(4,), (3,), (3,), (2,), (1,)]
* cursor2: [(4,), (3,), (3,), (2,), (1,)]
4
3

1

* cursor1: [(4,), (3,), (3,), (2,), (1,)]
* cursor2: [(4,), (3,), (3,), (2,), (1,)]
4
3

1