{
 "cells": [
  {
   "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 enable to mapping agtype to python class(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
}