test_example.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "6506adf6",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/jd/y6jxdm1s23l3qtl8w9js19880000gn/T/ipykernel_4907/3444967154.py:14: SADeprecationWarning: The _ConnectionFairy.connection attribute is deprecated; please use 'driver_connection' (deprecated since: 2.0)\n",
      "  if hasattr(conn.connection, \"c\"):\n",
      "WARNING:root:\n",
      "@base <http://example.com/base/> .\n",
      "@prefix ns1: <http://www.w3.org/ns/r2rml#> .\n",
      "\n",
      "[] a ns1:TriplesMap ;\n",
      "    ns1:logicalTable [ ns1:tableName \"\\\"Student\\\"\" ] ;\n",
      "    ns1:predicateObjectMap [ ns1:objectMap [ ns1:column \"\\\"Name\\\"\" ] ;\n",
      "            ns1:predicate <Student#Name> ] ;\n",
      "    ns1:subjectMap [ ns1:class <Student> ;\n",
      "            ns1:termType ns1:BlankNode ] .\n",
      "\n",
      "\n",
      "WARNING:root:pattern: _ _ _, results: 2\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "@prefix ns1: <http://example.com/base/Student#> .\n",
      "\n",
      "[] a <http://example.com/base/Student> ;\n",
      "    ns1:Name \"Venus\" .\n",
      "\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[('_:Student#1',\n",
       "  '<http://www.w3.org/1999/02/22-rdf-syntax-ns#type>',\n",
       "  '<http://example.com/base/Student>'),\n",
       " ('_:Student#1', '<http://example.com/base/Student#Name>', 'Venus')]"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from rdflib_r2r import R2RStore, R2RMapping\n",
    "from sqlalchemy import create_engine\n",
    "from pathlib import Path\n",
    "import rdflib\n",
    "from rdflib.compare import to_isomorphic, graph_diff\n",
    "\n",
    "echo = False\n",
    "\n",
    "def create_database(db, path, *sql_fnames):\n",
    "    for sql_fname in sql_fnames:\n",
    "        sql_script = (path / sql_fname).open().read()\n",
    "        conn = db.raw_connection()\n",
    "        try:\n",
    "            if hasattr(conn.connection, \"c\"):\n",
    "                cursor = conn.connection.c\n",
    "            else:\n",
    "                cursor = conn.cursor()\n",
    "            cursor.execute(sql_script, (), None)\n",
    "        except Exception as e:\n",
    "            try:\n",
    "                cursor = conn.cursor()\n",
    "                cursor.executescript(sql_script)\n",
    "            except Exception as e:\n",
    "                raise Exception(f\"Problem with {path}: {e}\")\n",
    "            finally:\n",
    "                conn.close()\n",
    "        finally:\n",
    "            conn.close()\n",
    "\n",
    "# db = create_engine(\n",
    "#     \"duckdb:///:memory:\",\n",
    "#     echo=echo,\n",
    "#     # future=True,\n",
    "#     connect_args={\"read_only\": False},\n",
    "# )\n",
    "# db.dialect.server_version_info = (0,)\n",
    "\n",
    "import sqlite3\n",
    "db = create_engine(\n",
    "    \"sqlite:///:memory:\",\n",
    "    echo=echo,\n",
    "    future=True, \n",
    "    connect_args={\n",
    "        \"detect_types\": sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES,\n",
    "        'timeout': 30 * 1000\n",
    "    },\n",
    "    native_datetime=False, # breaks rdb2rdf test D016\n",
    "    # native_datetime=True, # breaks BSBM test 3\n",
    ")\n",
    "\n",
    "# path = Path('tests/rdb2rdf-ts/D014-3tables1primarykey1foreignkey') # 14b\n",
    "# path = Path('tests/rdb2rdf-ts/D000-1table1column0rows')\n",
    "path = Path('tests/rdb2rdf-ts/D001-1table1column1row')\n",
    "sql_fname = 'create.sql'\n",
    "\n",
    "sql_script = (path / sql_fname).open().read()\n",
    "create_database(db, path, sql_fname)\n",
    "\n",
    "# Loads R2RML mapping\n",
    "# mapfile = path / 'r2rmlb.ttl'\n",
    "# fmt = rdflib.util.guess_format(str(mapfile))\n",
    "# mapping = R2RMapping(rdflib.Graph().parse(str(mapfile), format=fmt))\n",
    "\n",
    "# Creates Direct Mapping from DB schema\n",
    "mapping = R2RMapping.from_db(db)\n",
    "\n",
    "g_made = rdflib.Graph(R2RStore(db=db, mapping=mapping))\n",
    "print(to_isomorphic(g_made).serialize())\n",
    "\n",
    "#print(g_made.store.getSQL(\"SELECT * WHERE {?s ?p ?o}\"))\n",
    "query = g_made.store.getSQL(\"SELECT * WHERE {?s ?p ?o}\")\n",
    "db.raw_connection().cursor().execute(query).fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "2020c715",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('_:Student#1', '<http://www.w3.org/1999/02/22-rdf-syntax-ns#type>', '<http://example.com/base/Student>')\n",
      "('_:Student#1', '<http://example.com/base/Student#Name>', 'Venus')\n"
     ]
    }
   ],
   "source": [
    "# 模版\n",
    "from sqlalchemy import text\n",
    "\n",
    "with db.connect() as conn:\n",
    "    for r in conn.execute(text(g_made.store.getSQL(\"SELECT * WHERE {?s ?p ?o}\"))):\n",
    "        print(r)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "11f24003",
   "metadata": {},
   "outputs": [],
   "source": [
    "# D001\n",
    "# with db.connect() as conn:\n",
    "# #     result = conn.execute(text(\"\"\"SELECT rowid, Name FROM Student\"\"\"))\n",
    "#     result = conn.execute(text(\"\"\"\n",
    "#     SELECT CAST(:param_1 AS VARCHAR) || CAST(CAST(\"Student\".rowid AS VARCHAR) AS VARCHAR) AS s, '<http://www.w3.org/1999/02/22-rdf-syntax-ns#type>' AS p, '<http://example.com/base/Student>' AS o, NULL AS g\n",
    "#     FROM \"Student\" UNION ALL SELECT CAST(:param_2 AS VARCHAR) || CAST(CAST(\"Student\".rowid AS VARCHAR) AS VARCHAR) AS s, '<http://example.com/base/Student#Name>' AS p, \"Student\".\"Name\" AS o, NULL AS g\n",
    "#     FROM \"Student\"\n",
    "#     \"\"\"), {\"param_1\":\"_:Student#\", \"param_2\":\"_:Student#\"})\n",
    "#     display(result.all())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "5ad6c4a8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('_:Student#1', '<http://example.com/base/Student#Name>', 'Venus', None)]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "with db.connect() as conn:\n",
    "    result = conn.execute(text(\"\"\"\n",
    "    SELECT '_:Student#' || CAST(CAST(\"Student\".rowid AS VARCHAR) AS VARCHAR) AS s,\n",
    "                    '<http://example.com/base/Student#Name>' AS p,\n",
    "                    \"Student\".\"Name\" AS o,\n",
    "                    NULL AS g\n",
    "   FROM \"Student\"\n",
    "    \"\"\"),{})\n",
    "    display(result.all())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "336ea0ef",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/vickyli117/Desktop/rdflib-r2r/rdflib_r2r/r2r_store.py:652: SAWarning: SELECT statement has a cartesian product between FROM element(s) \"DEPT_ref\" and FROM element \"EMP\".  Apply join condition(s) between each element to resolve.\n",
      "  rows = list(conn.execute(query))\n",
      "WARNING:root:pattern: _ _ _, results: 19\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(rdflib.term.BNode('DEPT#1'), rdflib.term.URIRef('http://www.w3.org/1999/02/22-rdf-syntax-ns#type'), rdflib.term.URIRef('http://example.com/base/DEPT'))\n",
      "(rdflib.term.BNode('DEPT#1'), rdflib.term.URIRef('http://example.com/base/DEPT#dname'), rdflib.term.Literal('APPSERVER'))\n",
      "(rdflib.term.BNode('DEPT#1'), rdflib.term.URIRef('http://example.com/base/DEPT#loc'), rdflib.term.Literal('NEW YORK'))\n",
      "(rdflib.term.BNode('DEPT#1'), rdflib.term.URIRef('http://example.com/base/DEPT#deptno'), rdflib.term.Literal('10', datatype=rdflib.term.URIRef('http://www.w3.org/2001/XMLSchema#integer')))\n",
      "(rdflib.term.URIRef('http://example.com/base/EMP/empno=7369'), rdflib.term.URIRef('http://www.w3.org/1999/02/22-rdf-syntax-ns#type'), rdflib.term.URIRef('http://example.com/base/EMP'))\n",
      "(rdflib.term.URIRef('http://example.com/base/EMP/empno=7369'), rdflib.term.URIRef('http://example.com/base/EMP#job'), rdflib.term.Literal('CLERK'))\n",
      "(rdflib.term.URIRef('http://example.com/base/EMP/empno=7369'), rdflib.term.URIRef('http://example.com/base/EMP#ename'), rdflib.term.Literal('SMITH'))\n",
      "(rdflib.term.URIRef('http://example.com/base/EMP/empno=7369'), rdflib.term.URIRef('http://example.com/base/EMP#deptno'), rdflib.term.Literal('10', datatype=rdflib.term.URIRef('http://www.w3.org/2001/XMLSchema#integer')))\n",
      "(rdflib.term.URIRef('http://example.com/base/EMP/empno=7369'), rdflib.term.URIRef('http://example.com/base/EMP#empno'), rdflib.term.Literal('7369', datatype=rdflib.term.URIRef('http://www.w3.org/2001/XMLSchema#integer')))\n",
      "(rdflib.term.URIRef('http://example.com/base/EMP/empno=7369'), rdflib.term.URIRef('http://example.com/base/EMP#ref-deptno'), rdflib.term.BNode('DEPT#1'))\n",
      "(rdflib.term.URIRef('http://example.com/base/EMP/empno=7369'), rdflib.term.URIRef('http://example.com/base/EMP#etype'), rdflib.term.Literal('PART_TIME'))\n",
      "(rdflib.term.BNode('LIKES#1'), rdflib.term.URIRef('http://www.w3.org/1999/02/22-rdf-syntax-ns#type'), rdflib.term.URIRef('http://example.com/base/LIKES'))\n",
      "(rdflib.term.BNode('LIKES#2'), rdflib.term.URIRef('http://www.w3.org/1999/02/22-rdf-syntax-ns#type'), rdflib.term.URIRef('http://example.com/base/LIKES'))\n",
      "(rdflib.term.BNode('LIKES#1'), rdflib.term.URIRef('http://example.com/base/LIKES#id'), rdflib.term.Literal('7369', datatype=rdflib.term.URIRef('http://www.w3.org/2001/XMLSchema#integer')))\n",
      "(rdflib.term.BNode('LIKES#2'), rdflib.term.URIRef('http://example.com/base/LIKES#id'), rdflib.term.Literal('7369', datatype=rdflib.term.URIRef('http://www.w3.org/2001/XMLSchema#integer')))\n",
      "(rdflib.term.BNode('LIKES#1'), rdflib.term.URIRef('http://example.com/base/LIKES#likedObj'), rdflib.term.Literal('Soccer'))\n",
      "(rdflib.term.BNode('LIKES#2'), rdflib.term.URIRef('http://example.com/base/LIKES#likedObj'), rdflib.term.Literal('Basketball'))\n",
      "(rdflib.term.BNode('LIKES#1'), rdflib.term.URIRef('http://example.com/base/LIKES#likeType'), rdflib.term.Literal('Playing'))\n",
      "(rdflib.term.BNode('LIKES#2'), rdflib.term.URIRef('http://example.com/base/LIKES#likeType'), rdflib.term.Literal('Watching'))\n"
     ]
    }
   ],
   "source": [
    "for t in g_made:\n",
    "    print(t)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "219de290",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/vickyli117/Desktop/rdflib-r2r/rdflib_r2r/r2r_store.py:652: SAWarning: SELECT statement has a cartesian product between FROM element(s) \"EMP\" and FROM element \"DEPT_ref\".  Apply join condition(s) between each element to resolve.\n",
      "  rows = list(conn.execute(query))\n",
      "WARNING:root:pattern: _ _ _, results: 19\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "@prefix ns1: <http://example.com/base/EMP#> .\n",
      "@prefix ns2: <http://example.com/base/LIKES#> .\n",
      "@prefix ns3: <http://example.com/base/DEPT#> .\n",
      "@prefix xsd: <http://www.w3.org/2001/XMLSchema#> .\n",
      "\n",
      "<http://example.com/base/EMP/empno=7369> a <http://example.com/base/EMP> ;\n",
      "    ns1:deptno 10 ;\n",
      "    ns1:empno 7369 ;\n",
      "    ns1:ename \"SMITH\" ;\n",
      "    ns1:etype \"PART_TIME\" ;\n",
      "    ns1:job \"CLERK\" ;\n",
      "    ns1:ref-deptno [ a <http://example.com/base/DEPT> ;\n",
      "            ns3:deptno 10 ;\n",
      "            ns3:dname \"APPSERVER\" ;\n",
      "            ns3:loc \"NEW YORK\" ] .\n",
      "\n",
      "[] a <http://example.com/base/LIKES> ;\n",
      "    ns2:id 7369 ;\n",
      "    ns2:likeType \"Playing\" ;\n",
      "    ns2:likedObj \"Soccer\" .\n",
      "\n",
      "[] a <http://example.com/base/LIKES> ;\n",
      "    ns2:id 7369 ;\n",
      "    ns2:likeType \"Watching\" ;\n",
      "    ns2:likedObj \"Basketball\" .\n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "print(to_isomorphic(g_made).serialize())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "8ca41ad4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "@prefix ns1: <http://example.com/base/EMP#> .\n",
      "@prefix ns2: <http://example.com/base/LIKES#> .\n",
      "@prefix ns3: <http://example.com/base/DEPT#> .\n",
      "@prefix xsd: <http://www.w3.org/2001/XMLSchema#> .\n",
      "\n",
      "<http://example.com/base/EMP/empno=7369> a <http://example.com/base/EMP> ;\n",
      "    ns1:deptno 10 ;\n",
      "    ns1:empno 7369 ;\n",
      "    ns1:ename \"SMITH\" ;\n",
      "    ns1:etype \"PART_TIME\" ;\n",
      "    ns1:job \"CLERK\" ;\n",
      "    ns1:ref-deptno [ a <http://example.com/base/DEPT> ;\n",
      "            ns3:deptno 10 ;\n",
      "            ns3:dname \"APPSERVER\" ;\n",
      "            ns3:loc \"NEW YORK\" ] .\n",
      "\n",
      "[] a <http://example.com/base/LIKES> ;\n",
      "    ns2:id 7369 ;\n",
      "    ns2:likeType \"Playing\" ;\n",
      "    ns2:likedObj \"Soccer\" .\n",
      "\n",
      "[] a <http://example.com/base/LIKES> ;\n",
      "    ns2:id 7369 ;\n",
      "    ns2:likeType \"Watching\" ;\n",
      "    ns2:likedObj \"Basketball\" .\n",
      "\n",
      "\n",
      "(rdflib.term.URIRef('http://example.com/base/EMP/empno=7369'), rdflib.term.URIRef('http://example.com/base/EMP#empno'), rdflib.term.Literal('7369', datatype=rdflib.term.URIRef('http://www.w3.org/2001/XMLSchema#integer')))\n",
      "(rdflib.term.URIRef('http://example.com/base/EMP/empno=7369'), rdflib.term.URIRef('http://www.w3.org/1999/02/22-rdf-syntax-ns#type'), rdflib.term.URIRef('http://example.com/base/EMP'))\n",
      "(rdflib.term.URIRef('http://example.com/base/EMP/empno=7369'), rdflib.term.URIRef('http://example.com/base/EMP#etype'), rdflib.term.Literal('PART_TIME'))\n",
      "(rdflib.term.BNode('n8deac2d0a8914f73a337e8d11f3ddde5b3'), rdflib.term.URIRef('http://www.w3.org/1999/02/22-rdf-syntax-ns#type'), rdflib.term.URIRef('http://example.com/base/LIKES'))\n",
      "(rdflib.term.BNode('n8deac2d0a8914f73a337e8d11f3ddde5b1'), rdflib.term.URIRef('http://example.com/base/DEPT#deptno'), rdflib.term.Literal('10', datatype=rdflib.term.URIRef('http://www.w3.org/2001/XMLSchema#integer')))\n",
      "(rdflib.term.URIRef('http://example.com/base/EMP/empno=7369'), rdflib.term.URIRef('http://example.com/base/EMP#ref-deptno'), rdflib.term.BNode('n8deac2d0a8914f73a337e8d11f3ddde5b1'))\n",
      "(rdflib.term.BNode('n8deac2d0a8914f73a337e8d11f3ddde5b2'), rdflib.term.URIRef('http://www.w3.org/1999/02/22-rdf-syntax-ns#type'), rdflib.term.URIRef('http://example.com/base/LIKES'))\n",
      "(rdflib.term.BNode('n8deac2d0a8914f73a337e8d11f3ddde5b3'), rdflib.term.URIRef('http://example.com/base/LIKES#likeType'), rdflib.term.Literal('Watching'))\n",
      "(rdflib.term.BNode('n8deac2d0a8914f73a337e8d11f3ddde5b2'), rdflib.term.URIRef('http://example.com/base/LIKES#likedObj'), rdflib.term.Literal('Soccer'))\n",
      "(rdflib.term.BNode('n8deac2d0a8914f73a337e8d11f3ddde5b1'), rdflib.term.URIRef('http://example.com/base/DEPT#loc'), rdflib.term.Literal('NEW YORK'))\n",
      "(rdflib.term.BNode('n8deac2d0a8914f73a337e8d11f3ddde5b3'), rdflib.term.URIRef('http://example.com/base/LIKES#likedObj'), rdflib.term.Literal('Basketball'))\n",
      "(rdflib.term.URIRef('http://example.com/base/EMP/empno=7369'), rdflib.term.URIRef('http://example.com/base/EMP#job'), rdflib.term.Literal('CLERK'))\n",
      "(rdflib.term.BNode('n8deac2d0a8914f73a337e8d11f3ddde5b3'), rdflib.term.URIRef('http://example.com/base/LIKES#id'), rdflib.term.Literal('7369', datatype=rdflib.term.URIRef('http://www.w3.org/2001/XMLSchema#integer')))\n",
      "(rdflib.term.URIRef('http://example.com/base/EMP/empno=7369'), rdflib.term.URIRef('http://example.com/base/EMP#ename'), rdflib.term.Literal('SMITH'))\n",
      "(rdflib.term.BNode('n8deac2d0a8914f73a337e8d11f3ddde5b1'), rdflib.term.URIRef('http://example.com/base/DEPT#dname'), rdflib.term.Literal('APPSERVER'))\n",
      "(rdflib.term.BNode('n8deac2d0a8914f73a337e8d11f3ddde5b2'), rdflib.term.URIRef('http://example.com/base/LIKES#id'), rdflib.term.Literal('7369', datatype=rdflib.term.URIRef('http://www.w3.org/2001/XMLSchema#integer')))\n",
      "(rdflib.term.BNode('n8deac2d0a8914f73a337e8d11f3ddde5b1'), rdflib.term.URIRef('http://www.w3.org/1999/02/22-rdf-syntax-ns#type'), rdflib.term.URIRef('http://example.com/base/DEPT'))\n",
      "(rdflib.term.URIRef('http://example.com/base/EMP/empno=7369'), rdflib.term.URIRef('http://example.com/base/EMP#deptno'), rdflib.term.Literal('10', datatype=rdflib.term.URIRef('http://www.w3.org/2001/XMLSchema#integer')))\n",
      "(rdflib.term.BNode('n8deac2d0a8914f73a337e8d11f3ddde5b2'), rdflib.term.URIRef('http://example.com/base/LIKES#likeType'), rdflib.term.Literal('Playing'))\n"
     ]
    }
   ],
   "source": [
    "outfile = path / 'directGraph.ttl'\n",
    "# outfile = path / 'mappeda.nq'\n",
    "\n",
    "fmt = rdflib.util.guess_format(str(outfile)) \n",
    "print(rdflib.Graph().parse(str(outfile), format=fmt).serialize())\n",
    "\n",
    "for t in rdflib.Graph().parse(str(outfile), format=fmt):\n",
    "    print(t)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "280ddd77-c3bc-4643-b588-23bc50d8bc70",
   "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.5"
  },
  "vscode": {
   "interpreter": {
    "hash": "aee8b7b246df8f9039afb4144a1f6fd8d2ca17a180786b69acc140d282b71a49"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}