DuckDB_and_Postgres.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Postgres Integration with DuckDB\n",
    "\n",
    "We gave you already a short introduction in the previous section and how it integrates with pandas. In this section we will show you how to integrate DuckDB with Postgres. You can quite easily use DuckDB as your tool of choice to query a postgres database inside a juptyer notebook and combine it with pandas if you want to.\n",
    "\n",
    "We loaded the pokemon data via Airbyte into a postgres database. We will now connect to this database and query it with DuckDB."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import duckdb\n",
    "import pandas as pd\n",
    "\n",
    "# Import jupysql Jupyter extension to create SQL cells\n",
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Configure the notebook\n",
    "Set configrations on the ipython sql extension to directly output data to Pandas and to simplify the output that is printed to the notebook."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%config SqlMagic.autopandas = True\n",
    "%config SqlMagic.feedback = False\n",
    "%config SqlMagic.displaycon = False"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We now have to create a in memory database first and than install the postgres and the postgres_scanner extension for duckdb:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "duckdb:///:memory:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "INSTALL postgres;\n",
    "INSTALL postgres_scanner;\n",
    "LOAD postgres;\n",
    "LOAD postgres_scanner;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And now we can connect to the postgres database and query it:\n",
    "\n",
    "With the `ATTACH` command we can attach the postgres database to the duckdb database. We can now query the postgres database with the `pokemon` alias."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "ATTACH 'host=localhost port=5432 user=postgres password=postgres dbname=pokemon' AS pokemon (TYPE postgres);\n",
    "USE pokemon;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT pokemon_identifier, pokemon_name, pokemon_type  FROM pokemon__masterdata LIMIT 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And for example put it into a pandas dataframe:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "pokemon_masterdata << SELECT pokemon_identifier, pokemon_name, pokemon_type FROM pokemon__masterdata;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pokemon_masterdata.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You don't need to use sqlalchemy or other tools to query the postgres database. You can simply use duckdb to query the postgres database and combine it with pandas if you want to. "
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".venv",
   "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.11.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}