Note: we'll be using our home-built API connector Create an API connector, and OpenPyXL for accessing the Excel file. We could simplify this by using the 'official' Unicat client lib, but that wouldn't give the API insights because it is more high-level.
Note: at the end of the page, we show the same example while using the Unicat client lib.
We have an Excel file with a few thousand rows, and two columns. In column A we have an article number, and in column B is its price.
We want to update the price in Unicat for each article in the Excel sheet.
We need some infrastructure for our program.
from ccapi import UnicatApi
from openpyxl import load_workbook
def main():
print("Alright, the imports seem to work!")
if __name__ == "__main__":
main()
We'll be adding to the main() function, and we skip over the part where we set up the config for the project_gid and api_key.
workbook = load_workbook('price-update.xlsx')
sheet = workbook.active
for row in sheet.values:
artnr, price = row
OpenPyXL is very flexible when retrieving data, but they have their own documentation.
When searching you must provide a language, but since the price isn't localized, the actual language doesn't really matter.
ccapi = UnicatApi("https://unicat.app", PROJECT_GID)
success, result = ccapi.connect(SECRET_API_KEY)
data = { "language": "en",
"filter": ["and", "", [
["fields", "has", "artnr"],
["value", "is", ["artnr", artnr]]
],
}
success, result = ccapi.call("/records/search", data)
if result["records"]:
print(result["records"])
If we have multiple records with the same article number, we can update them all. Actually, we would check the pagination then, but for now let's assume there's not that many duplicated articles.
ccapi = UnicatApi("https://unicat.app", PROJECT_GID)
success, result = ccapi.connect(SECRET_API_KEY)
workbook = load_workbook('price-update.xlsx')
sheet = workbook.active
for row in sheet.values:
artnr, price = row
data = { "language": "en",
"filter": ["and", "", [
["fields", "has", "artnr"],
["value", "is", ["artnr", artnr]]
],
}
success, result = ccapi.call("/records/search", data)
if result["records"]:
for record_gid in result["records"]:
data = { "record": record_gid,
"fields": {"en": {"price": price}},
}
success, result = ccapi.call("/records/update", data)
pip install unicat
Ok, here it goes:
from unicat import Unicat
from unicat.utils import DuckObject
from openpyxl import load_workbook
from .config import PROJECT_GID, SECRET_API_KEY, LOCAL_ASSET_FOLDER
unicat = Unicat("https://unicat.app", PROJECT_GID, SECRET_API_KEY, LOCAL_ASSET_FOLDER)
if not unicat.connect():
raise Exception("Invalid connection settings")
language = unicat.project.languages[0]
workbook = load_workbook('price-update.xlsx')
sheet = workbook.active
for row in sheet.values:
artnr, price = row
query = DuckObject({
q = "",
filter = ["and", "", [
["fields", "has", "artnr"],
["value", "is", ["artnr", artnr],
]],
})
# no need to check for pagination, walk_record_query has us covered
for record in unicat.walk_record_query(language, query):
unicat.mutate.update_record(record, {"en": {"price": price}})