How can I create a list inside of a "dictionary" with sqlite3?-CodePudding

Instead of using a JSON file to store data, I've decided I wanted to use a database instead. Here is how the data currently looks inside of the JSON file:

{"userID": ["reason 1", "reason 2", "reason 3"]}

I made it so that after a certain amount of time a reason is removed. For example, "reason 2" will be removed after 12 hours of it being added. However, I realised that if I terminate the process and then run it again the reason would just stay there until I manually remove it.

I've decided to use sqlite3 to make a database and have a discord.py task loop to remove it for me. How can I replicate the dictionary inside the database? Here is what I'm thinking at the moment:

c = sqlite3.connect('file_name.db')
cursor = c.cursor()

cursor.execute("""CREATE TABLE table_name (
    userID text,
    reason blob
)"""

CodePudding user response：

Simply use nested loops to insert each reason into a row of the table.

insert_query = """INSERT INTO table_name (userID, reason) VALUES (?, ?)"""
for user, reasons in json_data.items():
    for reason in reasons:
        cursor.execute(insert_query, (user, reason))

CodePudding user response：

Try the following table to store the reasons:

CREATE TABLE reasons (
 reason_id PRIMARY KEY,
 user_id,
 reason,
 is_visible,
 created_at
)

Then the reasons could be soft deleted for every user by running:

UPDATE reasons
SET is_visible = 0
WHERE created_at   3600 < CAST( strftime('%s', 'now') AS INT )

The example shows hiding reasons after 1 hour (3600 seconds).

The reasons can be hard deleted later by running the following query:

DELETE reasons
WHERE is_visible = 0

The soft delete comes in handy for verification and getting data back in case of a future bug in the software.