2-postgres101.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%pip install -r requirements.txt # Only run if haven't run before"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 1 - Import dependencies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import psycopg2 # Library for interacting with S3 protocol\n",
    "from psycopg2 import sql # Library for loading in yaml files\n",
    "import yaml # Library for loading in yaml files"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 2 - Load Secrets & Global Variables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "keys_file_path = 'keys.yaml' # TODO: Make sure this is in your repository and get your own access/secret keys from MinIO!\n",
    "\n",
    "with open(keys_file_path, 'r') as file:\n",
    "    keys = yaml.safe_load(file)\n",
    "\n",
    "postgres_credentials = keys.get('postgres', {})\n",
    "postgres_endpoint = keys.get('services')['postgres_endpoint'] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "username = postgres_credentials.get('username')\n",
    "password = postgres_credentials.get('password')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 3 - Set up postgres session"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dbname = \"test\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "db_params = {\n",
    "    'dbname': dbname,\n",
    "    'user': username,\n",
    "    'password': password,\n",
    "    'host': postgres_endpoint,\n",
    "    'port': 5432 # NOTE: Postgres default port is 5432\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 4 - Profit"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Function to execute a SQL query\n",
    "def execute_query(query, params=None):\n",
    "    try:\n",
    "        connection = psycopg2.connect(**db_params)\n",
    "        cursor = connection.cursor()\n",
    "\n",
    "        # If the query has parameters, use sql.SQL and sql.Identifier to safely construct the query\n",
    "        if params:\n",
    "            cursor.execute(sql.SQL(query).format(*[sql.Identifier(param) for param in params]))\n",
    "        else:\n",
    "            cursor.execute(query)\n",
    "\n",
    "        try:\n",
    "            # Fetch results if any (for SELECT queries)\n",
    "            results = cursor.fetchall()\n",
    "            return results\n",
    "        except:\n",
    "            print(\"No returns\")\n",
    "            # Commit changes (for INSERT, UPDATE, DELETE queries)\n",
    "            connection.commit()    \n",
    "\n",
    "    except (Exception, psycopg2.Error) as error:\n",
    "        print(f\"Error: {error}\")\n",
    "    finally:\n",
    "        # Close the database connection\n",
    "        if connection:\n",
    "            cursor.close()\n",
    "            connection.close()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Select Example*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "table_name = \"users\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Example query: Select all rows from a table\n",
    "select_query = f\"SELECT * FROM {table_name};\"\n",
    "\n",
    "# Execute the query\n",
    "results = execute_query(select_query)\n",
    "\n",
    "# Display the results\n",
    "for row in results:\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Insert Example*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Example data to be inserted\n",
    "insert_params = ('jinny_doe', 'john.doe@example.com', '2022-01-30 12:00:00')\n",
    "\n",
    "# Example INSERT query\n",
    "insert_query = f\"\"\"\n",
    "    INSERT INTO {table_name} (username, email, created_at)\n",
    "    VALUES {str(insert_params)}\n",
    "\"\"\"\n",
    "\n",
    "# Call the execute_query function to execute the INSERT query\n",
    "execute_query(insert_query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}