bottle_mysql Encoding failure

Question

My implementation goes like this : Centos Server, MySQL with Rails server and currently working on a new bottle application on that.

I have a database that i want to share the date in both Rails and Bottle app. Some data in my DB are in greek.

#!/usr/bin/env python
# -*- coding: utf-8 -*-

import bottle
import bottle_mysql

app = bottle.Bottle()
# dbhost is optional, default is localhost
plugin = bottle_mysql.Plugin(dbuser='user', dbpass='pass' , dbname='db')
app.install(plugin)

@app.route('/show/<item>')
def show(item, db):
    db.execute('SELECT * from visitors where id=1')
    row = db.fetchone()
    if row:
        print row['first_name'].decode("utf-8", "replace")
        return template('showitem', page=row)
    print "Empty"
    return HTTPError(404, "Page not found")

app.run(host='domain.tld', port=8080)

The record in my DB are one row in greek (id=1) and one in english (id=2) Without setting the charset during the connection: I get no errors when using id=2 I get this error when using id=1

UnicodeEncodeError: 'ascii' codec can't encode characters in position 0-8: ordinal not in range(128)

Connecting to the DB like that :

plugin = bottle_mysql.Plugin(dbuser='user', dbpass='pass , dbname='db', charset='utf-8')

I get this error :

OperationalError: (2019, "Can't initialize character set utf-8 (path: /usr/share/mysql/charsets/)")

Any workaround on this kind of errors ?

UPDATE : I changed the charset on my connection string into 'utf8' and went back to the first error.

`SHOW CREATE TABLE`. Show us the connection parameters. `SELECT col, HEX(col) ...` so we can see what got stored. — Rick James, May 08 '15 at 23:22
Can't Python work directly in utf8, and not need `decode()`? — Rick James, May 08 '15 at 23:22
@RickJames, i cannot see how this is relevant, you can see the select query above, here the schema in general : id - int(11) AI PK first_name - varchar(255) last_name - varchar(255) email - varchar(255) student_id - int(11) created_at - datetime updated_at - datetime looks_part_time - tinyint(1) looks_full_time - tinyint(1) looks_intership - tinyint(1) looks_research - tinyint(1) looks_master - tinyint(1) looks_phd - tinyint(1) university_id - int(11) university_department_id - int(11) studies_level - varchar(255) — Dimitris Sapikas, May 10 '15 at 08:16
@RickJames, what do you mean ? Where am i supposed to decode() ? It cannot even initialize the charset ! — Dimitris Sapikas, May 10 '15 at 08:16
Please provide (1) `SHOW CREATE TABLE` -- what you provided does not include the charset of the columns, (2) what version of MySQL you installed, (3) hex of the bytes you have in hand -- In utf8, Greek characters are encoded in 2 bytes each, CExx or CFxx. — Rick James, May 10 '15 at 23:42
@Rick James, the collation of the columns has nothing to do with that, it's only used when comparing values. You could insert UTF8 strings in a latin1 column without any problem (you would of course run into problems when selecting from this later). — Capsule, May 11 '15 at 04:35
To me the problem has nothing to do with MySQL but is more a general Python encoding error as described here: http://stackoverflow.com/questions/9942594/unicodeencodeerror-ascii-codec-cant-encode-character-u-xa0-in-position-20 — Capsule, May 11 '15 at 04:38

score 2 · Answer 1 · edited May 23 '17 at 11:43

Checklist:

# -*- coding: utf-8 -*- -- (you have that)
charset='utf8' in connect() call -- Is that buried in bottle_mysql.Plugin? (Note: Try 'utf-8' and 'utf8')
Text encoded in utf8.
No need for encode() or decode() if you are willing to accept utf8 everywhere.
u'...' for literals
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> near start of html page
CHARACTER SET utf8 COLLATE utf8_general_ci on column (or table) definition in MySQL.

References:

score 1 · Accepted Answer · answered May 14 '15 at 07:41

row['first_name'] is unicode, you don't need to decode it.

In contrast, you need to encode it when printing, and if you not, python interpreter will do it for you (For example if you are printing it to the stdout, python interpreter will encode it with sys.stdout.encoding).

I got the same error in your code, but it will work fine after I remove .decode("utf-8", "replace")

bottle_mysql Encoding failure

2 Answers2