CODEWITHSUNDEEP
python
Ash
Ash

Reputation: 27

compare two query results in Python

I am new to python and trying to write a script to compare data from two databases.

In particular, trying to generate output to list match/unmatch sequences for ecah client.

There are two databases

AORCL database

SQL> select * from ffduser.client_mapping;
  CLIENTNO  CLIENTSEQ STATUS
---------- ---------- ----------
         1       1000 VALID
         2       2000 VALID

BORCL database

SQL> select * from ffduser.client_mapping;
  CLIENTNO  CLIENTSEQ STATUS
---------- ---------- ----------
         1       1000 VALID
         2       2002 VALID

Expected output

Clientno 1 has Clientseq 1000 in AORCL and 1000 in BORCL -> Match
Clientno 2 has Clientseq 2000 in AORCL and 2001 in BORCL -> UnMatch

==========

I wrote below code but not able to generate expected output. I think it may require nested loop but I am novice. Any help greatly appreciated.

$ cat test.py
#!/usr/bin/pytho
import cx_Oracle
pdb_name = 'AORCL'
ddb_name = 'BORCL'
pserver_name = 'ff1db03'
dserver_name = 'ff1db03'
pcon = cx_Oracle.connect('system/xxxxx@'+pserver_name+':1521/'+pdb_name)
dcon = cx_Oracle.connect('system/xxxxx@'+dserver_name+':1521/'+ddb_name)
pcur = pcon.cursor()
dcur = dcon.cursor()
pstat = pcur.execute('select clientno,clientseq from ffduser.client_mapping order by clientno').fetchall()

for plogresult in pstat:
    print (pdb_name +'->'+ ' Client# '+str(plogresult[0])+' Seq No# ' +str(plogresult[1]))

dstat = dcur.execute("select clientno,clientseq from ffduser.client_mapping where status='VALID' order by clientno").fetchall()
for dlogresult in dstat:
        print (pdb_name +'->'+' Client# '+str(dlogresult[0])+' Seq No# ' +str(dlogresult[1]))

pcur.close()
pcon.close()
dcur.close()
dcon.close()


$ python test.py
AORCL-> Client# 1 Seq No# 1000
AORCL-> Client# 2 Seq No# 2000
AORCL-> Client# 1 Seq No# 1000
AORCL-> Client# 2 Seq No# 2002

Upvotes: 0

Views: 4785

Answers (1)

guettli
guettli

Reputation: 27987

I think you need one method which I would call select_to_name_value_mapping(table_name)

This method returns a dictionary with the "names" as keys and the Eno as values.

mapping1=select_to_name_value_mapping('table1')
mapping2=select_to_name_value_mapping('table2')

for key1, value1 in mapping1.items():
    value2=mapping2.get(key1)
    print('key=%s table1=%s table2=%s' % (key1, value1, value2))

print('missing in mapping1: %s' % set(mapping1.keys())-set(mapping2.keys()))

The implementation of select_to_name_value_mapping(table_name) is up to you :-)

Above is the implementation in Python. It is easier for human brains to find this iterative approach. You can et the same result with SQL, but it is harder to get the "thinking in sets" solution. My advice: learn thinking in sets.

Upvotes: 1

Related Questions