3
\$\begingroup\$

I am writing a script to read rows (records) from an excel file with xlrd package and then create a dictionary for each row. In next step, I append each dictionary to a list. After I finished reading rows from excel file and prepared the list of dictionaries; I use odoorpc to connect to the interface of an Odoo application and create records in an Odoo model (database table). But before writing the records to the Odoo model I need to make sure to not insert duplicate rows (dictionaries) to the Odoo model. I wrote bellow script, now I want to know where in my script I should check for duplicate rows? Should I do it when I read rows from excel file or check it in the list of dictionaries I have created from rows or even do one select query to the database and check for existent of rows in database? I am asking this because I need to improve performance and also understand how to check for duplicate dictionary as items in a list of dictionaries.

# -*- coding: utf-8 -*-
import psycopg2
import psycopg2.extras
import sys
import odoorpc
import xlrd
import base64


class Product():
    #Database connection
    def connet_to_database(self):
        self.apps_params = {
            "database":"test_db",
            "user":"postures",
            "password":"testpassword",
            "host": "testipaddress",
            "port" : "5432"
        }
        try:
            self.db_connection = psycopg2.connect(**self.apps_params)
            self.db_cursor = self.db_connection.cursor(cursor_factory=psycopg2.extras.RealDictCursor)

            psycopg2.extensions.register_type(psycopg2.extensions.UNICODE)
            psycopg2.extensions.register_type(psycopg2.extensions.UNICODEARRAY)

            print ('\033[1;32m Successfully Connected to Database \033[1;m')
        except:
            print ('Could not Connected to Database', sys.exc_info()[1])
            return        
    
    # Interface connection to add product itmes.
    def connect_to_interface(self):
        try:
            odoo = odoorpc.ODOO('127.0.0.1', port=8000)
            odoo.login('setara_erp','admin', 'admin')
            user = odoo.env.user
            print('\033[1;32m Successfully Connected to Interface \033[1;m')
            return odoo, user

        except Exception as e:
            print('Could not Connected to Interface', sys.exc_info()[1], e)
            return

    def add_product_item(self):
        connection = self.connect_to_interface()
        user = connection[1]
        xmlrpc_object = connection[0]

        counter = 0
        product_items = []
        product_item_values = {}
        file_location = 'product_items.xlsx'

        workbook = xlrd.open_workbook(file_location)
        sheet = workbook.sheet_by_index(0)

        for i in range(sheet.nrows):
            print('Adding item values to list: ', i)
            commodity_group_name = sheet.row_values(i)[3]
                        
            commodity_group_id = None
            self.db_cursor.execute("""
                            SELECT id 
                            FROM commodity_group 
                            WHERE name =%s""", (commodity_group_name,)
            )
            commodity_group = self.db_cursor.fetchone()
            if commodity_group:
                commodity_group_id = commodity_group['id']
        
            encoded_string = ''
            try:
                with open('photos/'+sheet.row_values(i)[4], "rb") as image_file:
                    encoded_string = base64.b64encode(image_file.read()).decode("utf-8")
            except Exception as e:
                print('File not found for this product item.', sheet.row_values(i)[4])

            product_item_values = {
                'active': True,
                'type': 'product',
                'default_code': sheet.row_values(i)[0],
                'name': sheet.row_values(i)[1],
                'image_1920': encoded_string,
                'part_number': sheet.row_values(i)[2],
                'commodity_group_id': commodity_group_id,
            }
            product_items.append(product_item_values)
        
        try:
            for item in product_items:
                xmlrpc_object.execute('product.template', 'create', item)
                print (str(counter)+' Items added')
                counter +=1
        except Exception as e:      
            print ('Could not add product. ', e)

apps = Product()
apps.connet_to_database()
apps.connect_to_interface()
apps.add_product_item()
\$\endgroup\$
1
  • \$\begingroup\$ What is the criteria for detecting duplicates? \$\endgroup\$
    – hjpotter92
    Oct 27 '20 at 14:41
2
\$\begingroup\$

Passwords

I sincerely hope that testpassword and admin will be replaced with more secure passwords. Also, they should not be hard-coded in this script; externalize them to a configuration file, wallet, environmental variables or the like, following good security practices.

Grammar

Could not Connected to Interface -> Could not Connect to Interface

Magic indexes

sheet.row_values(i)[3]
sheet.row_values(i)[4]

are mysterious. I understand that you're getting them out of a spreadsheet so options for structure are limited, but XLSX (and xlrd) support named ranges. You should use these and avoid magic indexes to improve legibility and maintainability. This assumes that you have control over the format of the spreadsheet.

If you do not control the spreadsheet, consider doing this in your loop:

default_code, name, part_number, commodity_group_name, photo = sheet.row_values(i)[:5]

which will be a little more clear.

Encoding

It appears that you're doing your own base-64 encoding and putting that into a string col. Do not do this. Use an actual blob; psycopg2 supports this natively.

Interactive vs. non-interactive

These:

\033[1;32m

will harm your logs, if you ever need to run this in non-interactive mode. Consider deleting them altogether, or (if you have a burning desire for fancy text) wrap this in a function that conditionally shows ANSI escape codes. Read this and similar.

\$\endgroup\$
2
  • \$\begingroup\$ There is also double call to connect_to_interface \$\endgroup\$
    – hjpotter92
    Oct 27 '20 at 15:56
  • \$\begingroup\$ Thank you for the nice comments, I will improve the script considering these points. But my main question was about where should I check for duplicate rows (records) in the script? Also, for the list I have created, how should I check if one dictionary exist in that list of not? \$\endgroup\$ Oct 28 '20 at 5:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.