{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Intermine-Python: Tutorial 12: More about Queries"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Welcome to your twelfth intermine-python tutorial.  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This tutorial will cover some more functionalities of a query. Queries are the basis of all research in InterMine and being able to manage them more effectively is always useful. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from intermine.webservice import Service"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "service = Service(\"https://www.flymine.org/query/service\")\n",
    "query=service.new_query()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<intermine.query.Query at 0x7fb6660a7e10>"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query.add_view(\"Gene.organism.name\",\"Gene.symbol\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Suppose, the query is not as simple as a strict cumulative filter and the user wants combinations of constraints. For example, the user wants all genes such that the gene symbol is either ‘eve’ or ‘zen’. This can be incorporated in the following way using set_logic: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<intermine.query.Query at 0x7fb6660a7e10>"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gene_is_eve = query.add_constraint(\"Gene.symbol\", \"=\", \"eve\")\n",
    "gene_is_zen = query.add_constraint(\"Gene.symbol\", \"=\", \"zen\")\n",
    "query.set_logic(gene_is_eve | gene_is_zen)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Gene: organism.name='Drosophila melanogaster' symbol='eve'\n",
      "Gene: organism.name='Drosophila melanogaster' symbol='zen'\n"
     ]
    }
   ],
   "source": [
    "for row in query.rows():\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The query results can be converted into a dictionary in the following way: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'Gene.organism.name': 'Drosophila melanogaster', 'Gene.symbol': 'eve'}\n",
      "{'Gene.organism.name': 'Drosophila melanogaster', 'Gene.symbol': 'zen'}\n"
     ]
    }
   ],
   "source": [
    "for row in query.rows():\n",
    "    print(row.to_d())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Similarly, row.to_l() can be used for conversion of the results into a list."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "count() can be used to print the total number of rows in a query: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "to_xml() can be used to return a readable XML serialisation of the query:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'<query constraintLogic=\"A or B\" longDescription=\"\" model=\"genomic\" name=\"\" sortOrder=\"Gene.organism.name asc\" view=\"Gene.organism.name Gene.symbol\"><constraint code=\"A\" op=\"=\" path=\"Gene.symbol\" value=\"eve\"/><constraint code=\"B\" op=\"=\" path=\"Gene.symbol\" value=\"zen\"/></query>'"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query.to_xml()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "clear_view() can be used to clear the output column list:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "query.clear_view()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Gene: briefDescription=None cytoLocation='46C10-46C10' description=None id=1007357 length=1477 name='even skipped' primaryIdentifier='FBgn0000606' score=None scoreType=None secondaryIdentifier='CG2328' symbol='eve'\n",
      "Gene: briefDescription=None cytoLocation='84A5-84A5' description=None id=1007877 length=1331 name='zerknullt' primaryIdentifier='FBgn0004053' score=None scoreType=None secondaryIdentifier='CG1046' symbol='zen'\n"
     ]
    }
   ],
   "source": [
    "for row in query.rows():\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In these ways, queries can be utilized to a greater extent and produce more fruitful results. "
   ]
  }
 ],
 "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.6.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}