CODEWITHSUNDEEP
mysqlflaskutf-8python-2.xhebrew
JMS
JMS

Reputation: 1091

mysql storing text as question marks (flask server)

I'm trying to store Hebrew in a MySQL table - but I end up seeing question marks. I'm using a Python flask server. I believe character sets and collation are set correctly (here is the output for running 'show create table':

CREATE TABLE some_table (some_field varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

My SQLALCHEMY_DATABASE_URI is:

'mysql://username:password@localhost/database?charset=utf8'

The data which I insert into the table is initially received within a Python view function, and I process it as follows:

data = request.get_json() 
some_field = data['some_field'].encode('utf-8')

I then insert the "some_field" object into the some_field column.

Note: When I try printing the some_field variable to the Python console I also see a gibberish string, but I assume that's because the console itself can't display Hebrew (not sure if this is causing the problem)...

Upvotes: 0

Views: 1052

Answers (4)

Daniel Ben Zaken
Daniel Ben Zaken

Reputation: 117

Try to add collation='utf8_bin' to the column definition. for example, at models.py :

class Users(db.Model):
    __tablename__ = 'Users'
    id = db.Column(db.Integer, primary_key=True)
    full_name = db.Column(db.String(80, collation='utf8_bin'), unique=False, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)

Upvotes: 0

Yanoom
Yanoom

Reputation: 21

I'm using MySQLdb with Flask (python), and encountered exactly the same (Hebrew from DB appears as question marks ?????)

solved it with these steps:

  1. [link] - Change character sets for each of the levels: DB, Table and Column!
  2. [link] - SET NAMES 'utf8';

  3. This is what I think made the difference: [link] MySQLdB connection arguments (char set). This is what my (python) connection string looks like now:

    db = MySQLdb.connect(host="username.mysql.pythonanywhere-services.com",  # your host
                     user="username",  # username
                     passwd="yourpassword",  # password
                     db="db(schema)name", # name of the database
                     charset="utf8",
                     use_unicode=True)

Notice the charset and use_unicode although I put the default values, this change solved the problem.

I am not sure if all the previous steps and both arguments were necessary.

Upvotes: 0

Rick James
Rick James

Reputation: 142298

Don't use encode or decode. Stick with UTF-8, not Unicode. I predict you will be unhappy with Unicode in the long run. See "question mark" in Trouble with UTF-8 characters; what I see is not what I stored for discussion of likely causes.

Upvotes: 0

JMS
JMS

Reputation: 1091

When I eventually tried rendering the data directly (rather than just staring it in my mysql console), it turns out that it was stored correctly as unicode. Thank you @Amadan

Upvotes: 0

Related Questions