Error Keyerror 255 when executing pymysql.connect

Question

Here is the code

import pymysql
pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    password='iDontWannaSay',
    db='iDontWannaShow',
    charset='utf8'
)

and the error Traceback was:

data is :::::b'\xff\x02\x00\xff\x81\x15'....##### I was add near line 1279 which is print("data is :::::%s...."%data[i:i+6])
Traceback (most recent call last):
  File "C:\Users\123\Desktop\pymysqldebug.py", line 8, in <module>
    charset='utf8'
  File "D:\Program Files (x86)\Python\Python35\lib\site-packages\pymysql\__init__.py", line 90, in Connect
    return Connection(*args, **kwargs)
  File "D:\Program Files (x86)\Python\Python35\lib\site-packages\pymysql\connections.py", line 709, in __init__
    self.connect()
  File "D:\Program Files (x86)\Python\Python35\lib\site-packages\pymysql\connections.py", line 934, in connect
    self._get_server_information()
  File "D:\Program Files (x86)\Python\Python35\lib\site-packages\pymysql\connections.py", line 1279, in _get_server_information
    self.server_charset = charset_by_id(lang).name
  File "D:\Program Files (x86)\Python\Python35\lib\site-packages\pymysql\charset.py", line 39, in by_id
    return self._by_id[id]
KeyError: 255

seems like struct.unpack method parse '\xff\' to 255 and assigned to self.server_language, whatever the non-null charset argument passed.

Is this an MySQL version problem?(version 8.0.1-dmr)

Answer 1

To extend the above link-only accepted answer, consider the following changes on your current pymysql install. With MySQL 8, the mysql-python API does not recognize possibly newer character sets and hence the raised KeyError.

To resolve, locate the connectors.py script under the pymysql module found at this directory:

print(pymysql.__file__)

Backup orginal connectors.py script. Then incorporate the following changes.

Original (lines 1268-1269)

self.server_language = lang
self.server_charset = charset_by_id(lang).name  

Replace (lines 1268 - 1272)

self.server_language = lang
try:
    self.server_charset = charset_by_id(lang).name
except KeyError:
    self.server_charset = None

Be careful to include hidden tabs in indentation with above lines.

Reference

• PyMySQL Git fix #591