SQL error 1054, "Unknown column 'xxxxx' in 'field list'" in Python with placeholders
<p>I am new to mySQL with Python and I am getting the common 1054 error when I try to execute a pre-prepared SQL INSERT statement with Python friendly placeholders ('%s')</p>

<p>I understand the normal solution is to add quotes in place of back-ticks, or add them if they're missing for the inserted values, however I have run a check on the cols and vals tuples and I can see they're contained within single quotes, so I am a bit puzzled why this error occurs.  </p>

<p>Below is a snippet of my code: (BTW 'cols' and 'vals' are tuples of strings (with emphasis on strings :) )</p>

<p>first I add the column names:</p>

<pre><code>for item in cols:
    try:
        # First add columns if not already present
        query = "ALTER TABLE Parameters ADD COLUMN %s VARCHAR(200)" % (str(item))

        cursor.execute(query)
        connection.commit()
    except:
        pass
</code></pre>

<p>Then I try adding the values:</p>

<pre><code>sql = "INSERT INTO Parameters ({0}) VALUES ({1})".format(', '.join(cols), ', '.join(['%s'] * len(cols)));
    try:
        cursor.execute(sql, vals)

    except Exception as e:
        print(e)

    connection.close()
</code></pre>

<p>Sometimes there is no problem, but otherwise I get this error:</p>

<pre><code>(1054, "Unknown column 'ColumnName' in 'field list'")
</code></pre>

<p>Is my Python statement completely incorrect, and are placeholders not best practice?  I use placeholders as my lists are very large.</p>

<p>Here's a dumbed down version of the SQL statement print.</p>

<pre><code>    INSERT INTO Parameters (TestID, Dev_Type, Version, MAC, XXXX, XXXX, XXXX, XXXX, XXXX, XXXX, XXXX, XXXX, XXXX, XXXX, XXXX, XXXX, XXXX, XXXX, XXXX, XXXX, XXXX, XXXX, XXXX, XXXX, XXXX, XXXX, XXXX, XXXX, XXXX, DayActNxtDateTime, XXXX) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
</code></pre>

<p>And error:</p>

<pre><code>(1054, "Unknown column 'DayActNxtDateTime' in 'field list'")
</code></pre>

<p>Thanks @Gordon Linoff and @Shuo for all your help.  It turned out my dumbed down version of the SQL statement was misleading.  I played around with manually adding columns directly using SQL command line to find the answer.  </p>

<p>It turned out some of my column names exceeded the 64 character limit imposed by SQL (these column names are generated programmatically), hence why the loop sometimes worked and other times it didn't.  Not sure if there's a way in SQL to shorten the column title, I saw something about a COMPRESS method after a quick glance?  </p>

<p>In the mean time I created a Python dictionary to capture these extra long column names on creation and mapped them to a manually created alternative value, it'll do for now I guess.  Thanks again for giving me direction.</p>

python mysql sql mariadb pymysql