Load Data from a JSON File into DuckDB using Python

📘 Introduction

Working with structured data is a core part of any data engineering or analytics workflow. DuckDB, often called the SQLite for analytics, makes it incredibly easy to query local data files—including JSON—without requiring a complex setup. In this tutorial, you’ll learn how to load JSON data directly into a DuckDB table using Python.

✅ Prerequisites

Before you begin, make sure you have:

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

📁1️⃣ Create Sample JSON file

First, create a sample JSON file called student.json within your project folder:

project-folder/
├── student.json

Add the following content to the .json file:

[
    {
        "id":"s1",
        "name":"Lara Fitzgerald",
        "major":"Data Analytics"
    },
    {
        "id":"s2",
        "name":"Mike Meyer",
        "major":"Data Engineering"
    },
    {
        "id":"s3",
        "name":"Eliza Gomez",
        "major":"Data Science"
    },
    {
        "id":"s4",
        "name":"Travis Robinson",
        "major":"Data Analytics"
    },
    {
        "id":"s5",
        "name":"Jackie Brown",
        "major":"Data Engineering"
    },
    {
        "id":"s6",
        "name":"Caleb Pearson",
        "major":"Data Science"
    },
    {
        "id":"s7",
        "name":"Ava Johansson",
        "major":"Data Science"
    },
    {
        "id":"s8",
        "name":"Nathan Williams",
        "major":"Data Engineering"
    },
    {
        "id":"s9",
        "name":"Cooper Harris",
        "major":"Data Analytics"
    },
    {
        "id":"s10",
        "name":"Murphy Fraser",
        "major":"Data Science"
    }
]

💡 Fun Fact: If you’re curious how to convert a CSV into a JSON file, check out this detailed guide: 

How to convert CSV to JSON in Python using Pandas

📘 Introduction JSON is one of the most widely used data formats for APIs, configurations, storage, and modern applications. Converting CSV to JSON in Python is incredibly simple using Pandas. In this tutorial, we will walk through the full process: creating a sample CSV file, loading it into a Pandas DataFrame,

Deep Learning Nerds | The ultimate Learning Platform for AI and Data ScienceData Scientist

🐍2️⃣ Create Python script

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

project-folder/
├── student.json
└── insert_data_from_json.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: