4
\$\begingroup\$

I have just migrated from Python 2 to Python 3. I have written the following script to fetch all records of a table, ir_attachment, from PostgreSQL. After fetching all the records I check following points:

  1. If an attachment record has no value for one of related_model, related_model_id or saved_file_name, then I append it to the useless attachment records.

    If all exist then check the OS directory to see if there is any file corresponding with this attachment record. If any file found in directory and it was not related to more than one record I deleted that file from directory.

  2. Get the count of all useless attachment records and delete them from table.

  3. If any attachment record's related_model not found in application_models it is counted as application_delete_models records and it also should be delete from the attachment table.
  4. At the end I print all deleted attachment records as total useless attachment records.
# -*- coding:utf-8 -*-
import sys
import os
import psycopg2
import psycopg2.extras
from datetime import datetime


class DeleteAttachment:
    # Class variables to handle counts golobaly
    attachments_with_file_in_directory = 0

    def connect_to_application_database(self):
        self.params = {
            "database": "test",
            "user": "test",
            "password": "test",
            "host": "100.100.100.100",
            "port": "5432"
        }
        try:
            self.application_connection = psycopg2.connect(**self.params)
            self.application_cursor = self.application_connection.cursor(
                    cursor_factory=psycopg2.extras.RealDictCursor
                )
            psycopg2.extensions.register_type(psycopg2.extensions.UNICODE)
            psycopg2.extensions.register_type(psycopg2.extensions.UNICODEARRAY)
            print('++++++++++++++++Connected to application Database++++++++++++++++')
        except:
            print("Could not connect to application database", sys.exc_info()[1])
            return

    def get_attachment_with_deleted_relating_record(self):
        self.application_cursor.execute("""
            SELECT model FROM ir_model WHERE model IS NOT NULL;
        """)
        application_models = self.application_cursor.fetchall()
        application_models_names = [model.get('model') for model in application_models]

        self.application_cursor.execute("""
                SELECT id,related_model_id,related_model, saved_file_name FROM ir_attachment;
        """)
        attachment_records = self.application_cursor.fetchall()

        print('==============Checking attachment records started==============')

        useless_attachment_records = []
        application_deleted_models = []

        for record in attachment_records:
            if not record['related_model']:
                self.check_if_attachmet_record_file_exits_in_directory(record)
                useless_attachment_records.append(record['id'])
                continue

            if not record['related_model_id']:
                self.check_if_attachmet_record_file_exits_in_directory(record)
                useless_attachment_records.append(record['id'])
                continue 

            if not record['saved_file_name']:
                useless_attachment_records.append(record['id'])
                continue

            if record['related_model'] and record['related_model_id']:
                if record['related_model'] in application_models_names:  
                    model_name = str(record['related_model'].replace('.', '_'))
                    self.application_cursor.execute(
                            "SELECT id FROM " + model_name + " WHERE id=%s", 
                        (record['related_model_id'],))
                    model_record = self.application_cursor.fetchall()

                    if not model_record:
                        self.check_if_attachmet_record_file_exits_in_directory(record)
                        useless_attachment_records.append(record['id'])
                else:
                    application_deleted_models.append(record['id'])

        print('===========Start deleting useless attachment records===========')
        # Call method to delete all useless attachment records.
        if useless_attachment_records:
            self.delete_attachment_record(useless_attachment_records)

        print("=====Start deleting Application deleted models' attachment records====")
        # Call method to delete those files related to unique attachment record 
        # in attachment diectory of the os server.
        if application_deleted_models:
            self.delete_attachment_record(application_deleted_models)

        print('+++++++++++++++++++++++++++Counts++++++++++++++++++++++++++++++')
        print('Total useless attachment records:', 
                len(application_deleted_models) 
                + len(useless_attachment_records)
            )
        print('Tatal attachment records with attachment file in OS directory: ',
                self.attachments_with_file_in_directory
             )

    def check_if_attachmet_record_file_exits_in_directory(self, attachment_record):
        if attachment_record['saved_file_name']:
            attachment_file = '/application/attachments/application/filestore/application' +\
                '/' + attachment_record['saved_file_name']
            if os.path.isfile(attachment_file):
                self.attachments_with_file_in_directory += 1
                self.application_cursor.execute("""SELECT id FROM ir_attachment 
                                        WHERE saved_file_name=%s AND id!=%s
                                """, (attachment_record['saved_file_name'], 
                                attachment_record['id'],))
                check_attachment_existance_for_another_record = \
                    self.application_cursor.fetchall()

                if not check_attachment_existance_for_another_record:
                    self.delete_file_from_directory(attachment_file)

    def delete_attachment_record(self, attachment):
        self.application_cursor.execute("""
                DELETE FROM ir_attachment WHERE id in %s;
            """, (tuple(attachment),))

    def delete_file_from_directory(self, file_name):
        os.remove(file_name)

    def close_datebase_connection(self):
        self.application_connection.close()
        self.application_cursor.close()

delete_attachment_obj = DeleteAttachment()
start_time = datetime.now()

delete_attachment_obj.connect_to_application_database()
delete_attachment_obj.get_attachment_with_deleted_relating_record()
delete_attachment_obj.application_connection.close()
delete_attachment_obj.application_cursor.close()

print('Script running time:', datetime.now() - start_time)

How Pythonic is this structure, naming conventions, using data structures (Python 3 methods), line length and coding style.

Also, because I use this script to fetch and process huge amount of records, how can I make it faster?

\$\endgroup\$

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.