Using psycopg2 to insert list of tuples which includes a JSON array field into Postgres database

Question

I'm trying to insert rows into a database that has three columns: publication, publication_year, and authors. The field authors is of type JSON []. Currently, I have the following code:

query = "INSERT INTO " + store_table_name + "( " + ",".join(tuple(key_types.keys())) + ") VALUES %s"
try:
    # connect to the database
    conn, cur = pg_connect(DB_OSP, db_host, db_user, db_password)
    conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)

    logger.info('Adding %s rows to %s' % (len(data), store_table_name))

    execute_values(cur, query, data)

    cur.close()
    conn.commit()
# exception handling to follow

Data is a list of tuples where each entry is (publication, publication_year, authors). So for example, an entry could look like:

('Sample Book', 2019, ['{"Author1_fname": "Billy", "Author1_lname": "Bob"}', '{"Author2_fname": "King", "Author2_lname": "Kong"}'])

Initially, the code doesn't work as there is a type mismatch (it tries to insert authors as a text array instead of a JSON array). I tried changing the array to string type which didn't work either. I'm not quite sure how to explicitly add the ":::json[]" cast using execute_values. Does anyone have experience doing this?

Are you sure you want an array of json for authors? What you have is not a 'json array', it's an array of json objects, which is rarely useful. — Jeremy
– Jeremy, Commented Apr 10, 2020 at 4:27

Ionut Ticus · Accepted Answer · 2020-04-10 08:18:04Z

1

Try using Json from psycopg2.extras by formatting your data as:

(
  "Sample Book",
  2019, 
  Json([{"Author1_fname": "Billy", "Author1_lname": "Bob"}, {"Author2_fname": "King", "Author2_lname": "Kong"}])
)

answered Apr 10, 2020 at 8:18

Ionut Ticus

2,7992 gold badges22 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using psycopg2 to insert list of tuples which includes a JSON array field into Postgres database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related