MySQLdb syntax issues - HELP

[wes]

Luke wrote:
> 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.

Luke,
    It would be most helpful if you assigned the sql to
a string, printed the string, executed the string, and
posted the error message.

sql = "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)

print "sql=",sql
cursor.execute(sql)

-then show the error message here

wes