I am trying to write a method that compares the values within a given SQLite database table to a another preexisting database table. I have already written a "describe" method for the given database that for every table in the database, the following tuple is appended to an array:
databaseInfo.append((table, numberOfColumns, numberOfRows, numberOfCells))
tables = [str(i[0]) for i in databaseInfo]
columns = [int(i[1]) for i in databaseInfo]
rows = [int(i[2]) for i in databaseInfo]
cells = [int(i[3]) for i in databaseInfo]
+----+-------+-----+---------------+
| pk | name | age | occupation |
+----+-------+-----+---------------+
| 0 | Joey | 17 | Student |
| 1 | Jerry | 18 | Student |
| 2 | Gabe | 20 | Mathematician |
| 3 | Ian | 34 | Unemployed |
| 4 | Rob | 33 | Teacher |
| 5 | Harry | 18 | Student |
| 6 | Joey | 20 | Economist |
| 7 | Jerry | 33 | Coach |
+----+-------+-----+---------------+
+----+----------------+--------------------+---------------+
| pk | Husband | height(decimeters) | occupation |
+----+----------------+--------------------+---------------+
| 0 | Joey | 16 | Student |
| 1 | Jim | 17 | Programmer |
| 2 | Joey | 16 | Teacher |
| 3 | Jerry | 20 | Unemployed |
| 4 | Tim | 13 | Unemployed |
| 5 | Harry | 18 | Programmer |
| 6 | Steve | 18 | Economist |
| 7 | Jerry | 11 | Painter |
+----+----------------+--------------------+---------------+
ColumnsToExclude = pk
╔════╦════════════╦════════════════════╦════════════╗
║ pk ║ Husband ║ height(decimeters) ║ occupation ║
╠════╬════════════╬════════════════════╬════════════╣
║ 0 ║ redundant! ║ 16 ║ redundant! ║
║ 1 ║ Jim ║ redundant! ║ Programmer ║
║ 2 ║ redundant! ║ 16 ║ redundant! ║
║ 3 ║ redundant! ║ redundant! ║ redundant! ║
║ 4 ║ Tim ║ 13 ║ redundant! ║
║ 5 ║ redundant! ║ redundant! ║ Programmer ║
║ 6 ║ Steve ║ redundant! ║ redundant! ║
║ 7 ║ redundant! ║ 11 ║ Painter ║
╚════╩════════════╩════════════════════╩════════════╝
It would be possible to construct an SQL query to look up the values in the corresponding column of the other table:
SELECT pk,
CASE
WHEN Husband IN (SELECT Husband FROM MyTable)
THEN 'redundant!' ELSE Husband
END AS Husband,
CASE
WHEN height IN (SELECT age FROM MyTable)
THEN 'redundant!' ELSE height
END AS height,
CASE
WHEN occupation IN (SELECT occupation FROM MyTable)
THEN 'redundant!' ELSE occupation
END AS occupation
FROM GivenTable;
The table and column names would have to be customized according to whatever tables you want to match.