{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "2e236ac7-6f78-4a59-bed7-45f593d060c2",
   "metadata": {},
   "source": [
    "# Basic Samples : Agtype mapper for psycopg driver\n",
    "\n",
    "You can make transactions and queries for PostgreSQL with Psycopg.\n",
    "\n",
    "This module enables the mapping of agtype to Python classes (Path, Vertex, Edge).\n",
    "\n",
    "## Connect to PostgreSQL and agType setting"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "98a5863c-1e79-438e-81d4-d1f5354a1bdb",
   "metadata": {},
   "outputs": [],
   "source": [
    "import psycopg \n",
    "import age\n",
    "\n",
    "GRAPH_NAME = \"test_graph\"\n",
    "ag = age.connect(host=\"172.17.0.2\", port=\"5432\", dbname=\"postgre\", user=\"postgres\", password=\"agens\", graph=GRAPH_NAME)\n",
    "conn = ag.connection\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "ebcf65a5-de7c-4224-aacc-2695c9e5f8d5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CREATED:: {label:Person, id:844424930131971, properties:{name: Tom, title: Manager}}::VERTEX\n",
      "------- [Select Vertices] --------\n",
      "844424930131969 Person Joe Developer\n",
      "--> {label:Person, id:844424930131969, properties:{name: Joe, title: Developer}}::VERTEX\n",
      "844424930131970 Person Smith Developer\n",
      "--> {label:Person, id:844424930131970, properties:{name: Smith, title: Developer}}::VERTEX\n",
      "844424930131971 Person Tom Manager\n",
      "--> {label:Person, id:844424930131971, properties:{name: Tom, title: Manager}}::VERTEX\n",
      "<class 'psycopg.ClientCursor'>\n",
      "------- [Select Paths] --------\n",
      "1 Joe 2 workWith 5 1 Smith\n",
      "--> [{label:Person, id:844424930131969, properties:{name: Joe, title: Developer}}::VERTEX,{label:workWith, id:1125899906842625, properties:{weight: 5}, start_id:844424930131969, end_id:844424930131970}::EDGE,{label:Person, id:844424930131970, properties:{name: Smith, title: Developer}}::VERTEX]::PATH\n",
      "1 Smith 2 workWith 3 1 Tom\n",
      "--> [{label:Person, id:844424930131970, properties:{name: Smith, title: Developer}}::VERTEX,{label:workWith, id:1125899906842626, properties:{weight: 3}, start_id:844424930131970, end_id:844424930131971}::EDGE,{label:Person, id:844424930131971, properties:{name: Tom, title: Manager}}::VERTEX]::PATH\n"
     ]
    }
   ],
   "source": [
    "with conn.cursor() as cursor:\n",
    "    try :\n",
    "        cursor.execute(\"\"\"SELECT * from cypher(%s, $$ CREATE (n:Person {name: 'Joe', title: 'Developer'}) $$) as (v agtype); \"\"\", (GRAPH_NAME,) )\n",
    "        cursor.execute(\"\"\"SELECT * from cypher(%s, $$ CREATE (n:Person {name: 'Smith', title: 'Developer'}) $$) as (v agtype); \"\"\", (GRAPH_NAME,))\n",
    "        cursor.execute(\"\"\"SELECT * from cypher(%s, $$ \n",
    "            CREATE (n:Person {name: 'Tom', title: 'Manager'}) \n",
    "            RETURN n\n",
    "            $$) as (v agtype); \"\"\", (GRAPH_NAME,))\n",
    "        for row in cursor:\n",
    "            print(\"CREATED::\", row[0])\n",
    "        \n",
    "        \n",
    "        cursor.execute(\"\"\"SELECT * from cypher(%s, $$ \n",
    "            MATCH (a:Person {name: 'Joe'}), (b:Person {name: 'Smith'}) CREATE (a)-[r:workWith {weight: 5}]->(b)\n",
    "            $$) as (v agtype); \"\"\", (GRAPH_NAME,))\n",
    "        \n",
    "        cursor.execute(\"\"\"SELECT * from cypher(%s, $$ \n",
    "            MATCH (a:Person {name: 'Smith'}), (b:Person {name: 'Tom'}) CREATE (a)-[r:workWith {weight: 3}]->(b)\n",
    "            $$) as (v agtype); \"\"\", (GRAPH_NAME,))\n",
    "        \n",
    "        # When data inserted or updated, You must commit.\n",
    "        conn.commit()\n",
    "    except Exception as ex:\n",
    "        print(type(ex), ex)\n",
    "        # if exception occurs, you must rollback all transaction. \n",
    "        conn.rollback()\n",
    "\n",
    "with conn.cursor() as cursor:\n",
    "    try:\n",
    "        print(\"------- [Select Vertices] --------\")\n",
    "        cursor.execute(\"\"\"SELECT * from cypher(%s, $$ MATCH (n) RETURN n $$) as (v agtype); \"\"\", (GRAPH_NAME,))\n",
    "        for row in cursor:\n",
    "            vertex = row[0]\n",
    "            print(vertex.id, vertex.label, vertex[\"name\"], vertex[\"title\"])\n",
    "            print(\"-->\", vertex)\n",
    "            \n",
    "        print(type(cursor))\n",
    "        print(\"------- [Select Paths] --------\")\n",
    "        cursor.execute(\"\"\"SELECT * from cypher(%s, $$ MATCH p=()-[]->() RETURN p LIMIT 10 $$) as (v agtype); \"\"\", (GRAPH_NAME,))\n",
    "        for row in cursor:\n",
    "            path = row[0]\n",
    "            v1 = path[0]\n",
    "            e1 = path[1]\n",
    "            v2 = path[2]\n",
    "            print(v1.gtype , v1[\"name\"], e1.gtype , e1.label, e1[\"weight\"], v2.gtype , v2[\"name\"])\n",
    "            print(\"-->\", path)\n",
    "    except Exception as ex:\n",
    "        print(type(ex), ex)\n",
    "        # if exception occurs, you must rollback even though just retrieving.\n",
    "        conn.rollback()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "f45f7c7d-2256-4aea-92f6-e0ad71017feb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Joe workWith Smith\n",
      "--> ('Joe', 'workWith', 'Smith')\n",
      "Smith workWith Tom\n",
      "--> ('Smith', 'workWith', 'Tom')\n"
     ]
    }
   ],
   "source": [
    "with conn.cursor() as cursor:\n",
    "    try:\n",
    "        cursor.execute(\"\"\"SELECT * from cypher(%s, $$ \n",
    "            MATCH p=(a)-[b]->(c) RETURN a.name, label(b), c.name \n",
    "            $$) as (a agtype, b agtype, c agtype); \"\"\", (GRAPH_NAME,))\n",
    "        for row in cursor:\n",
    "            print(row[0], row[1], row[2])\n",
    "            print(\"-->\", row)\n",
    "    except Exception as ex:\n",
    "        print(ex)\n",
    "        conn.rollback()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "c40b9076-d45e-43e6-85ae-296ba68a3031",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Joe {'weight': 5} Smith\n",
      "Smith {'weight': 3} Tom\n",
      "Jack {'weight': 2} John\n"
     ]
    }
   ],
   "source": [
    "with conn.cursor() as cursor:\n",
    "    try :\n",
    "        cursor.execute(\"\"\"SELECT * from cypher(%s, $$ \n",
    "            CREATE (n:Person {name: 'Jack', title: 'Developer', score:-6.45161290322581e+46}) \n",
    "            $$) as (v agtype); \"\"\", (GRAPH_NAME,) )\n",
    "        cursor.execute(\"\"\"SELECT * from cypher(%s, $$ \n",
    "            CREATE (n:Person {name: 'John', title: 'Developer'}) \n",
    "            $$) as (v agtype); \"\"\", (GRAPH_NAME,))\n",
    "\n",
    "        cursor.execute(\"\"\"SELECT * from cypher(%s, $$ \n",
    "            MATCH (a:Person {name: 'Jack'}), (b:Person {name: 'John'}) \n",
    "            CREATE (a)-[r:workWith {weight: 2}]->(b)\n",
    "            $$) as (v agtype); \"\"\", (GRAPH_NAME,))\n",
    "        \n",
    "        # When data inserted or updated, You must commit \n",
    "        conn.commit()\n",
    "    except Exception as ex:\n",
    "        print(ex)\n",
    "        conn.rollback()\n",
    "\n",
    "with conn.cursor() as cursor:\n",
    "    try :\n",
    "        cursor.execute(\"\"\"SELECT * from cypher(%s, $$ \n",
    "            MATCH p=(a )-[b]->(c) RETURN a , b, c \n",
    "            $$) as (ta agtype, tb agtype, tc agtype); \"\"\", (GRAPH_NAME,))\n",
    "        \n",
    "        for row in cursor:\n",
    "            print(row[0][\"name\"], row[1].properties, row[2][\"name\"])\n",
    "            \n",
    "    except Exception as ex:\n",
    "        print(ex)\n",
    "        conn.rollback()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "29ffe1b7-86df-446a-9df0-635be25a9eea",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Joe 1125899906842625 {'weight': 5} Smith\n",
      "Smith 1125899906842626 {'weight': 3} Tom\n",
      "Jack 1125899906842627 {'weight': 2} John\n"
     ]
    }
   ],
   "source": [
    "with conn.cursor() as cursor:\n",
    "    try:\n",
    "        cursor.execute(\"\"\"SELECT * from cypher(%s, $$ \n",
    "            MATCH p=(a)-[b]->(c) RETURN p  \n",
    "            $$) as (v agtype); \"\"\", (GRAPH_NAME,))\n",
    "        for row in cursor:\n",
    "            path = row[0]\n",
    "            print(path[0][\"name\"], path[1].id, path[1].properties, path[2][\"name\"])\n",
    "    except Exception as ex:\n",
    "        print(ex)\n",
    "        conn.rollback()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "428e6ddf-3958-49ff-af73-809b9a1ce42b",
   "metadata": {},
   "outputs": [],
   "source": [
    "age.deleteGraph(conn, GRAPH_NAME)\n",
    "conn.close()"
   ]
  }
 ],
 "metadata": {
  "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.11.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}