âIncorrect string valueâ when trying to insert String into MySQL via Python and Text file
<p>What is causing this incorrect string? I have read many questions and answers and here are my results. I still am getting same error after reading answers.</p>

<p>I am getting the following error:
<code>ERROR 1366 (HY000) at line 34373: Incorrect string value: '\xEF\xBB\xBF&lt;?x...' for column 'change' at row 1</code></p>

<p>When I try to enter the following into SQL:
Line number 34373: <code>INSERT INTO gitlog_changes VALUES ('123456', 'NhincCommonEntity.xsd', 'ï»¿&lt;?xml version=\"1.0\" encoding=\"UTF-8\"?&gt;');</code></p>

<p>My table looks like this:</p>

<pre class="lang-sql prettyprint-override"><code>DROP TABLE IF EXISTS `gitlog_changes`;
CREATE TABLE `gitlog_changes` (
  `hashID` varchar(40) NOT NULL,
  `filename` varchar(450) DEFAULT NULL,
  `change` mediumtext
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
</code></pre>

<p>I read many answers that say change the charset to UTF8 <a href="http://stackoverflow.com/questions/1168036/how-to-fix-incorrect-string-value-errors">[1]</a><a href="http://stackoverflow.com/questions/23670754/exception-java-sql-sqlexception-incorrect-string-value-xf0-x9f-x92-xbc-for">[2]</a><a href="http://stackoverflow.com/questions/10957238/incorrect-string-value-when-trying-to-insert-utf-8-into-mysql-via-jdbc">[3]</a><a href="http://stackoverflow.com/questions/2108824/mysql-incorrect-string-value-error-when-save-unicode-string-in-django">[4]</a>. So I execute this:
<code>alter table yourTableName DEFAULT CHARACTER SET utf8;</code></p>

<p>I continue to get the same error. Then I <code>alter table yourTableName DEFAULT CHARACTER SET utf8mb4_general_ci;</code></p>

<p>Still same error occurs. </p>

<p>I also attempt to read in a file from python and do direct commits to the database. From this answer<a href="http://stackoverflow.com/questions/1168036/how-to-fix-incorrect-string-value-errors">[1]</a>. I get a warning instead of an error.</p>

<p>I insert the following code into my python script:</p>

<pre class="lang-py prettyprint-override"><code>    cursor.execute("SET NAMES 'utf8'")
    cursor.execute("SET CHARACTER SET utf8")
</code></pre>

<p>Python script: </p>

<pre class="lang-py prettyprint-override"><code>def insert_changes(modList):
    db = MySQLdb.connect("localhost", "user", "password", "table")
    cursor = db.cursor()

    cursor.execute("SET NAMES 'utf8'")
    cursor.execute("SET CHARACTER SET utf8")

    for mod in modList:
        hashID = mod["hashID"]
        fileName = mod["fileName"]
        change = mod["change"]

        cursor.execute("INSERT INTO gitlog_changes VALUES (%s, %s, %s" , (hashID, fileName, change))
    # # disconnect from server
    db.commit()
    db.close()
</code></pre>

<p>The warning I get here is: <code>Warning: Invalid utf8 character string: '\xEF\xBB\xBF&lt;?x...'
  cursor.execute("INSERT INTO gitlog_changes VALUES (%s, %s, %s)" , (hashID, fileName, change))</code></p>

<p>Text you're trying to insert contains <a href="https://en.wikipedia.org/wiki/Byte_order_mark" rel="nofollow">UTF-8 BOM</a> in the beginning (that's the <strong>\xEF\xBB\xBF</strong> in your error).</p>

<p>Please <a href="http://stackoverflow.com/questions/13590749/reading-unicode-file-data-with-bom-chars-in-python">check this answer</a> to see how to convert from UTF-8 with BOM into UTF-8.</p>

<hr>

<p>As stated in <a href="http://dev.mysql.com/doc/refman/5.7/en/charset-unicode.html" rel="nofollow">MySQL docs</a></p>

<blockquote>
  <p>MySQL uses no BOM for UTF-8 values.</p>
</blockquote>

<p>So the only solution is decoding this string in your python code.</p>

python mysql utf-8