CODEWITHSUNDEEP
pythonmysqlgoogle-cloud-platformflask-sqlalchemy
Tomy137
Tomy137

Reputation: 111

SQLAlchemy / Flask / Google Cloud SQL -- How deals with UTF8MB4

This is my error :

pymysql.err.DataError: (1366, "Incorrect string value: '\\xF0\\x9F\\x91\\x87' for column 'text' at row 1")

It's a smiley that I want to write in column. So initialy not manage by SQL. UTF8 vs UTF8MB4.

With the help of lot of topics there I try many things to change my parameters. This is what I've already set :

#1

My database is one Google Cloud SQL so I change parameters via this field : enter image description here

#2

I try to manualy force via this command directly on the SQL terminal :

ALTER DATABASE my_database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

#3

I add charset parameter at the end of the SQLALCHEMY URL :

SQLALCHEMY_DATABASE_URI = '[URL]?charset=utf8mb4'

#4 I try to add this parameter to FLASK SQLACHEMY :

MYSQL_DATABASE_CHARSET = 'utf8mb4'
MYSQL_CHARSET = 'utf8mb4'

Didn't work, always the same error.

This is my configuration on SQL :

mysql> SHOW VARIABLES LIKE '%character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

mysql> show variables like "%collation%";
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8_general_ci    |
| collation_database   | utf8mb4_unicode_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+

+-------+-------------+--------------------+
| Field | Type        | Collation          |
+-------+-------------+--------------------+
| text  | text        | utf8mb4_unicode_ci |

Some fields still on utf8 like character_set_client or character_set_connection. Is it the problem ?

How change this values without acces to the my.cnf ?

Thanks in advance for your help ! !

More informations

This is the table's declaration :

@dataclass
class Post(db.Model):

    __tablename__ = 'post'
    __table_args__ = {'extend_existing': True}

[...]

    text: str #dataclass
    text = db.Column(db.Text,unique=False,nullable=True)

[...]

    def __init__(self, id, social_media):
        self.id = id
        self.social_media = social_media

and the part of code :

data = {'id':'64145', 'message': 'some text... and the smiley : 👇', [OTHER_FIELDS]}

new_post = db.session.query(Post).filter(Post.id == data['id']).one_or_none() or Post(
    id = data['id'],
    social_media = 'social'
)

new_post.text = data.get('message') or None

db.session.add(new_post)
db.session.commit()

Upvotes: 1

Views: 444

Answers (1)

Tomy137
Tomy137

Reputation: 111

Interesting fact

I try to connect my flask app directly from my local computer to the SQL Cloud with the proxy and it's working !

The two urls :

SQLALCHEMY_DATABASE_URI = "mysql+pymysql://robot:__password__@/__database_name__?unix_socket=/cloudsql/__google_app__:__region__:__google_database__?charset=utf8mb4"
SQLALCHEMY_DATABASE_URI = "mysql+pymysql://robot:__password__@localhost/__database_name__?charset=utf8mb4"

OK. So I'm just dumb.

SQLALCHEMY_DATABASE_URI = "mysql+pymysql://robot:__password__@/__database_name__?unix_socket=/cloudsql/__google_app__:__region__:__google_database__?charset=utf8mb4"

Has to be :

SQLALCHEMY_DATABASE_URI = "mysql+pymysql://robot:__password__@/__database_name__?unix_socket=/cloudsql/__google_app__:__region__:__google_database__&charset=utf8mb4"

Upvotes: 1

Related Questions