Typeerror in INSERT via Python in Postgresql

Question

I have the following code

import json
import io
import psycopg2

connection = psycopg2.connect("dados da conexão")
connection.autocommit = True
cursor = connection.cursor()

readTest = io.open("arquivo.json", encoding = "utf-8")
readAll = readTest.readlines()

cursor. executemany("INSERT INTO tabela (coluna) VALUES (%s)",(readAll))

When I insert the.json file with a single line the same works but when using more than 2 lines the same appears the following error:

TypeError: not all arguments converted during string formatting

Example of.json file content with more than two lines

{"id":"BRA","count":2,"distance":0.8,"longitude":-80.004114}
{"id":"USA","count":9,"distance":1.2,"longitude":-20.011111}

Answer 1

You can insert row by row by just using a for:

with open("arquivo.json", encoding="utf-8") as arq:
   for linha in arq:
       cursor.execute("INSERT INTO tabela (coluna) VALUES (%s)", (linha,))

Notice the comma on (linha,), this comma is very important because it creates a tuple of a single element, which is what the execute asks for.

If you prefer to use the executemany need to pass a list of tuples, each with an element:

with open("arquivo.json", encoding="utf-8") as arq:
    lista_de_tuplas = [(linha,) for linha in arq]
cursor.executemany("INSERT INTO tabela (coluna) VALUES (%s)", lista_de_tuplas)