Mam tabelę z jedną kolumną typu varchar i 2 kolumnami typu json, którą utworzyłem za pomocą:

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

Teraz chcę wstawić takie wartości za pomocą Pythona:

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

Dla którego napisałem ciąg w pythonie w taki sposób, aby wykonać:

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) + "'); "

Ale to nie działa. Otrzymuję następujący błąd:

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...

Szukałem podobnych odpowiedzi, ale nie mogłem ich znaleźć. To powinno być proste.

W tabeli powinno to wyglądać tak:

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}
0
Vinita Kumari 17 czerwiec 2021, 22:47

4 odpowiedzi

Najlepsza odpowiedź

Po prostu użyj json.dumps dla danych json (serializowanych do ciągu), jak wspomniano w docs i pozwól psycopg2 wykonać całą pracę i powiązać parametry:

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())

Na zewnątrz:

[('cattle_A', {'1': 22, '4': 12}, {'2': 24, '6': 98})]
2
Maurice Meyer 17 czerwiec 2021, 20:44

NIE twórz zapytań, łącząc ze sobą ciągi znaków. To prawie na pewno doprowadzi do powstania luk w zabezpieczeniach.

Biblioteka psycopg2 ma doskonale dobrą obsługę JSON i są jasne przykłady w dokumentacji. Nawet jeśli chcesz zignorować obsługę JSON, użyj w zapytaniu symboli zastępczych.

2
Richard Huxton 17 czerwiec 2021, 20:08

Odradzałbym "splatanie" zapytania sql. To może nastawić cię na ataki iniekcyjne, jeśli zrobisz z tego nawyk.

Użyj python sql api w ten sposób, zakładając, że masz kursor (cur) w postaci cur.execute('SQL CODE w/ insertions as ? ? ?', (tupel, of, substitutions):

cur.execute('INSERT INTO global_records(cattle_id, step_count, speed) VALUES (?,?,?)', (cattle_id, step_count_dict, speed_dict))

Biblioteka bazy danych, której używasz, powinna być w stanie dowiedzieć się, jak przeanalizować podane zmienne do odpowiedniego formularza, aby wstawić wiersz do używanej bazy danych sql.

1
Jonathan Doorn 17 czerwiec 2021, 20:06

Aby wstawić dane do kolumny JSON, musisz upewnić się, że dane są w poprawnym formacie JSON, np.

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

Więc prawdopodobnie musisz serializować słowniki do formatu JSON jako:

import json

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+"', '" + json.dumps(step_count_dict) + "', '" + json.dumps(speed_dict) + "'); "
-2
Pepe N O 17 czerwiec 2021, 20:10