{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "2e236ac7-6f78-4a59-bed7-45f593d060c2",
   "metadata": {},
   "source": [
    "# Basic Samples : Agtype mapper for Psycopg2 driver\n",
    "\n",
    "You can make transactions and queries for PostgreSQL with Psycopg2.\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": null,
   "id": "98a5863c-1e79-438e-81d4-d1f5354a1bdb",
   "metadata": {},
   "outputs": [],
   "source": [
    "import psycopg2 \n",
    "import age\n",
    "\n",
    "GRAPH_NAME = \"test_graph\"\n",
    "conn = psycopg2.connect(host=\"172.17.0.2\", port=\"5432\", dbname=\"postgres\", user=\"postgres\", password=\"agens\")\n",
    "\n",
    "age.setUpAge(conn, GRAPH_NAME)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ebcf65a5-de7c-4224-aacc-2695c9e5f8d5",
   "metadata": {},
   "outputs": [],
   "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": null,
   "id": "f45f7c7d-2256-4aea-92f6-e0ad71017feb",
   "metadata": {},
   "outputs": [],
   "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": null,
   "id": "c40b9076-d45e-43e6-85ae-296ba68a3031",
   "metadata": {},
   "outputs": [],
   "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": null,
   "id": "29ffe1b7-86df-446a-9df0-635be25a9eea",
   "metadata": {},
   "outputs": [],
   "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": null,
   "id": "428e6ddf-3958-49ff-af73-809b9a1ce42b",
   "metadata": {},
   "outputs": [],
   "source": [
    "age.deleteGraph(conn, GRAPH_NAME)\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a4819e39-9f37-4dd5-bdbd-337b6d289158",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "02041ef2-9761-4eb3-b270-ded23e1caa6d",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.9.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}