Pygame/MySQLdb problem

[natehome]

so im trying to make a very basic multiplayer game that gets a players x y from a server then displays the player. the script works fine when there is only one user in the database but when more users are added the script wont display the right player. i think the error is in the displayplayers2() function. Any ideas on what i did wrong or how to make the script better?

import MySQLdb 
import sys
import pygame,string,random
from pygame import *
import threading
import thread
from pygame import font
def textren(text):
    text = font.render(text, True, (255,255, 255), (0,0,0))
    return text
hostad = 'localhost'
usern = 'root'
passw = ''
datab = 'pygame'
table= 'networkgame'         
def getplayerpos(user):
    myDB = MySQLdb.connect(host=hostad, port=3306, user=usern, passwd=passw, db=datab)
    Cursor = myDB.cursor()
    sql = "SELECT * FROM "+table+" WHERE name = '"+user+"'"
    Cursor.execute(sql)
    results = Cursor.fetchall()
##    print results
    myDB.close()
    return str(results[0][2]).replace('L',''),str(results[0][3]).replace('L','')
def getplayercolor(user):
    myDB = MySQLdb.connect(host=hostad, port=3306, user=usern, passwd=passw, db=datab)
    Cursor = myDB.cursor()
    sql = "SELECT * FROM "+table+" WHERE name = '"+user+"'"
    Cursor.execute(sql)
    results = Cursor.fetchall()
    myDB.close()
    return results[0][4]
def displayplayers():
    myDB = MySQLdb.connect(host=hostad, port=3306, user=usern, passwd=passw, db=datab)
    Cursor = myDB.cursor()
    sql = "SELECT * FROM "+table+""
    Cursor.execute(sql)
    results = Cursor.fetchall()            
    myDB.close()
    return results
def displayplayers2():
    myDB = MySQLdb.connect(host=hostad, port=3306, user=usern, passwd=passw, db=datab)
    Cursor = myDB.cursor()
    sql = "SELECT * FROM "+table
    Cursor.execute(sql)
    results = Cursor.fetchall()            
    for i in results:
        print i
        pos = int(str(results[0][2]).replace('L','')),int(str(results[0][3]).replace('L','')),5,5
        pygame.draw.rect(screen, red, pos, 0)
        screen.blit(textren(str(i[1].replace('\'',''))), (pos[0]-10,pos[1]-15))
    myDB.close()
def updateposy(user,y):
    myDB = MySQLdb.connect(host=hostad, port=3306, user=usern, passwd=passw, db=datab)
    Cursor = myDB.cursor()
    #sql = "INSERT INTO users(name,x,y) VALUES ('"+str(user)+"','"+str(x)+"','"+str(y)+"')"
    sql = "UPDATE "+table+" SET y='"+str(y)+"' WHERE name = '"+user+"'"
    try:
       Cursor.execute(sql)
       myDB.commit()
    except:
       myDB.rollback()
    myDB.close()
def updateposx(user,x):
    myDB = MySQLdb.connect(host=hostad, port=3306, user=usern, passwd=passw, db=datab)
    Cursor = myDB.cursor()
    sql = "UPDATE "+table+" SET x='"+str(x)+"' WHERE name = '"+user+"'"
    try:
       Cursor.execute(sql)
       myDB.commit()
    except:
       myDB.rollback()
    myDB.close()
black    = (   0,   0,   0)
white    = ( 255, 255, 255)
grey     = ( 128, 128, 128)
green    = (   0, 255,   0)
red      = ( 255,   0,   0)
blue     = ( 0,   191,   255)
purple   = ( 238,   130,   238)
pygame.init()
size=[500,500]
screen=pygame.display.set_mode(size)
pygame.display.set_caption("Networkerz")
done=False
clock=pygame.time.Clock()
yaxis = 0
xaxis = 0
playerpos = [0,0]
username = "nathan"
speed = 10
x,y = getplayerpos(username)
x=int(x)
y=int(y)
thread.start_new_thread (displayplayers2, ())
while done==False:
    font = pygame.font.Font(None, 15)
    screen.fill(black)
    for event in pygame.event.get(): 
        if event.type == pygame.QUIT: 
            done=True 
        if event.type == pygame.KEYDOWN:
            if event.key == K_ESCAPE:
                pygame.quit()
                sys.exit()
            if event.key == K_UP:
##                updateposy(username,y-speed)
                thread.start_new_thread (updateposy, (username,y-speed))
            if event.key == K_RIGHT:
##                updateposx(username,x+speed)
                thread.start_new_thread (updateposx, (username,x+speed))
            if event.key == K_LEFT:
##                updateposx(username,x-speed)
                thread.start_new_thread (updateposx, (username,x-speed))
            if event.key == K_DOWN:
##                updateposy(username,y+speed)
                thread.start_new_thread (updateposy, (username,y+speed))

    thread.start_new_thread (displayplayers2, ())
    x,y = getplayerpos(username)
    x=int(x)
    y=int(y)
##    print x, y
    cpos = x,y,5,5
    pygame.draw.rect(screen, green, cpos, 0)
    y += 1
    x += 1
    clock.tick(20)
    pygame.display.flip()
pygame.quit ()

[christopher.j.burnham]

What specifically is the problem? You say it won't display the right player, but what goes wrong and what is it supposed to do?

It looks like you store the coordinates in the db using a different username for each player. But in the main code, it looks like you only ever use the username 'nathan'. Could it be that both player's positions are being saved to the same row in the database?

On a side note, storing player position in a MySql database is an interesting idea. I would think you'd find it too slow to store real time positions of your players. Good luck! If you run into speed problems, it might help to keep one connection to the database going, so you're not reconnecting to the database several times a second.