Python Await Async Tutorial with Real Examples and Simple Explanations

Q: What is async/await in Python?

async def defines a coroutine function and await pauses execution until an asynchronous operation completes. This enables concurrent I/O operations without threading, using the asyncio event loop.

Q: How do I run multiple async tasks concurrently?

Use asyncio.gather(task1(), task2()) to run multiple coroutines concurrently. Use asyncio.create_task() to schedule without immediately waiting.

Q: What does 'coroutine was never awaited' mean?

You called an async function without await . Async functions return coroutine objects that must be awaited. Add await before the call or use asyncio.run() from synchronous code.

Q: Can I mix synchronous and asynchronous code?

Yes. Use asyncio.run() to call async from sync. Use loop.run_in_executor() to run blocking functions inside async code without blocking the event loop.

Last Updated: June 01, 2026

Table of Contents

What is async await in Python?
How does async await work in Python?
Cooking Hamburgers Asynchronously and coding the event loop manually
Async Await Code Example in Python
Async Asynchronous Calling Another Async Function Code Example
Async Await Real World Example With Web Crawler in Python
How To Mix Asynchronous And Synchronous Code With Await Async in Python
Conclusion
Related Articles
Frequently Asked Questions

Advanced

The python await and async is one of the more advanced features to help run your programs faster by making sure the CPU is spending as little time as possible waiting and instead as much time as possible working. If ever you see a capable chef, you’ll know what I mean. The chef is not just following a recipe step by step (i.e. working synchronously), the chef is boiling water to cook the pasta , measuring the amount of pasta, chopping tomatoes for the pasta sauce until the water boils etc (i.e. the chef is working asynchronously). The chef is minimizing the time they are waiting idle and always working on a task. That’s the same idea with async and await.

For this tutorial, we will focus on python 3.7 as it has some of the more modern features of await and async. We will call out some of the differences for python 3.4 – 3.6.

Written by Pubs

Python developer and educator with 15+ years building production systems across data engineering, web APIs, and AI tooling. Founder of Python How To Program — 270+ in-depth tutorials covering the modern Python stack.

View all tutorials by Pubs →

What is async await in Python?

The async await keywords help to define in your program which parts need to run sequentially, and which parts may take sometime but other parts of the program can execute while this step completes. A modern example of this is that if you’re downloading a web page it may take a few seconds, while the download is happening you can execute other parts of your program.

How does async await work in Python?

Sometimes the best way to explain something is to show how you would achieve the same thing without the feature.

Continuing with the restaurant theme, suppose you are running a hamburger stall (you’re the waiter and the chef) and it is almost instant to collect payment for a customer and serve the final hamburger, but the most time consuming task is to cooking the beef patty which takes 2 seconds (one could only wish!).

See the below diagram:

Figure 1: Sequentially serving customers at a hamburger stall

In the above diagram:

Step 1: you would first get the order and collect the money from Customer 1
Step 2: you would then put a beef patty on the cook top and then wait for 2 seconds for the beef patty to cook. At the same time, Customer 1 is also waiting for 2 seconds.
Step 3: when the beef patty is cooked, you can then plate this onto a hamburger bun
Step 4: pass the final hamburger to Customer 1
Step 5: You would then start to serve Customer 2 (who has already been waiting 2 seconds for you to serve Customer 1). You can then repeat steps 2-4

With the above approach, Customer 1 would have their burger in about 2 seconds, Customer 2 approx 4 seconds, and then Customer 3 approx 6 seconds.

The equivalent code would be as follows:

import time, datetime, timeit

customer_queue = [ "C1", "C2", "C3" ]

def get_next_customer():
    return customer_queue.pop(0)    #Get the first customer from list

def cook_hamburger(customer):
    start_customer_timer = timeit.default_timer()
    print( f"[{customer}]: Start cooking hamberger for customer")
    time.sleep(2)   # It takes 2 seconds to cook the hamburger
    end_customer_timer = timeit.default_timer()
    print( f"[{customer}]: Finish cooking hamberger for customer.  Total {end_customer_timer-start_customer_timer} seconds\n")

def run_shop():
    while customer_queue:
        curr_customer = get_next_customer()
        cook_hamburger(curr_customer)

def main():
    print('Hamburger Shop')
    start = timeit.default_timer()
    run_shop()
    stop = timeit.default_timer()
    print(f"** Total runtime: {stop-start} seconds ***")

if __name__ == '__main__':
    main()

The code above is fairly straightforward. We have a list of customers that are queuing in the list customer_queue which are being looped under the def run_shop(). For each customer (get_next_customer()), we call cook_hamburger() to cook the hamburger for 2 seconds and wait for it to complete.

Running this code you would get the following output:

As expected, the total runtime for 3 customers is 6 seconds since each customer is served sequentially.

Cooking Hamburgers Asynchronously and coding the event loop manually

Instead of serving the customer and cooking the hamburger for each customer, you can obviously do some of the tasks asynchronously, meaning you can start the task but you don’t have to sit and wait, you can do something else. See the following diagram where the chef/waiter is serving multiple customers and cooking at the same time. It’s not explicitly shown here, but the chef/waiter is constantly checking on the status of the next task and if a task doesn’t require his/her attention they’ll move on to the next task. This process of always looking for something to do is the equivalent of the “event loop”. The Event Loop is a programming construct where the logic is to always look for a task to execute and if there’s a task which will take some time it can release control to the next task in the loop.

Figure 2: *Example of how the event loop works in a real life example – the chef/waiter is always busy!*

In the above example, the following is happening:

Step 1: you would first get the order and collect the money from Customer 1
Step 2: you would then put a beef patty on the cook top and then let it cook, then immediately move on to the next customer while the patty is cooking.
Step 3: you would first get the order and collect the money from Customer 2. You would also check if the first beef patty has completed cooking yet.
Step 4: you would then put another beef patty on the cook top and then let it cook, then immediately move on to the next customer while the patty is cooking.
…
Step 5: When any of the beef patties are done, you would plate it
Step 6: Pass the plated hamburger to the respective customer. Note, in the above example we’ve assumed it to be Customer 1, but it could be any customer depending on which beef patty cooked fully first.
Step 7: When any of the beef patties are done, you would plate it, and server

This is the equivalent of the event loop. The chef/waiter is constantly checking if it needs to serve the customer or check on the hamburgers which are cooking. When there’s a hamburger is placed on the stove and we need to wait 2 seconds, the chef/waiter moves to the next task and does not wait for the 2 seconds to complete. When the hamburger is done, it is then served to the customer.

How can this be done programatically? Glad you asked:

import time ,datetime, timeit

customer_queue = [ "C1", "C2", "C3" ]
hamburger_queue = []

def get_next_customer():
    if customer_queue: return customer_queue.pop(0)    #Get the first customer from list
    return None 

def start_cooking_hamburger(customer):
    print( f"[{customer}]: Start cooking hamberger for customer")
    hamburger = { "customer":customer, "start_cooking_time": timeit.default_timer(), "cooked":False}
    hamburger_queue.append( hamburger )

def check_hamburger_status():
    curr_timer = timeit.default_timer()

    #Check if it's cooking, but release control
    for index, hamburger in enumerate(hamburger_queue):         
        elapsed_time = curr_timer-hamburger['start_cooking_time']
        if elapsed_time > 2: #2 second has passed for hamrburger to cook
            print( f"[{hamburger['customer']}]: Finish cooking hamberger for customer.  Total {elapsed_time} seconds\n")
            del hamburger_queue[ index].  #delete from list to mark as done

def run_shop():
    while customer_queue or hamburger_queue:        #Event loop
        curr_customer = get_next_customer()
        if curr_customer: start_cooking_hamburger(curr_customer)
        check_hamburger_status()

def main():
    print('Hamburger Shop')
    start = timeit.default_timer()
    run_shop()
    stop = timeit.default_timer()
    print(f"** Total runtime: {stop-start} seconds ***")

if __name__ == '__main__':
    main()

The output of the code is as follows:

*Output running asynchronously – notice the runtime of 2 seconds compared to the 6 seconds in the synchronsous method*.

So there’s a few things happening here:

There’s a new list called hamburger_queue[] which is keeping track of each hamburger that is being cooked
The event loop is the while customer_queue or hamburger_queue within the run_shop() function
We have a new function called start_cooking_hamburger() which helps to keep track of the task to cooking starting. Why is this needed? Well in the past we would simply wait for a given task. Now, since we are doing something else while we wait, we need to remember a few things to come back to the task
We also have a new function called check_hamburger_status() which checks the status of each hamburger being cooked (i.e. item in hamburger_queue[]), and if it is cooked (i.e. 2 seconds have passed), then it is considered complete

You may notice in the output that Customer 3 was in fact served before Customer 2. This is because that the execution order is not guarantee.

How To Use Ibis for Portable DataFrames Across SQL Backends

by Pubs | Aug 5, 2026 | Data Processing

Intermediate

You write a data pipeline in pandas, it works perfectly on your laptop with a CSV file, and then production arrives. The data is in BigQuery. You spend two days rewriting everything in the BigQuery Python client. Six months later, the company switches to Snowflake. You rewrite it again. Then someone asks you to run the same logic against a local DuckDB file for a quick analysis. At this point you have three versions of the same query, and they all drift apart every time business logic changes.

Ibis solves this by giving you one DataFrame API that compiles to SQL for more than 20 backends — DuckDB, BigQuery, Snowflake, Spark, PostgreSQL, SQLite, Trino, and more. You write the expression once, connect it to whichever backend you have available, and execute. Switching backends is a one-line change. Install it with pip install ibis-framework[duckdb] (swap duckdb for any backend you need, or use pip install ibis-framework[all] for every supported backend).

This tutorial covers everything you need to use Ibis effectively: connecting to backends, building portable expressions, filtering and aggregating data, joining tables, and understanding deferred vs eager execution. The final section builds a real multi-backend analysis pipeline. By the end you will know how to write data transformations that run anywhere without modification.

Ibis for Portable DataFrames: Quick Example

Here is a complete, runnable Ibis example that loads data into DuckDB, runs a query, and prints the result — all without writing a single line of SQL:

# quick_ibis.py
import ibis
import ibis.selectors as s

# Connect to an in-memory DuckDB instance (no file needed)
con = ibis.duckdb.connect()

# Create a table from a Python dict (simulates loading real data)
con.create_table(
    "sales",
    ibis.memtable({
        "region": ["North", "South", "North", "East", "South", "East"],
        "product": ["Widget", "Gadget", "Gadget", "Widget", "Widget", "Gadget"],
        "revenue": [1200, 850, 960, 1100, 730, 980],
        "units": [12, 8, 10, 11, 7, 9],
    }),
    overwrite=True,
)

# Build an expression -- nothing executes yet
sales = con.table("sales")

result = (
    sales
    .group_by(["region", "product"])
    .aggregate(
        total_revenue=sales.revenue.sum(),
        total_units=sales.units.sum(),
        avg_revenue=sales.revenue.mean().round(2),
    )
    .order_by(ibis.desc("total_revenue"))
)

# Execute and convert to pandas
df = result.to_pandas()
print(df)

  region  product  total_revenue  total_units  avg_revenue
0  North   Gadget           960           10       960.00
1  North   Widget          1200           12      1200.00
2   East   Gadget           980            9       980.00
3   East   Widget          1100           11      1100.00
4  South   Gadget           850            8       850.00
5  South   Widget           730            7       730.00

Three things worth noticing: First, ibis.duckdb.connect() gives you an in-memory backend with no setup — perfect for development and testing. Second, ibis.memtable() wraps a plain Python dict as an Ibis table, so you can start experimenting without any files or databases. Third, the expression you build with .group_by(), .aggregate(), and .order_by() does nothing until you call .to_pandas(). This is lazy evaluation — Ibis builds an expression tree, compiles it to SQL behind the scenes, and only hits the backend when you ask for results.

The sections below show how to connect to real backends, build more complex expressions, and switch between backends with a single line change.

What Is Ibis and Why Use It?

Ibis is a Python DataFrame library that treats SQL backends as interchangeable execution engines. Instead of wrapping a single engine (like pandas wraps NumPy, or PySpark wraps Spark), Ibis defines a portable expression language that compiles down to the SQL dialect of whichever backend you connect to. The same .filter(), .group_by(), and .join() calls produce DuckDB SQL, BigQuery Standard SQL, Spark SQL, or Postgres SQL depending on your connection.

This matters most in three situations: when you develop locally but deploy to a cloud warehouse; when you need to run the same analysis across different databases without copy-pasting SQL; and when you want to use Python ergonomics (method chaining, tab-completion, Python type checking) while still pushing computation to the database engine rather than pulling data into memory.

Library	Execution	Backends	API style	Best for
pandas	In-memory (NumPy)	1	Eager	Small data, local exploration
PySpark	Spark cluster	1	Lazy (Spark)	Hadoop ecosystem, batch ETL
SQLAlchemy	Any SQL DB	20+	ORM / Core SQL	App databases, ORM models
Polars	In-memory (Rust)	1	Lazy + Eager	Fast local analytics, CSV/Parquet
Ibis	Delegated to backend	20+	Lazy DataFrame	Portable analytics, multi-backend

The key tradeoff with Ibis is that not every backend supports every expression. An operation that DuckDB handles natively might not be available in SQLite, so Ibis raises a TranslationError rather than silently producing wrong results. This is a feature, not a bug — it forces you to write expressions that are genuinely portable.

Cache Katie sitting on a SQL database cylinder with backend logos orbiting, cartoon style — One API. Twenty-two backends. The query runs wherever your data lives.

Connecting to Backends

Every Ibis workflow starts with a connection. The connection object is your interface to a specific backend — it handles authentication, compiles your expressions to the right SQL dialect, and executes queries. Ibis provides shortcut functions for every supported backend:

# connections.py
import ibis

# DuckDB -- in-memory (no file, no setup)
con_mem = ibis.duckdb.connect()

# DuckDB -- persistent file (data survives across sessions)
con_file = ibis.duckdb.connect("my_analytics.ddb")

# SQLite -- works with any .sqlite or .db file
con_sqlite = ibis.sqlite.connect("app_data.db")

# PostgreSQL -- requires psycopg2 installed
# con_pg = ibis.postgres.connect(
#     host="localhost", port=5432,
#     database="mydb", user="analyst", password="secret"
# )

# BigQuery -- requires google-cloud-bigquery installed
# con_bq = ibis.bigquery.connect(project="my-gcp-project", dataset="analytics")

# Print what tables exist in a connection
print("Tables in memory DuckDB:", con_mem.list_tables())
print("Tables in file DuckDB:", con_file.list_tables())

Tables in memory DuckDB: []
Tables in file DuckDB: []

For cloud backends like BigQuery or Snowflake, authentication uses the same mechanism as the underlying client library — service account keys, gcloud auth application-default login, or environment variables. Ibis does not add its own auth layer, which means your existing credentials work out of the box. For development, DuckDB in-memory mode is the fastest feedback loop: zero setup, runs entirely in your process, and supports almost the entire Ibis expression set.

Loading and Querying Data

Ibis gives you several ways to get data into the system depending on where your data lives. The most common patterns are loading from Parquet or CSV files (DuckDB handles these natively), reading from an existing database table, or wrapping a Python dict or pandas DataFrame using ibis.memtable():

# loading_data.py
import ibis
import tempfile, os, json

con = ibis.duckdb.connect()

# Pattern 1: memtable from a Python dict (great for tests and examples)
orders = ibis.memtable({
    "order_id": [1001, 1002, 1003, 1004, 1005],
    "customer": ["Alice", "Bob", "Alice", "Carol", "Bob"],
    "amount": [250.0, 89.99, 410.50, 130.0, 75.25],
    "status": ["shipped", "pending", "shipped", "cancelled", "shipped"],
})

# Pattern 2: create a real DuckDB table for repeated use
con.create_table("orders", orders, overwrite=True)

# Pattern 3: read back from the connection as a Table expression
t = con.table("orders")
print("Schema:", t.schema())
print("Row count:", t.count().to_pandas())

# Pattern 4: read a Parquet file directly (DuckDB backend)
# parquet_table = con.read_parquet("data/sales_2025.parquet")

# Preview the first 3 rows
print(t.limit(3).to_pandas())

Schema: ibis.Schema {
  order_id  int64
  customer  string
  amount    float64
  status    string
}
Row count: 5

   order_id customer  amount    status
0      1001    Alice  250.00   shipped
1      1002      Bob   89.99   pending
2      1003    Alice  410.50   shipped

ibis.memtable() is the Swiss Army knife of Ibis development — you can wrap a pandas DataFrame, a list of dicts, or a raw dict of columns, and Ibis will handle the type inference. Notice that t.count() is also a lazy expression; you call .to_pandas() to pull the scalar result out. Ibis is consistently lazy by default — nothing touches the database until you request results.

Sudo Sam connecting backends at a giant switchboard, cartoon style — One connection. Swap the backend, keep the query. That’s the whole pitch.

Filtering, Selecting, and Transforming

Ibis expressions are built by chaining methods on a Table object. Filtering uses .filter(), column selection uses .select() or .drop(), and new columns are added with .mutate(). All of these return new expression objects — the original table is never modified, and nothing executes until you call .to_pandas(), .to_pyarrow(), or .execute():

# filtering.py
import ibis

con = ibis.duckdb.connect()

orders = ibis.memtable({
    "order_id": [1001, 1002, 1003, 1004, 1005],
    "customer": ["Alice", "Bob", "Alice", "Carol", "Bob"],
    "amount": [250.0, 89.99, 410.50, 130.0, 75.25],
    "status": ["shipped", "pending", "shipped", "cancelled", "shipped"],
})

t = orders  # memtable is already an expression

# Filter: shipped orders over $100
shipped_large = t.filter([
    t.status == "shipped",
    t.amount > 100,
])

# Add a derived column: tax estimate at 10%
with_tax = shipped_large.mutate(
    tax=shipped_large.amount * 0.10,
    total=(shipped_large.amount * 1.10).round(2),
)

# Select only the columns we care about
result = with_tax.select("order_id", "customer", "amount", "tax", "total")

print(result.to_pandas())

   order_id customer  amount    tax    total
0      1001    Alice  250.00   25.0   275.00
1      1003    Alice  410.50   41.05  451.55

.filter() accepts a list of boolean expressions — they are AND-ed together automatically, just like WHERE a AND b in SQL. .mutate() is the equivalent of SELECT *, new_col AS expr — it keeps all existing columns and appends new ones. The expression shipped_large.amount * 0.10 does not perform any Python arithmetic; it creates an Ibis expression tree that compiles to amount * 0.10 in SQL. When you call .to_pandas(), the entire chain compiles to a single SQL query and executes in one shot.

Grouping and Aggregating

Aggregation in Ibis follows the .group_by().aggregate() pattern, which maps cleanly to SQL’s GROUP BY. You can mix any combination of aggregate functions: .sum(), .mean(), .count(), .min(), .max(), .std(), and more. Conditional aggregation (the equivalent of SUM(CASE WHEN ...) in SQL) uses .sum(where=...):

# aggregation.py
import ibis

orders = ibis.memtable({
    "order_id": [1001, 1002, 1003, 1004, 1005, 1006, 1007],
    "customer": ["Alice", "Bob", "Alice", "Carol", "Bob", "Alice", "Carol"],
    "amount": [250.0, 89.99, 410.50, 130.0, 75.25, 195.0, 310.0],
    "status": ["shipped", "pending", "shipped", "cancelled", "shipped", "shipped", "shipped"],
})

# Group by customer, aggregate multiple metrics
summary = (
    orders
    .group_by("customer")
    .aggregate(
        total_orders=orders.order_id.count(),
        total_revenue=orders.amount.sum().round(2),
        avg_order=orders.amount.mean().round(2),
        shipped_revenue=orders.amount.sum(where=orders.status == "shipped").round(2),
    )
    .order_by(ibis.desc("total_revenue"))
)

print(summary.to_pandas())

  customer  total_orders  total_revenue  avg_order  shipped_revenue
0    Alice             3         855.50     285.17           855.50
1    Carol             2         440.00     220.00           310.00
2      Bob             2         165.24      82.62            75.25

The where= argument on .sum() and other aggregates is equivalent to a SQL FILTER (WHERE ...) clause or a CASE WHEN inside the aggregate — it lets you compute conditional totals without subqueries. This compiles to backend-specific SQL automatically: DuckDB uses SUM(amount) FILTER (WHERE status = 'shipped'), while backends that do not support that syntax fall back to SUM(CASE WHEN status = 'shipped' THEN amount ELSE 0 END).

API Alice juggling GROUP BY SUM COUNT data cubes through a funnel, cartoon style — group_by().aggregate() — because fourteen lines of SQL GROUP BY deserve a better life.

Joining Tables

Ibis supports all SQL join types: inner, left, right, outer, and semi/anti joins. The .join() method takes a second table and a join condition (or a list of column name strings for equi-joins). After a join, use .select() to pick the columns you want, since both tables’ columns are available and some names may collide:

# joins.py
import ibis

orders = ibis.memtable({
    "order_id": [1001, 1002, 1003, 1004],
    "customer_id": [1, 2, 1, 3],
    "amount": [250.0, 89.99, 410.50, 130.0],
    "status": ["shipped", "pending", "shipped", "shipped"],
})

customers = ibis.memtable({
    "customer_id": [1, 2, 3, 4],
    "name": ["Alice", "Bob", "Carol", "Dave"],
    "tier": ["gold", "silver", "gold", "bronze"],
})

# Inner join: only orders with a matching customer
result = (
    orders
    .join(customers, orders.customer_id == customers.customer_id)
    .select(
        "order_id",
        "name",
        "tier",
        "amount",
        "status",
    )
    .filter(lambda t: t.tier == "gold")  # filter after join
    .order_by("order_id")
)

print(result.to_pandas())

   order_id   name  tier  amount   status
0      1001  Alice  gold  250.00  shipped
1      1003  Alice  gold  410.50  shipped

After a join, column references can get ambiguous if both tables share column names. Ibis uses orders.customer_id on the left and customers.customer_id on the right to disambiguate — the join condition references both explicitly. The .select() after the join picks clean output columns by name, avoiding duplicates. You can also pass how="left", how="right", or how="outer" to the .join() call; the default is an inner join.

Switching Backends

The core promise of Ibis is that your expressions are backend-agnostic. Once you have an expression, switching backends is a matter of changing the connection. The expression itself — the chain of .filter(), .group_by(), .join() calls — does not change at all. This pattern is most useful in testing: develop and test against fast in-memory DuckDB, then run the same expression against the production Snowflake warehouse without modifying the expression code:

# switching_backends.py
import ibis

# Define the expression independently of any backend
def build_summary_expr(orders_table):
    """Returns the aggregation expression -- no backend reference."""
    return (
        orders_table
        .filter(orders_table.status == "shipped")
        .group_by("customer")
        .aggregate(
            order_count=orders_table.order_id.count(),
            total_revenue=orders_table.amount.sum().round(2),
        )
        .order_by(ibis.desc("total_revenue"))
    )

SAMPLE_DATA = {
    "order_id": [1001, 1002, 1003, 1004, 1005],
    "customer": ["Alice", "Bob", "Alice", "Carol", "Bob"],
    "amount": [250.0, 89.99, 410.50, 130.0, 75.25],
    "status": ["shipped", "pending", "shipped", "cancelled", "shipped"],
}

# Backend A: DuckDB in-memory (development / testing)
con_dev = ibis.duckdb.connect()
orders_dev = ibis.memtable(SAMPLE_DATA)
expr_dev = build_summary_expr(orders_dev)
print("DuckDB result:")
print(expr_dev.to_pandas())

# Backend B: DuckDB file (local persistence)
con_prod = ibis.duckdb.connect("analytics.ddb")
con_prod.create_table("orders", ibis.memtable(SAMPLE_DATA), overwrite=True)
orders_prod = con_prod.table("orders")
expr_prod = build_summary_expr(orders_prod)
print("\nDuckDB file result (same expression):")
print(expr_prod.to_pandas())

DuckDB result:
  customer  order_count  total_revenue
0    Alice            2         660.50
1      Bob            1          75.25

DuckDB file result (same expression):
  customer  order_count  total_revenue
0    Alice            2         660.50
1      Bob            1          75.25

The key design pattern here is to put your transformation logic inside a function that accepts a table expression and returns an expression, without ever touching a specific connection. The connection only appears at the call site when you bind the function to a real table. This is the “expression first, connection second” pattern, and it is what makes Ibis code genuinely portable. For cloud backends, you would replace ibis.memtable(SAMPLE_DATA) with con_bq.table("dataset.orders") and the expression function stays identical.

Pyro Pete yanking three backend levers simultaneously with sparks flying, cartoon style — You wrote it once. It runs everywhere. This is the whole point.

Understanding Lazy Evaluation and `to_pandas()`

Ibis is lazy by default, which means every method call builds up a query plan without touching the database. This has two practical benefits: expressions compose cheaply (you can build a complex query without any I/O), and the backend optimizer sees the whole query at once rather than being forced into multiple round-trips. Understanding when execution happens lets you avoid accidentally running expensive queries in a loop:

# lazy_execution.py
import ibis

con = ibis.duckdb.connect()
orders = ibis.memtable({
    "order_id": list(range(1, 11)),
    "customer": ["Alice", "Bob"] * 5,
    "amount": [100.0 + i * 15 for i in range(10)],
    "status": ["shipped", "pending"] * 5,
})

# Build a complex expression -- zero database calls so far
shipped = orders.filter(orders.status == "shipped")
enriched = shipped.mutate(discounted=shipped.amount * 0.95)
summary = enriched.group_by("customer").aggregate(
    total=enriched.amount.sum(),
    discounted_total=enriched.discounted.sum().round(2),
)

# Print the SQL Ibis will run (useful for debugging and auditing)
print("SQL Ibis will execute:")
print(ibis.to_sql(summary))
print()

# This triggers the single database call
df = summary.to_pandas()
print("Result:")
print(df)

SQL Ibis will execute:
SELECT
  "customer",
  SUM("amount") AS "total",
  ROUND(SUM("amount" * 0.95), 2) AS "discounted_total"
FROM (
  SELECT
    "customer",
    "amount",
    "amount" * 0.95 AS "discounted"
  FROM "ibis_memtable_..."
  WHERE "status" = 'shipped'
) t0
GROUP BY "customer"

Result:
  customer  total  discounted_total
0    Alice  375.0           356.25
1      Bob  450.0           427.50

ibis.to_sql(expr) is an invaluable debugging tool — it shows you exactly what SQL your expression will produce, formatted for the connected backend. This is useful when you need to audit query performance, add database-side hints, or verify that a complex expression compiles the way you expect. The SQL is always generated fresh from the expression tree, so it always reflects the current state of your chain.

Real-Life Example: Multi-Backend Sales Analysis Pipeline

This example builds a realistic sales analysis pipeline where the transformation logic is defined once and executed against two different backends — a fast in-memory DuckDB instance for quick iteration, and a persistent DuckDB file simulating a “production” warehouse:

# sales_pipeline.py
import ibis
import pandas as pd

# -------------------------------------------------------------------
# Step 1: Define the transformation logic (backend-agnostic)
# -------------------------------------------------------------------

def compute_customer_scorecard(orders_table):
    """
    Given an orders table, return a customer scorecard expression.
    Works on any backend that supports the Ibis expressions used here.
    """
    t = orders_table
    return (
        t.group_by("customer_id")
        .aggregate(
            lifetime_orders=t.order_id.count(),
            lifetime_revenue=t.amount.sum().round(2),
            avg_order_value=t.amount.mean().round(2),
            shipped_orders=t.order_id.count(where=t.status == "shipped"),
            cancelled_orders=t.order_id.count(where=t.status == "cancelled"),
        )
        .mutate(
            # Derived column: fulfillment rate
            fulfillment_rate=lambda t: (
                (t.shipped_orders / t.lifetime_orders * 100).round(1)
            )
        )
        .order_by(ibis.desc("lifetime_revenue"))
    )

def tag_customer_tier(scorecard_expr):
    """Apply a tier label based on lifetime revenue."""
    t = scorecard_expr
    return t.mutate(
        tier=ibis.case()
        .when(t.lifetime_revenue >= 1000, "Platinum")
        .when(t.lifetime_revenue >= 500, "Gold")
        .when(t.lifetime_revenue >= 200, "Silver")
        .else_("Bronze")
        .end()
    )

# -------------------------------------------------------------------
# Step 2: Sample data (in production this would be a real table)
# -------------------------------------------------------------------

ORDERS = {
    "order_id": list(range(1, 16)),
    "customer_id": [1, 2, 3, 1, 2, 3, 4, 1, 2, 4, 3, 1, 4, 2, 3],
    "amount": [
        250.0, 89.99, 410.50, 130.0, 75.25, 310.0,
        95.0, 480.0, 210.0, 55.0, 175.0, 320.0, 440.0, 35.0, 290.0
    ],
    "status": [
        "shipped", "pending", "shipped", "shipped", "shipped", "cancelled",
        "shipped", "shipped", "shipped", "pending", "shipped", "shipped",
        "shipped", "cancelled", "shipped",
    ],
}

# -------------------------------------------------------------------
# Step 3: Run on Backend A (in-memory DuckDB -- fast dev iteration)
# -------------------------------------------------------------------

con_dev = ibis.duckdb.connect()
orders_dev = ibis.memtable(ORDERS)
scorecard = compute_customer_scorecard(orders_dev)
tiered = tag_customer_tier(scorecard)

print("=== Dev Backend (in-memory DuckDB) ===")
print(tiered.to_pandas().to_string(index=False))

# -------------------------------------------------------------------
# Step 4: Run identical logic on Backend B (file-based DuckDB)
# -------------------------------------------------------------------

con_prod = ibis.duckdb.connect("warehouse.ddb")
con_prod.create_table("orders", ibis.memtable(ORDERS), overwrite=True)
orders_prod = con_prod.table("orders")
scorecard_prod = compute_customer_scorecard(orders_prod)
tiered_prod = tag_customer_tier(scorecard_prod)

print("\n=== Prod Backend (file DuckDB) ===")
print(tiered_prod.to_pandas().to_string(index=False))

# Clean up the demo file
import os
os.remove("warehouse.ddb")

=== Dev Backend (in-memory DuckDB) ===
 customer_id  lifetime_orders  lifetime_revenue  avg_order_value  shipped_orders  cancelled_orders  fulfillment_rate      tier
           1                4            1180.0           295.00               4                 0             100.0  Platinum
           3                5            1185.5           237.10               4                 1              80.0  Platinum
           2                5             410.24            82.05               3                 1              60.0      Gold
           4                3             590.0           196.67               2                 0              66.7      Gold

=== Prod Backend (file DuckDB) ===
 customer_id  lifetime_orders  lifetime_revenue  avg_order_value  shipped_orders  cancelled_orders  fulfillment_rate      tier
           1                4            1180.0           295.00               4                 0             100.0  Platinum
           3                5            1185.5           237.10               4                 1              80.0  Platinum
           2                5             410.24            82.05               3                 1              60.0      Gold
           4                3             590.0           196.67               2                 0              66.7      Gold

The identical results from both backends confirm the portability. To run this against a real BigQuery or Snowflake warehouse, replace ibis.duckdb.connect() with ibis.bigquery.connect(project="...", dataset="...") or ibis.snowflake.connect(...), and replace ibis.memtable(ORDERS) with con.table("orders") pointing at your existing table. The compute_customer_scorecard() and tag_customer_tier() functions require zero changes.

Debug Dee giving thumbs-up in front of two matching terminal screens, cartoon style — Same result, different backend. Your migration risk just went from weeks of rewriting to one line.

Frequently Asked Questions

When should I use Ibis instead of pandas?

Use Ibis when your data lives in a database or data warehouse and you want to keep computation there instead of pulling everything into memory. If you have a 500MB CSV file and pandas handles it comfortably, you do not need Ibis. But if your data is in BigQuery, Snowflake, or a DuckDB file measured in gigabytes, Ibis lets you filter, aggregate, and join at the database layer before materializing the result with .to_pandas(). Ibis also makes sense when you need the same transformation logic to run against different databases in different environments — development, staging, and production with different backends.

What happens if I use an operation the backend does not support?

Ibis raises a com.ibis_framework.common.exceptions.OperationNotDefinedError or a backend-specific TranslationError at expression evaluation time. This is intentional — Ibis tells you that your expression cannot be compiled for that backend rather than silently computing wrong results. The practical fix is to either find the Ibis equivalent that the backend supports, or to pull the data into pandas first with a simpler query and finish the computation locally. The ibis.options.interactive = True setting makes Ibis eager (executes immediately), which can make these errors appear closer to the problem line during development.

What is `ibis.options.interactive` and when should I use it?

Setting ibis.options.interactive = True turns Ibis into an eager evaluator — expressions execute and display results immediately, similar to how a pandas DataFrame prints when you type it in a Jupyter notebook. This is very convenient for exploration and debugging because you do not need to call .to_pandas() constantly. Turn it off (ibis.options.interactive = False) in production pipelines, where you want full control over when queries execute and want to compose complex expressions before triggering any I/O.

Does Ibis add significant overhead compared to writing SQL directly?

The overhead from Ibis expression compilation is negligible — typically under one millisecond. The compiled SQL is then sent to the backend exactly as if you had written it by hand, so query performance is determined entirely by the backend engine and your expression logic, not by Ibis itself. In practice, Ibis often produces better SQL than hand-written queries because it consistently generates clean, optimizer-friendly SQL patterns without the human tendency to write multi-step subqueries or redundant intermediate steps.

Can I mix raw SQL with Ibis expressions?

Yes — Ibis provides ibis.expr.types.relations.Table.sql() and the con.sql() method to incorporate raw SQL into an Ibis workflow. Call con.sql("SELECT ... FROM ...") to run a raw SQL string and get back an Ibis Table expression you can then chain further Ibis operations on. This escape hatch is useful for backend-specific SQL features that Ibis does not expose through its expression API, or for migrating existing SQL queries incrementally — you can wrap the raw SQL today and gradually replace sections with portable Ibis expressions over time.

What are Ibis selectors and when do I need them?

Ibis selectors (imported as import ibis.selectors as s) let you select columns by type or pattern rather than by name. For example, s.numeric() selects all numeric columns, s.string() selects all string columns, and s.matches("revenue") selects columns whose names match a regex pattern. This is useful when you are writing generic transformations that should apply to all columns of a certain type without hardcoding column names — common in data cleaning pipelines, feature engineering, and schema-flexible ETL workflows.

Conclusion

Ibis gives you a portable DataFrame API that compiles to SQL for more than 20 backends. The key patterns to keep in your toolkit are: ibis.memtable() for quick in-memory development, .filter().mutate().group_by().aggregate() for building transformations, ibis.to_sql() for inspecting the generated SQL, and the “expression first, connection second” function pattern for genuinely backend-agnostic code. The lazy evaluation model means your expression logic composes without any I/O until you call .to_pandas(), .to_pyarrow(), or .execute().

The real-life example in this tutorial is a solid foundation for a production analytics pipeline. You can extend it by adding more derived metrics to compute_customer_scorecard(), connecting to a real Postgres or BigQuery backend instead of DuckDB, writing the result back with con.create_table("scorecard", tiered), or scheduling the pipeline to run daily and append results to a history table. The transformation functions stay identical across all of these changes.

The official Ibis documentation at ibis-project.org covers every supported backend, the full expression API reference, and backend-specific features. The Expressions guide is particularly useful once you are comfortable with the basics and want to explore window functions, user-defined functions, and the full selector API.

« Older Entries

Async Await Code Example in Python

In the previous section we created an asynchronous version manually. Here’s the same outcome but written with the async await syntax. As you’ll notice it is very similar to the original synchronous version:

import time, datetime, time
import asyncio

import time, datetime, timeit

customer_queue = [ "C1", "C2", "C3"  ]

def get_next_customer():
    return customer_queue.pop(0)    #Get the first customer from list

async def cook_hamburger(customer):     
    start_customer_timer = timeit.default_timer()
    print( f"[{customer}]: Start cooking hamberger for customer")
    await asyncio.sleep(2)   # Sleep but release control
    end_customer_timer = timeit.default_timer()
    print( f"[{customer}]: Finish cooking hamberger for customer.  Total {end_customer_timer-start_customer_timer} seconds\n")

async def run_shop():
    cooking_queue = []

    while customer_queue:
        curr_customer = get_next_customer()
        cooking_queue.append(  cook_hamburger(curr_customer)  )   #this returns a task only

    #cooking_queue[] has all the async tasks
    await asyncio.gather( *cooking_queue )      #Run all in parallel

def main():
    print('Hamburger Shop')
    start = timeit.default_timer()

    asyncio.run( run_shop() )           #Start the event loop

    stop = timeit.default_timer()
    print(f"** Total runtime: {stop-start} seconds ***")

if __name__ == '__main__':
    main()

Output as follows:

Let’s walk through the code:

Firstly, the async await is available from the library asyncio hence the import asyncio
There’s funny set of async keywords which precede the def run_shop() and the def cook_hamburger(customer) functions. In addition the run_shop() is no longer called directly, instead it is called with a asyncio.run( run_shop() ) function call. So here’s what is happening:
- The asyncio.run() function is the trigger for the so-called event loop. It continues to run forever until all the tasks given to it are completed. You must pass it a function with the async def... prefix hence why run_shop() has the async prefix
- In the async def run_shop() function call, the code iterates while there are customers in the queue to process, and then there’s a call to cook_hamburger(curr_customer) for each customer. A direct call to the customer does not actually call the function but instead creates a task to execute this. That is what the async tells the compiler – that when called directly, return a task.
- At the end of the function code in def run_shop() there’s a call to function await asyncio.gather( *cooking_queue). There’s a few things going on here:
  - The await keywords indicates that you need wait for the work to complete but python can do something else in the meantime
  - The call to gather() actually executes all the tasks given to it as a parameter collectively as a group and then returns the results sequentially (please note that the order of the tasks being executed may be random)
  - The *customer_queue simply expands the list into a list of parameter items. So for example if customer_queue[] == [ '1', '2', '3'] then the gather( *customer_queue) would be the same as gather( '1', '2', '3').
- When the await asyncio.gather( *customer_queue ) is called, the await keyword releases control to any activities that are pending and one of them would be to the calls to function cook_hamburger() which was added to the customer_queue list. Hence calls to cook_hamburger() would be triggered.
- Within cook_hamburger() there is also an await asyncio.sleep(2). This simply waits for 2 seconds, however, it does not force the program to wait for the 2 seconds to complete, instead the await keyword releases python to do something else in the meantime. This is similar to step 3 in Figure 2 where the chef/waiter puts the hamburger on the grill, but then doesn’t wait for the 2 second but instead does something else (i.e. serve the next customer)
The asyncio.run() are new keywords as part of python 3.7. In older versions of python you may see the following but it is the same as simply running asyncio.run( run_shop() ) :
- loop = asyncio.get_event_loop()
- loop.run_until_complete(run_shop())
- loop.close()
As you will notice, this is very similar to the synchronous code that covers Figure 1 above. This is the beauty of async/await

So remember, whenever there’s an await then that means python pauses at that point for that task to complete but then also releases python to do something else. That’s how the performance improvement occurs. In this example, the runtime of this is 2 seconds instead of the sequential 6 seconds!

Async Asynchronous Calling Another Async Function Code Example

Suppose you want t also call another async function once your first async function is completed – how do you go about this? Remember the rule, if you want to run something asynchronously, you have to use the await keyword, and that the function you’re calling has to be defined with async def ...

To continue with the restaurant theme, suppose that after the hamburger is cooked you ask an assistant to put the hamburger into a takeaway bag which takes 1 second. This is also another task that you need not ‘block’ and wait for it to complete. Hence, this action can be put into a function which is defined as an async. Here’s what the code can look like:

import time, datetime, time
import asyncio

customer_queue = [ "C1", "C2", "C3" ]

def get_next_customer():
    return customer_queue.pop(0)    #Get the first customer from list

async def cook_hamburger(customer):     
    start_customer_timer = timeit.default_timer()
    print( f"[{customer}]: Start cooking hamberger for customer")
    await asyncio.sleep(2)   # Sleep but release control
    end_customer_timer = timeit.default_timer()
    print( f"[{customer}]: Finish cooking hamberger for customer.  Total {end_customer_timer-start_customer_timer} seconds")
    await put_hamburger_in_takeaway_bag( customer )

async def put_hamburger_in_takeaway_bag( customer):
    start_customer_timer = timeit.default_timer()
    print( f"[{customer}]: Start packing hamberger")
    await asyncio.sleep(1)   # It takes 2 seconds to cook the hamburger
    end_customer_timer = timeit.default_timer()
    print( f"[{customer}]: Finish packing hamberger.  Total {end_customer_timer-start_customer_timer} seconds\n")

async def run_shop():
    cooking_queue = []

    while customer_queue:
        curr_customer = get_next_customer()
        cooking_queue.append( cook_hamburger(curr_customer) )   #Get each of the event loops
    await asyncio.gather( *cooking_queue )      #Run all in parallel

def main():
    print('Hamburger Shop')
    start = timeit.default_timer()
    asyncio.run( run_shop() )           #Start the event loop 
    stop = timeit.default_timer()
    print(f"** Total runtime: {stop-start} seconds ***")

if __name__ == '__main__':
    main()

The output would be:

See how once the hamburger is cooked (e.g. [C1]: Finish cooking hamburger for customer. Total 2.000924572115764 seconds), then immediately afterwards you have the [C1]: Start packing hamburger step but also gets called asynchronously.

Async Await Real World Example With Web Crawler in Python

One difficulty in learning Async / Await is that many examples provided simply provide the asyncio.sleep() as an example which is helpful to understand the concept, but not very helpful when you want to make something more useful. Let’s try a more complex example where you want to get some stock data from finance.yahoo.com and then, for that same stock, you also get the first 3 newspaper articles from news.google.com in the last 24 hours.

Now one thing you will realise is that await only works with functions that are defined as async. So you cannot call any function with await. Why? Well recall that when you call await you are expecting a function to return a task and not actually call the function, hence that function needs to be defined as async in order to tell python that it returns a task to be executed at the next available time.

Let’s see the synchronous version of the code:

import asyncio, requests, timeit
from bs4 import BeautifulSoup
from pygooglenews import GoogleNews

stock_list = [ "TSLA", "AAPL"]

def get_stock_price_data(stock):
    print(f"-- getting stock data for {stock}")
    data = {"stock":stock, "price_open":0, "price_close":0 }
    stock_page = requests.get( 'https://finance.yahoo.com/quote/' + stock, headers={'Cache-Control': 'no-cache',  "Pragma": "no-cache"})

    soup = BeautifulSoup(stock_page.text, 'html.parser')
    #<fin-streamer active="" class="Fw(b) Fz(36px) Mb(-4px) D(ib)" data-field="regularMarketPrice" data-pricehint="2" data-symbol="TSLA" data-test="qsp-price" data-trend="none" value="759.63">759.63</fin-streamer>
    data['price_close'] = soup.find('fin-streamer', attrs={"data-symbol":stock, "data-field":"regularMarketPrice"} ).text

    #<td class="Ta(end) Fw(600) Lh(14px)" data-test="OPEN-value">723.25</td>
    data['price_open'] = soup.find( attrs={"data-test":"OPEN-value"}).text

    return data

def get_recent_news(stock):
    print(f"-- getting news data for {stock}")
    gn = GoogleNews()
    search = gn.search(f"stocks {stock}", when = '24h')
    news = search['entries'][0:3]
    return news

def print_stock_update(stock, data, news):
    print(f"Stock:{ stock }")
    price_change = 0
    if int(float(data['price_open'])) != 0: price_change = round( 100 * ( float( data['price_close'])/float(data['price_open'])-1), 2)
    print(f"Open Price:{data['price_open']} Close Price:{data['price_close']} Change:{price_change}% ")
    print("Latest News:")
    for news_item in news:        
        print( f"{news_item.published}:{news_item.source.title} - {news_item.title}" )
    print("\n")

def process_stocks():
    for stock in stock_list:
        data = get_stock_price_data( stock )
        news=[]
        news = get_recent_news( stock )
        print_stock_update(stock, data, news)

if __name__ == '__main__':
    start_timer = timeit.default_timer()
    process_stocks()
    end_timer = timeit.default_timer()

    print(f"** Total runtime: {end_timer-start_timer} seconds ***")

Output as follows:

So what’s happening here. Well, you are looping through two stocks TSLA and AAPL, and for each stock the following happens sequentially:

A call to data = get_stock_price_data( stock ) occurs in order to make a call to requests.get( 'https://finance.yahoo.com/quote/' + stock) to get the HTML page for the TSLA stock. Effectively, this page: https://finance.yahoo.com/quote/TSLA
Next we use BeautifulSoup() in order to find the HTML snippet that contains the stock price data for the opening price and the closing price:

After the call to yahoo is complete, then there’s a call to news = get_recent_news( stock ) which uses the module pygooglenews to get the latest google news. In fact we have used this function in our previous Twitter Bot article.
Once this is all done, that output is printed out with the call to print_stock_update(stock, data, news)

Clearly this could be called asynchronously as we are looping each time for each stock, and then also the call to get the stock data is independent to getting the news data. However, one thing has to happen sequentially is the print_stock_update(stock, data, news) which has to wait for both the async calls to complete.

One wait to try is to simply call the website download with:

stock_page = await requests.get( 'https://finance.yahoo.com/quote/' + stock, headers={'Cache-Control': 'no-cache',  "Pragma": "no-cache"})

However, you will get the following error:

The reason is, as you may have guessed, is that the requests.get() is not created with the async def... construct and hence cannot be called asynchronously.

What you can do however is to use another ‘get’ web page module called httpx. This function is defined with async def... and can be called similar to requests. That same line would be re-written as:

import httpx
#....

async def get_stock_price_data(stock):
    print(f"-- stock data:getting stock data for {stock}")
    data = {"stock":stock, "price_open":0, "price_close":0 }

    #*** instead of requests.get('https://finance.yahoo.com/quote/' + stock)) ****
    client = httpx.AsyncClient() 
    stock_page = await client.get( 'https://finance.yahoo.com/quote/' + stock)

    soup = BeautifulSoup(stock_page.text, 'html.parser')
    #<fin-streamer active="" class="Fw(b) Fz(36px) Mb(-4px) D(ib)" data-field="regularMarketPrice" data-pricehint="2" data-symbol="TSLA" data-test="qsp-price" data-trend="none" value="759.63">759.63</fin-streamer>
    data['price_close'] = soup.find('fin-streamer', attrs={"data-symbol":stock, "data-field":"regularMarketPrice"} ).text

    #<td class="Ta(end) Fw(600) Lh(14px)" data-test="OPEN-value">723.25</td>
    data['price_open'] = soup.find( attrs={"data-test":"OPEN-value"}).text
    print(f"-- stock data:done {stock}")
    return data

Ok, that works well. However, but what about the GoogleNews() code. There is no such async version of this function, so how can this be called asynchronously? Well for this, you can actually wrap it around a new thread. A ‘thread’ is way to run a piece of code under the same CPU process but in a parallel. It warrants a whole separate article but for now you can think of it as finding a separate space to execute this independent of the current execution path. However, to execute this in a separate thread, there’s a bit more involved.

The code looks like the following:

### Original Version
def get_recent_news(stock):
    print(f"-- stock news:getting stock data for {stock}")
    gn = GoogleNews()
    search = gn.search(f"stocks {stock}", '24h') #Slow code to run asynchronously
    news = search['entries'][0:3]
    print(f"-- stock news:done {stock}")
    return news

### Asynchronous Version
async def get_recent_news(stock):
    print(f"-- stock news:getting stock data for {stock}")
    gn = GoogleNews()
    search = await asyncio.get_event_loop().run_in_executor( None, gn.search, f"stocks {stock}", '24h')
    news = search['entries'][0:3]
    print(f"-- stock news:done {stock}")
    return news

Here what’s happening is that firstly we are using the await keyword to call the gn.search() function which is now being called through this asyncio.get_event_loop().run_in_executor( .. ) function call. What’s happening here is that we are asking the asyncio module to get access to the event loop (that piece of code that continuously checks for tasks to be done) and then to run in a separate thread. The way it is called is that the parameters must be passed in separate to the function call and hence why the parameters are to be passed in after the function name itself. You will also notice that the whole function can now be defined as async def get_recent_news(stock)

How To Mix Asynchronous And Synchronous Code With Await Async in Python

Now the final problem to be solved is how do we call the two functions of get_stock_price_data( stock ) and get_recent_news(stock) to be run asynchronously, but then wait for both to finish, and THEN run the print. This is where these steps should all be grouped under one function. This is the trick to mix asynchronous and synchronous code.

In order to run a group of tasks in parallel as a group you use asyncio.gather(). However, if you want to execute a synchronous function when ALL tasks that were given to asyncio.gather() is complete, then you should wrap it in another asyncio.gather()

async def process_stock_batch(stock):
    (data, news) = await asyncio.gather( get_stock_price_data( stock ), get_recent_news(stock)  )
    print('-- print:request printing')
    print_stock_update(stock, data, news) 
    print('-- print:done')

async def process_stocks():
    run_stock_list = []
    for stock in stock_list:
        run_stock_list.append(   process_stock_batch(stock) )
    await asyncio.gather( *run_stock_list )

Before we solve it for the real world examples, lets show a simpler example. Suppose we had the following example:

import asyncio, timeit

async def get_web_data_A(index):
    await asyncio.sleep(1)
    print(f"Get Web Data-A[{index}] - sleep 1 second")
        
async def get_web_data_B(index):
    await asyncio.sleep(1)
    print(f"Get Web Data-B[{index}] - sleep 1 second")

async def process(index, start_timer):
    await asyncio.gather( get_web_data_A(index), get_web_data_B(index) )
    print(f"Calculate [{index}] - Elapsed time:[{timeit.default_timer()-start_timer}]")

async def run_all():
    start_timer = timeit.default_timer()
    for index in range(0,2):
        await process(index, start_timer)

if __name__ == '__main__':
    asyncio.run( run_all() )

This has the following output:

What is encouraging with this code, is that even though the call to get_web_data_A() and get_web_data_B() both sleep for 1 second, since they were doing that asynchronously, then the total runtime is still just a little over 1 second. This can be shown by the Calculate [0]... output. However, the problem is that the code still iterates each index sequentially, meaning, that index 0 is processed completely first, and once that’s done, then index 1 is processed. What we want instead is to run all the slow get_web_data_A() and get_web_data_B() first, and then run the code to calculate afterwards. This is where you need to first create the tasks for ALL the iterations, and then call gather() on all the tasks. See the following code:

import asyncio, timeit

async def get_web_data_A(index):
    await asyncio.sleep(1)
    print(f"Get Web Data-A[{index}] - sleep 1 second")
        
async def get_web_data_B(index):
    await asyncio.sleep(1)
    print(f"Get Web Data-B[{index}] - sleep 1 second")

async def process(index, start_timer):
    await asyncio.gather( get_web_data_A(index), get_web_data_B(index) )
    print(f"Calculate [{index}] - Elapsed time:[{timeit.default_timer()-start_timer}]")

async def run_all_2():
    start_timer = timeit.default_timer()
    task_queue = []
    for index in range(0,2):
        task_queue.append( process(index, start_timer) )
    await asyncio.gather( *task_queue )

if __name__ == '__main__':
    asyncio.run( run_all_2() )

Here, in the function async def run_all_2() when we loop, we do not call the blocking code await asyncio.gather... inside the for loop. Instead, we are adding all the tasks to call process(..) into a list called task_queue[], and then at the end of the for loop we are calling await asyncio.gather( *task_queue ) on all tasks in one go. Hence, the output is as follows:

You’ll notice that ALL the get_web_data_A() and get_web_data_B() are being called asynchronously, and then the calculate function is called on all the available data. Hence, the elapsed time for all the iterations is only 1 second, compared to the previous 2 seconds.

So what does this mean for our real world example for getting stock data from Yahoo and then calling Google News asynchronously, and then only printing the data once both are done? Well, the same principle applies. The code is as follows:

import asyncio, httpx, timeit
from bs4 import BeautifulSoup
from pygooglenews import GoogleNews

stock_list = [ "TSLA", "AAPL"]

async def get_stock_price_data(stock):
    print(f"-- stock data:getting stock data for {stock}")
    data = {"stock":stock, "price_open":0, "price_close":0 }

    client = httpx.AsyncClient()
    stock_page = await client.get( 'https://finance.yahoo.com/quote/' + stock)

    soup = BeautifulSoup(stock_page.text, 'html.parser')
    #<fin-streamer active="" class="Fw(b) Fz(36px) Mb(-4px) D(ib)" data-field="regularMarketPrice" data-pricehint="2" data-symbol="TSLA" data-test="qsp-price" data-trend="none" value="759.63">759.63</fin-streamer>
    data['price_close'] = soup.find('fin-streamer', attrs={"data-symbol":stock, "data-field":"regularMarketPrice"} ).text

    #<td class="Ta(end) Fw(600) Lh(14px)" data-test="OPEN-value">723.25</td>
    data['price_open'] = soup.find( attrs={"data-test":"OPEN-value"}).text
    print(f"-- stock data:done {stock}")
    return data

async def get_recent_news(stock):
    print(f"-- stock news:getting stock data for {stock}")
    gn = GoogleNews()
    search = await asyncio.get_event_loop().run_in_executor( None, gn.search, f"stocks {stock}", '24h')
    news = search['entries'][0:3]
    print(f"-- stock news:done {stock}")
    return news

def print_stock_update(stock, data, news):
    print('-- print:starting print')
    print(f"Stock:{ stock }")
    price_change = 0
    if int(float(data['price_open'])) != 0: price_change = round( 100 * ( float( data['price_close'])/float(data['price_open'])-1), 2)
    print(f"Open Price:{data['price_open']} Close Price:{data['price_close']} Change:{price_change}% ")
    print("Latest News:")
    for news_item in news:        
        print( f"{news_item.published}:{news_item.source.title} - {news_item.title}" )

    print("\n")

async def process_stock_batch(stock):
    (data, news) = await asyncio.gather( get_stock_price_data( stock ), get_recent_news(stock)  )
    print('-- print:request printing')
    print_stock_update(stock, data, news) 
    print('-- print:done')

async def process_stocks():
    run_stock_list = []
    for stock in stock_list:
        run_stock_list.append(   process_stock_batch(stock) )
    await asyncio.gather( *run_stock_list )

if __name__ == '__main__':
    start_timer = timeit.default_timer()
    asyncio.run( process_stocks() )
    end_timer = timeit.default_timer()

    print(f"** Total runtime: {end_timer-start_timer} seconds ***")

The key bit of code is in the async def process_stocks() which now iterates over each of the stocks, creates tasks, and then calls await asyncio.gather( *run_stock_list ) on all the stocks in one go, and then in the function process_stock_batch(stock) we have the asynchronous call to (data, news) = await asyncio.gather( get_stock_price_data( stock ), and then the synchronous call to print_stock_update(stock, data, news) once both web data is complete.

Conclusion

The await and async function is an incredibly useful feature of python which takes a bit of getting used to in order to understand the concept, but once you’ve got the hang of it, it can be incredibly useful to get an improve of the performance of your code by leveraging idle time where you are waiting for a task to complete. Remember to be sure about the sequencing and being mindful of whether you care to have a follow-up activity once that task is completed, or you can simply continue to execute.

This not easy to grasp as a beginner, but follow the example code above, and if you get stuck feel free to reach out through our email list below.

How To Use Ibis for Portable DataFrames Across SQL Backends

by Pubs | Aug 5, 2026 | Data Processing

Intermediate

Ibis for Portable DataFrames: Quick Example

Here is a complete, runnable Ibis example that loads data into DuckDB, runs a query, and prints the result — all without writing a single line of SQL:

# quick_ibis.py
import ibis
import ibis.selectors as s

# Connect to an in-memory DuckDB instance (no file needed)
con = ibis.duckdb.connect()

# Create a table from a Python dict (simulates loading real data)
con.create_table(
    "sales",
    ibis.memtable({
        "region": ["North", "South", "North", "East", "South", "East"],
        "product": ["Widget", "Gadget", "Gadget", "Widget", "Widget", "Gadget"],
        "revenue": [1200, 850, 960, 1100, 730, 980],
        "units": [12, 8, 10, 11, 7, 9],
    }),
    overwrite=True,
)

# Build an expression -- nothing executes yet
sales = con.table("sales")

result = (
    sales
    .group_by(["region", "product"])
    .aggregate(
        total_revenue=sales.revenue.sum(),
        total_units=sales.units.sum(),
        avg_revenue=sales.revenue.mean().round(2),
    )
    .order_by(ibis.desc("total_revenue"))
)

# Execute and convert to pandas
df = result.to_pandas()
print(df)

  region  product  total_revenue  total_units  avg_revenue
0  North   Gadget           960           10       960.00
1  North   Widget          1200           12      1200.00
2   East   Gadget           980            9       980.00
3   East   Widget          1100           11      1100.00
4  South   Gadget           850            8       850.00
5  South   Widget           730            7       730.00

The sections below show how to connect to real backends, build more complex expressions, and switch between backends with a single line change.

What Is Ibis and Why Use It?

Library	Execution	Backends	API style	Best for
pandas	In-memory (NumPy)	1	Eager	Small data, local exploration
PySpark	Spark cluster	1	Lazy (Spark)	Hadoop ecosystem, batch ETL
SQLAlchemy	Any SQL DB	20+	ORM / Core SQL	App databases, ORM models
Polars	In-memory (Rust)	1	Lazy + Eager	Fast local analytics, CSV/Parquet
Ibis	Delegated to backend	20+	Lazy DataFrame	Portable analytics, multi-backend

Connecting to Backends

# connections.py
import ibis

# DuckDB -- in-memory (no file, no setup)
con_mem = ibis.duckdb.connect()

# DuckDB -- persistent file (data survives across sessions)
con_file = ibis.duckdb.connect("my_analytics.ddb")

# SQLite -- works with any .sqlite or .db file
con_sqlite = ibis.sqlite.connect("app_data.db")

# PostgreSQL -- requires psycopg2 installed
# con_pg = ibis.postgres.connect(
#     host="localhost", port=5432,
#     database="mydb", user="analyst", password="secret"
# )

# BigQuery -- requires google-cloud-bigquery installed
# con_bq = ibis.bigquery.connect(project="my-gcp-project", dataset="analytics")

# Print what tables exist in a connection
print("Tables in memory DuckDB:", con_mem.list_tables())
print("Tables in file DuckDB:", con_file.list_tables())

Tables in memory DuckDB: []
Tables in file DuckDB: []

Loading and Querying Data

# loading_data.py
import ibis
import tempfile, os, json

con = ibis.duckdb.connect()

# Pattern 1: memtable from a Python dict (great for tests and examples)
orders = ibis.memtable({
    "order_id": [1001, 1002, 1003, 1004, 1005],
    "customer": ["Alice", "Bob", "Alice", "Carol", "Bob"],
    "amount": [250.0, 89.99, 410.50, 130.0, 75.25],
    "status": ["shipped", "pending", "shipped", "cancelled", "shipped"],
})

# Pattern 2: create a real DuckDB table for repeated use
con.create_table("orders", orders, overwrite=True)

# Pattern 3: read back from the connection as a Table expression
t = con.table("orders")
print("Schema:", t.schema())
print("Row count:", t.count().to_pandas())

# Pattern 4: read a Parquet file directly (DuckDB backend)
# parquet_table = con.read_parquet("data/sales_2025.parquet")

# Preview the first 3 rows
print(t.limit(3).to_pandas())

Schema: ibis.Schema {
  order_id  int64
  customer  string
  amount    float64
  status    string
}
Row count: 5

   order_id customer  amount    status
0      1001    Alice  250.00   shipped
1      1002      Bob   89.99   pending
2      1003    Alice  410.50   shipped

Filtering, Selecting, and Transforming

# filtering.py
import ibis

con = ibis.duckdb.connect()

orders = ibis.memtable({
    "order_id": [1001, 1002, 1003, 1004, 1005],
    "customer": ["Alice", "Bob", "Alice", "Carol", "Bob"],
    "amount": [250.0, 89.99, 410.50, 130.0, 75.25],
    "status": ["shipped", "pending", "shipped", "cancelled", "shipped"],
})

t = orders  # memtable is already an expression

# Filter: shipped orders over $100
shipped_large = t.filter([
    t.status == "shipped",
    t.amount > 100,
])

# Add a derived column: tax estimate at 10%
with_tax = shipped_large.mutate(
    tax=shipped_large.amount * 0.10,
    total=(shipped_large.amount * 1.10).round(2),
)

# Select only the columns we care about
result = with_tax.select("order_id", "customer", "amount", "tax", "total")

print(result.to_pandas())

   order_id customer  amount    tax    total
0      1001    Alice  250.00   25.0   275.00
1      1003    Alice  410.50   41.05  451.55

Grouping and Aggregating

# aggregation.py
import ibis

orders = ibis.memtable({
    "order_id": [1001, 1002, 1003, 1004, 1005, 1006, 1007],
    "customer": ["Alice", "Bob", "Alice", "Carol", "Bob", "Alice", "Carol"],
    "amount": [250.0, 89.99, 410.50, 130.0, 75.25, 195.0, 310.0],
    "status": ["shipped", "pending", "shipped", "cancelled", "shipped", "shipped", "shipped"],
})

# Group by customer, aggregate multiple metrics
summary = (
    orders
    .group_by("customer")
    .aggregate(
        total_orders=orders.order_id.count(),
        total_revenue=orders.amount.sum().round(2),
        avg_order=orders.amount.mean().round(2),
        shipped_revenue=orders.amount.sum(where=orders.status == "shipped").round(2),
    )
    .order_by(ibis.desc("total_revenue"))
)

print(summary.to_pandas())

  customer  total_orders  total_revenue  avg_order  shipped_revenue
0    Alice             3         855.50     285.17           855.50
1    Carol             2         440.00     220.00           310.00
2      Bob             2         165.24      82.62            75.25

Joining Tables

# joins.py
import ibis

orders = ibis.memtable({
    "order_id": [1001, 1002, 1003, 1004],
    "customer_id": [1, 2, 1, 3],
    "amount": [250.0, 89.99, 410.50, 130.0],
    "status": ["shipped", "pending", "shipped", "shipped"],
})

customers = ibis.memtable({
    "customer_id": [1, 2, 3, 4],
    "name": ["Alice", "Bob", "Carol", "Dave"],
    "tier": ["gold", "silver", "gold", "bronze"],
})

# Inner join: only orders with a matching customer
result = (
    orders
    .join(customers, orders.customer_id == customers.customer_id)
    .select(
        "order_id",
        "name",
        "tier",
        "amount",
        "status",
    )
    .filter(lambda t: t.tier == "gold")  # filter after join
    .order_by("order_id")
)

print(result.to_pandas())

   order_id   name  tier  amount   status
0      1001  Alice  gold  250.00  shipped
1      1003  Alice  gold  410.50  shipped

Switching Backends

# switching_backends.py
import ibis

# Define the expression independently of any backend
def build_summary_expr(orders_table):
    """Returns the aggregation expression -- no backend reference."""
    return (
        orders_table
        .filter(orders_table.status == "shipped")
        .group_by("customer")
        .aggregate(
            order_count=orders_table.order_id.count(),
            total_revenue=orders_table.amount.sum().round(2),
        )
        .order_by(ibis.desc("total_revenue"))
    )

SAMPLE_DATA = {
    "order_id": [1001, 1002, 1003, 1004, 1005],
    "customer": ["Alice", "Bob", "Alice", "Carol", "Bob"],
    "amount": [250.0, 89.99, 410.50, 130.0, 75.25],
    "status": ["shipped", "pending", "shipped", "cancelled", "shipped"],
}

# Backend A: DuckDB in-memory (development / testing)
con_dev = ibis.duckdb.connect()
orders_dev = ibis.memtable(SAMPLE_DATA)
expr_dev = build_summary_expr(orders_dev)
print("DuckDB result:")
print(expr_dev.to_pandas())

# Backend B: DuckDB file (local persistence)
con_prod = ibis.duckdb.connect("analytics.ddb")
con_prod.create_table("orders", ibis.memtable(SAMPLE_DATA), overwrite=True)
orders_prod = con_prod.table("orders")
expr_prod = build_summary_expr(orders_prod)
print("\nDuckDB file result (same expression):")
print(expr_prod.to_pandas())

DuckDB result:
  customer  order_count  total_revenue
0    Alice            2         660.50
1      Bob            1          75.25

DuckDB file result (same expression):
  customer  order_count  total_revenue
0    Alice            2         660.50
1      Bob            1          75.25

Understanding Lazy Evaluation and `to_pandas()`

# lazy_execution.py
import ibis

con = ibis.duckdb.connect()
orders = ibis.memtable({
    "order_id": list(range(1, 11)),
    "customer": ["Alice", "Bob"] * 5,
    "amount": [100.0 + i * 15 for i in range(10)],
    "status": ["shipped", "pending"] * 5,
})

# Build a complex expression -- zero database calls so far
shipped = orders.filter(orders.status == "shipped")
enriched = shipped.mutate(discounted=shipped.amount * 0.95)
summary = enriched.group_by("customer").aggregate(
    total=enriched.amount.sum(),
    discounted_total=enriched.discounted.sum().round(2),
)

# Print the SQL Ibis will run (useful for debugging and auditing)
print("SQL Ibis will execute:")
print(ibis.to_sql(summary))
print()

# This triggers the single database call
df = summary.to_pandas()
print("Result:")
print(df)

SQL Ibis will execute:
SELECT
  "customer",
  SUM("amount") AS "total",
  ROUND(SUM("amount" * 0.95), 2) AS "discounted_total"
FROM (
  SELECT
    "customer",
    "amount",
    "amount" * 0.95 AS "discounted"
  FROM "ibis_memtable_..."
  WHERE "status" = 'shipped'
) t0
GROUP BY "customer"

Result:
  customer  total  discounted_total
0    Alice  375.0           356.25
1      Bob  450.0           427.50

Real-Life Example: Multi-Backend Sales Analysis Pipeline

# sales_pipeline.py
import ibis
import pandas as pd

# -------------------------------------------------------------------
# Step 1: Define the transformation logic (backend-agnostic)
# -------------------------------------------------------------------

def compute_customer_scorecard(orders_table):
    """
    Given an orders table, return a customer scorecard expression.
    Works on any backend that supports the Ibis expressions used here.
    """
    t = orders_table
    return (
        t.group_by("customer_id")
        .aggregate(
            lifetime_orders=t.order_id.count(),
            lifetime_revenue=t.amount.sum().round(2),
            avg_order_value=t.amount.mean().round(2),
            shipped_orders=t.order_id.count(where=t.status == "shipped"),
            cancelled_orders=t.order_id.count(where=t.status == "cancelled"),
        )
        .mutate(
            # Derived column: fulfillment rate
            fulfillment_rate=lambda t: (
                (t.shipped_orders / t.lifetime_orders * 100).round(1)
            )
        )
        .order_by(ibis.desc("lifetime_revenue"))
    )

def tag_customer_tier(scorecard_expr):
    """Apply a tier label based on lifetime revenue."""
    t = scorecard_expr
    return t.mutate(
        tier=ibis.case()
        .when(t.lifetime_revenue >= 1000, "Platinum")
        .when(t.lifetime_revenue >= 500, "Gold")
        .when(t.lifetime_revenue >= 200, "Silver")
        .else_("Bronze")
        .end()
    )

# -------------------------------------------------------------------
# Step 2: Sample data (in production this would be a real table)
# -------------------------------------------------------------------

ORDERS = {
    "order_id": list(range(1, 16)),
    "customer_id": [1, 2, 3, 1, 2, 3, 4, 1, 2, 4, 3, 1, 4, 2, 3],
    "amount": [
        250.0, 89.99, 410.50, 130.0, 75.25, 310.0,
        95.0, 480.0, 210.0, 55.0, 175.0, 320.0, 440.0, 35.0, 290.0
    ],
    "status": [
        "shipped", "pending", "shipped", "shipped", "shipped", "cancelled",
        "shipped", "shipped", "shipped", "pending", "shipped", "shipped",
        "shipped", "cancelled", "shipped",
    ],
}

# -------------------------------------------------------------------
# Step 3: Run on Backend A (in-memory DuckDB -- fast dev iteration)
# -------------------------------------------------------------------

con_dev = ibis.duckdb.connect()
orders_dev = ibis.memtable(ORDERS)
scorecard = compute_customer_scorecard(orders_dev)
tiered = tag_customer_tier(scorecard)

print("=== Dev Backend (in-memory DuckDB) ===")
print(tiered.to_pandas().to_string(index=False))

# -------------------------------------------------------------------
# Step 4: Run identical logic on Backend B (file-based DuckDB)
# -------------------------------------------------------------------

con_prod = ibis.duckdb.connect("warehouse.ddb")
con_prod.create_table("orders", ibis.memtable(ORDERS), overwrite=True)
orders_prod = con_prod.table("orders")
scorecard_prod = compute_customer_scorecard(orders_prod)
tiered_prod = tag_customer_tier(scorecard_prod)

print("\n=== Prod Backend (file DuckDB) ===")
print(tiered_prod.to_pandas().to_string(index=False))

# Clean up the demo file
import os
os.remove("warehouse.ddb")

=== Dev Backend (in-memory DuckDB) ===
 customer_id  lifetime_orders  lifetime_revenue  avg_order_value  shipped_orders  cancelled_orders  fulfillment_rate      tier
           1                4            1180.0           295.00               4                 0             100.0  Platinum
           3                5            1185.5           237.10               4                 1              80.0  Platinum
           2                5             410.24            82.05               3                 1              60.0      Gold
           4                3             590.0           196.67               2                 0              66.7      Gold

=== Prod Backend (file DuckDB) ===
 customer_id  lifetime_orders  lifetime_revenue  avg_order_value  shipped_orders  cancelled_orders  fulfillment_rate      tier
           1                4            1180.0           295.00               4                 0             100.0  Platinum
           3                5            1185.5           237.10               4                 1              80.0  Platinum
           2                5             410.24            82.05               3                 1              60.0      Gold
           4                3             590.0           196.67               2                 0              66.7      Gold

Frequently Asked Questions

When should I use Ibis instead of pandas?

What happens if I use an operation the backend does not support?

What is `ibis.options.interactive` and when should I use it?

Does Ibis add significant overhead compared to writing SQL directly?

Can I mix raw SQL with Ibis expressions?

What are Ibis selectors and when do I need them?

Conclusion

« Older Entries

How to Build a Twitter Bot with Python and Twitter API v2

Easy guide for data storage options in Python

Further Reading: For more details, see the Python asyncio documentation.

Frequently Asked Questions

What is async/await in Python?

async def defines a coroutine function and await pauses execution until an asynchronous operation completes. This enables concurrent I/O operations without threading, using the asyncio event loop.

When should I use async/await instead of threading?

Use async/await for I/O-bound tasks like network requests and database queries with many concurrent connections. Use threading for CPU-bound tasks or libraries that do not support async.

How do I run multiple async tasks concurrently?

Use asyncio.gather(task1(), task2()) to run multiple coroutines concurrently. Use asyncio.create_task() to schedule without immediately waiting.

What does ‘coroutine was never awaited’ mean?

You called an async function without await. Async functions return coroutine objects that must be awaited. Add await before the call or use asyncio.run() from synchronous code.

Can I mix synchronous and asynchronous code?

Yes. Use asyncio.run() to call async from sync. Use loop.run_in_executor() to run blocking functions inside async code without blocking the event loop.

Continue Learning Python

Tutorials you might also find useful:

Post Views: 1,153

Python Await Async Tutorial with Real Examples and Simple Explanations

What is async await in Python?

How does async await work in Python?

Cooking Hamburgers Asynchronously and coding the event loop manually

How To Use Ibis for Portable DataFrames Across SQL Backends

Ibis for Portable DataFrames: Quick Example

What Is Ibis and Why Use It?

Connecting to Backends

Loading and Querying Data

Filtering, Selecting, and Transforming

Grouping and Aggregating

Joining Tables

Switching Backends

Understanding Lazy Evaluation and to_pandas()

Real-Life Example: Multi-Backend Sales Analysis Pipeline

Frequently Asked Questions

When should I use Ibis instead of pandas?

What happens if I use an operation the backend does not support?

What is ibis.options.interactive and when should I use it?

Does Ibis add significant overhead compared to writing SQL directly?

Can I mix raw SQL with Ibis expressions?

What are Ibis selectors and when do I need them?

Conclusion

Related Articles

Async Await Code Example in Python

Async Asynchronous Calling Another Async Function Code Example

Async Await Real World Example With Web Crawler in Python

How To Mix Asynchronous And Synchronous Code With Await Async in Python

Conclusion

How To Use Ibis for Portable DataFrames Across SQL Backends

Ibis for Portable DataFrames: Quick Example

What Is Ibis and Why Use It?

Connecting to Backends

Loading and Querying Data

Filtering, Selecting, and Transforming

Grouping and Aggregating

Joining Tables

Switching Backends

Understanding Lazy Evaluation and to_pandas()

Real-Life Example: Multi-Backend Sales Analysis Pipeline

Frequently Asked Questions

When should I use Ibis instead of pandas?

What happens if I use an operation the backend does not support?

What is ibis.options.interactive and when should I use it?

Does Ibis add significant overhead compared to writing SQL directly?

Can I mix raw SQL with Ibis expressions?

What are Ibis selectors and when do I need them?

Conclusion

Related Articles

Related Articles

Frequently Asked Questions

What is async/await in Python?

When should I use async/await instead of threading?

How do I run multiple async tasks concurrently?

What does ‘coroutine was never awaited’ mean?

Can I mix synchronous and asynchronous code?

Continue Learning Python

Related Articles

Submit a Comment Cancel reply

Understanding Lazy Evaluation and `to_pandas()`

What is `ibis.options.interactive` and when should I use it?

Understanding Lazy Evaluation and `to_pandas()`

What is `ibis.options.interactive` and when should I use it?