As of March 31, 2022 Octopart APIv3 will no longer be supported. As of June 30, 2022 this version will be completely disabled.
Octopart APIv3 and Nexar Legacy API REST provide methods which make it easy to match line items in a BOM against Octopart parts and retrieve information about pricing and availability. In this tutorial we will walk you throgh building a Python script which parses a BOM, matches it against Octopart parts, and prices the components. We'll provide sample Python code along the way. For the purposes of this tutorial, we'll be working with a publicly available Arduino BOM. For simplicity, we've already converted the BOM into a CSV formatted file and made it available for download here.
Parsing a BOM
We'll need to convert our BOM into queries which the API recognizes. We'll be using the parts/match endpoint. This endpoint can accept a variety of parameters to match on, we'll be using the 'mpn' and the 'brand' of the manufacturer, though we could also leave out the 'brand' or use the 'sku' instead. The code snippet below uses the Python CSV DictReader to iterate through the BOM specified on the command line and converts line items into queries.
# Section 1# Convert csv file into line items and queries# This code assumes a file format similar to the one on the# Arduino BOMimportcsvcsv_file=open("arduino_bom.csv","r")csv_reader=csv.DictReader(csv_file)line_items=[]queries=[]forline_itemincsv_reader:# Skip line items without part numbers and manufacturersifnotline_item['Part Number']ornotline_item['Manufacturer']:continueline_items.append(line_item)queries.append({'mpn':line_item['Part Number'],'brand':line_item['Manufacturer'],'reference':len(line_items)-1})
namespaceOctopartApi{usingCsvHelper;usingNewtonsoft.Json;usingRestSharp;usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingSystem.IO;usingSystem.Web.Script.Serialization;publicstaticclassBomQuote{publicstaticvoidExecuteSearch(){// Section 1// Convert CSV file into line items and queries// This code assumes a file format similar to the one on the// Arduino BOMList<Dictionary<string,string>>line_items=newList<Dictionary<string,string>>();List<Dictionary<string,string>>queries=newList<Dictionary<string,string>>();using(StreamReaderreader=File.OpenText("arduino_bom.csv")){using(varcsv=newCsvReader(reader)){while(csv.Read()){varmpn=csv.GetField<string>("Part Number");varbrand=csv.GetField<string>("Manufacturer");if(!string.IsNullOrEmpty(mpn)&&!string.IsNullOrEmpty(brand)){line_items.Add(Enumerable.Range(0,csv.FieldHeaders.Length).ToDictionary(i=>csv.FieldHeaders[i],i=>csv.CurrentRecord[i]));queries.Add(newDictionary<string,string>{{"mpn",mpn},{"brand",brand},{"reference",(line_items.Count-1).ToString()}});}}}}
ImportsCsvHelperImportsNewtonsoft.JsonImportsRestSharpImportsSystemImportsSystem.Collections.GenericImportsSystem.LinqImportsSystem.IOImportsSystem.Web.Script.SerializationPublicModuleBomQuotePublicSubExecuteSearch()' Section 1' Convert CSV file into line items and queries' This code assumes a file format similar to the one on the' Arduino BOMDimline_items=NewList(OfDictionary(OfString,String))()Dimqueries=NewList(OfDictionary(OfString,String))()UsingreaderAsStreamReader=File.OpenText("arduino_bom.csv")UsingcsvAsCsvReader=NewCsvReader(reader)Whilecsv.Read()DimmpnAsString=csv.GetField("Part Number")DimbrandAsString=csv.GetField("Manufacturer")If(NotString.IsNullOrEmpty(mpn)AndNotString.IsNullOrEmpty(brand))Thenline_items.Add(Enumerable.Range(0,csv.FieldHeaders.Length).ToDictionary(OfString,String)(Function(i)csv.FieldHeaders(i),Function(i)csv.CurrentRecord(i)))queries.Add(NewDictionary(OfString,String)()From _
{{"mpn",mpn}, _
{"brand",brand}, _
{"reference",(line_items.Count-1).ToString()}})EndIfEndWhileEndUsingEndUsing
Now that we've aggregated our Octopart APIv3 formatted queries, we'll send them to the API for matching to Octopart parts. As the parts/match endpoint can only accept twenty line items at a time, we'll batch our queries into groups of twenty and aggregate the results.
# Section 2# Send queries to REST API for part matching.importjsonimporturllibresults=[]foriinrange(0,len(queries),20):# Batch queries in groups of 20, query limit of# parts match endpointbatched_queries=queries[i:i+20]url='http://octopart.com/api/v3/parts/match?queries=%s' \
%urllib.quote(json.dumps(batched_queries))url+='&apikey=REPLACE_ME'data=urllib.urlopen(url).read()response=json.loads(data)# Record results for analysisresults.extend(response['results'])
namespaceOctopartApi{usingCsvHelper;usingNewtonsoft.Json;usingRestSharp;usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingSystem.IO;usingSystem.Web.Script.Serialization;publicstaticclassBomQuote{publicstaticvoidExecuteSearch(){// Section 2// Send queries to REST API for part matchingList<dynamic>results=newList<dynamic>();for(inti=0;i<queries.Count;i+=20){// Batch queries in groups of 20, query limit of// parts match endpointvarbatched_queries=queries.GetRange(i,Math.Min(20,queries.Count-i));stringoctopartUrlBase="http://octopart.com/api/v3";// Octopart API urlstringoctopartUrlEntpoint="parts/match";// Octopart search typestringapiKey=APIKEY;// -- your API key -- (https://octopart.com/api/register)// Create the search requeststringqueryString=(newJavaScriptSerializer()).Serialize(batched_queries);varclient=newRestClient(octopartUrlBase);varreq=newRestRequest(octopartUrlEntpoint,Method.GET).AddParameter("apikey",apiKey).AddParameter("queries",queryString);// Perform the search and obtain resultsvardata=client.Execute(req).Content;varresponse=JsonConvert.DeserializeObject<dynamic>(data);results.AddRange(response["results"]);}
ImportsCsvHelperImportsNewtonsoft.JsonImportsRestSharpImportsSystemImportsSystem.Collections.GenericImportsSystem.LinqImportsSystem.IOImportsSystem.Web.Script.SerializationPublicModuleBomQuotePublicSubExecuteSearch()' Section 2' Send queries to REST API for part matchingDimresultsAsNewList(OfObject)ForiAsInteger=0Toqueries.CountStep20' Batch queries in groups of 20, query limit of' parts match endpointDimbatched_queries=queries.GetRange(i,Math.Min(20,queries.Count-i))DimoctopartUrlBaseAsString="http://octopart.com/api/v3"' Octopart API urlDimoctopartUrlEntpointAsString="parts/match"' Octopart search type' -- your API key -- (https:'octopart.com/api/register)DimapiKeyAsString=BomQuote.APIKEY' Create the search requestDimqueryString=(NewJavaScriptSerializer()).Serialize(batched_queries)Dimclient=NewRestClient(octopartUrlBase)Dimreq=NewRestRequest(octopartUrlEntpoint,Method.GET) _
.AddParameter("apikey",apiKey) _
.AddParameter("queries",queryString)' Perform the search and obtain resultsDimdata=client.Execute(req).ContentDimresponse=JsonConvert.DeserializeObject(data)results.AddRange(response("results"))Next
Now for the big payoff! We iterate through our results, keeping track of line items which didn't match on an Octopart part via the 'reference' parameter. For parts that did match, we'll extract a price in US dollars averaged across all offers at the price break for the specified quantity in the line item. If desired, we could extend this analysis further to find the minimum price on all components, favor particular distributors or even isolate supply shortages.
# Section 3# Analyze results sent back by Octopart APIfromdecimalimportDecimalprint"Found %s line items in BOM."%len(line_items)# Price BOMhits=0total_avg_price=0forresultinresults:line_item=line_items[result['reference']]iflen(result['items'])==0:print"Did not find match on line item %s"%line_itemcontinue# Get pricing from the first item for desired quantityquantity=Decimal(line_items[result['reference']]['Qty'])prices=[]forofferinresult['items'][0]['offers']:if'USD'notinoffer['prices'].keys():continueprice=Noneforprice_tupleinoffer['prices']['USD']:# Find correct price breakifprice_tuple[0]>quantity:break# Cast pricing string to Decimal for precision# calculationsprice=Decimal(price_tuple[1])ifpriceisnotNone:prices.append(price)iflen(prices)==0:print"Did not find pricing on line item %s"%line_itemcontinueavg_price=quantity*sum(prices)/len(prices)total_avg_price+=avg_pricehits+=1print"Matched on %.2f of BOM, total average price is USD %.2f"%( \
hits/float(len(line_items)),total_avg_price)
namespaceOctopartApi{usingCsvHelper;usingNewtonsoft.Json;usingRestSharp;usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingSystem.IO;usingSystem.Web.Script.Serialization;publicstaticclassBomQuote{publicstaticvoidExecuteSearch(){// Section 3// Analyze results sent back Octopart APIConsole.WriteLine("Found "+line_items.Count+" line items in BOM.");// Price BOMinthits=0;doubletotal_avg_price=0.0;foreach(varresultinresults){varline_item=line_items[(int)result["reference"]];if(!result["items"].HasValues){Console.WriteLine(String.Format("Did not find a match on line item #{0} ({1})",(int)result["reference"]+1,string.Join(" ",line_item.Values.ToArray<string>())));continue;}// Get pricing from the first item for desired quantityintquantity=int.Parse(line_item["Qty"]);List<double>prices=newList<double>();foreach(varofferinresult["items"][0]["offers"]){if(offer["prices"]["USD"]==null)continue;doubleprice=0;foreach(varprice_tupleinoffer["prices"]["USD"]){// Find correct price breakif(price_tuple[0]>quantity)break;price=price_tuple[1];}if(price!=0){prices.Add(price);}}if(prices.Count==0){Console.WriteLine(String.Format("Did not find USD pricing on line item #{0} ({1})",(int)result["reference"]+1,string.Join(" ",line_item.Values.ToArray<string>())));continue;}doubleavg_price=quantity*prices.Sum()/prices.Count;total_avg_price+=avg_price;hits++;}Console.WriteLine(String.Format("Matched on {0:0.0}% of BOM, total average prices is USD {1:0.00}.",(hits/(float)line_items.Count)*100,total_avg_price));}privateconststringAPIKEY="REPLACE_ME";}}
ImportsCsvHelperImportsNewtonsoft.JsonImportsRestSharpImportsSystemImportsSystem.Collections.GenericImportsSystem.LinqImportsSystem.IOImportsSystem.Web.Script.SerializationPublicModuleBomQuotePublicSubExecuteSearch()' Section 3' Analyze results sent back Octopart APIConsole.WriteLine("Found "&line_items.Count&" line items in BOM.")' Price BOMDimhitsAsInteger=0Dimtotal_avg_priceAsDouble=0.0ForEachresultInresultsDimline_itemAsObject=line_items(result("reference"))If(Notresult("items").HasValues)ThenConsole.WriteLine(String.Format("Did not find a match on line item #{0} ({1})",result("reference")+1,String.Join(" ",CType(line_item,Dictionary(OfString,String)).Select(Function(kvp)kvp.Value).ToArray())))ContinueForEndIf' Get pricing from the first item for desired quantityDimquantityAsInteger=Integer.Parse(line_item("Qty"))Dimprices=NewList(OfDouble)()ForEachofferInresult("items")(0)("offers")If(offer("prices")("USD")IsNothing)ThenContinueForEndIfDimpriceAsDouble=0ForEachprice_tupleInoffer("prices")("USD")' Find correct price breakIf(price_tuple(0)>quantity)ThenExitForEndIfprice=price_tuple(1)NextIf(Notprice=0)Thenprices.Add(price)EndIfNextIf(prices.Count=0)ThenConsole.WriteLine(String.Format("Did not find USD pricing on line item #{0} ({1})",result("reference")+1,String.Join(" ",CType(line_item,Dictionary(OfString,String)).Select(Function(kvp)kvp.Value).ToArray())))ContinueForEndIfDimavg_priceAsDouble=quantity*prices.Sum()/prices.Counttotal_avg_price+=avg_pricehits=hits+1NextConsole.WriteLine(String.Format("Matched on {0:0.0}% of BOM, total average prices is USD {1:0.00}.",(hits/line_items.Count)*100,total_avg_price))EndSubPrivateConstAPIKEYAsString="REPLACE_ME"EndModule
Putting all of this code together, running the script results in the following output (this assumes 'arduino_bom.csv' is in the same directory as the Python script).
$ python bom_quote.py
Found 47 line items in BOM.
Matched on 0.83 of BOM, total average price is USD 37.68
The final assembled script is available for download here.