BuildAndPopulateDB.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    from sdRDM import DataModel\n",
    "    from sdRDM.database import build_sql_database\n",
    "    \n",
    "except ModuleNotFoundError:\n",
    "    # Install package if not given\n",
    "    import subprocess\n",
    "    import sys\n",
    "    \n",
    "    subprocess.call([sys.executable, \"-m\", \"pip\", \"install\", \"git+https://github.com/JR-1991/software-driven-rdm.git\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Fetching the data model\n",
    "\n",
    "The PyEED data model can be inspected [here](https://github.com/PyEED/pyeed-data-model) which incorporates a [markdown file](https://github.com/PyEED/pyeed-data-model/blob/main/specifications/model.md) that defines the model. By using sdRDM's ```DataModel``` class and ```from_git``` method we can generate the corresponding Python code in-memory and use the model right away. See the printed tree to verify that the code is present."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ProteinSequence\n",
      "├── id\n",
      "├── name\n",
      "├── amino_acid_sequence\n",
      "├── nr_id\n",
      "├── uniprot_id\n",
      "├── pdb_id\n",
      "├── organism\n",
      "│   └── Organism\n",
      "│       ├── id\n",
      "│       └── ncbi_taxonomy_id\n",
      "├── domain\n",
      "│   └── Domain\n",
      "│       ├── id\n",
      "│       ├── name\n",
      "│       ├── start_position\n",
      "│       └── end_position\n",
      "├── reference_sequence\n",
      "├── equivalence\n",
      "│   └── Equivalence\n",
      "│       ├── id\n",
      "│       ├── reference_position\n",
      "│       └── sequence_position\n",
      "└── annotation\n",
      "    └── Annotation\n",
      "        ├── id\n",
      "        ├── start_position\n",
      "        ├── function\n",
      "        └── end_position\n"
     ]
    }
   ],
   "source": [
    "# pyeed=DataModel.from_markdown('./specifications/model.md')\n",
    "pyeed = DataModel.from_git(\"https://github.com/gfeuerriegel/pyeed-data-model.git\")\n",
    "\n",
    "pyeed.ProteinSequence.visualize_tree()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Building the SQL database\n",
    "\n",
    "Next, we are going to use the ```build_sql_database``` function to set up an SQLite database file. This one will be used later on to populate data from our model. The database will construct a table for each object/attribute and thus facilitate an easy transfer from an application to the database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "build_sql_database(pyeed.ProteinSequence, pyeed.DNASequence, loc=\"./test.db\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Importing PAZy database\n",
    "\n",
    "Next we are importing the PAZy database from an excel file with all the important information\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "import subprocess\n",
    "subprocess.call([sys.executable, \"-m\", \"pip\", \"install\", \"openpyxl\"])\n",
    "subprocess.call([sys.executable, \"-m\", \"conda\", \"install\", \"pandas\"])\n",
    "import pandas as pd\n",
    "PAZy = pd.read_excel('./PAZy_DB.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Creating a dataset and populating the database\n",
    "\n",
    "In order to demonstrate how to populate the database using our model, we are going to construct a small dataset using the data model we just loaded. \n",
    "\n",
    "Finally, we are going to add the data to our previously created database by using the datasets ```to_sql``` method where we also submit the location of our database file. This will create an Object Relation Model (ORM) that represents the Database structure and map the values present in our dataset to the corresponding tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "ename": "AttributeError",
     "evalue": "'ProteinSequence' object has no attribute 'add_to_organism'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mAttributeError\u001b[0m                            Traceback (most recent call last)",
      "Cell \u001b[1;32mIn [9], line 10\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[39mfor\u001b[39;00m index, row \u001b[39min\u001b[39;00m PAZy\u001b[39m.\u001b[39miterrows():\n\u001b[0;32m      2\u001b[0m     dataset \u001b[39m=\u001b[39m pyeed\u001b[39m.\u001b[39mProteinSequence(\n\u001b[0;32m      3\u001b[0m         name\u001b[39m=\u001b[39mrow[\u001b[39m\"\u001b[39m\u001b[39mEnzyme\u001b[39m\u001b[39m\"\u001b[39m],\n\u001b[0;32m      4\u001b[0m         pdb_id\u001b[39m=\u001b[39m[row[\u001b[39m\"\u001b[39m\u001b[39mPDB_Accession\u001b[39m\u001b[39m\"\u001b[39m]],\n\u001b[1;32m   (...)\u001b[0m\n\u001b[0;32m      8\u001b[0m     \u001b[39m#   organism=row[\"Microbial host\"]    \u001b[39;00m\n\u001b[0;32m      9\u001b[0m     )\n\u001b[1;32m---> 10\u001b[0m     dataset\u001b[39m.\u001b[39madd_to_organism(name\u001b[39m=\u001b[39mrow[\u001b[39m\"\u001b[39m\u001b[39mMicrobial host\u001b[39m\u001b[39m\"\u001b[39m], ncbi_taxonomy_id\u001b[39m=\u001b[39m row[\u001b[39m\"\u001b[39m\u001b[39mNCBI_Accession\u001b[39m\u001b[39m\"\u001b[39m])\n\u001b[0;32m     11\u001b[0m     dataset\u001b[39m.\u001b[39mto_sql(loc\u001b[39m=\u001b[39m\u001b[39m\"\u001b[39m\u001b[39m./test.db\u001b[39m\u001b[39m\"\u001b[39m)\n",
      "\u001b[1;31mAttributeError\u001b[0m: 'ProteinSequence' object has no attribute 'add_to_organism'"
     ]
    }
   ],
   "source": [
    "for index, row in PAZy.iterrows():\n",
    "    dataset = pyeed.ProteinSequence(\n",
    "        name=row[\"Enzyme\"],\n",
    "        pdb_id=[row[\"PDB_Accession\"]],\n",
    "        amino_acid_sequence=row[\"Sequence\"],\n",
    "        substrate=[row[\"Substrate\"]],\n",
    "    #   ncbi_id=row[\"NCBI_Accession\"],\n",
    "    #   organism=row[\"Microbial host\"]    \n",
    "    )\n",
    "    dataset.add_to_organism(name=row[\"Microbial host\"], ncbi_taxonomy_id= row[\"NCBI_Accession\"])\n",
    "    dataset.to_sql(loc=\"./test.db\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.10.6 64-bit",
   "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.10.8"
  },
  "vscode": {
   "interpreter": {
    "hash": "26de051ba29f2982a8de78e945f0abaf191376122a1563185a90213a26c5da77"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}