PostgreSql and Python

Question

I am using python and postgresql. I have a table with 6 column. One id and 5 entries. I want to copy the id and most repeated entry in 5 entries to a new table.

I have done this:

import psycopg2
connection=psycopg2.connect("dbname=homedb user=ria")
cursor=connection.cursor()
l_dict= {'licence_id':1}
cursor.execute("SELECT * FROM im_entry.usr_table")
rows=cursor.fetchall()


cursor.execute("INSERT INTO im_entry.pr_table (image_1d) SELECT  image_1d  FROM im_entry.usr_table")



for row in rows:

   p = findmax(row) #to get most repeated entry from first table
   .................
   .................

Then how can I enter this p value to the new table?

Please help me

This can and should be done entirely in one sql statement. Please show some example table data and expected outcome data. Are all fields in a repeated entry identical? — kevpie
– kevpie, Commented Mar 17, 2011 at 7:17
current table is image_1d | entry1 | entry2 | entry3 | entry4 | entry5 ----------+--------+--------+--------+--------+-------- id1 | apple | apple | apple | apple | apple id2 | monkey | monkey | donkey | monkey | monkey id3 | cut | cut | put | put | put id4 | get | set | set | get | get and expected table is image_1d | selected_entry ----------+---------------- id2 | id3 | id4 | | apple id1 | apple (5 rows) — Ria
– Ria, Commented Mar 17, 2011 at 8:09
Can you explain what findmax does. The reason is that the loop that you want to do with "for row in rows" should really be performed in postgresql. — kevpie
– kevpie, Commented Mar 17, 2011 at 8:15
def findmax(row): mylist=list(row) print mylist a= mylist.count(mylist[0]) b= mylist.count(mylist[1]) c= mylist.count(mylist[2]) d= mylist.count(mylist[3]) e= mylist.count(mylist[4]) f= mylist.count(mylist[5]) sizelist=[a,b,c,d,e,f] mostcount= max(sizelist) #print sizelist d = sizelist.index(mostcount) #print d return mylist[d] # End of function — Ria
– Ria, Commented Mar 17, 2011 at 8:18

axaroth · Accepted Answer · 2011-03-17 07:31:09Z

1

p is a tuple so you can create a new execute with the INSERT statement passing the tuple (or part):

cursor.execute("INSERT INTO new_table (x, ...) VALUES (%s, ...)", p)

where:

(x, ....) contains the column names
(%s, ...) %s is repeated for each column

answered Mar 17, 2011 at 7:31

axaroth

2312 silver badges4 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ria Over a year ago

it retutn Traceback (most recent call last): File "homedb4.py", line 54, in <module> cursor.execute("INSERT INTO im_entry.pr_table (selected_entry) VALUES (%s) WHERE image_id = %s", p,idn) TypeError: an integer is required

Ria Over a year ago

File "homedb4.py", line 54, in <module> cursor.execute("INSERT INTO im_entry.pr_table (selected_entry) VALUES ('%s') ", p) TypeError: not all arguments converted during string formatting

axaroth Over a year ago

"not all arguments converted during string formatting" means that p is a tuple with length greater that 1, use p[0] or (%s, %s, %s, %s, %s) in the query, one %s for each element of p (I presume len(p)==5). You can find more here: psycopg

Collectives™ on Stack Overflow

PostgreSql and Python

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related