How to convert numpy array to postgresql list

Question

I am trying to use python to insert 2 columns of a numpy array into a postgresql table as two arrays.

postgresql table is DOS: primary_key energy integer[] dos integer[]

I have a numpy array that is a 2d array of 2x1D arrays:

finArray = np.array([energy,dos])

I am trying to use the following script for inserting into a database and I keep getting errors with the insert. I can't figure out how to format the array so that it properly formats in the form: INSERT INTO dos VALUES(1,'{1,2,3}','{1,2,3}')"

Script:

import psycopg2
import argparse
import sys
import re
import numpy as np
import os

con = None


try:    
    con = psycopg2.connect(database='bla', user='bla')
    cur = con.cursor()
    cur.execute("INSERT INTO dos VALUES(1,'{%s}')", [str(finArray[0:3,0].tolist())[1:-1]])
    con.commit()


except psycopg2.DatabaseError, e:
    if con:
        con.rollback()

    print 'Error %s' % e
    sys.exit(1)

finally:
    if con:
        con.close()

The part I can't figure out is I will get errors like this:

Error syntax error at or near "0.31691105000000003"
LINE 1: INSERT INTO dos VALUES(1,'{'0.31691105000000003, -300.0, -19...

I can't figure out where that inner ' ' is coming from in the bracket.

Answer 1

Too late, but putting this out anyway.

I was trying to insert a numpy array into Redshift today. After trying odo, df.to_sql() and what not, I finally got this to work at a pretty fast speed (~3k rows/minute). I won't talk about the issues I faced with those tools but here's something simple that works:

cursor = conn.cursor()

args_str = b','.join(cursor.mogrify("(%s,%s,...)", x) for x in tuple(map(tuple,np_data)))

cursor.execute("insert into table (a,b,...) VALUES "+args_str.decode("utf-8"))
cursor.commit()
cursor.close()

The 2nd line will need some work based on the dimensions of your array.

You might want to check these answers too:

1. Converting from numpy array to tuple
2. Multiple row inserts in psycopg2

Answer 2

You probably have an array of strings, try changing your command adding astype(float), like:

cur.execute("INSERT INTO dos VALUES(1,'{%s}')", [str(finArray[0:3,0].astype(float).tolist())[1:-1]])