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]])

Answer 3

The quotes come during the numpy.ndarray.tolist() and come because you actually have strings. If you don't want to assume that data is float-typed as @Saullo Castro suggested you could also do a simple str(finArray[0:3,0].tolist()).replace("'","")[1:-1] to get rid of them.

However, more appropriately, if you are treating the data in finArray in any way in your script and assume they are numbers, you should probably make sure they are imported into the array as numbers to start with. You can require the array to have a certain datatype while initiating it by specifying, e.g. finArray = np.array(..., dtype=np.float) and then work backwards towards where it is suitable to enforce the type.

Answer 4

Psycopg will adapt a Python list to an array so you just have to cast the numpy array to a Python list and pass it to the execute method

import psycopg2
import numpy as np

energy = [1, 2, 3]
dos = [1, 2, 3]
finArray = np.array([energy,dos])
insert = """
    insert into dos (pk, energy) values (1, %s);
;"""
conn = psycopg2.connect("host=localhost4 port=5432 dbname=cpn")
cursor = conn.cursor()
cursor.execute(insert, (list(finArray[0:3,0]),))
conn.commit()
conn.close()

Answer 5

You need convert the numpy array to a list, example:

import numpy as np
import psycopg2
fecha=12
tipo=1
precau=np.array([20.35,25.34,25.36978])
conn = psycopg2.connect("dbname='DataBase' user='Administrador' host='localhost' password='pass'")
cur = conn.cursor()
#make a list
vec1=[]
for k in precau:
    vec1.append(k)
#make a query
query=cur.mogrify("""UPDATE prediccioncaudal SET fecha=%s, precaudal=%s WHERE idprecau=%s;""", (fecha,vec1,tipo))
#execute a query
cur.execute(query)
#save changes
conn.commit()
#close connection
cur.close()
conn.close()