Copy data from python lists into postgresql tables

Question

I have a python list cve_id, pkg_name & vuln_status. I'd like to import the data from these lists int a postgresql table

What I have tried:

from scanner import *
import psycopg2

try:
    conn = psycopg2.connect(database="test", user="postgres",password="something", host="127.0.0.1", port="5432")

except:
    print "Database un-successfull"
    quit()

cur = conn.cursor()

cur.execute("INSERT into vuln_data(cve_id, pkg_name, status) VALUES (%s,%s, %s)", (cve_id, pkg_name, vuln_status))

conn.commit()
conn.close()

I get an error saying

psycopg2.DataError: multidimensional arrays must have array expressions with matching dimensions

Would love if someone could point out what can be done, here.

Thanks

your column "cve_id" is of type integer (eg: 1,4, 99) and value of cve_id is of type text[] (eg: '{blah,blah}') you cant insert text array into integer field — Vao Tsun
– Vao Tsun, Commented Mar 28, 2017 at 12:05
@VaoTsun I have updated the question. Please have a look at the new error. — Kartik Sibal
– Kartik Sibal, Commented Mar 28, 2017 at 12:16

Community · Accepted Answer · 2017-05-23 12:02:11Z

1

If your data is in the form:

[
    (val, val, val),
    (val, val, val),
    (val, val, val),
]

Then the answer is cursor.executemany( sql, data ):

psycopg2: insert multiple rows with one query

If your data is not in this form, then put it in this form with zip() or list comprehensions.

There are HUGE performance implications.

If Autocommit is ON, you get one COMMIT per query, which will be slow as hell.
Solution 1: BEGIN, then executemany(), then COMMIT.
Solution 2: see link above, create a multi-value INSERT and do it as a single query.

edited May 23, 2017 at 12:02

CommunityBot

11 silver badge

answered Mar 29, 2017 at 13:36

bobflux

11.6k3 gold badges31 silver badges33 bronze badges

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

Comments

Sabin Chacko · Accepted Answer · 2017-03-28 13:08:42Z

0

Try replacing the cur.execute the code with the following

cur.execute("""INSERT into vuln_data(cve_id, pkg_name, status) VALUES (%s,%s, %s);""", (cve_id, pkg_name, vuln_status))

Make sure the items in the Lists are in a sequence, otherwise convert it to a tuple.

edited Mar 28, 2017 at 13:08

answered Mar 28, 2017 at 12:59

Sabin Chacko

7617 silver badges17 bronze badges

Collectives™ on Stack Overflow

Copy data from python lists into postgresql tables

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related