Python threading with Postgres: things are not being updated properly

Question

I have a thread that runs independently

thread.start_new_thread(listeningTF2Servers, ())

which calls up this method eventually:

    def updateStats():
        ...
        for player in pastGames[i]['players']:
        ...
                        cursor.execute('SELECT yes FROM newstats WHERE nick = \'' + player['nick'] + '\' ORDER BY totalgames DESC LIMIT 1')
                        for row in cursor.fetchall():
                            if row[0] == 1:
                                if scoreDict[player['team']] == 1:
                                    cursor.execute('UPDATE newstats SET totalgames = totalgames + 1, wins = wins + 1, medicgames = medicgames + 1, medicwins = medicwins +1 WHERE nick = \'' + lower(player['nick']) + '\'')
                                    cursor.execute('COMMIT;')
                                else:
                                    #similar query
                            else:
                                if scoreDict[player['team']] == 1:
                                    cursor.execute('UPDATE newstats SET totalgames = totalgames + 1, wins = wins + 1 WHERE nick = \'' + lower(player['nick']) + '\'')
                                    cursor.execute('COMMIT;')
                                else:
                                    #similar query
       ...

Here's the thing. Everything else in the thread that comes before this method works fine.

But updateStats() seems to work... weirdly. But about HALF of the players that have to get updated do not seem to get updated in the database.

For example, after every game this method is called so that players' game stats and win stats are incremented. But for some group of players, this NEVER happens, even though for all other players who played the SAME GAME the stats get updated properly.

Is there an issue with my code that causes issues with threading or postgres? Or is there something with SQL or postgres or Python that I'm not aware of that causes these issues?

My point is: Never pass variables through string manipulation into a query. This opens the gates to hell and the nether realms and awakens Him Who Waits Behind The Wall. Let your driver take care of this. I.E. instead of execute ('select 1 where a=\'' + x + '\'') use execute ('select 1 where a=%s', [x]). — Hyperboreus
– Hyperboreus, Commented Aug 20, 2013 at 4:58

Hyperboreus · Accepted Answer · 2013-08-20 05:09:09Z

1

Just an educated guess: To the first query you pass player['nick'], but to the other calls lower(player['nick']) although all queries affect newstats.nick. So my guess is that the results will be different depending on whether a player's nick is all lower-case or not.

You should try something like this:

lowerNick = lower(player['nick'])
...
cursor.execute('SELECT yes FROM newstats WHERE nick = %s ORDER BY totalgames DESC LIMIT 1', [lowerNick] )
...
cursor.execute('UPDATE newstats SET totalgames = totalgames + 1, wins = wins + 1, medicgames = medicgames + 1, medicwins = medicwins +1 WHERE nick = %s', [lowerNick])
...
cursor.execute('UPDATE newstats SET totalgames = totalgames + 1, wins = wins + 1 WHERE nick = %s', [lowerNick])

answered Aug 20, 2013 at 5:09

Hyperboreus

32.5k9 gold badges50 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Joe Over a year ago

Yeah... I just noticed that. I think that might be the issue, I'll double check.

Collectives™ on Stack Overflow

Python threading with Postgres: things are not being updated properly

1 Answer 1

1 Comment

Your Answer

Collectives™ on Stack Overflow

Python threading with Postgres: things are not being updated properly

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related