Extract data from Postgres using PyAirbyte

# Add virtual environment support for running in Google Colab
!apt-get install -qq python3.10-venv

# Install PyAirbyte
%pip install --quiet airbyte

# Install postgresql server
!sudo apt-get -y -qq update
!sudo apt-get -y -qq install postgresql
!sudo service postgresql start

# Setup a password `postgres` for username `postgres`
!sudo -u postgres psql -U postgres -c "ALTER USER postgres PASSWORD 'postgres';"

# Setup a database with name `pyairbyte_demo` to be used
!sudo -u postgres psql -U postgres -c 'DROP DATABASE IF EXISTS pyairbyte_demo;'
!sudo -u postgres psql -U postgres -c 'CREATE DATABASE pyairbyte_demo;'

# Import PyAirbyte
import airbyte as ab

# Show all available connectors
ab.get_available_connectors()

import airbyte as ab

# Create and install the source:
source: ab.Source = ab.get_source("source-faker")

# Configure the source
source.set_config(
    config={
        "count": 50_000,  # Adjust this to get a larger or smaller dataset
        "seed": 123,
    },
)
# Verify the config and creds by running `check`:
source.check()

from airbyte.caches import PostgresCacheConfig, PostgresCache

#Define a Postgres Cache and pass the necessary configuration
pg_cache = PostgresCache(
    PostgresCacheConfig(
      host="localhost",
      port=5432,
      username="postgres",
      password="postgres",
      database="pyairbyte_demo"
    )
)

# Select all of the source's streams and read data into the previously defined Postgres cache:
source.select_all_streams()
read_result: ab.ReadResult = source.read(cache=pg_cache)

# Install JupySQL to enable SQL cell magics
%pip install --quiet jupysql
# Load JupySQL extension
%load_ext sql
# Configure max row limit (optional)
%config SqlMagic.displaylimit = 200

# Get the SQLAlchemy 'engine' object for the cache
engine = read_result.cache.get_sql_engine()
# Pass the engine to JupySQL
%sql engine

# Get table objects for the 'users' and 'purchases' streams
users_table = read_result.cache["users"].to_sql_table()
purchases_table = read_result.cache["purchases"].to_sql_table()
display([users_table.fullname, purchases_table.fullname])

%%sql
# Show most recent purchases by purchase date:
SELECT users.id, users.name, purchases.product_id, purchases.purchased_at
FROM {{ users_table.fullname }} AS users
JOIN {{ purchases_table.fullname }} AS purchases
ON users.id = purchases.user_id
ORDER BY purchases.purchased_at DESC
LIMIT 10

40	Kelvin	80	None
44	Jen	1	None
24	Nestor	20	None
19	Marquitta	93	None
43	Tari	26	None
28	Porter	10	None
1	Murray	41	None
9	Travis	70	None
5	Osvaldo	89	None
46	Rodger	35	None