MySQLdb syntax issues

[elvenson]

Im very new to SQL in general, let alone coding it into python. I can interact with a MySQL database just fine for the most part, but im running into some problems here... This is the function in my script that keeps raising errors:

def NewChar():
    """ NewChar() -
    Call this function to begin new character generation. 
    
    At this time it is the only function you need to call from the NewCharacter module.
    
    It takes no arguments.
    
    """
    CharAccount = NewAccount()
    CharName = raw_input("Enter New Characters Name: \n")
    CharGender = NewGender()
    CharJob = NewJob()
    CharLevel = "1"
    Attributes = GetAtt() ###--- imports the attributes to be added to character info
    Strength = Attributes[0]
    Dexterity = Attributes[1]
    Inteligence = Attributes[2]
    Charm = Attributes[3]
    Luck = Attributes[4]
    
    ###--- This will print the results to a script that will be used to store 
    ###--- and retrieve character data for use in the game. It will eventually
    ###--- be phased out by a database or encrypted file and calling scripts so
    ###--- it wont be accessable by the user.
    #AppendChar = '\n["' + CharName + '", "' + CharGender + '", "' + CharJob + '", ' + CharLevel + ', ' + Strength + ', ' + Dexterity + ', ' + Inteligence + ', ' + Charm + ', ' + Luck + ']'
    
    #CharSheet = "Character.hro"
    #CharOutput = open(CharSheet, 'a', 5000)
    #CharOutput.writelines(AppendChar)
    #CharOutput.close()
    
    
    ###--- MySQL implementation of the new character save.
    conn = MySQLdb.connect(host = 'localhost', user = 'hero', passwd = 'password', db = 'hero')
    cursor = conn.cursor()
    
    cursor.execute("""
        CREATE TABLE %s
        ( 
         name     CHAR(40),
         gender   CHAR(40),
         job      CHAR(40),
         level    TEXT,
         str      TEXT,
         dex      TEXT,
         intel    TEXT,
         cha      TEXT,
         luc      TEXT
        ) 
    """ % CharAccount)
    
    CharInfo = (CharAccount, CharName, CharGender, CharJob, CharLevel, Strength, Dexterity, Inteligence, Charm, Luck)
    
    cursor.execute("""
       INSERT INTO %s (name, gender, job, level, str, dex, intel, cha, luc)
       VALUES
        (%s, %s, %s, %s, %s, %s, %s, %s, %s)
    """, (CharAccount, CharName, CharGender, CharJob, CharLevel, Strength, Dexterity, Inteligence, Charm, Luck))
    
    cursor.execute("SELECT name, job FROM %s" % CharAccount)
    while (1):
        row = cursor.fetchone()
        if row == None:
            break
        print "\n \n \n You just created: %s \n Job class is: %s" % (row[0], row[1])
    
    cursor.close()
    conn.commit()
    conn.close()
        
    print "\n \n \n Your character is made!"
    
    MakeAgain = raw_input("\n \n \n Would you like to make another character while we are at it? (y, n): \n")
    MakeAgain = MakeAgain.lower()
    MakeAgain = MakeAgain[0]
    if MakeAgain == "y":
        NewChar()
    else:
        return

The part that keeps getting me errors is:

cursor.execute("""
       INSERT INTO %s (name, gender, job, level, str, dex, intel, cha, luc)
       VALUES
        (%s, %s, %s, %s, %s, %s, %s, %s, %s)
    """, (CharAccount, CharName, CharGender, CharJob, CharLevel, Strength, Dexterity, Inteligence, Charm, Luck))

i have tried various ways to do this string insertion, and i keep getting errors. it seems that MySQLdb doesnt like me assigning a string to the table name if im going to assign more to values... I have reworked the last line so many times its pathetic and have seen about 3 or 4 different errors, but they are almost all being raised by the same functions within the MySQLdb code. All variables i am attempting to assign are in string form and have worked in a test script i made that does everything the same except substituting the strings within my query. Ive been stuck on this all week and have read numerous tutorials, the DB-API specification sheet, the MySQL manual, the MySQLdb documentation, and a few books... none of which seem to adress my problem since they are all only assigning variables to the table name OR the values of the query, not both. Please help me figure this out.

[woooee]

Since no one else has answered, I use one string to do the job, but don't think that this is Pythonic or "SQLic". So

cursor.execute("""
INSERT INTO %s (name, gender, job, level, str, dex, intel, cha, luc)
VALUES
(%s, %s, %s, %s, %s, %s, %s, %s, %s)
""", (CharAccount, CharName, CharGender, CharJob, CharLevel, Strength, Dexterity, Inteligence, Charm, Luck))

becomes

cursor.execute(stmt)

I'm sure you can go from step a to step b, but as I said, surely there is another way.
EDIT: There has to be a MySQL forum somewhere which might be a better place to ask.

[elvenson]

Since no one else has answered, I use one string to do the job, but don't think that this is Pythonic or "SQLic". So
becomes
I'm sure you can go from step a to step b, but as I said, surely there is another way.
EDIT: There has to be a MySQL forum somewhere which might be a better place to ask.

i get what your saying and was actually just given the same idea yesterday from someone on the python mailing list... i didnt think of it however since i obviously wasnt looking for such a simple solution as using a string to store my data before executing it... i feel like such an idiot now :P It happens when your coding 15 hours a day all week though, just needed a lil break i suppose. thanks though :)