Python connects DuckDB to data-processing workflows without requiring every input to become a database table. Its API can ingest files, query Python objects as relations, and return results to Python data structures. section 6.0
DuckDB's Python integration supports:
- Reading CSV, Parquet, and JSON through Python functions. section 6.2.1
- Querying returned DuckDBPyRelation objects with SQL or relational API methods. section 6.2.1
- Working with pandas DataFrames and Apache Arrow tables, then exporting query results to those formats. section 6.0
- Using Python alongside SQL for transformations and computations in automated data pipelines. section 8.5
Query a remote CSV from Python
Connect to an in-memory DuckDB database, load the remote Populations CSV with the httpfs extension, and assign the resulting queryable relation to population. The relation can then be queried with con.execute or composed with relational API methods such as count and show. section 6.2.1
Listing 6.1 Querying a CSV file
import duckdb
con = duckdb.connect(database=':memory:')
con.execute("INSTALL httpfs") #1
con.execute("LOAD httpfs") #2
population = \
con.read_csv("https://bit.ly/3KoiZR0") #3
Runnable Python setup for connecting to DuckDB, enabling httpfs, and reading a remote CSV into a DuckDBPyRelation. section 6.2.1
