{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Importing and exporting tabular data\n",
    "\n",
    "View tabular data information, and export the values from a record. Create a new record containing tabular data, and import this into a Granta MI database. \n",
    "\n",
    "This example demonstrates:\n",
    "\n",
    "- Get tabular data attribute information from a Granta MI database\n",
    "- Export tabular data from a record\n",
    "- Import a new record and contains tabular data\n",
    "- Update tabular data for a record in a database"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create a Granta MI Session\n",
    "\n",
    "Import the GRANTA_MIScriptingToolkit package, and create a connection to a Granta MI server."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import datetime\n",
    "import GRANTA_MIScriptingToolkit as gdl\n",
    "\n",
    "session = gdl.GRANTA_MISession('http://localhost/mi_servicelayer', autoLogon=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Get tabular data attribute information\n",
    "\n",
    "Search for the record \"Shore A75, flame retarded\" in the \"MI_Training\" database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "dbKey = \"MI_Training\"\n",
    "tableName = \"MaterialUniverse\"\n",
    "recordName = \"Shore A75, flame retarded\"\n",
    "\n",
    "req = gdl.RecordNameSearchRequest(caseSensitiveNames=False, searchShortNames=True, recordName=recordName)\n",
    "req.table = gdl.TableReference(DBKey=dbKey, name=tableName)\n",
    "resp = session.searchService.RecordNameSearch(req)\n",
    "record = resp.searchResults[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Use the browse service to get the column headers for the tabular data attribute \"Specifications\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Substance name\n",
      "CAS number\n",
      "Amount\n",
      "Substance rating\n",
      "Legislation name\n",
      "Legislation rating\n",
      "Effective date\n",
      "Category\n",
      "Function\n",
      "Comments\n"
     ]
    }
   ],
   "source": [
    "attribName = \"Restricted substances that may be associated with this material\"\n",
    "\n",
    "browse = gdl.BrowseService(session.mi_session)\n",
    "a = gdl.AttributeReference(name=attribName, \n",
    "                           partialTableReference=gdl.PartialTableReference(tableName=tableName), \n",
    "                           DBKey=dbKey)\n",
    "\n",
    "resp = browse.GetAttributeDetails(gdl.GetAttributeDetailsRequest([a]))\n",
    "for col in resp.attributeDetails[0].tabular.columns:\n",
    "    print(col.name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Export tabular data\n",
    "\n",
    "Perform a data export request to get column data for the tabular data attribute."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Table found.\n"
     ]
    }
   ],
   "source": [
    "dataExportRequest = gdl.GetRecordAttributesByRefRequest(recordReferences=[record.recordReference],\n",
    "                                                        attributeReferences=[a])\n",
    "\n",
    "dataExportResponse = session.dataExportService.GetRecordAttributesByRef(dataExportRequest)\n",
    "myRecordData = dataExportResponse.recordData\n",
    "\n",
    "for rec in myRecordData:\n",
    "    for attr in rec.attributeValues:\n",
    "        if attr.attributeName == attribName:\n",
    "            if not attr.dataType == \"TABL\":\n",
    "                raise TypeError(\"No tables found! Check your record.\")\n",
    "            myTable = attr.tabularDataType\n",
    "            print(\"Table found.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Print the data exported from the Granta MI database for this record. Note that attribute values, row cells, and list items all have a dataType attribute to help you pick the right data member to inspect."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Row 1:\n",
      "\tBis (2-ethyl(hexyl)phthalate) (DEHP)\n",
      "\t117-81-7\n",
      "\t0.0-50.0\n",
      "\tTo be phased-out\n",
      "\t\n",
      "Row 2:\n",
      "\tDibutyl phthalate\n",
      "\t84-74-2\n",
      "\t0.0-50.0\n",
      "\tTo be phased-out\n",
      "\t\n",
      "Row 3:\n",
      "\tAlkanes, C10-13, chloro;  (Short chain chlorinated paraffins )\n",
      "\t85535-84-8\n",
      "\t0.0-50.0\n",
      "\tHigh risk of phase-out\n",
      "\t\n"
     ]
    }
   ],
   "source": [
    "i = 1\n",
    "for row in myTable.tabularDataRows:\n",
    "\tif len(row.cells[0].listDataValue.items) > 0:\n",
    "\t\tprint(\"Row {0}:\".format(i))\n",
    "\t\tprint(\"\\t{0}\".format(row.cells[0].listDataValue.items[0].shortTextDataValue.value))\n",
    "\t\tprint(\"\\t{0}\".format(row.cells[1].listDataValue.items[0].shortTextDataValue.value))\n",
    "\t\tprint(\"\\t{0}-{1}\".format(row.cells[2].rangeDataValue.low, row.cells[2].rangeDataValue.high))\n",
    "\t\tprint(\"\\t{0}\".format(row.cells[3].discreteDataValue.discreteValues[0].value))\n",
    "\t\tprint(\"\\t{0}\".format(row.cells[4].shortTextDataValue.value))\n",
    "\t\ti = i + 1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import tabular data\n",
    "\n",
    "Search the database for a parent record under which you can create a new record. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "dbKey = \"MI_Training\"\n",
    "tableName = \"Training Exercise for Import\"\n",
    "recordName = \"Metal\"\n",
    "\n",
    "req = gdl.RecordNameSearchRequest(caseSensitiveNames=False, searchShortNames=True, recordName=recordName)\n",
    "req.table = gdl.TableReference(DBKey=dbKey, name=tableName)\n",
    "resp = session.searchService.RecordNameSearch(req)\n",
    "destination = resp.searchResults[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create an empty data structure, and populate it with tabular data values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Created a table with cell data 1 and Some notes about a material\n"
     ]
    }
   ],
   "source": [
    "newTable = gdl.TabularDataType()\n",
    "newTable.AddColumn(\"Order\")\n",
    "newTable.AddColumn(\"Notes\")\n",
    "\n",
    "\n",
    "newRow = newTable.CreateRow()\n",
    "newInt = gdl.IntegerDataType(1)\n",
    "newRow.cells[0].data = newInt\n",
    "\n",
    "newText = gdl.ShortTextDataType(value=\"Some notes about a material\")\n",
    "newRow.cells[1].data = newText\n",
    "\n",
    "print(\"Created a table with cell data {0} and {1}\".format(newInt.value, newText.value))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create a new import attribute, \"Characterization of this material\", that contains these tabular data values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "attribName = \"Characterization of this material\"\n",
    "\n",
    "a = gdl.AttributeReference(name=attribName,\n",
    "                           partialTableReference=gdl.PartialTableReference(tableName=tableName),\n",
    "                           DBKey=dbKey)\n",
    "\n",
    "importAtribute = gdl.ImportAttributeValue(attributeReference=a)\n",
    "importAtribute.tabularDataValue = newTable"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create a new import record."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Import-test@23/03/2021 16:54:30\n"
     ]
    }
   ],
   "source": [
    "now = datetime.datetime.now()\n",
    "currentTime = now.strftime(\"%d/%m/%Y %H:%M:%S\")\n",
    "recName = \"Import-test@{0}\".format(currentTime)\n",
    "\n",
    "importRecord = gdl.ImportRecord(recordName=recName,\n",
    "                                existingRecord=destination.recordReference,\n",
    "                                importAttributeValues=[importAtribute])\n",
    "print(recName)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Import the record to a Granta MI database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 records imported\n"
     ]
    }
   ],
   "source": [
    "setRecordAttributesRequest = gdl.SetRecordAttributesRequest(importRecords=[importRecord])\n",
    "response = session.dataImportService.SetRecordAttributes(setRecordAttributesRequest)\n",
    "recordsImported = response.recordsImported\n",
    "\n",
    "print(\"{0} records imported\".format(len(recordsImported)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Update the record\n",
    "\n",
    "Retrieve the row ID of the data that you want to update."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "This row's ID is 94427\n"
     ]
    }
   ],
   "source": [
    "dataExportRequest = gdl.GetRecordAttributesByRefRequest(recordReferences=[response.recordsImported[0].recordReference],  \n",
    "                                                        attributeReferences=[a])\n",
    "dataExportResponse = session.dataExportService.GetRecordAttributesByRef(dataExportRequest)\n",
    "myRecordData = dataExportResponse.recordData[0]\n",
    "attribute = myRecordData.attributeValues[0].tabularDataType\n",
    "\n",
    "aid = attribute.tabularDataRows[0].Id\n",
    "\n",
    "print(\"This row's ID is {0}\".format(aid))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create a table containing the values you want to update. You only need to specify the columns that contain updated data values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Updated previous value to 1000\n"
     ]
    }
   ],
   "source": [
    "updatedValue = gdl.IntegerDataType(value=newInt.value * 1000)\n",
    "print(\"Updated previous value to {0}\".format(updatedValue.value))\n",
    "\n",
    "tableUpdates = gdl.TabularDataType()\n",
    "tableUpdates.AddColumn(\"Order\")\n",
    "\n",
    "changetype = gdl.TabularDataType.ChangeType.Update\n",
    "\n",
    "rowChange = tableUpdates.CreateUpdateRow(changetype, aid)\n",
    "rowChange.cells[0].data = updatedValue"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Import the updated data into Granta MI using SetRecordAttributes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Use MI Viewer to view Import-test@23/03/2021 16:54:30 and check that the Order in 'Characterization of this material' is 1000\n"
     ]
    }
   ],
   "source": [
    "attribute = gdl.ImportAttributeValue(attributeReference=a)\n",
    "attribute.tabularDataValue = tableUpdates\n",
    "\n",
    "updateRecord = gdl.ImportRecord(importRecordMode=\"Update\", \n",
    "                                existingRecord=recordsImported[0].recordReference,\n",
    "                                importAttributeValues=[attribute])\n",
    "\n",
    "setRecordAttributesRequest = gdl.SetRecordAttributesRequest(importRecords=[updateRecord])\n",
    "response = session.dataImportService.SetRecordAttributes(setRecordAttributesRequest)\n",
    "\n",
    "print(\"Use MI Viewer to view {0} and check that the Order in 'Characterization of this material' is {1}\".format(recName, updatedValue.value))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
