{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#!pip install python-dotenv\n",
    "from dotenv import load_dotenv\n",
    "\n",
    "# Specify the path to your .env file (optional if in the current dir)\n",
    "load_dotenv(dotenv_path=\".env\", override=True)\n",
    "\n",
    "import os\n",
    "import psycopg\n",
    "from sshtunnel import SSHTunnelForwarder\n",
    "\n",
    "if (os.environ.get(\"SSH_TUNNEL_BASED\") == \"true\"):\n",
    "    print(\"SSH tunnel: True\")\n",
    "else:\n",
    "    print(\"SSH tunnel: False\")\n",
    "\n",
    "connect_info = \"host={} port={} user={} password={} dbname={}\".format(os.environ.get(\"DB_HOST\"), os.environ.get(\"DB_PORT\"), os.environ.get(\"DB_USER\"), os.environ.get(\"DB_PASSWORD\"), os.environ.get(\"DB_NAME\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "if (os.environ.get(\"SSH_TUNNEL_BASED\") == \"true\"):\n",
    "    ssh_tunnel = SSHTunnelForwarder(\n",
    "        (os.environ.get(\"REMOTE_HOST\"), int(os.environ.get(\"REMOTE_SSH_PORT\"))), \n",
    "        ssh_username=os.environ.get(\"REMOTE_USERNAME\"), ssh_password=os.environ.get(\"REMOTE_PASSWORD\"), \n",
    "        remote_bind_address=('localhost', int(os.environ.get(\"REMOTE_PORT\"))), local_bind_address=('localhost', int(os.environ.get(\"DB_PORT\"))) \n",
    "    )\n",
    "    ssh_tunnel.start()\n",
    "\n",
    "try:\n",
    "    with psycopg.connect(connect_info) as conn:\n",
    "        if True:\n",
    "            for t in conn.execute(\"\"\"\n",
    "                        SELECT * from URLS WHERE id IN (SELECT id_url FROM URLS_SOURCE_SEARCH INNER JOIN SEARCH ON URLS_SOURCE_SEARCH.id_search = SEARCH.id WHERE SEARCH.search LIKE '%child abuse%') LIMIT 5;\n",
    "                    \"\"\").fetchall():\n",
    "                print(t)\n",
    "            \n",
    "except Exception as e:\n",
    "    print(\"Err:\", str(e))\n",
    "\n",
    "if (os.environ.get(\"SSH_TUNNEL_BASED\") == \"true\"):\n",
    "    ssh_tunnel.stop()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "matitos_urls",
   "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.12.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}