Why does a warning occur when using SQLAlchemy + PyMySQL?

Question

Flask

Hello ! Installed SQLAlchemy, PyMySQL, Flask-SQLAlchemy, Flask-Migrate.

When I do a migration or create a new entry, I get this warning:

domains\myapp\venv\lib\site-packages\pymysql\cursors.py:170: Warning: (1366, "Incorrect string value: '\\xCD\\xEE\\xE2\\xEE\\xF1\\xE8...' for column 'VARIABLE_VALUE' at row 512")
  result = self._query(query)
INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.env] No changes in schema detected.

The config is like this:

# Database
DB_USER = "user"
DB_PASS = "password"
DB_HOST = "localhost"
DB_PORT = 3306
DB_DATABASE = "app"
DB_CHARSET = "utf8mb4"

# SQLAlchemy
SQLALCHEMY_DATABASE_URI = f"mysql+pymysql://{DB_USER}:{DB_PASS}@{DB_HOST}:{DB_PORT}/{DB_DATABASE}?charset={DB_CHARSET}"
SQLALCHEMY_TRACK_MODIFICATIONS = False

Created a model:

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    full_name = db.Column(db.String(150), index=True)
    status = db.Column(db.Enum(UserStatus), index=True)
    card_number = db.Column(db.String(16), index=True)
    hash = db.Column(db.String(65))
    icon = db.Column(db.String(7))
    card_serial = db.Column(db.String(8))
    token = db.Column(db.String(33), index=True)
    time = db.Column(db.DateTime, default=dt.utcnow)

    def __repr__(self):
        return f"<User {self.full_name}>"

It seems to be something with the encoding, but I have utf8mb4 everywhere. I got into PyMySQL and dumped the generated SQL:

SET NAMES utf8mb4
SHOW VARIABLES LIKE 'sql_mode'
SHOW VARIABLES LIKE 'lower_case_table_names'
SELECT VERSION()
SELECT DATABASE()
SELECT @@transaction_isolation
show collation where `Charset` = 'utf8mb4' and `Collation` = 'utf8mb4_bin'
SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1
SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1
SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin AS anon_1
SET NAMES utf8mb4
SHOW VARIABLES LIKE 'lower_case_table_names'
SELECT VERSION()
SELECT DATABASE()
SELECT @@transaction_isolation
show collation where `Charset` = 'utf8mb4' and `Collation` = 'utf8mb4_bin'
SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1
SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1
SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin AS anon_1
SET NAMES utf8mb4

This is how it works: migrations are created, data is written, but I want to fix this problem. What could be the problem ?

Answer 1

https://github.com/PyMySQL/PyMySQL/issues/536