From Raspberry Pi Pico W to CSV, JSON, and SQLite

By Aaron Rose | Tech Reader Magazine / tech-reader.blog

You have a Raspberry Pi Pico W. You have a temperature sensor wired to it. And you have a question that most tutorials never actually answer: once you have that data, what do you do with it?

This article answers that question. We will collect temperature readings on the Pico W using MicroPython, save them to a local file on the device, serve that file over Wi-Fi via a built-in HTTP server, fetch it from a Raspberry Pi workstation using Python, and then convert it into JSON and SQLite in a single utility script. When you are done, you will have a working wireless data pipeline — not a blinking LED, not a toy demo, but a proof of concept that shows exactly how this class of problem gets solved.

One thing up front: this series requires a Pico W, not the original Pico. The W has Wi-Fi built in. That is what makes this architecture work. If you are shopping for hardware, get the W.

The Pipeline

Here is the shape of what we are building:

Pico W (MicroPython + Wi-Fi) --> HTTP --> Raspberry Pi OS (Python) --> CSV
                                                                    --> JSON
                                                                    --> SQLite

The Pico W reads the sensor every 60 seconds and appends each reading to a CSV file stored in its own local flash memory. After 30 readings — 30 minutes of data — it stops collecting and serves that file over HTTP. The Raspberry Pi workstation fetches the file on demand. No USB cables. No serial ports. A headless sensor node doing its job wirelessly.

This is a proof of concept. It demonstrates the pathway and the code that makes it work. A real deployment would add configuration for collection intervals, automatic file rotation, and scheduled fetching. But the architecture is the same.

The Hardware Side (Brief)

This is a code-focused series. We are not going deep on wiring diagrams or breadboard layouts. We built our setup around a DHT22 temperature and humidity sensor — inexpensive, widely available, and well-supported in MicroPython. The code pattern here applies to any sensor that returns numeric readings.

Common temperature sensors that work well with the Pico W:

DHT22 — temperature and humidity, very common, easy to find
DS18B20 — temperature only, 1-Wire protocol, waterproof versions available
BMP280 — temperature and barometric pressure, I2C or SPI

If you are using a different sensor, the MicroPython read code will differ slightly. Everything from the file storage and HTTP server onward stays the same. Ask an AI assistant to adapt the sensor read logic for your specific hardware — that is a two-minute job.

The MicroPython Side (Pico W)

The Pico W does four things. It connects to Wi-Fi and syncs the clock via NTP. It takes a temperature reading every 60 seconds. It appends each reading to a CSV file in local flash. After 30 readings it stops collecting and serves that file over HTTP, waiting for the workstation to come and fetch it.

# 1. Connect to Wi-Fi and sync the clock via NTP
# 2. Take a temperature reading every 60 seconds
# 3. Append each reading to a local CSV file
# 4. Stop after 30 readings, then start the HTTP server

import network
import socket
import ntptime
import machine
import dht
import utime

# Wi-Fi credentials
SSID = "your_network_name"
PASSWORD = "your_password"

# DHT22 sensor on GPIO pin 15
sensor = dht.DHT22(machine.Pin(15))

DATA_FILE = "sensor_data.csv"
TOTAL_READINGS = 30
INTERVAL_SECONDS = 60

def connect_wifi():
    wlan = network.WLAN(network.STA_IF)
    wlan.active(True)
    wlan.connect(SSID, PASSWORD)
    print("Connecting to Wi-Fi...")
    attempts = 0
    while not wlan.isconnected():
        utime.sleep(1)
        attempts += 1
        if attempts >= 20:
            print("ERROR: Could not connect to Wi-Fi. Check credentials.")
            raise RuntimeError("Wi-Fi connection failed")
    ip = wlan.ifconfig()[0]
    print("Connected:", ip)
    ntptime.settime()
    print("Clock synced via NTP (UTC)")
    return ip

def get_timestamp():
    t = utime.localtime()
    return "{:04d}-{:02d}-{:02d} {:02d}:{:02d}:{:02d}".format(
        t[0], t[1], t[2], t[3], t[4], t[5]
    )

def collect_readings():
    with open(DATA_FILE, "w") as f:
        f.write("timestamp,temperature_c,humidity_pct\n")

    print(f"Collecting {TOTAL_READINGS} readings...")

    for i in range(TOTAL_READINGS):
        try:
            sensor.measure()
            timestamp = get_timestamp()
            temp = sensor.temperature()
            humidity = sensor.humidity()
            line = f"{timestamp},{temp},{humidity}\n"
            with open(DATA_FILE, "a") as f:
                f.write(line)
            print(f"Reading {i+1}/{TOTAL_READINGS}: {line.strip()}")
        except Exception as e:
            print(f"Sensor error: {e}")
        utime.sleep(INTERVAL_SECONDS)

    print("Collection complete. Starting HTTP server...")

def serve_file(ip):
    addr = socket.getaddrinfo(ip, 80)[0][-1]
    s = socket.socket()
    s.bind(addr)
    s.listen(1)
    print(f"Serving at http://{ip}/data")

    while True:
        conn, _ = s.accept()
        try:
            _ = conn.recv(1024)
            with open(DATA_FILE, "r") as f:
                content = f.read()
            payload = content.encode("utf-8")
            headers = f"HTTP/1.0 200 OK\r\nContent-Type: text/csv\r\nContent-Length: {len(payload)}\r\nConnection: close\r\n\r\n"
            conn.sendall(headers.encode("utf-8"))
            conn.sendall(payload)
        except Exception as e:
            conn.send(b"HTTP/1.0 500 Error\r\n\r\n")
        finally:
            conn.close()

ip = connect_wifi()
collect_readings()
serve_file(ip)

When the Pico W boots, it connects to Wi-Fi, syncs the clock via NTP so timestamps reflect real-world time, and prints its IP address. Make a note of that IP — you will need it on the workstation side. It then takes 30 readings, one per minute, appending each to sensor_data.csv in local flash. When the 30th reading is done, it starts the HTTP server and waits.

One thing worth knowing: ntptime.settime() syncs to UTC. All timestamps in the CSV will be in UTC, not your local time. For a proof of concept that is fine. A production deployment would apply a timezone offset if local time were required.

The collection and serving phases are sequential by design. This keeps the code simple and the concept clear. In a production deployment you would start the HTTP server at boot and run collection concurrently in a background thread. The server would always be available, and the workstation could fetch at any time — getting whatever readings had accumulated so far. For this proof of concept, sequential is exactly right.

A note on flash memory: writing a small CSV file once per minute is well within the Pico W's NOR flash endurance limits. Writing to flash at high frequency — hundreds of times per second — is a different matter and would wear the device prematurely. Once a minute for small files is safe.

The Workstation Side (Raspberry Pi OS)

On the Raspberry Pi workstation, a Python script fetches the CSV file from the Pico W over HTTP and saves it locally. This is a single on-demand operation — you run it when you are ready to pull the data.

First, make sure requests is installed:

pip3 install requests

Then the fetch script:

import requests
import sys

PICO_URL = "http://192.168.1.x/data"   # Replace with your Pico W's IP address
OUTPUT_FILE = "sensor_data.csv"

def fetch_data():
    print(f"Fetching from {PICO_URL}...")
    try:
        response = requests.get(PICO_URL, timeout=10)
        response.raise_for_status()
        with open(OUTPUT_FILE, "w") as f:
            f.write(response.text)
        lines = response.text.strip().split("\n")
        print(f"Saved to {OUTPUT_FILE} — {len(lines) - 1} readings received.")
    except Exception as e:
        print(f"Error: {e}")
        sys.exit(1)

if __name__ == "__main__":
    fetch_data()

Run it from the terminal:

python3 fetch_sensor_data.py

One HTTP request, one file saved. The workstation is in control of when the fetch happens. The Pico just responds when asked.

Replace 192.168.1.x with the actual IP address your Pico W printed when it booted. If you need to find it again, your router's connected devices page will show it.

What the CSV Looks Like

After a successful fetch, your sensor_data.csv will look something like this:

timestamp,temperature_c,humidity_pct
2026-10-05 09:00:00,21.4,58.2
2026-10-05 09:01:00,21.5,58.1
2026-10-05 09:02:00,21.4,57.9
2026-10-05 09:03:00,21.6,58.3
...
2026-10-05 09:29:00,21.5,58.0

Thirty rows. Thirty minutes of data. Human-readable, opens in Excel or Google Sheets with no conversion. That is the point of CSV — it is the universal format that every tool understands.

But CSV is just the starting point. Here is where the pipeline gets interesting.

The Converter Script: CSV to JSON and SQLite

This is the closer for every article in this series. A single Python utility that takes your CSV file and produces a JSON file and a SQLite database in one pass.

import csv
import json
import sqlite3
import sys
import os

def convert_csv(input_file):
    base = os.path.splitext(input_file)[0]
    json_file = base + ".json"
    sqlite_file = base + ".db"

    rows = []

    # Read the CSV
    with open(input_file, "r", newline="", encoding="utf-8") as f:
        reader = csv.DictReader(f)
        for row in reader:
            rows.append(row)

    # Write JSON
    with open(json_file, "w") as f:
        json.dump(rows, f, indent=2)
    print(f"JSON written: {json_file}")

    # Write SQLite
    conn = sqlite3.connect(sqlite_file)
    cursor = conn.cursor()

    cursor.execute("""
        CREATE TABLE IF NOT EXISTS sensor_readings (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            timestamp TEXT,
            temperature_c REAL,
            humidity_pct REAL
        )
    """)

    for row in rows:
        try:
            cursor.execute("""
                INSERT INTO sensor_readings (timestamp, temperature_c, humidity_pct)
                VALUES (?, ?, ?)
            """, (
                row["timestamp"],
                float(row["temperature_c"]),
                float(row["humidity_pct"])
            ))
        except (ValueError, KeyError) as e:
            print(f"Skipping malformed row: {row} — {e}")

    conn.commit()
    conn.close()
    print(f"SQLite written: {sqlite_file}")

if __name__ == "__main__":
    if len(sys.argv) != 2:
        print("Usage: python3 convert_data.py sensor_data.csv")
        sys.exit(1)
    convert_csv(sys.argv[1])

Run it like this:

python3 convert_data.py sensor_data.csv

You will end up with three files in the same directory:

sensor_data.csv     # The original — 30 readings, timestamp, temp, humidity
sensor_data.json    # For developers and web apps
sensor_data.db      # SQLite database, queryable with standard SQL

The converter wraps each row's float conversion in a try/except. If a sensor glitch produced a malformed reading, the converter logs it and moves on rather than aborting the entire run.

Why Three Formats?

Each format serves a different consumer of the data.

CSV is for the humans. Open it in Excel. Email it to a manager. Import it into Google Sheets. No tooling, no explanation required.

JSON is for the systems. Web dashboards, APIs, and applications that need to consume this data programmatically speak JSON natively. In a later article in this series, we will build a local Flask dashboard on the Raspberry Pi workstation that reads directly from this JSON file.

SQLite is for the application layer. It is a real relational database stored in a single portable file. Query it with standard SQL. Read it from Python in two lines of code. It is the source of truth that the other two formats get generated from. In a serious deployment, SQLite is where your data lives — CSV and JSON are exports from it.

You do not always need all three. But having a converter script that produces all three in one pass means you always have options.

What You Have Now

At this point you have a complete wireless data pipeline. The Pico W collected 30 temperature readings over 30 minutes, stored them locally, and served the file over HTTP when asked. A Python script on your Raspberry Pi workstation fetched that file in a single request. A converter script turned it into JSON and SQLite on demand.

That is not a tutorial toy. It is a proof of concept for a legitimate IoT architecture — a headless sensor node collecting data locally, a workstation pulling it on demand, three output formats ready for whatever comes next.

In the next article, we take this further — building a local web dashboard on the Raspberry Pi workstation that reads the JSON file and visualizes the data in a browser using Flask.

Part of the Raspberry Pi Pico W Sensor Series on Tech Reader Magazine and tech-reader.blog.

Search This Blog

Tech-Reader.blog