DJango Production and Test database QC check on data

I am looking to create a data QC check between our production and development environments.
This is to ensure our systems templates are set up the same, there will be NO data writing to the database read-only

the models are as such:

class Plan(models.Model):
DatabaseOperations.max_name_length = lambda s: 40

# Define fields that match the columns in your existing table
PROJECT_CODE = models.TextField(primary_key=True)
PROJECT_NAME = models.TextField()
IS_TEMPLATE_PROJECT = models.TextField()
PLANTYPE = models.TextField()
ABR_SUPP_BY_SSG = models.TextField()

class Meta:
    db_table = '\"schema\".\"table\"'  # specify the schema and table name
    managed = False  # Prevent Django from managing the table schema

def __str__(self):
    return self.PROJECT_NAME

class Activity(models.Model): DatabaseOperations.max_name_length = lambda s: 40

# Define fields that match the columns in your existing table
ACTIVITY_ID = models.TextField(primary_key=True)
PROJECT_CODE = models.ForeignKey(Plan, on_delete=models.DO_NOTHING, db_column='PROJECT_CODE', related_name='activities')
ACTIVITY_NAME = models.TextField()
LINE_IDENTIFIER = models.IntegerField()
class Meta:
    db_table = '\"schamea\".\"table\"'  # specify the schema and table name
    managed = False  # Prevent Django from managing the table schema

def __str__(self):
    return self.ACTIVITY_NAME

I am using an HTML form to allow the users to select the database to use (more will be added later

        <form method="GET" action="{% url 'list_plans' %}">
    <label for="Databases">Choose Databases:</label><br>
    <input type="checkbox" id="db1" name="databases" value="db1">
    <label for="db1">Production</label><br>
    <input type="checkbox" id="db2" name="databases" value="db2">
    <label for="db2">Test</label><br>
    <button type="submit">Load Plans</button>
    </form>

from my view I am getting def list_plans(request): # Define database names for production and test selected_databases = request.GET.getlist('databases')

try:
    PlanModel = apps.get_model('compareTemplate', 'Plan')
    ActivityModel = apps.get_model('compareTemplate', 'Activity')
    combined_plans = []

    for database_name in selected_databases:
        # region Prefetch activities for each plan and filter on templates
        activities_prefetch = Prefetch('activities', queryset=ActivityModel.objects.using(database_name))

        # Apply filter for IS_TEMPLATE_PROJECT = 'true' and prefetch related activities
        filtered_plans = PlanModel.objects.using(database_name).filter(IS_TEMPLATE_PROJECT='true').prefetch_related(activities_prefetch)

        # region Prepare the plan data with field names and values
        for plan in filtered_plans:
            activities_data = [
                {'fields': get_fields_and_values(activity)}
                for activity in plan.activities.all()
            ]
            plan_data = {
                'database': database_name,
                'fields': get_fields_and_values(plan),
                'activities': activities_data
            }
            combined_plans.append(plan_data)

now what I need to do is compare the fields in my (db1) prod.Plans table to my (db2) test.Plans table

this code appends them, but I need to join them

The join would be on the PROJECT_CODE and LINE_INDENTIFIER in the activities table and the PROJECT_CODE on the plans table.

ultimately I would like an html output that reads

project_name table field line_number prod_value test_value

I am new to python, it took, me a week to figure this out sofar, please be nice :)

I don't have access to the DB as an admin and will not be given any and we cannot create a DB link between the two due to policies

Thanks

Back to Top