Incubator-superset: Dashboard filter error - 'ascii' codec can't encode character

[bbs2009]

Make sure these boxes are checked before submitting your issue - thank you!

• [x] I have checked the superset logs for python stacktraces and included it here as text if any
• [x] I have reproduced the issue with at least the latest released version of superset
• [x] I have checked the issue tracker for the same issue and I haven't found one similar

Superset version

{"GIT_SHA": "", "version": "0.17.2"}

Expected results

Filtering the table without errors

Actual results

'ascii' codec can't encode character u'\u0406' in position 1512: ordinal not in range(128)

in terminal

2017-03-31 13:40:17,476:ERROR:root:'ascii' codec can't encode character u'\u0406' in position 1512: ordinal not in range(128)
Traceback (most recent call last):
  File "/data/work/virtualenvs/superset/lib/python2.7/site-packages/superset/viz.py", line 234, in get_payload
    df = self.get_df()
  File "/data/work/virtualenvs/superset/lib/python2.7/site-packages/superset/viz.py", line 78, in get_df
    self.results = self.datasource.query(query_obj)
  File "/data/work/virtualenvs/superset/lib/python2.7/site-packages/superset/connectors/sqla/models.py", line 536, in query
    sql = self.get_query_str(**query_obj)
  File "/data/work/virtualenvs/superset/lib/python2.7/site-packages/superset/connectors/sqla/models.py", line 318, in get_query_str
    compile_kwargs={"literal_binds": True}
UnicodeEncodeError: 'ascii' codec can't encode character u'\u0406' in position 1512: ordinal not in range(128)
2017-03-31 13:40:17,479:INFO:root:Caching for the next 86400 seconds

Steps to reproduce

After selecting a filter

Thank you

[xrmx]

@bbs2009 Full backtrace please

[bbs2009]

I added. Thank you

[xrmx]

@bbs2009 btw blocks for stacktraces in markdown are 3 backticks, not 1 :)

[bbs2009]

Sorry, I did not understand your question

2017-03-31 13:48:34,676:INFO:root:SELECT "FACULTY" AS "FACULTY", count(*) AS count
FROM (SELECT distinct

       "FACULTY", "IDFACULTY"
FROM public.marks) AS expr_qry GROUP BY "FACULTY" ORDER BY count DESC
 LIMIT 2000000
2017-03-31 13:48:34,684:INFO:root:SELECT "FACULTY" AS "FACULTY", "KAFEDRA" AS "KAFEDRA", "SPEC" AS "SPEC", sum(mark5all) AS sum__mark5all, sum(mark4all) AS sum__mark4all, sum(mark3all) AS sum__mark3all, sum(mark2all) AS sum__mark2all, sum(mark2notretake) AS sum__mark2notretake
FROM (SELECT id,
       "DISCIPLINE",
       "STUDENTFIO",
       "STUDENTGROUP",
       "DATEZAN",
       "ELGZ1",
       "TEMAZAN",
       "STUDENTMARKID",
       "STUDENTPRESENT",
       "STUDENTMARK",
       "STUDENTRETAKE",
       "STUDENTNUMDOC",
       "STUDENTREASONABSENT",
       "STUDENTNUMINVOICE",
       "SEM1",
       "TYPEZAN",
       "ELG2",
       "GR1",
       "NUMZAN",
       "R1",
       "CONTROLZAN",
       "KAFEDRA",
       "IDSTUDENT",
       "IDKAFEDRA",
       "IDSPEC",
       "SPEC",
       "FINANCE",
       "DATEPARSING",
       "IDFACULTY",
       "FACULTY",
        CASE when ("STUDENTMARK"=5) then 1
       else 0
       end as "mark5all",
       CASE when "STUDENTMARK"=4 then 1
       else 0
       end as "mark4all",
       CASE when "STUDENTMARK"=3 then 1
       else 0
       end as "mark3all",
       CASE when "STUDENTMARK"=2 then 1
       else 0
       end as "mark2all",
       CASE when "STUDENTMARK"=2 and "STUDENTRETAKE"=0 then 1
       else 0
       end as "mark2notretake",
       CASE when "STUDENTMARK"=2 and "STUDENTRETAKE">0 then 1
       else 0
       end as "mark2retake"
FROM public.marks ) AS expr_qry
WHERE "DATEPARSING" >= '2017-03-24 13:48:34' AND "DATEPARSING" <= '2017-03-31 13:48:34' GROUP BY "FACULTY", "KAFEDRA", "SPEC" ORDER BY sum__mark5all DESC
 LIMIT 2000000
2017-03-31 13:48:35,067:INFO:root:SELECT "FACULTY" AS "FACULTY", count(*) AS count
FROM (SELECT distinct

       "FACULTY", "IDFACULTY"
FROM public.marks) AS expr_qry GROUP BY "FACULTY" ORDER BY count DESC
 LIMIT 2000000
2017-03-31 13:48:35,356:INFO:root:Caching for the next 86400 seconds
2017-03-31 13:48:35,430:INFO:root:Caching for the next 86400 seconds
2017-03-31 13:48:58,032:ERROR:root:'ascii' codec can't encode character u'\u0406' in position 1512: ordinal not in range(128)
Traceback (most recent call last):
  File "/data/work/virtualenvs/superset/lib/python2.7/site-packages/superset/viz.py", line 234, in get_payload
    df = self.get_df()
  File "/data/work/virtualenvs/superset/lib/python2.7/site-packages/superset/viz.py", line 78, in get_df
    self.results = self.datasource.query(query_obj)
  File "/data/work/virtualenvs/superset/lib/python2.7/site-packages/superset/connectors/sqla/models.py", line 536, in query
    sql = self.get_query_str(**query_obj)
  File "/data/work/virtualenvs/superset/lib/python2.7/site-packages/superset/connectors/sqla/models.py", line 318, in get_query_str
    compile_kwargs={"literal_binds": True}
UnicodeEncodeError: 'ascii' codec can't encode character u'\u0406' in position 1512: ordinal not in range(128)
2017-03-31 13:48:58,033:INFO:root:Caching for the next 86400 seconds

Thank you

[App1905]

@bbs2009 try use python3 ,it`s a kind of codec problem in python2

[xu6han]

我的也是报这个错

[mistercrunch]

If this is a mysql query try appending ?charset=utf8 to your SQLAlchemy URI

[xrmx]

This should fix the issue and doesn't break tests here:

diff --git a/superset/connectors/sqla/models.py b/superset/connectors/sqla/models.py
index 903182de..1a2bc37e 100644
--- a/superset/connectors/sqla/models.py
+++ b/superset/connectors/sqla/models.py
@@ -3,6 +3,7 @@ import logging
 import sqlparse

 import pandas as pd
+import six

 from sqlalchemy import (
     Column, Integer, String, ForeignKey, Text, Boolean,
@@ -315,7 +316,7 @@ class SqlaTable(Model, BaseDatasource):
     def get_query_str(self, **kwargs):
         engine = self.database.get_sqla_engine()
         qry = self.get_sqla_query(**kwargs)
-        sql = str(
+        sql = six.text_type(
             qry.compile(
                 engine,
                 compile_kwargs={"literal_binds": True}

[bbs2009]

After switching to python3, the problem disappeared. Thank you.

[xrmx]

@bbs2009 you resolved your personal issue but the code is still broken with non-ascii chars on python2

[bbs2009]

excuse me

[mistercrunch]

Notice: this issue has been closed because it has been inactive for 376 days. Feel free to comment and request for this issue to be reopened.

[Jemteddy]

Problem came back after upgrade for me on postgres database.

[UZi02]

I get the same error when open the menu "Security --> List Roles".

       Traceback (most recent call last):
  File "/home/user/venv/lib/python2.7/site-packages/flask/app.py", line 1982, in wsgi_app
    response = self.full_dispatch_request()
  File "/home/user/venv/lib/python2.7/site-packages/flask/app.py", line 1614, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/home/user/venv/lib/python2.7/site-packages/flask/app.py", line 1517, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/home/user/venv/lib/python2.7/site-packages/flask/app.py", line 1612, in full_dispatch_request
    rv = self.dispatch_request()
  File "/home/user/venv/lib/python2.7/site-packages/flask/app.py", line 1598, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/home/user/venv/lib/python2.7/site-packages/flask_appbuilder/security/decorators.py", line 26, in wraps
    return f(self, *args, **kwargs)
  File "/home/user/venv/lib/python2.7/site-packages/flask_appbuilder/views.py", line 478, in list
    widgets=widgets)
  File "/home/user/venv/lib/python2.7/site-packages/flask_appbuilder/baseviews.py", line 160, in render_template
    return render_template(template, **dict(list(kwargs.items()) + list(self.extra_args.items())))
  File "/home/user/venv/lib/python2.7/site-packages/flask/templating.py", line 134, in render_template
    context, ctx.app)
  File "/home/user/venv/lib/python2.7/site-packages/flask/templating.py", line 116, in _render
    rv = template.render(context)
  File "/home/user/venv/lib/python2.7/site-packages/jinja2/environment.py", line 1008, in render
    return self.environment.handle_exception(exc_info, True)
  File "/home/user/venv/lib/python2.7/site-packages/jinja2/environment.py", line 780, in handle_exception
    reraise(exc_type, exc_value, tb)
  File "/home/user/venv/lib/python2.7/site-packages/superset/templates/appbuilder/general/model/list.html", line 2, in top-level template code
    {% import 'appbuilder/general/lib.html' as lib %}
  File "/home/user/venv/lib/python2.7/site-packages/flask_appbuilder/templates/appbuilder/base.html", line 1, in top-level template code
    {% extends base_template %}
  File "/home/user/venv/lib/python2.7/site-packages/superset/templates/superset/base.html", line 1, in top-level template code
    {% extends "appbuilder/baselayout.html" %}
  File "/home/user/venv/lib/python2.7/site-packages/superset/templates/appbuilder/baselayout.html", line 2, in top-level template code
    {% import 'appbuilder/baselib.html' as baselib %}
  File "/home/user/venv/lib/python2.7/site-packages/flask_appbuilder/templates/appbuilder/init.html", line 46, in top-level template code
    {% block body %}
  File "/home/user/venv/lib/python2.7/site-packages/superset/templates/appbuilder/baselayout.html", line 21, in block "body"
    {% block content %}
  File "/home/user/venv/lib/python2.7/site-packages/superset/templates/appbuilder/general/model/list.html", line 8, in block "content"
    {% block list_search scoped %}
  File "/home/user/venv/lib/python2.7/site-packages/superset/templates/appbuilder/general/model/list.html", line 9, in block "list_search"
    {{ widgets.get('search')()|safe }}
  File "/home/user/venv/lib/python2.7/site-packages/flask_appbuilder/widgets.py", line 108, in __call__
    form_fields[col] = self.template_args['form'][col]()
  File "/home/user/venv/lib/python2.7/site-packages/wtforms/fields/core.py", line 155, in __call__
    return self.meta.render_field(self, kwargs)
  File "/home/user/venv/lib/python2.7/site-packages/wtforms/meta.py", line 56, in render_field
    return field.widget(field, **render_kw)
  File "/home/user/venv/lib/python2.7/site-packages/flask_appbuilder/fieldwidgets.py", line 176, in __call__
    return super(Select2ManyWidget, self).__call__(field, **kwargs)
  File "/home/user/venv/lib/python2.7/site-packages/wtforms/widgets/core.py", line 324, in __call__
    html.append(self.render_option(val, label, selected))
  File "/home/user/venv/lib/python2.7/site-packages/wtforms/widgets/core.py", line 337, in render_option
    return HTMLString('<option %s>%s</option>' % (html_params(**options), escape_html(label, quote=False)))
  File "/home/user/venv/lib/python2.7/site-packages/wtforms/widgets/core.py", line 31, in escape_html
    s = escape(text_type(s), quote=quote)
  File "/home/user/venv/lib/python2.7/site-packages/flask_appbuilder/security/sqla/models.py", line 46, in __repr__
    return str(self.permission).replace('_', ' ') + ' on ' + str(self.view_menu)
UnicodeEncodeError: 'ascii' codec can't encode character u'\xfc' in position 34: ordinal not in range(128)

I had some charts and a Dashboard containing the mentioned character ü (u'\xfc') but changed these occurences to 'ue'. The error remains.

I'm using Superset 0.27.0, Python 2.7.5, PostgreSQL 9.2.24 on CentOS 7.5.1804.

[mistercrunch]

We no longer support python2.7 in upcoming versions due to bugs like this one

[UZi02]

Okay. Is there a how-to available upgrading the virtual environment for Superset with a newer Python version? Sorry for asking but I'm quite new to all this stuff with virtual environments and pip...

I've found how to enable Python3 on CentOs and how to create a virtual environment there but I already did a lot within Superset and want to avoid to do all these things again.