python mysql error in query
Question: I want to generate a dynamic table:

    
    
    columnames=[element[0] for element in bufferdata['data'] ]
    for index,element in enumerate(columnames):
            columnames[index]=re.sub("[(%./)-]","",element)
        tuple(columnames)
        querycreatetable='''CREATE TABLE test (ID INT AUTO_INCREMENT,name VARCHAR(50),symbol VARCHAR(10),sector VARCHAR(50),
                                               %s FLOAT,%s FLOAT,%s FLOAT,%s FLOAT,%s FLOAT,
                                               %s FLOAT,%s FLOAT,%s FLOAT,%s FLOAT,%s FLOAT,
                                               %s FLOAT,%s FLOAT,%s FLOAT,%s FLOAT,%s FLOAT,
                                               %s FLOAT,%s FLOAT,%s FLOAT,%s FLOAT,%s FLOAT,
                                               %s FLOAT,%s FLOAT,%s FLOAT,%s FLOAT,%s FLOAT,
                                               %s FLOAT,%s FLOAT,%s FLOAT,%s FLOAT,%s FLOAT,
                                               %s FLOAT,%s FLOAT,%s FLOAT,%s FLOAT,%s FLOAT,
                                               %s FLOAT,%s FLOAT,%s FLOAT,%s FLOAT,%s FLOAT,
                                               %s FLOAT,%s FLOAT,%s FLOAT,%s FLOAT,%s FLOAT,
                                               %s FLOAT,%s FLOAT,%s FLOAT,%s FLOAT,%s FLOAT,
                                               %s FLOAT,%s FLOAT,%s FLOAT,%s FLOAT,%s FLOAT,
                                               %s FLOAT,%s FLOAT,%s FLOAT
                                               ) 
                            '''
        try:
            self.cursor.execute(querycreatetable,columnames)
        except MySQLdb.ProgrammingError, e:
            try:
                print "MySQL Error [%d]: %s" % (e.args[0], e.args[1])
            except IndexError:
                print "MySQL Error: %s" % str(e)
    

but i receive this error: MySQL Error [1064]: You have an error in your SQL
syntax; check the manual that corresponds to your MySQL server version for the
right syntax to use near ''SALES in millions' FLOAT,'Earnings per share'
FLOAT,'PE Ratio TTM' FLOAT,'PE Hi' at line 2

does anyone see where the problem is?


Answer: firstly, as told here : [Check for valid SQL column
name](http://stackoverflow.com/questions/4977898/check-for-valid-sql-column-
name)

> SQL identifiers and key words must begin with a letter (a-z, but also
> letters with diacritical marks and non-Latin letters) or an underscore (_).
> Subsequent characters in an identifier or key word can be letters,
> underscores, digits (0-9), or dollar signs ($). Note that dollar signs are
> not allowed in identifiers according to the letter of the SQL standard, so
> their use might render applications less portable

It comes from PostGre doc, but because PostGre is very close to the "ideal"
SQL syntax, it might be the same for mysql... So no parenthesis into column
names, no spaces...

And secondly, **Column names are not strings** :

The following syntax is valid:

    
    
    CREATE TABLE (test VARCHAR(100) NOT NULL, ...)
    

And the following one is invalid and will throw a syntax error:

    
    
    CREATE TABLE ('test' VARCHAR(100) NOT NULL, ...)
    

When you use the '%s' modifier, it parses data as STRING. so it surrounds it
with quotes, which is invalid...

So for create your table, I suggest a "for loop" which validate data (with
regexpr), and symply add it to the string:

    
    
    import re
    # ...
    query = "CREATE TABLE test (ID INT AUTO_INCREMENT,name VARCHAR(50)"
    for c in columnames:
            if (re.search(r"^[A-Za-z][A-Za-z0-9_]*$", c) query += c + ", FLOAT" #this regex validate string if  it begins with alphabetic char (upper or lower case), and if the others characters are alphanumeric, or are underscores
            else raise SyntaxError("Invalid Column name!!") #If not, we raise a syntax error
    query += ");"
    

And then you can create your table :)

