{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "722ec93e-b87c-43d0-9c54-61b30933d892",
   "metadata": {},
   "source": [
    "# AGE Samples \n",
    "\n",
    "## Prepare\n",
    "```\n",
    "import age\n",
    "```\n",
    "## Connect to PostgreSQL(with AGE extension)\n",
    "* Connect to PostgreSQL server \n",
    "* Load AGE and register agtype to db session (Psycopg2 driver)\n",
    "* Check graph exists and set graph. If not, age make that.\n",
    "\n",
    "```\n",
    "ag = age.connect(graph=\"(graph name}\", host=\"{host}\", port=\"{port}\", dbname=\"{dbname}\", user=\"{db username}\", password=\"{password}\")\n",
    "\n",
    "# or \n",
    "DSN = \"host={host} port={port} dbname={dbname} user={db username} password={password}\"\n",
    "ag = age.connect(graph=\"(graph name}\", dsn=DSN)\n",
    "\n",
    "# or Without Graph Name : you can make a new graph later.\n",
    "\n",
    "ag = age.connect(host=\"{host}\", port=\"{port}\", dbname=\"{dbname}\", user=\"{db username}\", password=\"{password}\")\n",
    "\n",
    "# And set graph - if you don't have one yet, setGraph make that.)\n",
    "ag = age.setGraph(\"{graph name}\")\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "34eaaafe-d9dc-442f-8248-0824c46c7b20",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import age\n",
    "from age.gen.AgtypeParser import *\n",
    "\n",
    "GRAPH_NAME = \"test_graph\"\n",
    "DSN = \"host=172.17.0.2 port=5432 dbname=postgres user=postgres password=agens\"\n",
    "\n",
    "ag = age.connect(graph=GRAPH_NAME, dsn=DSN)\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "96bbaf49-e774-4939-8fe9-f179ac9addc9",
   "metadata": {},
   "source": [
    "---\n",
    "# API\n",
    "\n",
    "### age.connect(graph:str=None, dsn:str=None, connection_factory=None, cursor_factory=None, **kwargs) -> Age\n",
    "> Connect PostgreSQL server \n",
    "  Parameters : dsn={dsn} or \n",
    "  host=\"{host}\", port=\"{port}\", dbname=\"{dbname}\", user=\"{db username}\", password=\"{password}\"\n",
    "\n",
    "### Age.commit() , Age.rollback()\n",
    "> If your statement change data, you must call 'Age.commit()' explicitly. Otherwise change will not make effect.\n",
    "> Or when execution error occurs, you must call 'Age.rollback()'\n",
    "\n",
    "### Age.close()\n",
    "> Closes connection to PostgreSQL.\n",
    "\n",
    "### Age.execCypher(cypherStmt:str, cols:list=None, params:tuple=None) -> psycopg2.extensions.cursor :\n",
    ">  Execute cypher statements to query or change data (CREATE, SET, REMOVE) with or without result.\n",
    ">  If your statement change data, you must call 'Age.commit()' explicitly. Otherwise change will not make effect.\n",
    "    \n",
    ">  If your execution returns no result or only one result, you don't have to set 'cols' argument.\n",
    ">  But it returns many columns, you have to pass columns names(and types) to 'cols' argument.\n",
    "\n",
    ">  cols : str list \\[ 'colName {type}', ... \\] : If column data type is not set, agtype is default.\n",
    "        \n",
    "### Age.cypher(cursor:psycopg2.extensions.cursor, cypherStmt:str, cols:list=None, params:tuple=None) -> psycopg2.extensions.cursor :\n",
    ">  If you want execute many statements (changing data statement maybe) with one transaction explicitly, you may use Age.cypher(...) function.\n",
    "\n",
    ">  For creating cursor and mamage transaction, you usually use 'with' clause.\n",
    "    \n",
    ">  If your execution returns no result or only one result, you don't have to set 'cols' argument.\n",
    ">  But it returns many columns, you have to pass columns names(and types) to 'cols' argument.\n",
    "\n",
    ">  cols : str list \\[ 'colName {type}', ... \\] : If column data type is not set, agtype is default.\n",
    "        "
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "0f15bc37-4b19-4204-af93-757b07e7e9f9",
   "metadata": {},
   "source": [
    "---\n",
    "## Create  & Change Vertices\n",
    "\n",
    "> If cypher statement changes data (create, set, remove), \n",
    "  you must use execCypher(cypherStmt, commit, *args). \n",
    "  \n",
    ">  If **'commit'** argument is **True**: the cypherStmt make effect automatically, but cursor is closed after execution. So you cannot access the result.  \n",
    "  If **False** : you can access the result, but you must commit session(ag.commit()) explicitly.\n",
    "  (Otherwise the execution cannot make any effect.)\n",
    "\n",
    "\n",
    "> execCypher(cypherStmt:str, commit:bool, *args) \n",
    "\n",
    "```\n",
    "cursor = ag.execCypher(\"CREATE(...)\", commit=False) # Cypher Create Statement\n",
    "...\n",
    "# check result in cursor\n",
    "...\n",
    "ag.commit() # commit explicitly\n",
    "```\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "99cbc91e-55ae-4d2e-b81e-a655f88ec807",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CREATED:  {label:Person, id:844424930131975, properties:{name: Jack, }}::VERTEX\n",
      "CREATED:  844424930131976\n",
      "SET:  \"Manager\"\n",
      "SET:  \"Manager\"\n",
      "REMOVE Prop:  844424930131970\n",
      "REMOVE Prop:  844424930131974\n"
     ]
    }
   ],
   "source": [
    "# Create Vertices\n",
    "ag.execCypher(\"CREATE (n:Person {name: 'Joe'})\")\n",
    "ag.execCypher(\"CREATE (n:Person {name: 'Smith'})\")\n",
    "    \n",
    "# Execution with one agtype result\n",
    "cursor = ag.execCypher(\"CREATE (n:Person {name: %s}) RETURN n\", params=('Jack',))\n",
    "for row in cursor:\n",
    "    print(\"CREATED: \", row[0]) \n",
    "    \n",
    "cursor = ag.execCypher(\"CREATE (n:Person {name: %s, title: 'Developer'}) RETURN id(n)\", params=('Andy',))\n",
    "for row in cursor:\n",
    "    print(\"CREATED: \", row[0])\n",
    "    \n",
    "\n",
    "# Execution with one result as SQL TYPE \n",
    "cursor = ag.execCypher(\"MATCH (n:Person {name: %s}) SET n.title=%s RETURN n.title\", cols=[\"a VARCHAR\"], params=('Smith','Manager',))\n",
    "for row in cursor:\n",
    "    print(\"SET: \", row[0])\n",
    "\n",
    "\n",
    "# Execution with one result as SQL TYPE \n",
    "cursor = ag.execCypher(\"MATCH (n:Person {name: %s}) REMOVE n.title RETURN id(n)\", cols=[\"a BIGINT\"], params=('Smith',))\n",
    "for row in cursor:\n",
    "    print(\"REMOVE Prop: \", row[0])\n",
    "\n",
    "# You must commit explicitly\n",
    "ag.commit()\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "cf0f16c8-07d0-49b9-ba4f-3f9044cac9e7",
   "metadata": {},
   "source": [
    "---\n",
    "## Query Vertices\n",
    "\n",
    "> execCypher(cypherStmt:str, cols:list=None, params:tuple=None) \n",
    "\n",
    "### Single result column\n",
    "\n",
    "```\n",
    "cursor = ag.execCypher(\"MATCH (n:Person {name: %s) RETURN n\", params('Andy',))\n",
    "for row in cursor:\n",
    "    vertex = row[0]\n",
    "    print(vertex.id, vertex[\"name\"], vertex) # row has id, label, properties \n",
    "```\n",
    "\n",
    "### Multi result columns\n",
    "\n",
    "```\n",
    "cursor = ag.execCypher(\"MATCH (n:Person) RETURN label(n), n.name\", cols=['label VARCHAR', 'name'])\n",
    "for row in cursor:\n",
    "    label = row[0]\n",
    "    name = row[1]\n",
    "    print(label, name) \n",
    "```\n",
    "\n",
    "\n",
    "### Vertex object has id, label attribute and __getitem__, __setitem__ for properties\n",
    "```\n",
    "vertex.id\n",
    "vertex.label\n",
    "vertex[\"property_name\"]\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "4cd66088-2c74-449e-88bc-76877779c86d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-- Query Vertices  --------------------\n",
      "844424930131969 Person Joe\n",
      "--> {label:Person, id:844424930131969, properties:{name: Joe, }}::VERTEX\n",
      "844424930131971 Person Jack\n",
      "--> {label:Person, id:844424930131971, properties:{name: Jack, }}::VERTEX\n",
      "844424930131972 Person Andy\n",
      "--> {label:Person, id:844424930131972, properties:{name: Andy, title: Developer, }}::VERTEX\n",
      "844424930131973 Person Joe\n",
      "--> {label:Person, id:844424930131973, properties:{name: Joe, }}::VERTEX\n",
      "844424930131975 Person Jack\n",
      "--> {label:Person, id:844424930131975, properties:{name: Jack, }}::VERTEX\n",
      "844424930131976 Person Andy\n",
      "--> {label:Person, id:844424930131976, properties:{name: Andy, title: Developer, }}::VERTEX\n",
      "844424930131970 Person Smith\n",
      "--> {label:Person, id:844424930131970, properties:{name: Smith, }}::VERTEX\n",
      "844424930131974 Person Smith\n",
      "--> {label:Person, id:844424930131974, properties:{name: Smith, }}::VERTEX\n",
      "-- Query Vertices with with multi columns. --------------------\n",
      "\"Person\" Joe\n",
      "\"Person\" Jack\n",
      "\"Person\" Andy\n",
      "\"Person\" Joe\n",
      "\"Person\" Jack\n",
      "\"Person\" Andy\n",
      "\"Person\" Smith\n",
      "\"Person\" Smith\n"
     ]
    }
   ],
   "source": [
    "\n",
    "# Query Vertices with parsed row cursor.\n",
    "print(\"-- Query Vertices  --------------------\")\n",
    "cursor = ag.execCypher(\"MATCH (n:Person) RETURN n\")\n",
    "for row in cursor:\n",
    "    vertex = row[0]\n",
    "    print(vertex.id, vertex.label, vertex[\"name\"])\n",
    "    print(\"-->\", vertex)\n",
    "\n",
    "# Query Vertices with with multi column\n",
    "print(\"-- Query Vertices with with multi columns. --------------------\")\n",
    "cursor = ag.execCypher(\"MATCH (n:Person) RETURN label(n), n.name\", cols=['label VARCHAR', 'name'])\n",
    "for row in cursor:\n",
    "    label = row[0]\n",
    "    name = row[1]\n",
    "    print(label, name) \n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "0a8606b5-8583-49f9-aa39-a1ac3e90d542",
   "metadata": {},
   "source": [
    "---\n",
    "## Create Relation \n",
    "\n",
    "> execCypher(cypherStmt:str, commit:bool, *args)\n",
    "\n",
    "\n",
    "```\n",
    "# Execute statement and handle results\n",
    "cursor = ag.execCypher(\"MATCH (a:Person), (b:Person) WHERE  a.name = %s AND b.name = %s CREATE p=((a)-[r:workWith]->(b)) RETURN p\", False, ('Andy', 'Smith',))\n",
    "...\n",
    "# You can access the results in cursor\n",
    "...\n",
    "ag.commit() # commit\n",
    "```\n",
    "\n",
    "```\n",
    "# Auto commit\n",
    "ag.execCypher(\"MATCH (a:Person), (b:Person) WHERE  a.name = 'Andy' AND b.name = 'Tom' CREATE (a)-[r:workWith]->(b)\", True)\n",
    "\n",
    "```\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "0f904526-59d7-4025-9878-15e458bc5b56",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[{label:Person, id:844424930131972, properties:{name: Andy, title: Developer, }}::VERTEX,{label:workWith, id:1125899906842637, properties:{}, start_id:844424930131972, end_id:844424930131970}::EDGE,{label:Person, id:844424930131970, properties:{name: Smith, }}::VERTEX]::PATH\n",
      "[{label:Person, id:844424930131972, properties:{name: Andy, title: Developer, }}::VERTEX,{label:workWith, id:1125899906842638, properties:{}, start_id:844424930131972, end_id:844424930131974}::EDGE,{label:Person, id:844424930131974, properties:{name: Smith, }}::VERTEX]::PATH\n",
      "[{label:Person, id:844424930131976, properties:{name: Andy, title: Developer, }}::VERTEX,{label:workWith, id:1125899906842639, properties:{}, start_id:844424930131976, end_id:844424930131970}::EDGE,{label:Person, id:844424930131970, properties:{name: Smith, }}::VERTEX]::PATH\n",
      "[{label:Person, id:844424930131976, properties:{name: Andy, title: Developer, }}::VERTEX,{label:workWith, id:1125899906842640, properties:{}, start_id:844424930131976, end_id:844424930131974}::EDGE,{label:Person, id:844424930131974, properties:{name: Smith, }}::VERTEX]::PATH\n",
      "(a) {label:Person, id:844424930131969, properties:{name: Joe, }}::VERTEX : (r) {label:workWith, id:1125899906842641, properties:{weight: 5, }, start_id:844424930131969, end_id:844424930131971}::EDGE : (b) {label:Person, id:844424930131971, properties:{name: Jack, }}::VERTEX\n",
      "(a) {label:Person, id:844424930131969, properties:{name: Joe, }}::VERTEX : (r) {label:workWith, id:1125899906842642, properties:{weight: 5, }, start_id:844424930131969, end_id:844424930131975}::EDGE : (b) {label:Person, id:844424930131975, properties:{name: Jack, }}::VERTEX\n",
      "(a) {label:Person, id:844424930131973, properties:{name: Joe, }}::VERTEX : (r) {label:workWith, id:1125899906842643, properties:{weight: 5, }, start_id:844424930131973, end_id:844424930131971}::EDGE : (b) {label:Person, id:844424930131971, properties:{name: Jack, }}::VERTEX\n",
      "(a) {label:Person, id:844424930131973, properties:{name: Joe, }}::VERTEX : (r) {label:workWith, id:1125899906842644, properties:{weight: 5, }, start_id:844424930131973, end_id:844424930131975}::EDGE : (b) {label:Person, id:844424930131975, properties:{name: Jack, }}::VERTEX\n"
     ]
    }
   ],
   "source": [
    "\n",
    "# Create Edges\n",
    "ag.execCypher(\"MATCH (a:Person), (b:Person) WHERE a.name = 'Joe' AND b.name = 'Smith' CREATE (a)-[r:workWith {weight: 3}]->(b)\")\n",
    "ag.execCypher(\"MATCH (a:Person), (b:Person) WHERE  a.name = 'Andy' AND b.name = 'Tom' CREATE (a)-[r:workWith {weight: 1}]->(b)\")\n",
    "ag.execCypher(\"MATCH (a:Person {name: 'Jack'}), (b:Person {name: 'Andy'}) CREATE (a)-[r:workWith {weight: 5}]->(b)\")\n",
    "\n",
    "ag.commit()\n",
    "\n",
    "# With Params and Return\n",
    "cursor = ag.execCypher(\"\"\"MATCH (a:Person), (b:Person) \n",
    "        WHERE  a.name = %s AND b.name = %s \n",
    "        CREATE p=((a)-[r:workWith]->(b)) \n",
    "        RETURN p\"\"\",  \n",
    "        params=('Andy', 'Smith',))\n",
    "\n",
    "for row in cursor:\n",
    "    print(row[0])\n",
    "\n",
    "ag.commit()\n",
    "\n",
    "# With many columns Return\n",
    "cursor = ag.execCypher(\"\"\"MATCH (a:Person {name: 'Joe'}), (b:Person {name: 'Jack'}) \n",
    "        CREATE (a)-[r:workWith {weight: 5}]->(b) \n",
    "        RETURN a, r, b \"\"\", cols=['a','r', 'b'])\n",
    "\n",
    "for row in cursor:\n",
    "    print(\"(a)\", row[0], \": (r)\", row[1], \": (b)\", row[2])\n",
    "    \n",
    "\n",
    "ag.commit()\n",
    "\n",
    "    "
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "2615465d-9cff-4e67-9935-21344df4574c",
   "metadata": {},
   "source": [
    "---\n",
    "## Query Relations\n",
    "\n",
    "> With single column\n",
    "```\n",
    "cursor = ag.execCypher(\"MATCH p=()-[:workWith]-() RETURN p\")\n",
    "for row in cursor:\n",
    "    path = row[0]\n",
    "    print(path)  \n",
    "```\n",
    "\n",
    "> With multi columns\n",
    "```\n",
    "cursor = ag.execCypher(\"MATCH p=(a)-[b]-(c) RETURN a,label(b),c\", cols=[\"a\",\"b VARCHAR\",\"c\"])\n",
    "for row in cursor:\n",
    "    start = row[0]\n",
    "    edge = row[1]\n",
    "    end = row[2]\n",
    "    print(start[\"name\"] , edge.label, end[\"name\"])  \n",
    "```\n",
    "\n",
    "\n",
    "### Edge object has id, label,start_id, end_id attribute and __getitem__, __setitem__ for properties\n",
    "```\n",
    "edge = path.rel\n",
    "edge.id\n",
    "edge.label\n",
    "edge.start_id\n",
    "edge.end_id\n",
    "edge[\"property_name\"]\n",
    "edge.properties\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "7673e270-4ea3-4878-961c-8fc97106e1bd",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "START: {label:Person, id:844424930131971, properties:{name: Jack, }}::VERTEX\n",
      "EDGE: {label:workWith, id:1125899906842628, properties:{weight: 5, }, start_id:844424930131969, end_id:844424930131971}::EDGE\n",
      "END: {label:Person, id:844424930131969, properties:{name: Joe, }}::VERTEX\n",
      "START: {label:Person, id:844424930131971, properties:{name: Jack, }}::VERTEX\n",
      "EDGE: {label:workWith, id:1125899906842641, properties:{weight: 5, }, start_id:844424930131969, end_id:844424930131971}::EDGE\n",
      "END: {label:Person, id:844424930131969, properties:{name: Joe, }}::VERTEX\n",
      "START: {label:Person, id:844424930131975, properties:{name: Jack, }}::VERTEX\n",
      "EDGE: {label:workWith, id:1125899906842642, properties:{weight: 5, }, start_id:844424930131969, end_id:844424930131975}::EDGE\n",
      "END: {label:Person, id:844424930131969, properties:{name: Joe, }}::VERTEX\n",
      "START: {label:Person, id:844424930131970, properties:{name: Smith, }}::VERTEX\n",
      "EDGE: {label:workWith, id:1125899906842625, properties:{weight: 3, }, start_id:844424930131969, end_id:844424930131970}::EDGE\n",
      "END: {label:Person, id:844424930131969, properties:{name: Joe, }}::VERTEX\n",
      "START: {label:Person, id:844424930131970, properties:{name: Smith, }}::VERTEX\n",
      "EDGE: {label:workWith, id:1125899906842629, properties:{weight: 3, }, start_id:844424930131969, end_id:844424930131970}::EDGE\n",
      "END: {label:Person, id:844424930131969, properties:{name: Joe, }}::VERTEX\n",
      "START: {label:Person, id:844424930131974, properties:{name: Smith, }}::VERTEX\n",
      "EDGE: {label:workWith, id:1125899906842630, properties:{weight: 3, }, start_id:844424930131969, end_id:844424930131974}::EDGE\n",
      "END: {label:Person, id:844424930131969, properties:{name: Joe, }}::VERTEX\n",
      "START: {label:Person, id:844424930131969, properties:{name: Joe, }}::VERTEX\n",
      "EDGE: {label:workWith, id:1125899906842628, properties:{weight: 5, }, start_id:844424930131969, end_id:844424930131971}::EDGE\n",
      "END: {label:Person, id:844424930131971, properties:{name: Jack, }}::VERTEX\n",
      "START: {label:Person, id:844424930131969, properties:{name: Joe, }}::VERTEX\n",
      "EDGE: {label:workWith, id:1125899906842641, properties:{weight: 5, }, start_id:844424930131969, end_id:844424930131971}::EDGE\n",
      "END: {label:Person, id:844424930131971, properties:{name: Jack, }}::VERTEX\n",
      "START: {label:Person, id:844424930131972, properties:{name: Andy, title: Developer, }}::VERTEX\n",
      "EDGE: {label:workWith, id:1125899906842626, properties:{weight: 5, }, start_id:844424930131971, end_id:844424930131972}::EDGE\n",
      "END: {label:Person, id:844424930131971, properties:{name: Jack, }}::VERTEX\n",
      "START: {label:Person, id:844424930131972, properties:{name: Andy, title: Developer, }}::VERTEX\n",
      "EDGE: {label:workWith, id:1125899906842633, properties:{weight: 5, }, start_id:844424930131971, end_id:844424930131972}::EDGE\n",
      "END: {label:Person, id:844424930131971, properties:{name: Jack, }}::VERTEX\n",
      "START: {label:Person, id:844424930131973, properties:{name: Joe, }}::VERTEX\n",
      "EDGE: {label:workWith, id:1125899906842643, properties:{weight: 5, }, start_id:844424930131973, end_id:844424930131971}::EDGE\n",
      "END: {label:Person, id:844424930131971, properties:{name: Jack, }}::VERTEX\n",
      "START: {label:Person, id:844424930131976, properties:{name: Andy, title: Developer, }}::VERTEX\n",
      "EDGE: {label:workWith, id:1125899906842634, properties:{weight: 5, }, start_id:844424930131971, end_id:844424930131976}::EDGE\n",
      "END: {label:Person, id:844424930131971, properties:{name: Jack, }}::VERTEX\n",
      "START: {label:Person, id:844424930131971, properties:{name: Jack, }}::VERTEX\n",
      "EDGE: {label:workWith, id:1125899906842626, properties:{weight: 5, }, start_id:844424930131971, end_id:844424930131972}::EDGE\n",
      "END: {label:Person, id:844424930131972, properties:{name: Andy, title: Developer, }}::VERTEX\n",
      "START: {label:Person, id:844424930131971, properties:{name: Jack, }}::VERTEX\n",
      "EDGE: {label:workWith, id:1125899906842633, properties:{weight: 5, }, start_id:844424930131971, end_id:844424930131972}::EDGE\n",
      "END: {label:Person, id:844424930131972, properties:{name: Andy, title: Developer, }}::VERTEX\n",
      "START: {label:Person, id:844424930131975, properties:{name: Jack, }}::VERTEX\n",
      "EDGE: {label:workWith, id:1125899906842635, properties:{weight: 5, }, start_id:844424930131975, end_id:844424930131972}::EDGE\n",
      "END: {label:Person, id:844424930131972, properties:{name: Andy, title: Developer, }}::VERTEX\n",
      "START: {label:Person, id:844424930131970, properties:{name: Smith, }}::VERTEX\n",
      "EDGE: {label:workWith, id:1125899906842627, properties:{}, start_id:844424930131972, end_id:844424930131970}::EDGE\n",
      "END: {label:Person, id:844424930131972, properties:{name: Andy, title: Developer, }}::VERTEX\n",
      "START: {label:Person, id:844424930131970, properties:{name: Smith, }}::VERTEX\n",
      "EDGE: {label:workWith, id:1125899906842637, properties:{}, start_id:844424930131972, end_id:844424930131970}::EDGE\n",
      "END: {label:Person, id:844424930131972, properties:{name: Andy, title: Developer, }}::VERTEX\n",
      "START: {label:Person, id:844424930131974, properties:{name: Smith, }}::VERTEX\n",
      "EDGE: {label:workWith, id:1125899906842638, properties:{}, start_id:844424930131972, end_id:844424930131974}::EDGE\n",
      "END: {label:Person, id:844424930131972, properties:{name: Andy, title: Developer, }}::VERTEX\n",
      "START: {label:Person, id:844424930131971, properties:{name: Jack, }}::VERTEX\n",
      "EDGE: {label:workWith, id:1125899906842643, properties:{weight: 5, }, start_id:844424930131973, end_id:844424930131971}::EDGE\n",
      "END: {label:Person, id:844424930131973, properties:{name: Joe, }}::VERTEX\n",
      "START: {label:Person, id:844424930131975, properties:{name: Jack, }}::VERTEX\n",
      "EDGE: {label:workWith, id:1125899906842644, properties:{weight: 5, }, start_id:844424930131973, end_id:844424930131975}::EDGE\n",
      "END: {label:Person, id:844424930131973, properties:{name: Joe, }}::VERTEX\n",
      "START: {label:Person, id:844424930131970, properties:{name: Smith, }}::VERTEX\n",
      "EDGE: {label:workWith, id:1125899906842631, properties:{weight: 3, }, start_id:844424930131973, end_id:844424930131970}::EDGE\n",
      "END: {label:Person, id:844424930131973, properties:{name: Joe, }}::VERTEX\n",
      "START: {label:Person, id:844424930131974, properties:{name: Smith, }}::VERTEX\n",
      "EDGE: {label:workWith, id:1125899906842632, properties:{weight: 3, }, start_id:844424930131973, end_id:844424930131974}::EDGE\n",
      "END: {label:Person, id:844424930131973, properties:{name: Joe, }}::VERTEX\n",
      "START: {label:Person, id:844424930131969, properties:{name: Joe, }}::VERTEX\n",
      "EDGE: {label:workWith, id:1125899906842642, properties:{weight: 5, }, start_id:844424930131969, end_id:844424930131975}::EDGE\n",
      "END: {label:Person, id:844424930131975, properties:{name: Jack, }}::VERTEX\n",
      "START: {label:Person, id:844424930131972, properties:{name: Andy, title: Developer, }}::VERTEX\n",
      "EDGE: {label:workWith, id:1125899906842635, properties:{weight: 5, }, start_id:844424930131975, end_id:844424930131972}::EDGE\n",
      "END: {label:Person, id:844424930131975, properties:{name: Jack, }}::VERTEX\n",
      "START: {label:Person, id:844424930131973, properties:{name: Joe, }}::VERTEX\n",
      "EDGE: {label:workWith, id:1125899906842644, properties:{weight: 5, }, start_id:844424930131973, end_id:844424930131975}::EDGE\n",
      "END: {label:Person, id:844424930131975, properties:{name: Jack, }}::VERTEX\n",
      "START: {label:Person, id:844424930131976, properties:{name: Andy, title: Developer, }}::VERTEX\n",
      "EDGE: {label:workWith, id:1125899906842636, properties:{weight: 5, }, start_id:844424930131975, end_id:844424930131976}::EDGE\n",
      "END: {label:Person, id:844424930131975, properties:{name: Jack, }}::VERTEX\n",
      "START: {label:Person, id:844424930131971, properties:{name: Jack, }}::VERTEX\n",
      "EDGE: {label:workWith, id:1125899906842634, properties:{weight: 5, }, start_id:844424930131971, end_id:844424930131976}::EDGE\n",
      "END: {label:Person, id:844424930131976, properties:{name: Andy, title: Developer, }}::VERTEX\n",
      "START: {label:Person, id:844424930131975, properties:{name: Jack, }}::VERTEX\n",
      "EDGE: {label:workWith, id:1125899906842636, properties:{weight: 5, }, start_id:844424930131975, end_id:844424930131976}::EDGE\n",
      "END: {label:Person, id:844424930131976, properties:{name: Andy, title: Developer, }}::VERTEX\n",
      "START: {label:Person, id:844424930131970, properties:{name: Smith, }}::VERTEX\n",
      "EDGE: {label:workWith, id:1125899906842639, properties:{}, start_id:844424930131976, end_id:844424930131970}::EDGE\n",
      "END: {label:Person, id:844424930131976, properties:{name: Andy, title: Developer, }}::VERTEX\n",
      "START: {label:Person, id:844424930131974, properties:{name: Smith, }}::VERTEX\n",
      "EDGE: {label:workWith, id:1125899906842640, properties:{}, start_id:844424930131976, end_id:844424930131974}::EDGE\n",
      "END: {label:Person, id:844424930131976, properties:{name: Andy, title: Developer, }}::VERTEX\n",
      "START: {label:Person, id:844424930131969, properties:{name: Joe, }}::VERTEX\n",
      "EDGE: {label:workWith, id:1125899906842625, properties:{weight: 3, }, start_id:844424930131969, end_id:844424930131970}::EDGE\n",
      "END: {label:Person, id:844424930131970, properties:{name: Smith, }}::VERTEX\n",
      "START: {label:Person, id:844424930131969, properties:{name: Joe, }}::VERTEX\n",
      "EDGE: {label:workWith, id:1125899906842629, properties:{weight: 3, }, start_id:844424930131969, end_id:844424930131970}::EDGE\n",
      "END: {label:Person, id:844424930131970, properties:{name: Smith, }}::VERTEX\n",
      "START: {label:Person, id:844424930131972, properties:{name: Andy, title: Developer, }}::VERTEX\n",
      "EDGE: {label:workWith, id:1125899906842627, properties:{}, start_id:844424930131972, end_id:844424930131970}::EDGE\n",
      "END: {label:Person, id:844424930131970, properties:{name: Smith, }}::VERTEX\n",
      "START: {label:Person, id:844424930131972, properties:{name: Andy, title: Developer, }}::VERTEX\n",
      "EDGE: {label:workWith, id:1125899906842637, properties:{}, start_id:844424930131972, end_id:844424930131970}::EDGE\n",
      "END: {label:Person, id:844424930131970, properties:{name: Smith, }}::VERTEX\n",
      "START: {label:Person, id:844424930131973, properties:{name: Joe, }}::VERTEX\n",
      "EDGE: {label:workWith, id:1125899906842631, properties:{weight: 3, }, start_id:844424930131973, end_id:844424930131970}::EDGE\n",
      "END: {label:Person, id:844424930131970, properties:{name: Smith, }}::VERTEX\n",
      "START: {label:Person, id:844424930131976, properties:{name: Andy, title: Developer, }}::VERTEX\n",
      "EDGE: {label:workWith, id:1125899906842639, properties:{}, start_id:844424930131976, end_id:844424930131970}::EDGE\n",
      "END: {label:Person, id:844424930131970, properties:{name: Smith, }}::VERTEX\n",
      "START: {label:Person, id:844424930131969, properties:{name: Joe, }}::VERTEX\n",
      "EDGE: {label:workWith, id:1125899906842630, properties:{weight: 3, }, start_id:844424930131969, end_id:844424930131974}::EDGE\n",
      "END: {label:Person, id:844424930131974, properties:{name: Smith, }}::VERTEX\n",
      "START: {label:Person, id:844424930131972, properties:{name: Andy, title: Developer, }}::VERTEX\n",
      "EDGE: {label:workWith, id:1125899906842638, properties:{}, start_id:844424930131972, end_id:844424930131974}::EDGE\n",
      "END: {label:Person, id:844424930131974, properties:{name: Smith, }}::VERTEX\n",
      "START: {label:Person, id:844424930131973, properties:{name: Joe, }}::VERTEX\n",
      "EDGE: {label:workWith, id:1125899906842632, properties:{weight: 3, }, start_id:844424930131973, end_id:844424930131974}::EDGE\n",
      "END: {label:Person, id:844424930131974, properties:{name: Smith, }}::VERTEX\n",
      "START: {label:Person, id:844424930131976, properties:{name: Andy, title: Developer, }}::VERTEX\n",
      "EDGE: {label:workWith, id:1125899906842640, properties:{}, start_id:844424930131976, end_id:844424930131974}::EDGE\n",
      "END: {label:Person, id:844424930131974, properties:{name: Smith, }}::VERTEX\n",
      "-- [Query path with multi columns --------\n",
      "Jack workWith 5 Joe\n",
      "Jack workWith 5 Joe\n",
      "Jack workWith 5 Joe\n",
      "Smith workWith 3 Joe\n",
      "Smith workWith 3 Joe\n",
      "Smith workWith 3 Joe\n",
      "Joe workWith 5 Jack\n",
      "Joe workWith 5 Jack\n",
      "Andy workWith 5 Jack\n",
      "Andy workWith 5 Jack\n",
      "Joe workWith 5 Jack\n",
      "Andy workWith 5 Jack\n",
      "Jack workWith 5 Andy\n",
      "Jack workWith 5 Andy\n",
      "Jack workWith 5 Andy\n",
      "Jack workWith 5 Joe\n",
      "Jack workWith 5 Joe\n",
      "Smith workWith 3 Joe\n",
      "Smith workWith 3 Joe\n",
      "Joe workWith 5 Jack\n",
      "Andy workWith 5 Jack\n",
      "Joe workWith 5 Jack\n",
      "Andy workWith 5 Jack\n",
      "Jack workWith 5 Andy\n",
      "Jack workWith 5 Andy\n",
      "Joe workWith 3 Smith\n",
      "Joe workWith 3 Smith\n",
      "Joe workWith 3 Smith\n",
      "Joe workWith 3 Smith\n",
      "Joe workWith 3 Smith\n"
     ]
    }
   ],
   "source": [
    "cursor = ag.execCypher(\"MATCH p=()-[:workWith]-() RETURN p\")\n",
    "for row in cursor:\n",
    "    path = row[0]\n",
    "    print(\"START:\", path[0])\n",
    "    print(\"EDGE:\", path[1])\n",
    "    print(\"END:\", path[2])  \n",
    "\n",
    "print(\"-- [Query path with multi columns --------\")\n",
    "cursor = ag.execCypher(\"MATCH p=(a)-[b]-(c) WHERE b.weight>2 RETURN a,label(b), b.weight, c\", cols=[\"a\",\"bl\",\"bw\", \"c\"], params=(2,))\n",
    "for row in cursor:\n",
    "    start = row[0]\n",
    "    edgel = row[1]\n",
    "    edgew = row[2]\n",
    "    end = row[3]\n",
    "    print(start[\"name\"] , edgel, edgew, end[\"name\"]) \n",
    "\n",
    "\n",
    "    "
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "22690e1d-f258-4f2f-87b2-4f5b0a7f4574",
   "metadata": {},
   "source": [
    "---\n",
    "## Query COLLECT\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "5771219a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Joe workWith ['Smith', 'Smith', 'Jack', 'Jack', 'Smith', 'Jack']\n",
      "Smith workWith ['Joe', 'Joe', 'Andy', 'Andy', 'Joe', 'Andy']\n",
      "Jack workWith ['Joe', 'Joe', 'Andy', 'Andy', 'Joe', 'Andy']\n",
      "Andy workWith ['Smith', 'Smith', 'Jack', 'Jack', 'Smith', 'Jack']\n",
      "Joe workWith ['Jack', 'Smith', 'Jack', 'Smith']\n",
      "Smith workWith ['Andy', 'Joe', 'Andy', 'Joe']\n",
      "Jack workWith ['Joe', 'Joe', 'Andy', 'Andy']\n",
      "Andy workWith ['Jack', 'Smith', 'Jack', 'Smith']\n",
      "Joe workWith ['Smith', 'Smith', 'Jack', 'Jack', 'Smith', 'Jack']\n",
      "Smith workWith ['Joe', 'Joe', 'Andy', 'Andy', 'Joe', 'Andy']\n",
      "Jack workWith ['Joe', 'Joe', 'Andy', 'Andy', 'Joe', 'Andy']\n",
      "Andy workWith ['Smith', 'Smith', 'Jack', 'Jack', 'Smith', 'Jack']\n",
      "Joe workWith ['Jack', 'Smith', 'Jack', 'Smith']\n",
      "Smith workWith ['Andy', 'Joe', 'Andy', 'Joe']\n",
      "Jack workWith ['Joe', 'Joe', 'Andy', 'Andy']\n",
      "Andy workWith ['Jack', 'Smith', 'Jack', 'Smith']\n"
     ]
    }
   ],
   "source": [
    "\n",
    "with ag.connection.cursor() as cursor:\n",
    "    ag.cypher(cursor, \"MATCH (a)-[:workWith]-(c) WITH a as V, COLLECT(c) as CV RETURN V.name, CV\", cols=[\"V\",\"CV\"])\n",
    "    for row in cursor:\n",
    "        nm = row[0]\n",
    "        collected = row[1]\n",
    "        print(nm, \"workWith\", [i[\"name\"] for i in collected])\n",
    "\n",
    "for row in ag.execCypher(\"MATCH (a)-[:workWith]-(c) WITH a as V, COLLECT(c) as CV RETURN V.name, CV\", cols=[\"V1\",\"CV\"]):\n",
    "    nm = row[0]\n",
    "    collected = row[1]\n",
    "    print(nm, \"workWith\", [i[\"name\"] for i in collected])\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "ac4d3e0e-0101-40cd-bc9a-59386a1e4485",
   "metadata": {},
   "source": [
    "--- \n",
    "## Many executions in one transaction & Multiple Edges"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "c8b36687-c842-4663-a7aa-084ae618301e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " Country USA\n",
      " > distance 4760 miles\n",
      " > > Country France\n",
      " > > > distance 9228 km\n",
      " > > > > Country Korea\n"
     ]
    }
   ],
   "source": [
    "with ag.connection.cursor() as cursor:\n",
    "    try :\n",
    "        ag.cypher(cursor, \"CREATE (n:Country {name: %s}) \", params=('USA',))\n",
    "        ag.cypher(cursor, \"CREATE (n:Country {name: %s}) \", params=('France',))\n",
    "        ag.cypher(cursor, \"CREATE (n:Country {name: %s}) \", params=('Korea',))\n",
    "        ag.cypher(cursor, \"CREATE (n:Country {name: %s}) \", params=('Russia',))\n",
    "\n",
    "        # You must commit explicitly after all executions.\n",
    "        ag.connection.commit()\n",
    "    except Exception as ex:\n",
    "        ag.rollback()\n",
    "        raise ex\n",
    "\n",
    "with ag.connection.cursor() as cursor:\n",
    "    try :# Create Edges\n",
    "        ag.cypher(cursor,\"MATCH (a:Country), (b:Country) WHERE a.name = 'USA' AND b.name = 'France' CREATE (a)-[r:distance {unit:'miles', value: 4760}]->(b)\")\n",
    "        ag.cypher(cursor,\"MATCH (a:Country), (b:Country) WHERE  a.name = 'France' AND b.name = 'Korea' CREATE (a)-[r:distance {unit: 'km', value: 9228}]->(b)\")\n",
    "        ag.cypher(cursor,\"MATCH (a:Country {name: 'Korea'}), (b:Country {name: 'Russia'}) CREATE (a)-[r:distance {unit:'km', value: 3078}]->(b)\")\n",
    "\n",
    "        # You must commit explicitly\n",
    "        ag.connection.commit()\n",
    "    except Exception as ex:\n",
    "        ag.rollback()\n",
    "        raise ex\n",
    "\n",
    "\n",
    "cursor = ag.execCypher(\"\"\"MATCH p=(:Country {name:\"USA\"})-[:distance]-(:Country)-[:distance]-(:Country) \n",
    "        RETURN p\"\"\")\n",
    "\n",
    "for row in cursor:\n",
    "    path = row[0]\n",
    "    indent = \"\"\n",
    "    for e in path:\n",
    "        if e.gtype == age.TP_VERTEX:\n",
    "            print(indent, e.label, e[\"name\"])\n",
    "        elif e.gtype == age.TP_EDGE:\n",
    "            print(indent, e.label, e[\"value\"], e[\"unit\"])\n",
    "        else:\n",
    "            print(indent, \"Unknown element.\", e)\n",
    "            \n",
    "        indent += \" >\"\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "f502e0db-a603-4eb9-90e8-dac2c9edd1d4",
   "metadata": {},
   "source": [
    "---\n",
    "## Query Scalar or properties value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "7f93e698-888a-4dde-b327-e591659e051f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-- Query scalar value --------------------\n",
      "844424930131969\n",
      "844424930131971\n",
      "844424930131972\n",
      "844424930131973\n",
      "844424930131975\n",
      "844424930131976\n",
      "844424930131970\n",
      "844424930131974\n",
      "-- Query properties --------------------\n",
      "{'name': 'Joe'}\n",
      "{'name': 'Jack'}\n",
      "{'name': 'Andy', 'title': 'Developer'}\n",
      "{'name': 'Joe'}\n",
      "{'name': 'Jack'}\n",
      "{'name': 'Andy', 'title': 'Developer'}\n",
      "{'name': 'Smith'}\n",
      "{'name': 'Smith'}\n",
      "-- Query property value --------------------\n",
      "Developer\n",
      "Developer\n"
     ]
    }
   ],
   "source": [
    "# Query scalar value\n",
    "print(\"-- Query scalar value --------------------\")\n",
    "for row in ag.execCypher(\"MATCH (n:Person) RETURN id(n)\"):\n",
    "    print(row[0])\n",
    "    \n",
    "# Query properties \n",
    "print(\"-- Query properties --------------------\")\n",
    "\n",
    "for row in ag.execCypher(\"MATCH (n:Person) RETURN properties(n)\"):\n",
    "    print(row[0])\n",
    "    \n",
    "# Query properties value\n",
    "print(\"-- Query property value --------------------\")\n",
    "for row in ag.execCypher(\"MATCH (n:Person {name: 'Andy'}) RETURN n.title\"):\n",
    "    print(row[0])\n",
    "    \n",
    "   "
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "8ce0a003-b038-4334-9de8-111569549040",
   "metadata": {},
   "source": [
    "## Close connection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "e15b0654-66d2-4da4-af66-6f776e6729ac",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Clear test data\n",
    "age.deleteGraph(ag.connection, GRAPH_NAME)\n",
    "# connection close\n",
    "ag.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "40e4d7a4-4009-4c42-892c-93cde00762d2",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ab18f5f5-84d3-439f-aa82-53ccfd569d31",
   "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
}