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, Mar 28 '17 at 12:05
@VaoTsun I have updated the question. Please have a look at the new error. — Kartik Sibal, Mar 28 '17 at 12:16

score 1 · Answer 1 · edited May 23 '17 at 12:02

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.

Sabin Chacko · Answer 2 · 2017-03-28T13:08:42.350

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 '17 at 13:08

answered Mar 28 '17 at 12:59

Sabin Chacko

713
6
17

Copy data from python lists into postgresql tables

2 Answers2