Insert Data from a CSV File into DuckDB using Python

📘 Introduction

In this tutorial, you will learn how to insert data from a CSV file into a DuckDB database using Python. DuckDB is a powerful in-process analytical database that makes working with structured data fast, simple, and efficient—perfect for data projects, notebooks, and local analytics.

✅ Prerequisites

Before you begin, make sure you have:

🐍☑️ Installed Python
📦☑️ Installed DuckDB
🌐☑️ Created and activated a virtual environment (venv)

📁1️⃣ Create Sample CSV file

First, create a sample CSV file called student.csv within your project folder:

project-folder/
├── student.csv

Add the following content to the .csv file:

id,name,birthdate,major
s1,Lara Fitzgerald,2002-08-05,Data Analytics 
s2,Mike Meyer,2003-05-20,Data Engineering 
s3,Eliza Gomez,2002-01-01,Data Science 
s4,Travis Robinson,2001-12-19,Data Analytics 
s5,Jackie Brown,2003-05-23,Data Engineering 
s6,Caleb Pearson,2000-03-02,Data Science 
s7,Ava Johansson,2000-07-26,Data Science 
s8,Nathan Williams,2002-11-20,Data Engineering 
s9,Cooper Harris,2001-06-08,Data Analytics 
s10,Murphy Fraser,2003-12-01,Data Science

🐍2️⃣ Create Python script

In the same folder as the CSV file, create a Python file (.py) or Jupyter notebook (.ipynb):

project-folder/
├── student.csv
└── insert_data_from_csv.ipynb

📥3️⃣ Import Libraries

Open your Python file and start by importing the required Python modules:

import duckdb

🔗4️⃣ Establish Connection to DuckDB Database file

Establish the connection to a DuckDB database file:

con = duckdb.connect("dlnerds_university.duckdb")

🏗️5️⃣ Create Table Using SQL CREATE TABLE

Let’s create a simple student table: