5_PostGIS_Cloud.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Writing to Clever Cloud PostGIS db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import psycopg2 as pg\n",
    "from psycopg2 import sql\n",
    "import pandas as pd\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "db_host = os.environ.get('DB_HOST_CLOUD')\n",
    "db_port = os.environ.get('DB_PORT_CLOUD')\n",
    "db_user = os.environ.get('DB_USER_CLOUD')\n",
    "db_password = os.environ.get('DB_PASSWORD_CLOUD')\n",
    "db_name = os.environ.get('DB_NAME_CLOUD')\n",
    "\n",
    "db_url = f'postgresql://{db_user}:{db_password}@{db_host}:{db_port}/{db_name}'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# #create table from dataframe - Clever Cloud\n",
    "# import pandas as pd\n",
    "# # read the csv file into a pandas dataframe, replacing NaN with None\n",
    "# df = pd.read_csv('final_output.csv', index_col=['cep_id'], dtype={'pa': 'Int64', 'eco': 'Int64', 'country': 'Int64'}).replace({pd.NA: None})\n",
    "# from sqlalchemy import create_engine\n",
    "# engine = create_engine(db_url)\n",
    "# df.to_sql('cep_water', engine, if_exists='replace')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# connect to the database\n",
    "conn = pg.connect(\n",
    "    database=db_name,\n",
    "    user=db_user,\n",
    "    password=db_password,\n",
    "    host=db_host,\n",
    "    port=db_port\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1, 895.792133, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0, 'Lithuania', 'LTU', 80412, 'Central European mixed forests', False, 0, None, False, 171.0)\n",
      "(2, 1366843086810.8289, 7194092084292.959, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 1, 'Area Beyond National Jurisdiction', 'ABNJ', 1, 'Antarctic', True, 0, None, False, None)\n",
      "(4, 0.0, 1836981.041237, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 1, 'Area Beyond National Jurisdiction', 'ABNJ', 1, 'Antarctic', True, 555547601, 'South Georgia and South Sandwich Islands Marine Protected Area', True, None)\n",
      "(6, 632877592038.22, 10387835827531.256, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 1, 'Area Beyond National Jurisdiction', 'ABNJ', 2, 'Antarctic Polar Front', True, 0, None, False, None)\n",
      "(7, 0.0, 11679257268.343046, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 1, 'Area Beyond National Jurisdiction', 'ABNJ', 2, 'Antarctic Polar Front', True, 345888, 'Terres Australes Françaises', True, None)\n"
     ]
    }
   ],
   "source": [
    "# check size of database\n",
    "cursor = conn.cursor()\n",
    "cursor.execute('SELECT * FROM cep_water LIMIT 5')\n",
    "result = cursor.fetchall()\n",
    "for row in result:\n",
    "    print(row)\n",
    "cursor.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "env",
   "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.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}