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 0 down vote favorite

Please consider me to be a complete novice with psycopg2. My aim is to insert a 1D numpy array of dtype object (where the elements are only strings) into a postgresQL table. My main program saves the fields as strings in the numpy array. I then want to add each separate element to a column in the postgresqL table (or if you prefer, the the 1d Array is one row). Please note, the actual array has 36 elements! I need a method to put them all in.

I am using the cur.execute command although I believe there is some problem with the string conversion. 

Array=np.empty(3,dype=object)
Array[0]='Hello'
Array[1]='Tea?'
Array[2]='Bye'

statement= "INSERT INTO testlog (field1,field2,field3) VALUES (%s)" #Etc.
cur.execute(statement,Array)

I get error:

cur.execute(statement,Array)
TypeError: not all arguments converted during string formatting

Also tried:

cur.executemany('INSERT INTO testlog VALUES ( %s )', [[v] for v in Array ]

Thanks

share|improve this question
    
@Clodoaldo You are right, read about it just now here –  Kobi K 2 days ago

1 Answer 1

up vote 0 down vote

Your statement should contain place holder for all values:

statement= "INSERT INTO testlog (field1,field2,field3) VALUES (%s, %s, %s)"

For Example:

=# create table testlog (field1 varchar(50), field2 varchar(50), field3 varchar(50))`;

then in the python shell (note dtype not dype:

Array=np.empty(3,dtype=object)
Array[0]='Hello'
Array[1]='Tea?'
Array[2]='Bye'
sql = "INSERT INTO testlog (field1, field2, field3) VALUES (%s, %s, %s)"
cur.execute(sql, [f for f in Array])
conn.commit()

And in DB:

select * from testlog;
 field1 | field2 | field3 
--------+--------+--------
 Hello  | Tea?   | Bye
(1 row)
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.