Insert small dictionary like {1:23, 2:45, 3:17} into a column of SQL database table in Postgres using python language

I have a table having one varchar type column and 2 json type columns, which i created using:

create table global_records(
     cattle_id varchar(255) not null primary key, 
     step_count json, 
     speed json
);

I now want to insert values like this using python:

INSERT INTO 
       global_records(cattle_id, step_count, speed) 
VALUES ('cattle_A', '{1: 22, 4: 12}', '{2: 24, 6: 98}');

For which i wrote a string in python like this to execute:

cattle_id = 'cattle_A'
step_count_dict = {1: 22, 4: 12}
speed_dict = {2: 24, 6: 98}

query = "INSERT INTO global_records(cattle_id, step_count, speed) VALUES ('"+cattle_id+"', '" + str(step_count_dict) + "', '" + str(speed_dict) + "'); "

But this doesn’t work. I am getting following error:

invalid input syntax for type json
    LINE 1: ... step_count) values ('cattle_A', '{1: 22}',...
                                                             ^
DETAIL:  Expected string or "}", but found "1".
CONTEXT:  JSON data, line 1: {1...

I searched for similar answers but couln’t find one. This should be simple.

It should look like this in the table:

cattle_id |   step_count   |    speed
----------+----------------+----------------
cattle_A  | {1: 22, 4: 12} | {2: 24, 6: 98}
cattle_B  | {4: 92, 6: 90} | {88: 4, 12: 23}

Answer

Simply use json.dumps for json data (serializes to string) as mentioned in the docs and let psycopg2 do all work and parameter binding:

cattle_id = 'cattle_A'
step_count_dict = json.dumps({1: 22, 4: 12})
speed_dict = json.dumps({2: 24, 6: 98})

cur = con.cursor()
query = "INSERT INTO global_records(cattle_id, step_count, speed) VALUES (%s, %s, %s)"
cur.execute(query, (cattle_id, step_count_dict, speed_dict))
con.commit()

cur.execute('Select * from global_records')
print(cur.fetchall())

Out:

[('cattle_A', {'1': 22, '4': 12}, {'2': 24, '6': 98})]