MySQL and Python - how to check an entry with user input?

Question

We're creating a basic MUD and want it to save/load from a MySQL database, as an initial command, we want to have an initial "Do you have a character?" prompt, we have the "no" part sorted out, and it successfully saves to the database. However, I'm having trouble with the "yes" part, more specifically, having it check the DB for a specific entry against user input to make sure it exists, and consequently load the data.

Here's my current code:

global name
name = ''
savename = ("INSERT INTO CharactersDB (ID) VALUES (%s)")
loadname = ("SELECT ID FROM CharactersDB WHERE ID=%s")

def newname():
    global name
    newchar = raw_input("Do you have a character? (y/n) ")
    if newchar == 'y':
        login = raw_input("Please enter your Character's name: ")
        logincheck = cur.execute(loadname, login)
        if login == logincheck:
            print "pass"
            print "Successfully loaded ", login, "from the database"

        else:
            print "Sorry, could not find you in the database"
            print "Or it just isn't working"

    else:
        name = raw_input("Please enter a new name: ")
        #save new name to the database
        cur.execute(savename, name)
        print "Name saved, you are now called ", name
        db.commit()
        return name

newname()

I don't have any errors, it just goes from the first if statement to the second one, saving the "new" name (though no changes are made to the db since the entry already exists)

unutbu · Accepted Answer · 2013-07-06T19:31:57.880

1

Accord to the DB API the return value of cur.execute is undefined. So if you use

logincheck = cur.execute(loadname, login)

then your code will be relying on the particular behavior of your database adapter. MySQLdb for example, returns the primary key of the row selected (if it exists), or 0L if there is no such row. Nevertheless, I think it is better not to rely on this.

Your code will be a little more general if you instead stick to only those behaviors guaranteed by the DB API.

Instead you could use

cur.execute(loadname, login)
logincheck = cur.fetchone()

This will make logincheck a tuple (if a row exists) or the value None. If login is an ID in CharactersDB, then logincheck will look like the tuple (login, ), not the string login. If login is not an ID, then logincheck will be None.

So the if-statement could then look like this:

if logincheck is None:
    print("Sorry, could not find you in the database\nOr it just isn't working")
else:
    print("pass\nSuccessfully loaded {} from the database".format(login))

edited Jul 06 '13 at 19:31

answered Jul 06 '13 at 19:01

unutbu

842,883
184
1,785
1,677

Sorry, the indentation was my fault with spacing incorrectly when I pasted the code. But thanks! I will try that right now – mathieu Jul 06 '13 at 19:03
I guess this is a silly question (we're still very new to programming in general), but with your new if statement, how does that check against the logincheck? – mathieu Jul 06 '13 at 19:27
Oops, my mistake. It should be `if logincheck is None`, not `if login is None`. – unutbu Jul 06 '13 at 19:32
awesome, works perfectly! Thank you so much! This was a huge step, since the rest of the data is essentially just replaces login with the other data assigned to the character. – mathieu Jul 06 '13 at 19:34

MySQL and Python - how to check an entry with user input?

1 Answers1