How to insert multiple rows in PostgreSQL using Python-CodePudding

My database has the following columns : id, name, group, timesamp, totaltime, errorcode

I am trying to insert the following list of data into the database:

data = [
        {"name": "samplename", "group": "samplegroup", "timestamp": sampletimestamp,
            "totaltime": sampletotaltime, "errorcode": sampleerrorcode},
        {"name": "samplename", "group": "samplegroup", "timestamp": sampletimestamp,
            "totaltime": sampletotaltime, "errorcode": sampleerrorcode},
        {"name": "samplename", "group": "samplegroup", "timestamp": sampletimestamp,
            "totaltime": sampletotaltime, "errorcode": sampleerrorcode},
        {"name": "samplename", "group": "samplegroup", "timestamp": sampletimestamp,
            "totaltime": sampletotaltime, "errorcode": sampleerrorcode}
    ]

What would be the best (shortest) way of inserting such a data list into database. My preference is inserting all the rows together.

CodePudding user response：

I would use executemany

import psycopg2

    
data = [
        {"name": "samplename", "group": "samplegroup", "timestamp": sampletimestamp,
            "totaltime": sampletotaltime, "errorcode": sampleerrorcode},
        {"name": "samplename", "group": "samplegroup", "timestamp": sampletimestamp,
            "totaltime": sampletotaltime, "errorcode": sampleerrorcode},
        {"name": "samplename", "group": "samplegroup", "timestamp": sampletimestamp,
            "totaltime": sampletotaltime, "errorcode": sampleerrorcode},
        {"name": "samplename", "group": "samplegroup", "timestamp": sampletimestamp,
            "totaltime": sampletotaltime, "errorcode": sampleerrorcode}
]


db = psycopg2.connect("dbname='my_database' user='user' password='password'")   
cursor = db.cursor() 
cursor.executemany(
    'INSERT INTO mytable (name, group, timestamp, totaltime, errorcode) '
    'VALUES (%(name)s, %(group)s, %(timestamp)s, %(totaltime)s, %(errorcode)s)',
    data
)

CodePudding user response：

Use a native parameterized query like the example below with the full json text as a parameter. All rows at once in a single transaction. The type casts may be different depending on your table structure.

insert into the_table (name, "group", "timesamp", totaltime, errorcode)
 select j->> 'name',
        (j ->> 'group')::integer, 
        (j ->> 'timestamp')::timestamp,
        (j ->> 'totaltime')::interval,
        (j ->> 'errorcode')::integer
 from jsonb_array_elements(%s::jsonb) j;