Python Mysql Error In Query

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('[(%./)-

Solution 1:

firstly, as told here : 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:

CREATETABLE (test VARCHAR(100) NOTNULL, ...)

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

CREATETABLE ('test'VARCHAR(100) NOTNULL, ...)

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 underscoreselseraise SyntaxError("Invalid Column name!!") #If not, we raise a syntax error
query += ");"

And then you can create your table :)