Natural Language Processing

Integrating DuckDB & Python: An Analytics Information

June 10, 2025
roosho

Integrating DuckDB & Python: An Analytics Information
Picture by Writer

 

DuckDB is a quick, in-process analytical database designed for contemporary knowledge evaluation. It runs instantly out of your Python script, which implies that there is no such thing as a separate server wanted, and it excels at advanced queries due to its columnar storage and vectorized execution.

As understanding learn how to take care of knowledge is turning into extra vital, in the present day I wish to present you learn how to construct a Python workflow with DuckDB and discover its key options.

Let’s dive in!

 

What Is DuckDB?

 
DuckDB is a free, open-source, in-process OLAP database constructed for quick, native analytics. In contrast to conventional databases that run as exterior providers, DuckDB runs inside your software, with no server required. As an OLAP system, DuckDB shops knowledge in columns (not rows like OLTP techniques), making it extremely environment friendly for analytical queries akin to joins, aggregations, and groupings.

Consider DuckDB as a light-weight, analytics-optimized model of SQLite, bringing the simplicity of native databases along with the facility of recent knowledge warehousing. And this leads us to the next pure query…

 

What Are DuckDB’s Major Options?

 

Blazing-Quick Analytical Queries

DuckDB delivers spectacular efficiency for OLAP workloads, typically stunning customers accustomed to conventional databases like PostgreSQL. In contrast to typical OLAP techniques that may be sluggish on account of processing massive volumes of knowledge, DuckDB leverages a columnar, vectorized execution engine. This design optimizes CPU cache utilization and considerably accelerates analytical question efficiency.

 

Native SQL Assist + Seamless Language Integration

DuckDB provides full help for advanced SQL queries and exposes APIs in a number of languages, together with Java, C, and C++. Its tight integration with Python and R makes it supreme for interactive knowledge evaluation. You possibly can write queries instantly in your most popular setting, with further SQL syntax enhancements (e.g., EXCLUDE, REPLACE, and ALL) to simplify question writing.

And the most effective half is that DuckDB is totally self-contained, with no exterior dependencies or setup complications.

 

Free And Open Supply

DuckDB is totally open-source and actively maintained by a rising group of contributors. This ensures speedy function growth and bug fixes. And sure, it’s free to make use of. Whereas future licensing modifications are all the time a risk, for now, you get a strong analytics engine at zero price.

Now that we all know its principal options, let’s get began with it!

 

Getting Began With DuckDB

 
The set up course of for DuckDB relies upon barely in your setting, however general, it’s fast and easy. Since DuckDB is an embedded database engine with no server necessities or exterior dependencies, setup sometimes takes just some traces of code. You’ll find the entire set up information within the official DuckDB documentation.

 

Conditions

Earlier than diving in, guarantee you’ve gotten the next:

  • Python 3.13 or later put in
  • A fundamental understanding of SQL and knowledge evaluation in Python

You possibly can simply set up DuckDB in your setting by executing the next command:

 

Working With DuckDB in Python

When you’ve put in DuckDB, it’s fairly easy to get began. You merely import DuckDB into your setting, then hook up with an current database or create a brand new one if required.

For instance:

import duckdb 
connection = duckdb.join()

 

If no database file is offered to the join() methodology, DuckDB will create a brand new in-memory database by default. That stated, the only solution to begin operating SQL queries is by utilizing the sql() methodology instantly.

# Supply: Primary API utilization - https://duckdb.org/docs/api/python/overview.html
import duckdb
duckdb.sql('SELECT 42').present()

 

Operating this command initializes a world in-memory DuckDB occasion inside the Python module and returns a relation, a symbolic illustration of the question.

Importantly, the question itself is not executed till you explicitly request the outcome, as proven beneath:

# Supply: Execute SQL - https://duckdb.org/docs/guides/python/execute_sql.html
outcomes = duckdb.sql('SELECT 42').fetchall()
print(outcomes)

"""
[(42,)]
"""

 

Let’s now work with some actual knowledge. DuckDB helps a variety of file codecs, together with CSV, JSON, and Parquet, and loading them is straightforward.

You possibly can see how simple it’s within the instance beneath:

# Supply: Python API - https://duckdb.org/docs/api/python/overview.html 
import duckdb
duckdb.read_csv('instance.csv') # learn a CSV file right into a Relation
duckdb.read_parquet('instance.parquet')# learn a Parquet file right into a Relation
duckdb.read_json('instance.json') # learn a JSON file right into a Relation
duckdb.sql('SELECT * FROM "instance.csv"')     # instantly question a CSV file

 

Working With Exterior Knowledge Sources In DuckDB

 
Considered one of DuckDB’s standout options is its means to question exterior knowledge recordsdata instantly, without having to import them right into a database or load whole datasets into reminiscence. In contrast to conventional databases that require knowledge to be ingested first, DuckDB helps a “zero-copy” execution mannequin, permitting it to learn solely the information required for a given question.

This method brings a number of key benefits:

  • Minimal reminiscence utilization: Solely the related parts of the file are learn into reminiscence.
  • No import/export overhead: Question your knowledge in place—no want to maneuver or duplicate it.
  • Streamlined workflows: Simply question throughout a number of recordsdata and codecs utilizing a single SQL assertion.

To exemplify the utilization of DuckDB, we will likely be utilizing a easy CSV file that you could acquire from the next Kaggle hyperlink .

To question the information, we are able to simply outline a easy question that factors out to our file path.

# Question knowledge instantly from a CSV file
outcome = duckdb.question(f"SELECT * FROM '{supply}'").fetchall()
print(outcome)

 

Now we are able to simply deal with knowledge utilizing SQL-like logic instantly with DuckDB.

 

Filtering Rows

To deal with particular subsets of knowledge, use the WHERE clause in DuckDB. It filters rows primarily based on circumstances utilizing comparability operators (>, , and many others.) and logical operators (AND, OR, NOT) for extra advanced expressions.


# Choose solely college students with a rating above 80
outcome = duckdb.question(f"SELECT * FROM '{supply}' WHERE total_passengers > 500").fetchall()
outcome

 

Sorting Outcomes

Use the ORDER BY clause to kind outcomes by a number of columns. It defaults to ascending (ASC), however you possibly can specify descending (DESC). To kind by a number of columns, separate them with commas.

#Kind months by variety of passengers
sorted_result = duckdb.question(f"SELECT * FROM '{supply}' ORDER BY total_passengers DESC ").fetchall()
print("nMonths sorted by whole site visitors:")
print(sorted_result)

 

Including Calculated Columns

Create new columns in your question utilizing expressions and the AS key phrase. Use arithmetic operators or built-in features to remodel knowledge—these columns seem within the outcomes however don’t have an effect on the unique file.

# Add 10 bonus factors to every rating
bonus_result = duckdb.question(f"""
   SELECT
       month,
       total_passengers,
       total_passengers/1000 AS traffic_in_thousands
   FROM '{supply}'
""").fetchall()
print("nScores with 10 bonus factors:")
print(bonus_result)

 

Utilizing CASE Expressions

For extra advanced transformations, SQL gives the CASE expression. This works equally to if-else statements in programming languages, permitting you to use conditional logic in your queries.

segmented_result = duckdb.question(f"""
   SELECT
       month,
       total_passengers,
       CASE
           WHEN total_passengers >= 100 THEN 'HIGH'
           WHEN total_passengers >= 50 THEN 'MEDIUM'
           ELSE 'LOW'
       END AS affluency
   FROM '{supply}'
""").fetchall()
print("nMonth by affluency of passangers")
print(segmented_result)

 

Conclusion

 
DuckDB is a high-performance OLAP database constructed for knowledge professionals who have to discover and analyze massive datasets effectively. Its in-process SQL engine runs advanced analytical queries instantly in your setting — no separate server is required. With seamless help for Python, R, Java, C++, and extra, DuckDB matches naturally into your current workflow, irrespective of your most popular language.

You possibly can go verify the complete code on the following GitHub repository.
 
 

Josep Ferrer is an analytics engineer from Barcelona. He graduated in physics engineering and is presently working within the knowledge science discipline utilized to human mobility. He’s a part-time content material creator targeted on knowledge science and expertise. Josep writes on all issues AI, masking the applying of the continuing explosion within the discipline.