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>Seems you are asking how to trouble shoot the issue?</p>

<pre><code>INSERT INTO Parameters ({0}) VALUES ({1})
</code></pre>

<p>The error reporting that in your ({0}), you has one field called "ColumnName", but it is not defined in your table Parameters.</p>

<p>Try:</p>

<pre><code>SHOW CREATE TABLE parameters;
</code></pre>

<p>And make sure your table contains the field.</p>

<p>If still has problems, then print out all the sql in your exception handler:</p>

<pre><code>print(e)
print(sql)
</code></pre>

<p>Then I'll run tcpdump to make sure the sql string is not mangled wrongly by any library</p>

<pre><code>sudo tcpdump -A -i lo0 dst port 3306
</code></pre>

<p>And then run your script.</p>

python mysql sql mariadb pymysql