Joey Smith Joey Smith - 1 year ago 84
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 ║
╚════╩════════════╩════════════════════╩════════════╝

CL. CL.
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