0

I must insert a row witha json column in a table on MySQL/MariaDB DB, often the json column contains utf8 chars as euro sign (€).
I'm using python 3.9 and SQLAlchemy.

When I execute the insert on MySQL 5.7, all goes fine.
When i try on new MariaDB 10.3.17 i got:

UnicodeEncodeError: 'ascii' codec can't encode character '\u20ac' in position 194: ordinal not in range(128)

I set the DB and the table to use the utf8mb4 CHARSET and I set the conn to use this charset too, so I don't understand why I got the error when I use MariaDB.

Below You can find the example code.

Thanks.

import json
import sqlalchemy as sqlal

# I use an ssh tunnel
db_conf = {
  "username": "abc",
  "userpwd": "xyz",
  "db_server_ip": "127.0.0.1",
  "db_server_port": "3306",
  "dbname": "MY_DEV_DB"
}


ldata = {
"id": "283738_1",
"tipo_evento": "RES",
"id_provider": 522,
"evento_data": {"descrizione": "DISTURBI D'ANSIA, DEPRESSIVI E RISCHIO DI SUICIDIO: DALLA TEORIA ALL'ESPERIENZA CLINICA",
                "provider": "DUERRE CONGRESSI SRL", "ID Evento": "283738", "Edizione": "1",
                "Costo": "0€", "Crediti": "12", "Ore": "12", "Dal": "02/11/2020", "al": "03/11/2020",
                "Tipologia Evento": "RES", "Obiettivo_formativo": "4 - Appropriatezza delle prestazioni sanitarie, sistemi di valutazione, verifica e miglioramento dell'efficienza ed efficacia. Livelli essenziali di assistenza (LEA)",
                "Professioni_destinatarie": {"Medico Chirurgo": "Psichiatria; Neurologia;"},
                "Segreteria Organizzativa": {"Nome_Referente": "MANUELA", "Cognome_Referente": "CURSALE", "CF_Referente": "CRSMNL67A46H501J", "Telefono": "0636010376", "Email": "MANUELACURSALE@DUERRECONGRESSI.COM"},
                "Indirizzo": {"Regione": "LOMBARDIA", "Provincia": "MILANO", "Comune": "MILANO", "Indirizzo": "VIA GALVANI 12", "Luogo": "HILTON MILAN"}
                },
"on_line": False,
"id_regione": 30,
"id_provincia": 17,
"istat_cod": "015146"}

# print(json.dumps(a))


sql_address = f'{db_conf["db_server_ip"]}:{db_conf["db_server_port"]}'
conn_string = "mysql+mysqldb://{}:{}@{}/{}?charset=utf8mb4".format(
    db_conf["username"],
    db_conf["userpwd"],
    sql_address,
    db_conf["dbname"])

engine = sqlal.create_engine(conn_string, isolation_level="READ COMMITTED")
conn = engine.connect()
# Base = automap_base()
# Base.prepare(engine, reflect=True)
meta = sqlal.MetaData()
work_eventi = sqlal.Table('work_eventi', meta, autoload=True, autoload_with=engine)
res = conn.execute(work_eventi.insert(ldata))
print(res.rowcount)

This is the table structure:

create table work_eventi
    ( _id           INT NOT NULL AUTO_INCREMENT PRIMARY KEY
    , id            VARCHAR(12) NOT NULL
    , tipo_evento   VARCHAR(3)
    , id_provider   MEDIUMINT(9) NULL
    , on_line       BOOLEAN NULL
    , evento_data   JSON NOT NULL
    , id_regione    SMALLINT NULL
    , id_provincia  SMALLINT NULL
    , istat_cod     CHAR(6) NULL
) ENGINE=InnoDB;
Georg Richter
  • 5,970
  • 2
  • 9
  • 15
Stefano G.
  • 143
  • 2
  • 14
  • use json.dumps doesn't work file ? ```res = conn.execute(work_eventi.insert(json.dumps(ldata)))``` – S.Hashiba Nov 08 '20 at 14:05
  • Hi @S.Hashiba, SqlAlchemy require an objecyt and not a string – Stefano G. Nov 08 '20 at 14:50
  • According to the MariaDB documentation for the [JSON Data Type](https://mariadb.com/kb/en/json-data-type/) "JSON is an alias for LONGTEXT" so it looks like you'll have to use `json.dumps()` to convert the `evento_data` dict to a string. For example, in my little test `ldata = {"id": 1, "js": {"foo": "0€"}}` failed, but `ldata = {"id": 1, "js": json.dumps({"foo": "0€"})}` worked. – Gord Thompson Nov 08 '20 at 19:24
  • But so this became a string an no more an real JSON – Stefano G. Nov 08 '20 at 21:35

0 Answers0