DRAFT
Imagine DuckDB as your personal data chef, ready to whip up delicious insights from raw data. But before the culinary magic happens, you need to connect to your kitchen (the database) and give the chef instructions (SQL queries). This chapter is your guide to setting up that connection and speaking the chef's language. Think of it like learning to turn on the oven and read a basic recipe - essential steps before you can bake a cake!
We'll cover the fundamental concepts of connecting to DuckDB, executing SQL queries, and defining your data's structure. You'll learn how to establish database connections in both in-memory and persistent modes, execute SQL queries securely and efficiently, define data structures using Data Definition Language (DDL), and implement basic error handling. This chapter is crucial because it lays the groundwork for everything else we'll do with DuckDB. Without a solid understanding of these core concepts, you'll be lost in the data wilderness.
By the end of this chapter, you'll be comfortable creating database connections, writing and executing SQL queries, designing table structures, and handling potential errors. These skills will serve as the foundation for the more advanced techniques we'll explore in later chapters. So, let's get cooking with DuckDB!
Before you can unleash the analytical power of DuckDB, you need to establish a connection. Think of it like plugging in your data pipeline – without a solid connection, nothing flows! This section will guide you through creating and managing DuckDB database connections in Python, covering both in-memory and persistent storage options. Choosing the right storage mode is crucial for performance and data persistence, so we'll explore the trade-offs and help you make the best decision for your specific use case. The connect() function is the entry point, returning a Connection object whose methods we'll explore in detail in Chapter 3 (Deep Dive into the DuckDB Python API).
DuckDB offers two primary storage modes: in-memory and persistent. In-memory databases are perfect for temporary data processing and experimentation, while persistent databases store data on disk for long-term analysis and building data pipelines. Let's dive into each mode and see how to create and manage them.
Let's start with in-memory databases.
# Import the DuckDB library
import duckdb
import pandas as pd
# Create an in-memory database connection
con = duckdb.connect(':memory:') # Use special :memory: identifier for an in-memory database
# Create a table
con.execute("""
CREATE TABLE my_table (
id INTEGER,
name VARCHAR
)
""")
# Insert some data
con.execute("""
INSERT INTO my_table
VALUES (1, 'Alice'), (2, 'Bob')
""")
# Query the data using .sql() and convert to a Pandas DataFrame
df = con.sql("SELECT * FROM my_table").df()
# Print the result
print(df)
# Close the connection
con.close()
In this example, duckdb.connect(':memory:') creates an in-memory database connection. Any data created within this connection will be lost when con.close() is called. Think of it like a whiteboard – great for brainstorming, but not for permanent records. This is ideal for testing, temporary data transformations, or situations where data persistence isn't required. Here, we primarily use the .sql() method for querying and the .df() method to retrieve the results as a Pandas DataFrame. The .execute() method is primarily used for DDL and DML operations, and is also useful when you need to use parameter binding for security. Chapter 3 discusses the nuances of different execution methods like .sql() and .execute() and the various methods for converting query results to different data structures.
Next, let's look at persistent databases.
# Import the DuckDB library
import duckdb
import pandas as pd
# Create a persistent database connection
con = duckdb.connect('my_database.duckdb') # Connects to/creates a file named 'my_database.duckdb'
# Create a table
con.execute("""
CREATE TABLE my_table (
id INTEGER,
name VARCHAR
)
""")
# Insert some data
con.execute("""
INSERT INTO my_table
VALUES (1, 'Alice'), (2, 'Bob')
""")
# Close the connection
con.close()
# Re-open the connection
con = duckdb.connect('my_database.duckdb')
# Query the data using .sql() and convert to a Pandas DataFrame
df = con.sql("SELECT * FROM my_table").df()
# Print the result
print(df)
# Close the connection
con.close()
Here, duckdb.connect('my_database.duckdb') creates a persistent database file named 'my_database.duckdb'. Data is stored on disk and persists across sessions. Closing and reopening the connection allows you to access the same data. This is suitable for storing analytical datasets, building data warehouses, or any scenario where you need to retain data between program executions. Think of this like a filing cabinet, where you can store and retrieve documents as needed. This uses standard SQL syntax. For loading CSV files, consider using the duckdb.read_csv_auto() function for optimized performance and automatic type detection. For more advanced SQL capabilities in DuckDB, refer to Chapter 7 (Advanced DuckDB Techniques).
So, how do you choose the right storage mode? It's a balancing act between data persistence, performance, and storage capacity. In-memory databases offer faster performance but lack persistence. Persistent databases provide data persistence but may be slower due to disk I/O. For more complex data transformations and pipelines, DuckDB offers a Lazy API that allows you to build query plans incrementally. Let's consider a table comparing these options.