CODEWITHSUNDEEP
pythonmysqlpandasencoding
Filipe Ferminiano
Filipe Ferminiano

Reputation: 8799

Wrong encoded characters when I send utf8 encoded characters from pandas to mysql with latin-1 encoding

I'm getting wrong encoded characters when I send utf8 encoded characters from pandas to mysql with latin-1 encoding. This is a sample value I'm getting in mysql:

Biquíni

This is my code:

df.breakdown_name = df.breakdown_name.str.encode('latin-1')

send_data(DB_ENGINE, MYSQL_USER, MYSQL_PASSWORD, MYSQL_HOST, MYSQL_PORT, MYSQL_DB, MYSQL_TABLE, df)

def send_data(db, db_user, db_password, db_host, db_port, db_name, db_table, df):
    """
        Return data
             1
    """
    # print db
    # print db_user
    # print db_password
    # print db_host
    # print db_port
    # print db_name
    # print query[0:20]    
    SQL_ENGINE = '{db}://{db_user}:{db_password}@{db_host}:{db_port}/{db_name}?charset={encoding}'.format(
      db=db,
      db_user=db_user,
      db_password=db_password,
      db_host=db_host,
      db_port=db_port,
      db_name=db_name,
      encoding='latin1',
    )
    engine = create_engine(SQL_ENGINE)    
    df.to_sql(name=db_table,con=engine,schema='xxx',if_exists='append', index=False, chunksize=50)

Upvotes: 0

Views: 1822

Answers (1)

MaximTitarenko
MaximTitarenko

Reputation: 886

I assume that the sample value should be:

print u'Biquíni'.encode('latin_1').decode('utf-8') # Biquíni

Thus, in your code you're doing the inverse transformation:

print u'Biquíni'.encode('utf-8').decode('latin_1') # Biquíni

The problem is that when you implement the chain:

  1. Encode unicode to 'utf-8' bytes.
  2. Decode those bytes with any 1-byte encoding (like 'latin_1').

for every non-ASCII unicode character at the input you'll always receive wrong characters at the output. That's happen because for such a character there is at least 2-byte value in 'utf-8'.

Let's look at examples:

print ord(u'z') # 122 => ASCII
print repr(u'z'.encode('utf-8')) # 'z', 1 byte
print repr('z'.decode('latin_1')) # u'z'

As we see, for ASCII-character everything works fine, but:

print ord(u'í') # 237 => non-ASCII

import unicodedata

print repr(u'í') # u'\xed'
print unicodedata.name(u'\xed') # LATIN SMALL LETTER I WITH ACUTE

print repr(u'\xed'.encode('utf-8')) # '\xc3\xad' => 2 bytes

print repr('\xc3'.decode('latin_1')) # u'\xc3' - the 1st char 
print repr('\xad'.decode('latin_1')) # u'\xad' - the 2nd char

print unicodedata.name(u'\xc3') # LATIN CAPITAL LETTER A WITH TILDE
print unicodedata.name(u'\xad') # SOFT HYPHEN

So, in your code for every non-ASCII character after encoding in 'utf-8' you get 2 bytes which are then decoded with 'latin_1' into 2 characters, and they don't correspond to initial character.

Thereby, the current scheme of your program will generate undesirable results.
I suggest to use the same encoding for encode() and decode() steps in your code.

Upvotes: 1

Related Questions