python 3 : deserialize nested dictionaries from sqlite

user_1878036 0
Viewed 272

I have this sqlite3.register_converter function :

def str_to_dict(s: ByteString) -> Dict:
    if s and isinstance(s, ByteString):
        s = s.decode('UTF-8').replace("'", '"')
        return json.loads(s)
    raise TypeError(f'value : "{s}" should be a byte string')

which returns this exception text :

File "/usr/lib64/python3.7/json/decoder.py", line 355, in raw_decode
    raise JSONDecodeError("Expecting value", s, err.value) from None
json.decoder.JSONDecodeError: Expecting value: line 1 column 30 (char 29)

when encounter with this string :

s = b"{'foo': {'bar': [('for', 'grid')]}}"

It seems that the issue comes from the nested list/tuple/dictionary but what I don't understand is that in the sqlite shell, the value is correctly returned with a select command :

select * from table;

whereas the same command issued from a python script returned the exception above :

class SqliteDb:

    def __init__(self, file_path: str = '/tmp/database.db'):

        self.file_path = file_path
        self._db = sqlite3.connect(self.file_path, detect_types=sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES)
        if self._db:
            self._cursor = self._db.cursor()
        else:
            raise ValueError

        # register data types converters and adapters
        sqlite3.register_adapter(Dict, dict_to_str)
        sqlite3.register_converter('Dict', str_to_dict)

        sqlite3.register_adapter(List, list_to_str)
        sqlite3.register_converter('List', str_to_list)

    def __del__(self):
        self._cursor.close()
        self._db.close()

    def select_from(self, table_name: str):
        with self._db:
            query = f'SELECT * FROM {table_name}'
            self._cursor.execute(query)

if __name__ == '__main__':

    try:
        sq = SqliteDb()
        selection_item = sq.select_from("table")[0]
        print(f'selection_item : {selection_item}')

    except KeyboardInterrupt:
        print('\n')
        sys.exit(0)

s, the value is already saved in database with no issue. Only the selection causes this issue. So, anybody has a clue why ?

python-3.x sqlite nested json
1 Answers

Your input is really a Python dict literal, and contains structures such as the tuple ('for', 'grid') that cannot be directly parsed as JSON even after you replace single quotes with double quotes.

You can use ast.literal_eval instead to parse the input:

from ast import literal_eval

def str_to_dict(s: ByteString) -> Dict:
    return literal_eval(s.decode())
user_68909120
Related