python encoding_error ai_generated true

sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (1366, "Incorrect string value: '\xF0\x9F\x98\x80' for column 'message' at row 1")

ID: python/sqlalchemy-mysql-charset-issue

Also available as: JSON · Markdown · 中文
80%Fix Rate
86%Confidence
0Evidence
2024-04-02First Seen

Version Compatibility

VersionStatusIntroducedDeprecatedNotes
1.4.x active — — —
2.0.x active — — —

Root Cause

The database/table/column character set is not utf8mb4, which cannot store 4-byte Unicode characters like emojis.

generic

中文

数据库/表/列的字符集不是 utf8mb4，无法存储像表情符号这样的 4 字节 Unicode 字符。

Workarounds

  1. 90% success 
    Use charset='utf8mb4' in the connection URL: engine = create_engine('mysql+pymysql://user:pass@host/db?charset=utf8mb4')
  2. 85% success 
    Alter the table to use utf8mb4: ALTER TABLE messages CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Dead Ends

Common approaches that don't work:

  1. 90% fail

    utf8 in MySQL is actually utf8mb3, which still doesn't support 4-byte characters.

  2. 80% fail

    Corrupts the data.