Store hashtag map in SQL database

Hi, devs,
I have a data retrieved from XML file:

    '1':  {'name': 'MAT_A', 'id': 'e65437b3'}, 
    '0':  {'name': 'GEO_A', 'id': 'be4c236b'}, 
    '2':  {'name': 'MAT_B', 'id': '6e352ed5'}

This data could be pulled several times and the number and value of keys might be different.

I have a sqlit3 database for my Python application and I am thinking about storing this data to the database. I come up with such options:

  1. Store data in a string cell with json.dump(), retrieve data with json.load()

  2. I can create a table with key and values columns and store 0, 1, 2 as keys and create other tables for values. In overall, this solution looks overcomplicated. Also, I will need to clean the key-value table each time I pull the data and I am not sure if it’s Ok.

Can you advise any better solution?

do you need to use sql ?
if this is the extent of your data why not use something like tinyDB and just store as json?

If your data really looks like the example above, sql will work fine – its just a three column table (key, name, id). However its only worth the effort if you need to do things like if name like geo and ID = e654.

If the key values (1,2 etc) are only there for ordering you can just store a flat list; if the keys have some meaning (β€œI want record 67 exactly!”) then the existing structure makes sense.

You can speed things up by using a namedtuole with name and key fields instead of a dictionary for each entry. Tuples are as cheap as it gets.

1 Like