current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

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.

share|improve this question

3 Answers 3

up vote 1 down vote

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]])
share|improve this answer
up vote 1 down vote

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.

share|improve this answer
    
Thanks for the help on this but I was still getting similar errors. The only solution that I can find which is working for me is creating a string like this: cur.execute(str("INSERT INTO dos VALUES(1,'{%s}','{%s}',%s,0)" % (str(atomNumbers)[1:-1],str(orbitalNum)[1:-1],finArray[0,0]))) There was some warning in psycopg2 that I should refrain from using % string interpolation even if I have a gun to my head so I am not sure if I just did a bad thing. [link]initd.org/psycopg/docs/usage.html –  Coherent May 9 at 16:30
up vote 0 down vote

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()
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.