LinuxQuestions.org
Download your favorite Linux distribution at LQ ISO.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices


Reply
  Search this Thread
Old 12-16-2007, 12:04 PM   #1
elvenson
LQ Newbie
 
Registered: Oct 2006
Location: buffalo, ny
Distribution: laptop - Mandriva2008 Free / XP Pro sp2 | Desktop - XP MC2k5 sp2
Posts: 28

Rep: Reputation: 15
Python MySQLdb syntax issues...


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:


Code:
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:

Code:
    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.
 
Old 12-16-2007, 04:31 PM   #2
PAix
Member
 
Registered: Jul 2007
Location: United Kingdom, W Mids
Distribution: SUSE 11.0 as of Nov 2008
Posts: 195

Rep: Reputation: 40
Code:
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'm guessing, because I'm not familiar with Python or cursors, but I notice that the INSERT INTO has nine items, the VALUES have nine items, but the following line has 10. beginning with CharAccount. I'm not exactly sure what CharAccount is, but I accept that it seems to be somehow tied up as part of the cursor.execute syntax.

Can you explain that for us, unless someone comes along and gives you the answer first of course.
 
Old 12-16-2007, 08:26 PM   #3
ghostdog74
Senior Member
 
Registered: Aug 2006
Posts: 2,697
Blog Entries: 5

Rep: Reputation: 244Reputation: 244Reputation: 244
Code:
 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))
 
Old 12-18-2007, 12:33 PM   #4
elvenson
LQ Newbie
 
Registered: Oct 2006
Location: buffalo, ny
Distribution: laptop - Mandriva2008 Free / XP Pro sp2 | Desktop - XP MC2k5 sp2
Posts: 28

Original Poster
Rep: Reputation: 15
Quote:
Originally Posted by ghostdog74 View Post
Code:
 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))
you would think that would work, but it sadly doesnt.... that was the first way i tried... someone helped me last night though from the python mailing list, so i found a solution...

Code:
query = """
 INSERT INTO %s (name, gender, job, level, str, dex, intel, cha, luc)
       VALUES
        (%%s, %%s, %%s, %%s, %%s, %%s, %%s, %%s, %%s)
    """ % CharAccount
    
cursor.execute(query, (CharAccount, CharName, CharGender, CharJob, CharLevel, Strength, Dexterity, Inteligence, Charm, Luck))
it was such a simple idea that i didnt even think of it... just escape the % and i can use both string formatting AND the mysqldb argument declaration...

thanx anyway
 
Old 12-18-2007, 12:36 PM   #5
elvenson
LQ Newbie
 
Registered: Oct 2006
Location: buffalo, ny
Distribution: laptop - Mandriva2008 Free / XP Pro sp2 | Desktop - XP MC2k5 sp2
Posts: 28

Original Poster
Rep: Reputation: 15
Quote:
Originally Posted by PAix View Post
Code:
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'm guessing, because I'm not familiar with Python or cursors, but I notice that the INSERT INTO has nine items, the VALUES have nine items, but the following line has 10. beginning with CharAccount. I'm not exactly sure what CharAccount is, but I accept that it seems to be somehow tied up as part of the cursor.execute syntax.

Can you explain that for us, unless someone comes along and gives you the answer first of course.
no problem, CharAccount was supposed to be inserted in place of the table name... "INSERT INTO %s" ... thats where it goes
 
  


Reply

Tags
mysql, mysqldb, python, sql


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
[Python] Invalid Syntax - If statement LinuxCrayon Programming 4 12-07-2007 09:11 AM
python syntax error, what am I doing wrong? Tortanick Programming 1 04-03-2007 10:37 AM
Hard time with installing MySQLdb for Python on Linux moeen49 Linux - Newbie 1 07-18-2006 04:25 AM
problem installing mysqldb for python ssaigol Slackware 1 06-15-2005 02:45 PM
Help me (python and MySQLdb module) Dark Carnival Programming 2 04-22-2004 07:31 AM

LinuxQuestions.org > Forums > Non-*NIX Forums > Programming

All times are GMT -5. The time now is 06:33 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration