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?
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")