Is There Junk in My Salesforce Pricebooks?

I had an interesting problem today: Find products in my Salesforce pricebooks that we have never sold.. In my case I want to delete these products from Salesforce (that's another post).

Finding the products was easy. I wanted all products where:

• No Opportunity referenced the product.
  
• No Asset referenced the product
  

Here is the script:

import SQLForce

session = SQLForce.Session("sandbox")

for product in session.select("SELECT id,name, division__c FROM Product2 ORDER BY name asc"):

    nOps = session.select("SELECT COUNT() FROM OpportunityLineItem WHERE PriceBookEntry.product2Id ='" + product[0]+ "'")[0][0]

    nAssets = session.select("SELECT COUNT() FROM Asset WHERE product2Id='" + product[0]+ "'")[0][0]

    pricebooks = []

    for pb in session.select("SELECT DISTINCT Pricebook2Id FROM PricebookEntry WHERE Product2Id='" + product[0]+ "'"):
        pricebooks.append(pb[0])

    if "0"==nOps and "0"==nAssets:
        notUsed = [product[0],product[2],product[1], nOps, nAssets, "\t".join(pricebooks)  ]
        print "\t".join(notUsed)

I put the results of the script into an Excel worksheet so marketing can look for mistakes. Later I will modify the script to delete the records I found.