'charmap' codec can't encode character '\u010c' in position 0: character maps to MariaDB and SQLAlchemy

Question

I am trying to insert data into a MariaDB database using SQLAlchemy. I am parsing an XML file to get the data I need to insert. I have no problem reading the data. Many of the questions asked regarding this error, such as this python 3.2 UnicodeEncodeError: 'charmap' codec can't encode character '\u2013' in position 9629: character maps to <undefined> refers to reading data from a file. However, I am getting the error at this step,

con_string = db_driver + '://' + db_user + ':' + db_password + '@' + db_host + ':' + db_port + '/' + db_name
engine = sqlalchemy.create_engine(con_string, pool_pre_ping=True)

meta_data = sqlalchemy.MetaData(engine)
table = sqlalchemy.Table('table', meta_data, autoload=True, autoload_with=engine)
table.insert().execute(data)

data is a dictionary. It contains the values obtained from parsing the XML file mapped to the columns of the table.

What could be the reason?

Please add a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example) of your problem. The code you've given us raises a `NameError`. — Jasmijn, Nov 12 '21 at 16:52
Is data an object? Did you possibly mean `table.insert().values(data)`? — gbtimmon, Nov 12 '21 at 17:00
`data` is a dictionary here. Apologies, I forgot to mention that. — Minura Punchihewa, Nov 12 '21 at 17:32
I have also tried `stmt = table.insert().values(data) engine.execute(stmt)` without any luck. — Minura Punchihewa, Nov 12 '21 at 17:35
I have been using `ALTER TABLE table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;` — Minura Punchihewa, Nov 12 '21 at 18:22
Your [mcve] should contain code snippet where you get `data` (incl. all `import`s). — JosefZ, Nov 12 '21 at 18:30
Does your connection URL end with `?charset=utf8mb4` like the example shown [here](https://docs.sqlalchemy.org/en/14/dialects/mysql.html#charset-selection)? — Gord Thompson, Nov 12 '21 at 19:31
@GordThompson That solved my issue! I have updated my question with how I created my `engine`. — Minura Punchihewa, Nov 13 '21 at 04:07

score 2 · Accepted Answer · answered Nov 13 '21 at 13:37

In order to support the full range of Unicode characters we need to add ?charset=utf8mb4 to the end of our connection URL as described at

https://docs.sqlalchemy.org/en/14/dialects/mysql.html#charset-selection

e.g.,

e = create_engine(
    "mysql+pymysql://scott:tiger@localhost/test?charset=utf8mb4")

'charmap' codec can't encode character '\u010c' in position 0: character maps to MariaDB and SQLAlchemy

1 Answers1