Scripts & Notebooks

View as Markdown

Anything with a PostgreSQL driver connects — psycopg, JDBC, pgx, ruby-pg, and the rest. Both text and binary result formats work, so no driver needs special configuration.

Keep credentials out of your code

A database password does not belong in a source file or a notebook cell. Put it in the environment and read it from there:

Terminal

export IB_HOST=db.instabooks.io
export IB_DBNAME=9341452924740861
export IB_USER=you@yourcompany.com
export IB_PASSWORD='…'

The examples below assume these environment variables have been set.

Python

With psycopg 3:

Python

import os
import psycopg

conn = psycopg.connect(
    host=os.environ["IB_HOST"],
    port=5432,
    dbname=os.environ["IB_DBNAME"],
    user=os.environ["IB_USER"],
    password=os.environ["IB_PASSWORD"],
    sslmode="require",
)

with conn.cursor() as cur:
    cur.execute(
        """
        select date_trunc('month', l.date) as month,
               sum(l.amount) as revenue
          from transaction_lines l
          join accounts a on l.account_oid = a.oid
         where a.type = 'Income' and l.date >= %s
         group by date_trunc('month', l.date)
         order by month
        """,
        ("2024-01-01",),
    )
    for month, revenue in cur.fetchall():
        print(month, revenue)

Parameters are passed separately, as above, rather than formatted into the SQL.

For SQLAlchemy or pandas, build the connection URL instead, with the email percent-encoded.

Python

import os
from urllib.parse import quote_plus
from sqlalchemy import create_engine

url = (
    "postgresql+psycopg://"
    f"{quote_plus(os.environ['IB_USER'])}:{quote_plus(os.environ['IB_PASSWORD'])}"
    f"@{os.environ['IB_HOST']}:5432/{os.environ['IB_DBNAME']}?sslmode=require"
)
engine = create_engine(url)

No server-side cursors. psycopg's named cursors and SQLAlchemy's stream_results are not supported. Fetch results normally; put a where or an aggregate in the query rather than pulling a whole table into memory.

pandas and notebooks

read_sql needs a SQLAlchemy engine, not a raw connection — passing one directly works but prints a warning on every call. Reuse the engine from above:

Python

import pandas as pd

df = pd.read_sql("""
    select a.type, a.fqn, sum(l.amount) as amount
      from transaction_lines l
      join accounts a on l.account_oid = a.oid
     group by a.type, a.fqn
     order by a.type, a.fqn
""", engine)

df.head()

JavaScript

node-postgres (npm install pg), on Node 18 or newer:

JavaScript

import pg from "pg";
const { Client } = pg;

const client = new Client({
  host: process.env.IB_HOST,
  port: 5432,
  database: process.env.IB_DBNAME,
  user: process.env.IB_USER,
  password: process.env.IB_PASSWORD,
  ssl: { rejectUnauthorized: true },
});

await client.connect();

const { rows } = await client.query(
  `select v.display_name, sum(l.amount) as spend
     from transaction_lines l
     join vendors v on l.vendor_oid = v.oid
     join accounts a on l.account_oid = a.oid
    where a.type = $1
    group by v.display_name
    order by sum(l.amount) desc
    limit 10`,
  ["Expense"],
);

for (const row of rows) {
  console.log(row.display_name, row.spend);
}

await client.end();

The $1 placeholders are the server's own — pass values as the second argument rather than building the SQL by hand.

Amounts arrive as strings. node-postgres hands back numeric as a string rather than a JavaScript number, because a double cannot hold every decimal exactly.

Java (JDBC)

The standard PostgreSQL JDBC driver, with no result-format configuration:

Java

String url = "jdbc:postgresql://" + System.getenv("IB_HOST")
           + ":5432/" + System.getenv("IB_DBNAME") + "?sslmode=require";

Properties props = new Properties();
props.setProperty("user", System.getenv("IB_USER"));
props.setProperty("password", System.getenv("IB_PASSWORD"));

try (Connection conn = DriverManager.getConnection(url, props);
     PreparedStatement ps = conn.prepareStatement(
         "select v.display_name, sum(l.amount) as spend "
       + "  from transaction_lines l "
       + "  join vendors v on l.vendor_oid = v.oid "
       + "  join accounts a on l.account_oid = a.oid "
       + " where a.type = ? "
       + " group by v.display_name "
       + " order by sum(l.amount) desc limit 10")) {
    ps.setString(1, "Expense");
    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
            System.out.println(rs.getString(1) + "\t" + rs.getBigDecimal(2));
        }
    }
}

Go

pgx in its default mode. Percent-encode the email in the URL, or use the key=value form as here:

dsn := fmt.Sprintf("host=%s port=5432 dbname=%s user=%s password=%s sslmode=require",
    os.Getenv("IB_HOST"), os.Getenv("IB_DBNAME"),
    os.Getenv("IB_USER"), os.Getenv("IB_PASSWORD"))

conn, err := pgx.Connect(context.Background(), dsn)
if err != nil {
    log.Fatal(err)
}
defer conn.Close(context.Background())

rows, err := conn.Query(context.Background(),
    `select type, count(*) from transactions group by type order by count(*) desc`)
if err != nil {
    log.Fatal(err)
}
defer rows.Close()

for rows.Next() {
    var accountType string
    var count int
    if err := rows.Scan(&accountType, &count); err != nil {
        log.Fatal(err)
    }
    fmt.Println(accountType, count)
}

Ruby

require "pg"

conn = PG.connect(
  host:     ENV.fetch("IB_HOST"),
  port:     5432,
  dbname:   ENV.fetch("IB_DBNAME"),
  user:     ENV.fetch("IB_USER"),
  password: ENV.fetch("IB_PASSWORD"),
  sslmode:  "require"
)

conn.exec("select fqn, type from accounts order by fqn") do |result|
  result.each { |row| puts "#{row['fqn']}\t#{row['type']}" }
end

Writing the SQL

We offer a focused subset of PostgreSQL, see Tables & SQL for details.

Next: the Tables and SQL reference.

Insta-query your books with SQL

Scripts & Notebooks

Keep credentials out of your code

Python

pandas and notebooks

JavaScript

Java (JDBC)

Go

Ruby

Writing the SQL