title: Python & MySql: Unicode and Encoding
body: <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>