Python Question
Comparing the values within SQLite tables across databases in Python 3
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))
Now I am trying to iterate over the columns and rows of each table to see if the value exists within my preexisting database. In doing so, I have set up the following variables:
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]
So what I am asking is how can I use sqlite commands to iterate over the cells of the database and compare them. I am seeking the logic/syntax of how I will be able to do so.
For example, if I am given a database with 4 tables, I want to make a method that for each table, it will run through and check if the value of each cell to see if it already exists in my database. So I would like to do something like the following, but in SQLite:
Say we have two tables
My Table:
+----+-------+-----+---------------+
| 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 |
+----+-------+-----+---------------+
Given table:
+----+----------------+--------------------+---------------+
| 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 |
+----+----------------+--------------------+---------------+
Given that I can describe the given table with my python method, I would like to make the cursor for each database, read through the given table and be able to identify and alter the values that are redundant relative to my database. For example, say that I want to replace every redundant value with "redundant!" Returning a table like this:
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 ║
╚════╩════════════╩════════════════════╩════════════╝
Recommended for you: Get network issues from WhatsUp Gold. Not end users.
Answer Source
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.
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download