|
Hi,
not sure if this will break existing applications. However you can use a simple converter:
>>> import mariadb
|
>>> from mariadb.constants import *
|
>>> import json
|
>>>
|
>>> def json_convert(s):
|
... return json.loads(s)
|
...
|
>>> conversions= {
|
... **{FIELD_TYPE.JSON: json_convert},
|
... }
|
>>>
|
>>> connection= mariadb.connect(db="test", converter=conversions)
|
>>>
|
>>> cursor= connection.cursor()
|
>>>
|
>>> cursor.execute("create temporary table t2 (a json DEFAULT NULL CHECK (json_valid(`a`)))")
|
>>> json_str= '{"id": 1, "name": "Monty"}'
|
>>> cursor.execute("insert into t2 values (?)", (json_str,))
|
>>>
|
>>> cursor.execute("select * from t2")
|
>>> row= cursor.fetchone()
|
>>> print(row[0])
|
{'id': 1, 'name': 'Monty'}
|
>>> print(type(row[0]))
|
<class 'dict'>
|
|
|
It could be implemented with a flag.
cur = conn.cursor(convertJSON=True)
|
|
|
It does not work for me:
MariaDB [test]> desc test;
|
+-------+-------------+------+-----+---------+-------+
|
| Field | Type | Null | Key | Default | Extra |
|
+-------+-------------+------+-----+---------+-------+
|
| a | varchar(10) | YES | | NULL | |
|
| data | longtext | YES | | NULL | |
|
+-------+-------------+------+-----+---------+-------+
|
2 rows in set (0.001 sec)
|
|
MariaDB [test]> select * from test;
|
+------+------+
|
| a | data |
|
+------+------+
|
| foo | {} |
|
+------+------+
|
#!/usr/bin/python3
|
|
import mariadb
|
from mariadb.constants import FIELD_TYPE
|
import json
|
|
def json_convert(s):
|
return json.loads(s)
|
|
def varchar_convert(s):
|
return "bar"
|
|
conversions = {
|
**{FIELD_TYPE.JSON: json_convert},
|
**{FIELD_TYPE.VARCHAR: varchar_convert}
|
}
|
|
conn = mariadb.connect(
|
user="test",
|
password="test",
|
host="127.0.0.1",
|
port=3306,
|
database="test",
|
converter=conversions
|
)
|
|
cursor = conn.cursor()
|
cursor.execute("select * from test")
|
for row in cursor:
|
print(row)
|
- pip3 list | grep mariadb
mariadb 1.0.7
mariadb-server-10.3
Do I need a special version?
|
|
Sorry, you will need Mariadb 10.5.2 Server or newer (see https://jira.mariadb.org/browse/MDEV-17832).
|
|
Thank you. I updated to Debian Bullseye with MariaDB 10.5.12 and it works fine.
However, I noticed one thing and that the field type varchar doesn't seem to be correct.
create table test (`varchar` varchar(10));
|
insert into test (`varchar`) values ('foo');
|
desc test;
|
+---------+-------------+------+-----+---------+-------+
|
| Field | Type | Null | Key | Default | Extra |
|
+---------+-------------+------+-----+---------+-------+
|
| varchar | varchar(10) | YES | | NULL | |
|
+---------+-------------+------+-----+---------+-------+
|
def varchar_convert(s):
|
return "bar"
|
|
conversions = {
|
**{FIELD_TYPE.VARCHAR: varchar_convert}
|
}
|
cursor.execute("select * from test")
|
for row in cursor:
|
print(row)
|
|
print("VARCHAR", FIELD_TYPE.VARCHAR)
|
|
for desc in cursor.description:
|
print(desc)
|
('foo',)
|
VARCHAR 15
|
('varchar', 253, 10, 40, 0, 0, True, 0)
|
FIELD_TYPE.VARCHAR is 15. cursor.description shows 253.
|