CODEWITHSUNDEEP
pythonpostgresql
Kartik Sibal
Kartik Sibal

Reputation: 51

Copy data from python lists into postgresql tables

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

Upvotes: 0

Views: 1893

Answers (2)

bobflux
bobflux

Reputation: 11581

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.

Upvotes: 1

Sabin Chacko
Sabin Chacko

Reputation: 761

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.

Upvotes: 0

Related Questions