Databases
StackOverflow2013
Postsdbo
POPython & MySql: Unicode and Encoding
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPython & MySql: Unicode and Encoding
    primarykey
    data
    text
    <p>I am parsing json data and trying to store some of the json data into Mysql database. I am currently getting following unicode error. My question is how should I handle this. </p> <ul> <li>Should I handle it from the database side, and if so how can I modify my table to do so?</li> <li>Should I handle it from python side?</li> </ul> <p>Here is my table structure</p> <pre><code>CREATE TABLE yahoo_questions ( question_id varchar(40) NOT NULL, question_subj varbinary(255), question_content varbinary(255), question_userId varchar(40) NOT NULL, question_timestamp varchar(40), category_id varbinary(20) NOT NULL, category_name varchar(40) NOT NULL, choosen_answer varbinary(255), choosen_userId varchar(40), choosen_usernick varchar(40), choosen_ans_timestamp varchar(40), UNIQUE (question_id) ); </code></pre> <p>Error While inserting via python code: </p> <pre><code>Traceback (most recent call last): File "YahooQueryData.py", line 78, in &lt;module&gt; +"VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)", (row[2], row[5], row[6], quserId, questionTime, categoryId, categoryName, qChosenAnswer, choosenUserId, choosenNickName, choosenTimeStamp)) File "/opt/local/Library/Frameworks/Python.framework/Versions/2.6/lib/python2.6/site-packages/MySQLdb/cursors.py", line 159, in execute query = query % db.literal(args) File "/opt/local/Library/Frameworks/Python.framework/Versions/2.6/lib/python2.6/site-packages/MySQLdb/connections.py", line 264, in literal return self.escape(o, self.encoders) File "/opt/local/Library/Frameworks/Python.framework/Versions/2.6/lib/python2.6/site-packages/MySQLdb/connections.py", line 202, in unicode_literal return db.literal(u.encode(unicode_literal.charset)) UnicodeEncodeError: 'latin-1' codec can't encode characters in position 204-230: ordinal not in range(256) </code></pre> <p>Python Code segment: </p> <pre><code> #pushing user id to the url to get full json stack urlobject = urllib.urlopen(base_url.format(row[2])) qnadatajson = urlobject.read() data = json.loads(qnadatajson) cur.execute("INSERT INTO yahoo_questions (question_id, question_subj, question_content, question_userId, question_timestamp," +"category_id, category_name, choosen_answer, choosen_userId, choosen_usernick, choosen_ans_timestamp)" +"VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)", (row[2], row[5], row[6], quserId, questionTime, categoryId, categoryName, qChosenAnswer, choosenUserId, choosenNickName, choosenTimeStamp)) </code></pre> <p>json Structure</p> <pre><code>questions: [ { Id: "20111201185322AA5HTDc", Subject: "what are the new pokemon call?", Content: "I used to know them I stop at dialga and palkia version and I heard there's new ones what's it call ", Date: "2011-12-01 18:53:22", Timestamp: "1322794402", </code></pre> <p>What I also did prior to running the query I execute the following on <code>mysql SET character_set_client = utf8</code></p> <p>And this how the mysql variables looks like:</p> <pre><code>mysql&gt; SHOW variables LIKE '%character_set%'; +--------------------------+--------------------------------------------------------+ | Variable_name | Value | +--------------------------+--------------------------------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/local/mysql-5.5.10-osx10.6-x86_64/share/charsets/ | +--------------------------+--------------------------------------------------------+ 8 rows in set (0.00 sec) </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USAdam Wenger
    1. USNull-Hypothesis
    plurals
    1. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    3. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POPython & MySql: Unicode and Encoding
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POPython & MySql: Unicode and Encoding
      1. USairfang
      1. VTFavorite
    3. VO
      singulars
      1. POPython & MySql: Unicode and Encoding
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThe usual "get mysql to accept utf8" incantation is to make sure that `SET NAMES 'utf8'` is your very first query when opening the connection. Have you tried that instead of `character_set_client`? (This is just a comment because I don't think that's the problem here, based on your var output. I think your MYSQLdb Python library is trying to encode to latin-1 and doesn't know it should be encoding to utf-8. Check the documentation and see if there is some way to inform it of this fact.)
      singulars
      1. POPython & MySql: Unicode and Encoding
      1. USFrancis Avila
    2. COYour problem is with the %s, you're trying to convert unicode data into a string. I have the same problem, just not sure how to fix it...
      singulars
      1. POPython & MySql: Unicode and Encoding
      1. USbailout00
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload