1

I'm importing some data from an API in Python, formatting it and saving it to a MySQL database with to_sql.

results, types, valid = self.process_data(data, [])
        if valid:
            results.to_sql(
                con=self.db.connection,
                name="degreed_" + method,
                if_exists="replace", 
                index=False,
                dtype=types,
            )

In my connection I have specified utf8mb4 as the charset:

self.connection = create_engine(
            'mysql+mysqlconnector://{0}:{1}@{2}/{3}?charset=utf8mb4'.
            format(database_username, database_password, database_ip, database_name))

and in my types I have text columns as:

NVARCHAR(length=500, collation='utf8mb4_bin').

However, I still get the error message:

COLLATION 'utf8mb4_bin' is not valid for CHARACTER SET 'utf8'

In MySQL my character_set_client is utf8mb4 and the default table charset is utf8mb4. Why is the character set utf8?

Apologies if I'm doing anything stupid here, I'm quite new to sqlalchemy and mysql in general.

gucciontoast
  • 11
  • 1

1 Answers1

0

Turns out the issue was that I was using NVARCHAR instead of VARCHAR, so the result was being cast as UTF8.

gucciontoast
  • 11
  • 1