Starting with Pandas

pandas_start.py

import pandas as pd
import re
from customer_purchase_order.models import CustomerPurchaseOrderItem
from django.db import connection
from django.db.models import Q

# read file XLSX
# file_name = r"D:\zina-files\NFList.xlsx"
# xl_file = pd.ExcelFile(file_name)
# show avaliable sheets
# xl_file.sheet_names
# get sheet
# dfs = xl_file.parse(xl_file.sheet_names[0])

# read file CSV
file_name = r"D:\zina-files\NFList2.csv"
dfs = pd.read_csv(file_name, sep=';')

# show avaliables columns
dfs.columns

# remove nan values
mask = dfs.applymap(lambda x: x in ['None', pd.np.nan])
dfs = dfs[-mask.any(axis=1)]

# remover nota de transferencia
dfs = dfs[-dfs['Material Description'].str.contains('Transfer')]

# create new dataframeset with only cpo and cpo_item
cpos = dfs[['Customer PO', 'Purchase order item']]

# remove nan values
# mask = cpos.applymap(lambda x: x in ['None', pd.np.nan])
# cpos[-mask.any(axis=1)]
# cpos = cpos[-mask.any(axis=1)]

# drop duplicated data
cpos = cpos.drop_duplicates(['Customer PO', 'Purchase order item'])

# remove trash arguments in values
def return_num(value):
    return ''.join(re.findall('\d+', value))

cpos = cpos.applymap(return_num)

# -----------------------------------------------------------------------------------
cpos_array = cpos.values

# In [120]: cpos_array[1]
# Out[120]: array([u'4505044717', u'1'], dtype=object)
# In [121]: cpos_array[1][1]
# Out[121]: u'1'
# In [122]: cpos_array[1][0]
# Out[122]: u'4505044717

qs = Q()
SQL = """
SELECT [cpo].[number], [cpo_item].[item]
FROM [nota_fiscal_notafiscalitem] [nf_item]
LEFT JOIN [nota_fiscal_notafiscal] [nf] ON ([nf_item].[nota_fiscal_id] = [nf].id)
LEFT JOIN [customer_customerteam] [ct] ON ([nf].[customer_team_id] = [ct].id)
LEFT JOIN [customer_purchase_order_customerpurchaseorderitem] [cpo_item] ON ([cpo_item].[id] = [nf_item].[customer_purchase_order_item_id])
LEFT JOIN [customer_purchase_order_customerpurchaseorder] [cpo] ON ([cpo_item].[customer_purchase_order_id] = [cpo].[id])
WHERE [nf].[cancelled] = 0 AND

"""


for i in cpos_array:
    SQL += """
    ([cpo].[number] = {cpo} AND [cpo_item].[item] = {cpo_item}) AND""".format(cpo=i[0], cpo_item=i[1])

# remove last end
SQL = SQL[:-3]

def my_custom_sql(self):
    cursor = connection.cursor()
    cursor.execute(SQL)
    row = cursor.fetchone()
    return row

my_custom_sql()

# -----------------------------------------------------------------------------------
# Preciso só achar quais Linhas não estão cadastradas e cadastrar no NF