Python Forum

Hi,

I make a match between 2 tables in a database and I want to use the ID's of the rows where the matches are made. 
Sometimes this works correct and i get the different row numbers returned, but not always. 

I have boiled down the problem to two examples:

Match 1: CORRECT Row return
(here i match amounts. There are 3 positive matches, for which the corresponding row id's are returned)

def testrowid2():  # from db table that is set as float
    for a in amount_to_pay:
        if a in amount_paid:
                cur.execute("select participant_id from participants WHERE amount_to_pay = (%s)", (a,))
                row = cur.fetchone()
                print(row)

Output:
('1',)
('2',)
('4',)

Match 2: INCORRECT row return
(here I match dates. For all selections there is a match, so I expect the ID's of all row nrs as output of this definition. Instead i get the ID nr of the first row ('6') printed for the number of rows where the match has been made.

def testrowid1():
    for d in date:
        if d in paid_date:
                cur.execute("select participant_id from participants WHERE participant_date = (%s)", (d,))
                row = cur.fetchone()
                print(row)

Output:
('6',)
('6',)
('6',)
('6',)
('6',)
('6',) etc etc

Any ideas on how I can always get the correct row id nr?

you can get ctid (same as rowid), see: https://www.postgresql.org/docs/8.3/stat...lumns.html
but be aware, it is volatile. You are better off to append a sequence number to the tables that
you need this for, as it will alwys be the same. See: http://www.neilconway.org/docs/sequences/