OiO.lk Community platform!

Oio.lk is an excellent forum for developers, providing a wide range of resources, discussions, and support for those in the developer community. Join oio.lk today to connect with like-minded professionals, share insights, and stay updated on the latest trends and technologies in the development field.
  You need to log in or register to access the solved answers to this problem.
  • You have reached the maximum number of guest views allowed
  • Please register below to remove this limitation

Avoiding redundant SQL update in python

  • Thread starter Thread starter 89Tain
  • Start date Start date
8

89Tain

Guest
I want to keep track of the updates made by storing the increment value the table has been updated
however, the update scripts still execute even when there are no different values

This is the log:

Code:
Updated record for accession_id: KCH2400337322, test_type: 35, new test_status: 3, old test_status: 3
Updated record for accession_id: KCH2400337323, test_type: 35, new test_status: 3, old test_status: 3
Updated record for accession_id: KCH2400337324, test_type: 35, new test_status: 3, old test_status: 3
Updated record for accession_id: KCH2400337325, test_type: 35, new test_status: 5, old test_status: 5
Updated record for accession_id: KCH2400337327, test_type: 35, new test_status: 5, old test_status: 5
Updated record for accession_id: KCH2400337328, test_type: 40, new test_status: 3, old test_status: 3
Updated record for accession_id: KCH2400337329, test_type: 35, new test_status: 3, old test_status: 3
Updated record for accession_id: KCH2400337329, test_type: 40, new test_status: 2, old test_status: 2


this is updateEntry.py:

Code:
import mysql.connector
from helper import getDepartmentIdHelper, getTestTypeID
from config import testType1, testType2, testType3, testType4, interval

intvl = interval  # 100
department_id = getDepartmentIdHelper()  # 3
test_type_id1 = getTestTypeID(testType1)  # 35
test_type_id2 = getTestTypeID(testType2)  # 39
test_type_id3 = getTestTypeID(testType3)  # 40
test_type_id4 = getTestTypeID(testType4)  # 41

def updateEntries():
    try:
        # Connect to iBlissDB
        iblis_connection = mysql.connector.connect(
            host="127.0.0.1",
            port="3306",
            user="root",
            password="root",
            database="tests"
        )

        # Connect to srsDB
        srs_connection = mysql.connector.connect(
            host="127.0.0.1",
            port="3306",
            user="root",
            password="root",
            database="Haematology"
        )

        iblis_cursor = iblis_connection.cursor(dictionary=True)
        srs_cursor = srs_connection.cursor(dictionary=True)

        # iblis_query to fetch the required data
        iblis_query = """
        WITH RankedTests AS (
            SELECT 
                specimens.accession_number AS accession_id,
                tests.test_type_id AS test_type,
                tests.test_status_id AS test_status,
                ROW_NUMBER() OVER (
                    PARTITION BY specimens.accession_number, tests.test_type_id 
                    ORDER BY tests.time_created DESC
                ) AS rn
            FROM 
                specimens
            INNER JOIN 
                tests ON specimens.id = tests.specimen_id
            WHERE 
                specimens.specimen_type_id = %s
                AND tests.test_status_id NOT IN (1, 6, 7, 8)
                AND tests.time_created >= NOW() - INTERVAL %s DAY
                AND tests.test_type_id IN (%s, %s, %s, %s)
        )
        SELECT
            accession_id,
            test_type,
            test_status
        FROM
            RankedTests
        WHERE
            rn = 1;
        """

        # Execute the query
        iblis_cursor.execute(iblis_query, (department_id, intvl, test_type_id1, test_type_id2, test_type_id3, test_type_id4))
        iblis_results = iblis_cursor.fetchall()

        # Insert the results into srsDB if they don't already exist
        for result in iblis_results:
            accession_id = result['accession_id']
            test_type = result['test_type']
            test_status = result['test_status']

            # Check if accession_id with the same test_type already exists in the srsDB tests table
            srs_cursor.execute("SELECT test_status FROM tests WHERE accession_id = %s AND test_type = %s", (accession_id, test_type))
            existing_record = srs_cursor.fetchone()

            if not existing_record:
                # Insert the record into srsDB
                srs_insert_query = """
                INSERT INTO tests (accession_id, test_type, test_status)
                VALUES (%s, %s, %s)
                """
                srs_cursor.execute(srs_insert_query, (accession_id, test_type, test_status))
                srs_connection.commit()
                print(f"Inserted new record for accession_id: {accession_id}, test_type: {test_type}, test_status: {test_status}")

            elif existing_record['test_status'] == test_status:
                print(f"No update needed for accession_id: {accession_id}, test_type: {test_type}, test_status: {test_status}")
                continue

            elif existing_record['test_status'] != test_status:
                # Update the status if it is different
                srs_update_query = """
                UPDATE tests
                SET test_status = %s
                WHERE accession_id = %s AND test_type = %s
                """
                srs_cursor.execute(srs_update_query, (test_status, accession_id, test_type))
                srs_connection.commit()
                print(f"Updated record for accession_id: {accession_id}, test_type: {test_type}, new test_status: {test_status}, old test_status: {existing_record['test_status']}")

    except mysql.connector.Error as err:
        print(f"Error: {err}")

    finally:
        # Close all connections and cursors
        if 'iblis_cursor' in locals():
            iblis_cursor.close()
        if 'iblis_connection' in locals():
            iblis_connection.close()
        if 'srs_cursor' in locals():
            srs_cursor.close()
        if 'srs_connection' in locals():
            srs_connection.close()

updateEntries()

I have tried to rewrite the code... was facing the same issue on the insert part so that is solved using if and elif and using if else all yield the same results. and again explicitly stating that if the existing status is equal to the fetched status it should continue does not work.
<p>I want to keep track of the updates made by storing the increment value the table has been updated<br />
however, the update scripts still execute even when there are no different values</p>
<p>This is the log:</p>
<pre><code>Updated record for accession_id: KCH2400337322, test_type: 35, new test_status: 3, old test_status: 3
Updated record for accession_id: KCH2400337323, test_type: 35, new test_status: 3, old test_status: 3
Updated record for accession_id: KCH2400337324, test_type: 35, new test_status: 3, old test_status: 3
Updated record for accession_id: KCH2400337325, test_type: 35, new test_status: 5, old test_status: 5
Updated record for accession_id: KCH2400337327, test_type: 35, new test_status: 5, old test_status: 5
Updated record for accession_id: KCH2400337328, test_type: 40, new test_status: 3, old test_status: 3
Updated record for accession_id: KCH2400337329, test_type: 35, new test_status: 3, old test_status: 3
Updated record for accession_id: KCH2400337329, test_type: 40, new test_status: 2, old test_status: 2
</code></pre>
<hr />
<p>this is updateEntry.py:</p>
<pre><code>import mysql.connector
from helper import getDepartmentIdHelper, getTestTypeID
from config import testType1, testType2, testType3, testType4, interval

intvl = interval # 100
department_id = getDepartmentIdHelper() # 3
test_type_id1 = getTestTypeID(testType1) # 35
test_type_id2 = getTestTypeID(testType2) # 39
test_type_id3 = getTestTypeID(testType3) # 40
test_type_id4 = getTestTypeID(testType4) # 41

def updateEntries():
try:
# Connect to iBlissDB
iblis_connection = mysql.connector.connect(
host="127.0.0.1",
port="3306",
user="root",
password="root",
database="tests"
)

# Connect to srsDB
srs_connection = mysql.connector.connect(
host="127.0.0.1",
port="3306",
user="root",
password="root",
database="Haematology"
)

iblis_cursor = iblis_connection.cursor(dictionary=True)
srs_cursor = srs_connection.cursor(dictionary=True)

# iblis_query to fetch the required data
iblis_query = """
WITH RankedTests AS (
SELECT
specimens.accession_number AS accession_id,
tests.test_type_id AS test_type,
tests.test_status_id AS test_status,
ROW_NUMBER() OVER (
PARTITION BY specimens.accession_number, tests.test_type_id
ORDER BY tests.time_created DESC
) AS rn
FROM
specimens
INNER JOIN
tests ON specimens.id = tests.specimen_id
WHERE
specimens.specimen_type_id = %s
AND tests.test_status_id NOT IN (1, 6, 7, 8)
AND tests.time_created >= NOW() - INTERVAL %s DAY
AND tests.test_type_id IN (%s, %s, %s, %s)
)
SELECT
accession_id,
test_type,
test_status
FROM
RankedTests
WHERE
rn = 1;
"""

# Execute the query
iblis_cursor.execute(iblis_query, (department_id, intvl, test_type_id1, test_type_id2, test_type_id3, test_type_id4))
iblis_results = iblis_cursor.fetchall()

# Insert the results into srsDB if they don't already exist
for result in iblis_results:
accession_id = result['accession_id']
test_type = result['test_type']
test_status = result['test_status']

# Check if accession_id with the same test_type already exists in the srsDB tests table
srs_cursor.execute("SELECT test_status FROM tests WHERE accession_id = %s AND test_type = %s", (accession_id, test_type))
existing_record = srs_cursor.fetchone()

if not existing_record:
# Insert the record into srsDB
srs_insert_query = """
INSERT INTO tests (accession_id, test_type, test_status)
VALUES (%s, %s, %s)
"""
srs_cursor.execute(srs_insert_query, (accession_id, test_type, test_status))
srs_connection.commit()
print(f"Inserted new record for accession_id: {accession_id}, test_type: {test_type}, test_status: {test_status}")

elif existing_record['test_status'] == test_status:
print(f"No update needed for accession_id: {accession_id}, test_type: {test_type}, test_status: {test_status}")
continue

elif existing_record['test_status'] != test_status:
# Update the status if it is different
srs_update_query = """
UPDATE tests
SET test_status = %s
WHERE accession_id = %s AND test_type = %s
"""
srs_cursor.execute(srs_update_query, (test_status, accession_id, test_type))
srs_connection.commit()
print(f"Updated record for accession_id: {accession_id}, test_type: {test_type}, new test_status: {test_status}, old test_status: {existing_record['test_status']}")

except mysql.connector.Error as err:
print(f"Error: {err}")

finally:
# Close all connections and cursors
if 'iblis_cursor' in locals():
iblis_cursor.close()
if 'iblis_connection' in locals():
iblis_connection.close()
if 'srs_cursor' in locals():
srs_cursor.close()
if 'srs_connection' in locals():
srs_connection.close()

updateEntries()
</code></pre>
<p>I have tried to rewrite the code... was facing the same issue on the insert part so that is solved using if and elif and using if else all yield the same results. and again explicitly stating that if the existing status is equal to the fetched status it should continue does not work.</p>
 
Top