csv_exporter_from_trino_presto.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from trino import dbapi, auth\n",
    "import warnings\n",
    "from datetime import datetime\n",
    "import pandas as pd\n",
    "\n",
    "def connection(dialect, username, password, host, port, database_name):\n",
    "        try:\n",
    "            print(\"Connecting to database\")\n",
    "            conn = dbapi.connect(\n",
    "                host=host,\n",
    "                port=port,\n",
    "                user=username,\n",
    "                catalog=dialect,\n",
    "                schema=database_name,\n",
    "                http_scheme='https',\n",
    "                auth=auth.BasicAuthentication(username, password),\n",
    "            )\n",
    "            conn._http_session.verify = False\n",
    "\n",
    "            print(\"[✓] Connected to database\")\n",
    "            return conn\n",
    "        except Exception as e:\n",
    "            print(f\"❌ Error on database connection: {e}\")        \n",
    "            return None\n",
    "\n",
    "#entre com suas variáveis abaixo / provide your variables below\n",
    "conn = connection('trino', 'your_user', 'your_password', 'ip or hostname from server', 'port_number', 'your_database')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#insira abaixo o diretório para exportação dos csvs / provide your CSV export directory below\n",
    "path = 'C:/Users/your_csv_export_directory_here'\n",
    "\n",
    "#função de exportação do CSV / exporting csv function\n",
    "def export_csv(db_conn, db_query, csv_file_name):\n",
    "        query = db_query\n",
    "        warnings.filterwarnings('ignore')\n",
    "        cur = db_conn.cursor()\n",
    "        cur.execute(query)\n",
    "        df = pd.DataFrame(cur.fetchall(),columns = [item[0] for item in cur.description])        \n",
    "        df.to_csv(path+csv_file_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#insira lista de tabelas ou views do banco de dados a serem iteradas / provide the list of table names or view names below\n",
    "table_name = [\"table_1\",\"view_1\",\"view_2\",\"table_2\"]\n",
    "\n",
    "length = len(table_name)\n",
    "\n",
    "for i in range(length):  \n",
    "    \n",
    "        query = \"\"\"   \n",
    "            SELECT * FROM \"\"\"+table_name[i];\n",
    "        \n",
    "        exportar_csv(conn, query, table_name[i]+'.csv')\n",
    "        \n",
    "        print(table_name[i])"
   ]
  }
 ],
 "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.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}