Abstract

DuckDB is a fast, embedded analytical database that outstands in in-memory operations. It provides a SQL interface, making it easy for users with database querying experience.

Duckdb

DuckDB excels in handling large datasets efficiently due to its columnar storage and vectorized execution engine. It is well-suited for scenarios where you need a fast and embedded database solution within your Python application. Here are some key things you can do with it:

Execute SQL Queries

Directly run SQL queries using duckdb.sql within your Python code.
Efficiently work with in-memory databases, making DuckDB ideal for quick data exploration and analysis.
DuckDB provides a full-fledged SQL dialect, enabling you to perform various data manipulation and analysis tasks using familiar SQL syntax.
You can use duckdb.sql to execute queries against in-memory or disk-based databases created with DuckDB.

Leverage In-memory and Persistent Databases

Create in-memory databases through duckdb.connect() for immediate querying without external storage needs.
Connect to persistent databases on disk using the same duckdb.connect() function, specifying the database path.

Work with Various Data Formats

Read and write data from different file formats, including CSV, Parquet, and JSON, both locally and remotely (e.g., S3 buckets).

Build Complex Queries Incrementally

Construct SQL queries step-by-step by storing the results (relations) of previous queries into variables.
Use these stored relations in subsequent queries, allowing for modular and reusable code.

Utilize DuckDB’s SQL Dialect

Take advantage of DuckDB’s comprehensive SQL dialect, supporting various operations like joins, aggregations, window functions, and more.

Load and manipulate data

DuckDB supports reading and writing data from various file formats like CSV, Parquet, and JSON.
You can load data from these files into DuckDB tables for further processing and analysis.
DuckDB offers various functions for data manipulation, including filtering, sorting, aggregation, and joining tables.

Explore and analyze data

DuckDB allows you to perform exploratory data analysis (EDA) using its SQL capabilities.
You can write queries to calculate descriptive statistics, identify patterns, and gain insights from your data.

Prototype and experiment

DuckDB’s lightweight and in-process nature makes it ideal for rapid prototyping and experimentation.
You can quickly test and iterate on data analysis tasks without the overhead of setting up and managing a separate database server.

Environment settings

Show code

import numpy as np
import pandas as pd
import polars as pl
import duckdb as db

Creating tables

You can create in-memory or persistent tables using SQL syntax from Python

Beers table

Create table

Show code

db.sql('''
    CREATE TABLE IF NOT EXISTS beers (
        CodC integer,
        Package varchar(255),
        Capacity float,
        Stock integer
    )
''')

Insert data

Show code

db.sql('''
    INSERT INTO beers
    VALUES (1, 'Botella', 0.2, 3600),
        (2, 'Botella', 0.33, 1200),
        (3, 'Lata', 0.33, 2400),
        (4, 'Botella', 1, 288),
        (5, 'Barril', 60, 30)
''')

Retrieve data

Show code

# Showing in polars dataframe
db.sql('SELECT * FROM beers').df()

	CodC	Package	Capacity	Stock
0	1	Botella	0.20	3600
1	2	Botella	0.33	1200
2	3	Lata	0.33	2400
3	4	Botella	1.00	288
4	5	Barril	60.00	30

Bars table

Create table

Show code

db.sql('''
    CREATE TABLE IF NOT EXISTS bars (
        CodB integer,
        Name varchar(255),
        Cif varchar(255),
        Location varchar(255)
    )
''')

Insert data

Show code

db.sql('''
    INSERT INTO bars
    VALUES (1, 'Stop', '111111X', 'Villa Botijo'),
        (2, 'Las Vegas', '222222Y', 'Villa Botijo'),
        (3, 'Club Social', '', 'Las Ranas'),
        (4, 'Otra Ronda', '333333Z', 'La Esponja')
''')

Retrieve data

Show code

# showing in pandas dataframe
db.sql('SELECT * FROM bars').df()

	CodB	Name	Cif	Location
0	1	Stop	111111X	Villa Botijo
1	2	Las Vegas	222222Y	Villa Botijo
2	3	Club Social		Las Ranas
3	4	Otra Ronda	333333Z	La Esponja

Employees table

Create table

Show code

db.sql('''
    CREATE TABLE IF NOT EXISTS employees (
        CodE integer,
        Name varchar(255),
        Salary float
    )
''')

Insert data

Show code

db.sql('''
    INSERT INTO employees
    VALUES (1, 'John Doe', 120000),
        (2, 'Vicent Meren', 110000),
        (3, 'Tom Simpson', 100000)
''')

Retrieve data

Show code

db.sql('SELECT * FROM employees').df()

	CodE	Name	Salary
0	1	John Doe	120000.0
1	2	Vicent Meren	110000.0
2	3	Tom Simpson	100000.0

Delivery table

Create table

Show code

db.sql('''
    CREATE TABLE IF NOT EXISTS delivery (
        CodE integer,
        CodB integer,
        CodC integer,
        Delivery_date date,
        Quantity integer
    )
''')

Insert data

Show code

db.sql('''
    INSERT INTO delivery
    VALUES (1, 1, 1, '2005-10-21', 240),
        (1, 1, 2, '2005-10-21', 48),
        (1, 2, 3, '2005-10-22', 60),
        (1, 4, 5, '2005-10-22', 4),
        (2, 2, 3, '2005-10-23', 48),
        (2, 2, 5, '2005-10-23', 2),
        (2, 4, 1, '2005-10-24', 480),
        (2, 4, 2, '2005-10-24', 72),
        (3, 3, 3, '2005-10-24', 48),
        (3, 3, 4, '2005-10-25', 20)
''')

Retrieve data

Show code

db.sql('SELECT * FROM delivery').df()

	CodE	CodB	CodC	Delivery_date	Quantity
0	1	1	1	2005-10-21	240
1	1	1	2	2005-10-21	48
2	1	2	3	2005-10-22	60
3	1	4	5	2005-10-22	4
4	2	2	3	2005-10-23	48
5	2	2	5	2005-10-23	2
6	2	4	1	2005-10-24	480
7	2	4	2	2005-10-24	72
8	3	3	3	2005-10-24	48
9	3	3	4	2005-10-25	20

Queries

Also, you can execute SQL queries within python environment

Query 1

Obtain the name of the employees who delivered to the Stop bar during the week of October 17 to 23, 2005.

Show code

db.sql(
    '''
    SELECT r.Delivery_date
        ,e.Name
        ,b.Name
    FROM delivery AS r
    LEFT JOIN employees AS e ON r.CodE = e.CodE
    LEFT JOIN bars AS b ON r.CodB = b.CodB
    WHERE b.Name = 'Stop'
        AND r.Delivery_date BETWEEN '2005-10-17' AND '2005-10-23'
    '''
)

┌───────────────┬──────────┬─────────┐
│ Delivery_date │   Name   │  Name   │
│     date      │ varchar  │ varchar │
├───────────────┼──────────┼─────────┤
│ 2005-10-21    │ John Doe │ Stop    │
│ 2005-10-21    │ John Doe │ Stop    │
└───────────────┴──────────┴─────────┘

Query 2

Obtain the CIF and name of the bars to which bottle-type beer with a capacity of less than 1 liter has been distributed, ordered by location.

Show code

db.sql(
    '''
    SELECT b.Cif
        ,b.Name
        ,c.Package
        ,c.Capacity
        ,b.Location
    FROM delivery AS r
    LEFT JOIN bars AS b ON r.CodB = b.CodB
    LEFT JOIN beers AS c ON r.CodC = c.CodC
    WHERE c.Package = 'Botella'
        AND c.Capacity < 1.0
    ORDER BY b.Location
    '''
)

┌─────────┬────────────┬─────────┬──────────┬──────────────┐
│   Cif   │    Name    │ Package │ Capacity │   Location   │
│ varchar │  varchar   │ varchar │  float   │   varchar    │
├─────────┼────────────┼─────────┼──────────┼──────────────┤
│ 333333Z │ Otra Ronda │ Botella │      0.2 │ La Esponja   │
│ 333333Z │ Otra Ronda │ Botella │     0.33 │ La Esponja   │
│ 111111X │ Stop       │ Botella │      0.2 │ Villa Botijo │
│ 111111X │ Stop       │ Botella │     0.33 │ Villa Botijo │
└─────────┴────────────┴─────────┴──────────┴──────────────┘

Query 3

Obtain the deliveries (name of the bar, container and capacity of the drink, date and quantity) made by Prudencio Caminero.

Show code

db.sql(
    '''
    SELECT b.Name
        ,c.Package
        ,c.Capacity
        ,r.Delivery_date
        ,r.Quantity
        ,e.Name
        ,e.CodE
    FROM delivery AS r
    LEFT JOIN bars AS b ON r.CodB = b.CodB
    LEFT JOIN beers AS c ON r.CodC = c.CodC
    LEFT JOIN employees AS e ON e.CodE = r.CodE
    WHERE e.Name ILIKE '%doe%'
    '''
)

┌────────────┬─────────┬──────────┬───────────────┬──────────┬──────────┬───────┐
│    Name    │ Package │ Capacity │ Delivery_date │ Quantity │   Name   │ CodE  │
│  varchar   │ varchar │  float   │     date      │  int32   │ varchar  │ int32 │
├────────────┼─────────┼──────────┼───────────────┼──────────┼──────────┼───────┤
│ Stop       │ Botella │      0.2 │ 2005-10-21    │      240 │ John Doe │     1 │
│ Stop       │ Botella │     0.33 │ 2005-10-21    │       48 │ John Doe │     1 │
│ Las Vegas  │ Lata    │     0.33 │ 2005-10-22    │       60 │ John Doe │     1 │
│ Otra Ronda │ Barril  │     60.0 │ 2005-10-22    │        4 │ John Doe │     1 │
└────────────┴─────────┴──────────┴───────────────┴──────────┴──────────┴───────┘

Query 4

Obtain the bars to which bottle-type containers with a capacity of 0.2 or 0.33 have been distributed.

Show code

db.sql(
    '''
    SELECT b.Name
        ,c.Package
        ,c.Capacity
    FROM delivery AS r
    LEFT JOIN bars AS b ON r.CodB = b.CodB
    LEFT JOIN beers AS c ON r.CodC = c.CodC
    WHERE c.Package = 'Botella'
        AND c.Capacity IN (0.2, 0.33)
    '''
)

┌────────────┬─────────┬──────────┐
│    Name    │ Package │ Capacity │
│  varchar   │ varchar │  float   │
├────────────┼─────────┼──────────┤
│ Stop       │ Botella │      0.2 │
│ Stop       │ Botella │     0.33 │
│ Otra Ronda │ Botella │      0.2 │
│ Otra Ronda │ Botella │     0.33 │
└────────────┴─────────┴──────────┘

Query 5

Name of the employees who have distributed bottled beers to the “Stop” and “Las Vegas” bars.

Show code

db.sql(
    '''
    SELECT e.Name
        ,b.Name
        ,c.Package
    FROM delivery AS r
    LEFT JOIN bars AS b ON r.CodB = b.CodB
    LEFT JOIN beers AS c ON r.CodC = c.CodC
    LEFT JOIN employees AS e On e.CodE = r.CodE
    WHERE b.Name IN ('Stop', 'Las Vegas')
        AND c.Package = 'Botella'
    '''
)

┌──────────┬─────────┬─────────┐
│   Name   │  Name   │ Package │
│ varchar  │ varchar │ varchar │
├──────────┼─────────┼─────────┤
│ John Doe │ Stop    │ Botella │
│ John Doe │ Stop    │ Botella │
└──────────┴─────────┴─────────┘

Query 6

Obtain the name and number of trips that each employee has made outside of Villa Botijo.

Show code

db.sql(
    '''
    SELECT e.Name
        ,COUNT(b.Location) AS Travles
    FROM delivery AS r
    LEFT JOIN bars AS b ON r.CodB = b.CodB
    LEFT JOIN employees AS e On e.CodE = r.CodE
    WHERE b.Location <> 'Villa Botijo'
    GROUP BY 1
    '''
)

┌──────────────┬─────────┐
│     Name     │ Travles │
│   varchar    │  int64  │
├──────────────┼─────────┤
│ John Doe     │       1 │
│ Tom Simpson  │       2 │
│ Vicent Meren │       2 │
└──────────────┴─────────┘

Query 7

Obtain the name and location of the bar that has purchased the most liters of beer.

Show code

db.sql(
    '''
    SELECT b.Name
        ,b.Location
        ,MAX(r.Quantity) AS Liters
    FROM delivery AS r
    LEFT JOIN bars AS b ON r.CodB = b.CodB
    LEFT JOIN beers AS c ON r.CodC = c.CodC
    GROUP BY 1, 2
    ORDER BY 3 DESC
    '''
)

┌─────────────┬──────────────┬────────┐
│    Name     │   Location   │ Liters │
│   varchar   │   varchar    │ int32  │
├─────────────┼──────────────┼────────┤
│ Otra Ronda  │ La Esponja   │    480 │
│ Stop        │ Villa Botijo │    240 │
│ Las Vegas   │ Villa Botijo │     60 │
│ Club Social │ Las Ranas    │     48 │
└─────────────┴──────────────┴────────┘

Query 8

Obtain bars that have purchased all types of beer with bottle packaging and a capacity less than 1 liter.

Show code

db.sql(
    '''
    SELECT b.Name
        ,c.Package
        ,c.Capacity
    FROM delivery AS r
    LEFT JOIN bars AS b ON r.CodB = b.CodB
    LEFT JOIN beers AS c ON r.CodC = c.CodC
    WHERE c.Capacity IN (SELECT c.Capacity
                            FROM beers AS c
                            WHERE c.Package = 'Botella'
                                AND c.Capacity < 1.0)
    '''
)

┌─────────────┬─────────┬──────────┐
│    Name     │ Package │ Capacity │
│   varchar   │ varchar │  float   │
├─────────────┼─────────┼──────────┤
│ Stop        │ Botella │      0.2 │
│ Stop        │ Botella │     0.33 │
│ Las Vegas   │ Lata    │     0.33 │
│ Las Vegas   │ Lata    │     0.33 │
│ Otra Ronda  │ Botella │      0.2 │
│ Otra Ronda  │ Botella │     0.33 │
│ Club Social │ Lata    │     0.33 │
└─────────────┴─────────┴──────────┘

Query 9

Raise the salary of the employee who has worked the most days by 5%.

Show code

# modify database records
db.sql(
    '''
    -- Raise 5% salary of workers
    INSERT INTO employees (CodE, Name, Salary)
    VALUES (1, 'John Doe', 120000*1.05),
            (2, 'Vicent Meren', 110000*1.05)
    '''
)

Show code

# verify inserted record
db.sql(
    '''
    SELECT e.*
    FROM employees AS e
    '''
)

┌───────┬──────────────┬──────────┐
│ CodE  │     Name     │  Salary  │
│ int32 │   varchar    │  float   │
├───────┼──────────────┼──────────┤
│     1 │ John Doe     │ 120000.0 │
│     2 │ Vicent Meren │ 110000.0 │
│     3 │ Tom Simpson  │ 100000.0 │
│     1 │ John Doe     │ 126000.0 │
│     2 │ Vicent Meren │ 115500.0 │
└───────┴──────────────┴──────────┘

Query 10

Insert a new distribution from the employee “Vicent Meren” to the “Stop” bar of 48 canned beers on 2005-10-26.

Show code

# Insert new record
db.sql(
    '''
    INSERT INTO delivery (CodE, CodB, CodC, Delivery_date, Quantity)
    VALUES (2, 1, 3, '2005-10-26', 48)
    '''
)

Show code

# verify inserted record
db.sql(
    '''
    SELECT * 
    FROM delivery
    WHERE Delivery_date = '2005-10-26'
    '''
)

┌───────┬───────┬───────┬───────────────┬──────────┐
│ CodE  │ CodB  │ CodC  │ Delivery_date │ Quantity │
│ int32 │ int32 │ int32 │     date      │  int32   │
├───────┼───────┼───────┼───────────────┼──────────┤
│     2 │     1 │     3 │ 2005-10-26    │       48 │
└───────┴───────┴───────┴───────────────┴──────────┘

Conclusions

DuckDB emerges as a powerful asset for data analysis in Python.

Its in-memory processing engine delivers lightning-fast query execution, while the familiar SQL interface makes data manipulation a breeze.

The seamless integration with Python, including support for Pandas and Polars DataFrames and Python DuckB API compliance, further strengthens its appeal.

By incorporating DuckDB into your Python workflows, you can achieve significant performance gains and streamline your data analysis tasks, ultimately leading to more efficient and productive data exploration.

Contact

Jesus LM
Economist & Data Scientist

Medium | Linkedin | Twitter