aalloo aalloo - 3 years ago 85
Python Question

compare each column of 2 tables and write matching rows in a 3rd table using loop in python

compare each column of 2 tables and write matching rows in a 3rd table and non-matching rows as "Not Mapped" in 3rd table using loop in python:

Compare first column of table A to first column of Table B, if this is true, then compare 2nd column of Table A with 2nd column of Table B, if this is also TRUE, compare 3rd column of Table A with 3rd column of Table B, if this is also True; then write matching rows into a new table C else write in table C as "Not Mapped"

I am not able to write proper code for this since I am new to python. Any help would be appreciated.

I have 2 tables:

Table A
employeeiD employee, managerID, DirectorID, Date
12 A 100 234 2017-01-01
13 B 101 235 2017-01-01
14 C 104 234 2017-01-02
15 D 101 236 2017-01-01


and Table B as:

Table B:
employeeID managerID DirectorID Director
12 100 234 X
12 101 235 Y
12 101 236 Z
13 102 236 W
14 104 234 V
17 105 239 U


and my Table C contains following column:
employeeid, managerid, directorid, director, Date

and this table C should have output as:

Table C:
employeeid managerid directorid director date
12 100 234 X 2017-01-01
14 104 234 V 2017-01-02


Following code I am trying:

cursor.execute(""" select * from employee """)
results = cursor.fetchall()

for result in results:
employeeID = result[0]
managerID = result[2]
DirectorID = result[3]
Date = result[4]

cursor.execute(""" select * from manager """)

dataall = cursor.fetchall()

for data in dataall:
employee = data[0]
manager = data[1]
Director = data[2]
Director_tableb = data[3]

i = 0
j = 0
while i < len(dataall) and j < len(results):
for result in results:
if employeeID == employee:
for data in dataall:
if (employeeID == employee) and (managerID == manager) and (DirectorID == Resource):
cursor.execute(""" Insert into Table_C (%s, %s, %s, %s, %s) """, (employeeID, managerID, DirectorID, Director_tableb, Date))
cursor_db.commit()
i =+ 1
j =+ 1

Answer Source

Now, that's some content to work with.
As it is often the case with my answer, I will try to give you a working solution rather than an optimized one.

First, I'll store the results gotten from the first table in a few lists.

cursor.execute(""" select * from employee """)
results = cursor.fetchall()

employeeIDs = []
managerIDs = []
directorIDs = []
dates = []
for result in results:
    employeeIDs.append(result[0])
    managerIDs.append(result[2])
    directorIDs.append(result[3])
    dates.append(result[4])

Now, I'll get the content from the 2nd table and filling the 3rd table at the same time.

cursor.execute(""" select * from manager """)

dataall = cursor.fetchall()

for data in dataall:
    employee = data[0]
    manager = data[1]
    director = data[2]
    director_tableb = data[3]

    i=0
    while (i<len(employeeIDs)):
        if (employeeIDs[i] == employee) & (managerIDs[i] == manager) & (directorIDs[i] == director):
            cursor.execute(""" Insert into Table_C (%s, %s, %s, %s, %s) """, (employeeIDs[i], managerIDs[i], directorIDs[i], director_tableb, dates[i]))
            cursor_db.commit()
            i=len(employeeIDs)
        i =+ 1

What I do : I loop over the elements of the 2nd table, and try to see for each element if it is also present in the first table.
If the element is present in the first table (if the condition of the if is true), then I put an end to the current inner loop and add the desired values to table C.
If the element is not found, then we'll move on the next data from the 2nd table.

I wrote this answer assuming the code you wrote with the cursors was correct, but if anything does not work as intended, tell me, so I can correct my answer.

If any clarification about what I did/why I did them is required, feel free to ask them.
If you have some ways to improve my code, do not hesitate to tell me so.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download