Q:

Inserting dictionary into sqlite3

In my project I need to count where each of the urls stored in database was accessed from.
For example: 
example.com/1 - {'UK': 3, 'Germany': 15}
example.com/2 - {'Italy': 7, 'USA': 4}

I'm currently using SQLite3 and SQLAlchemy.
Is there any way to save the data from the dictionaries in the database? Or is it possible to achieve this in a better way?
The only thing that comes to my mind is to save the data in JSON converted to string.

A:

It is a simple database design problem.
I would recommend:
CREATE TABLE country_clicks (
   id INTEGER PRIMARY KEY,
   country STRING,
   accessed_from STRING,
   clicks INTEGER
);
CREATE UNIQUE INDEX country_clicks_idx ON country_clicks(country, accessed_from);

Normally, I would recommend country and accessed_from as composite PRIMARY KEY. But SQLite had no such thing from the start (there is some mechanics to do so in newer versions).
You have to make sure, that when you insert new values in the table, that the combination country/accessed_from does not already exist. If so, just update clicks.

