ExamplesExample

Example: query your database

Your code never opens a database socket: scripts run with no network. Instead, the query lives in a SQL script that runs on one of the project’s connections, and any other script calls it by path and gets the rows back. The password stays in the connection; the code only ever sees data.

6 min readBefore this: IntegrationsOpen in Waiso
What you will learn
  • Write a SQL script with a bound parameter
  • Call it from Python or TypeScript and use its rows

The query, then the code

A SQL script declares its inputs in -- @param lines and uses them as :name. Each value goes to the database as a real bound parameter, never pasted into the text, so a value cannot change the query. Its result is the rows as objects keyed by column name, with numbers and dates already decoded.

docs-demoIntegrationsshop_dbHistory
shop_dbpostgres / docs_demo
Tables2
customers5
orders12
Views0
Functions0
Procedures0
customers
Add row
1SELECT *2FROM customers3LIMIT 100;
5 rows · 4 msRun Ctrl ↵
Try the query in the SQL workbench first, then save it as a script.

The code

First the query, saved as db/orders_by_status with your connection chosen in the editor:

orders_by_status.pg.sql
PostgreSQL
-- @param status string = late
SELECT c.name, c.email, o.total, o.created_at
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = :status
ORDER BY o.created_at

Then a script that calls it and sums the totals:

late_total.py
from waiso import run_script

def main(status: str = "late"):
    res = run_script("db/orders_by_status", {"status": status})
    rows = res.output["rows"]
    total = round(sum(r["total"] for r in rows), 2)
    return {"status": status, "orders": len(rows), "total": total}
  1. Create the SQL script, pick the connection and press Run to see the rows.
  2. Create the calling script and run it.

Limits and next steps

No code at all is needed for the common case: a SQL step in a flow runs the same query and hands its rows to a loop or an e-mail.

Related topics