QueryCrafting.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1dbe6bf5-d040-41ff-a6b5-a2bdbff96cf3",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "import json\n",
    "from pprint import pprint"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "123c8e51-6292-436b-87b9-df336c6e068a",
   "metadata": {},
   "outputs": [],
   "source": [
    "sqlite_path=\"./keybase_export.sqlite\"\n",
    "con = sqlite3.connect(sqlite_path)\n",
    "cur = con.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3e8dd9a8-a4d9-43a5-a75b-a4415d1ca7b2",
   "metadata": {},
   "outputs": [],
   "source": [
    "results = cur.execute(\"SELECT name FROM sqlite_master WHERE type='table';\").fetchall()\n",
    "tables = []\n",
    "for item in results:\n",
    "    tables.append(item[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fc20fd04-9c28-4b41-b6ff-0af1db2b02c3",
   "metadata": {},
   "outputs": [],
   "source": [
    "pprint(tables)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c9db5dd8-f36c-4225-affd-f875af664da3",
   "metadata": {},
   "outputs": [],
   "source": [
    "results = cur.execute(\"\"\"\n",
    "\tSELECT\n",
    "\t\tmsg_pkey,\n",
    "\t\tresult_json,\n",
    "\t\tjson_extract(result_json, '$.word')\n",
    "\tFROM nlp_group_messages_t\n",
    "\tWHERE result_json NOT NULL;\n",
    "\"\"\").fetchmany(3)\n",
    "pprint(results)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3b30b8c4-3a49-476b-bf42-3021ac6d2bf7",
   "metadata": {},
   "outputs": [],
   "source": [
    "results = cur.execute(\"\"\"\n",
    "\tSELECT\n",
    "\t\tmsg_pkey,\n",
    "\t\tresult_json,\n",
    "\t\tjson_extract(result_json, '$.word')\n",
    "\tFROM nlp_group_messages_t\n",
    "\tWHERE result_json NOT NULL;\n",
    "\"\"\").fetchmany(3)\n",
    "pprint(results)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e2efed09-5cc8-41d3-a0d8-4da7fbfe2bc9",
   "metadata": {},
   "outputs": [],
   "source": [
    "results = cur.execute(\"\"\"\n",
    "SELECT\n",
    "\tCOUNT ( DISTINCT( json_extract(result_json, '$.word') ) )\n",
    "FROM nlp_group_messages_t\n",
    "WHERE result_json NOT NULL;\n",
    "\"\"\").fetchmany(10)[0][0]\n",
    "pprint(results)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2040c67d-8ba8-4b69-8b48-63e52a36f9a6",
   "metadata": {},
   "outputs": [],
   "source": [
    "results = cur.execute(\"\"\"\n",
    "\tSELECT\n",
    "\t\tA.group_name,\n",
    "\t\tB.group_name,\n",
    "\t\tjson_extract(A.result_json, '$.entity'),\n",
    "\t\tjson_extract(A.result_json, '$.word'),\n",
    "\t\tjson_extract(B.result_json, '$.word')\n",
    "\tFROM\n",
    "\t\tnlp_group_messages_t A, nlp_group_messages_t B\n",
    "\tWHERE\n",
    "\t\tjson_extract(A.result_json, '$.word') = json_extract(B.result_json, '$.word')\n",
    "\t\tAND json_extract(A.result_json, '$.entity') = json_extract(B.result_json, '$.entity');\n",
    "\"\"\").fetchmany(10)\n",
    "pprint(results)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0540b600",
   "metadata": {},
   "outputs": [],
   "source": [
    "results = cur.execute(\"\"\"\n",
    "\tSELECT\n",
    "\t\tA.group_name,\n",
    "\t\tB.group_name,\n",
    "\t\tjson_extract(A.result_json, '$.entity'),\n",
    "\t\tjson_extract(A.result_json, '$.word'),\n",
    "\t\tjson_extract(B.result_json, '$.word')\n",
    "\tFROM\n",
    "\t\tnlp_group_messages_t A, nlp_group_messages_t B\n",
    "\tWHERE\n",
    "\t\tjson_extract(A.result_json, '$.word') = json_extract(B.result_json, '$.word')\n",
    "\t\tAND json_extract(A.result_json, '$.entity') = json_extract(B.result_json, '$.entity');\n",
    "\"\"\").fetchmany(3)\n",
    "pprint(results)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "33bd3bca",
   "metadata": {},
   "outputs": [],
   "source": [
    "results = cur.execute(\"\"\"\n",
    "\tSELECT DISTINCT(group_name) FROM group_messages_t;\n",
    "\"\"\").fetchmany(10)\n",
    "pprint(results)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "09d36afc",
   "metadata": {},
   "outputs": [],
   "source": [
    "results = cur.execute(\"\"\"\n",
    "\tSELECT DISTINCT\n",
    "\t\tjson_extract(nlp_group_messages_t.result_json, '$.entity'),\n",
    "\t\tjson_extract(nlp_group_messages_t.result_json, '$.word'),\n",
    "                nlp_group_messages_t.group_name,\n",
    "                json_extract(group_messages_t.message_json, '$.msg.content.text.body')\n",
    "\n",
    "\tFROM\n",
    "\t\tnlp_group_messages_t\n",
    "    JOIN group_messages_t ON (nlp_group_messages_t.group_name=group_messages_t.group_name);\n",
    "\"\"\").fetchmany(10)\n",
    "pprint(results)\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.10.8 64-bit",
   "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.10.8"
  },
  "vscode": {
   "interpreter": {
    "hash": "e7370f93d1d0cde622a1f8e1c04877d8463912d04d973331ad4851f04de6915a"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}