There was a question in adding objects to the database. When executing this code

#создаю словарь из атрибутов(head) и значений(lines) b = dict(zip(head, lines[i].replace('"', "").split(","))) #создаю json объект jsonarray = json.dumps(b, ensure_ascii=False) # на выходе получается, например ({"id": "666", "name":"вапв'аы"пы"}) #добавляю в базу json объект в виде сырой строки cursor.execute("""INSERT INTO events (data) VALUES (%r);""" % jsonarray)

I get an error in quotes in the "vapwy" line. How can I escape this correctly so that I can insert the database into the database and then use SELECT to get the selection and send it as JSON?

    1 answer 1

    Try:

     import psycopg2 b = dict(zip(head, lines[i].replace('"', "").split(","))) cursor.execute( """INSERT INTO events (data) VALUES (%s);""", [psycopg2.extras.Json(b)] )

    See the documentation for details.

    PEP 249 describes how to pass parameters to an SQL query string. https://www.python.org/dev/peps/pep-0249/#id15

    There are examples in the psycopg2 documentation. http://initd.org/psycopg/docs/usage.html#passing-parameters-to-sql-queries

    Json at psycopg http://initd.org/psycopg/docs/extras.html#json-adaptation

    • does not fit, type of data - jsonb field, that's why I add it to it and add json object - Valentin Fedyakov
    • And with cursor.execute("""INSERT INTO events (data) VALUES (%s::json[b]);""", jsonarray) ? - Chikiro
    • Traceback (most recent call last): File "C: /Users/User/PycharmProjects/untitled7/parse.py", line 34, in <module> cursor.execute ("" "INSERT INTO events (data) VALUES (% s :: json [b]); "" ", jsonarray) TypeError: not all arguments converted during string formatting - Valentin Fedyakov
    • See the corrected answer. On Postgres 9.4, this saving works. - Chikiro