更新对PostgreSQL表的查询无效

I am trying to build a PostgreSQL database with Python code that will simulate a tournament. The players table contains four columns - name, id, wins, matches.

我正在尝试用Python代码构建一个PostgreSQL数据库，它将模拟一个锦标赛。参与者表包含四列——名称、id、wins、match。

The reportMatch() function takes in two arguments, the ids of the winner and the loser of a particular match and updates the statistics in the database. It will increment the "wins" of the winner by 1, and the "matches" of both the players by 1.

reportMatch()函数接受两个参数:特定匹配的赢家和输家的id，并更新数据库中的统计信息。它将使获胜者的“获胜”增加1，使双方的“比赛”增加1。

def reportMatch(winner, loser):
    conn = connect()
    c = conn.cursor()
    SQL = 'update players set wins = 1 where id = %s;'
    data = (winner, )
    c.execute(SQL, data)
    SQL = 'update players set matches = 1 where id = %s or id = %s;'
    data = (winner, loser)
    c.execute(SQL, data)

I know I shouldn't set the wins and matches to 1, since it's not incrementing the current value, but the database currently has no matches. So, the first time I run it, setting the value to 1 works temporarily.

我知道我不应该将wins和matches设置为1，因为它没有增加当前值，但是数据库当前没有匹配。第一次运行时，将值设置为1暂时有效。

The above function is called through a client code function, testReportMatches():

通过客户端代码函数testReportMatches()调用上述函数:

def testReportMatches():
    registerPlayer("Bruno Walton")
    registerPlayer("Boots O'Neal")
    registerPlayer("Cathy Burton")
    registerPlayer("Diane Grant")
    standings = playerStandings()
    [id1, id2, id3, id4] = [row[1] for row in standings]
    reportMatch(id1, id2)
    reportMatch(id3, id4)
    standings = playerStandings()
    for (n, i, w, m) in standings:
        if m != 1:
            raise ValueError("Each player should have one match recorded.")
        if i in (id1, id3) and w != 1:
            raise ValueError("Each match winner should have one win recorded.")
        elif i in (id2, id4) and w != 0:
            raise ValueError("Each match loser should have zero wins recorded.")
    print "7. After a match, players have updated standings."

registerPlayer() is used to insert a new player into the players database. playerStandings() is used to get a list of tuples of all the players.

registerPlayer()用于向player数据库中插入一个新播放器。playerStandings()用于获取所有参与者的元组列表。

The problem that I am running into is the update query in reportMatch(), which doesn't seem to work. I tried printing out standings before and after the two calls to reportMatch() in testReportMatches(), but both of them have matches and wins as 0. Somehow, the matches and wins in the database are not being updated.

我遇到的问题是reportMatch()中的update查询，它似乎不能工作。我尝试在testReportMatches()中打印两次reportMatch()调用前后的排名，但它们都有匹配，并且都以0获胜。不知何故，数据库中的匹配和胜利没有被更新。

1 个解决方案

#1

You need to commit transaction with conn.commit() at the end of reportMatch function.

您需要在reportMatch函数的末尾使用conn.commit()提交事务。

See psycopg2 usage.

看到psycopg2的使用。

#1