Store hashtag map in SQL database

Coding
#1

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?

#2

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?

#3

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