The Atlantic created a searchable database of the music used to train AI

Introduction

In this tutorial, you'll learn how to create a simple searchable database of music using Python and SQLite. This project will help you understand how large datasets are organized and searched, similar to what the Atlantic reporter did with AI training music data. You'll build a database that can store and retrieve music information efficiently.

Prerequisites

• Basic understanding of computer operations
• Python installed on your computer (any version 3.x)
• Text editor or IDE (like VS Code or Python IDLE)
• Basic knowledge of databases and SQL concepts

Step-by-step instructions

Step 1: Set up your Python environment

First, you'll need to ensure Python is properly installed and working. Open your command prompt or terminal and type:

python --version

If you see a version number, Python is installed. If not, download and install Python from python.org.

Step 2: Create a new Python project folder

Create a new folder on your computer called music_database. This will be your project directory where all files will live.

Step 3: Create your main Python file

In your project folder, create a file named music_db.py. This will be your main program file.

Step 4: Import required libraries

Open music_db.py in your text editor and add the following imports:

import sqlite3
import csv
from datetime import datetime

We're importing SQLite for database operations, CSV for handling data files, and datetime for recording when entries are added.

Step 5: Create the database connection

Add this function to create and connect to your database:

def create_connection():
    conn = None
    try:
        conn = sqlite3.connect('music_database.db')
        print("Successfully connected to SQLite database")
    except sqlite3.Error as e:
        print(f"Error connecting to database: {e}")
    return conn

This function establishes a connection to your SQLite database file. SQLite is perfect for beginners because it doesn't require a separate server process.

Step 6: Create the music table

Add this function to create your music table structure:

def create_music_table(conn):
    create_table_sql = '''
    CREATE TABLE IF NOT EXISTS music (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        artist TEXT NOT NULL,
        album TEXT,
        genre TEXT,
        release_year INTEGER,
        duration INTEGER,
        added_date TEXT
    );'''
    try:
        c = conn.cursor()
        c.execute(create_table_sql)
        conn.commit()
        print("Music table created successfully")
    except sqlite3.Error as e:
        print(f"Error creating table: {e}")

This creates a table with columns for all the important music information. The id field automatically increments, added_date records when entries are added, and all other fields store music metadata.

Step 7: Insert sample data

Add this function to insert some sample music data:

def insert_sample_data(conn):
    sample_data = [
        ("Bohemian Rhapsody", "Queen", "A Night at the Opera", "Rock", 1975, 354, datetime.now().strftime("%Y-%m-%d %H:%M:%S")),
        ("Blinding Lights", "The Weeknd", "After Hours", "Pop", 2020, 200, datetime.now().strftime("%Y-%m-%d %H:%M:%S")),
        ("Shape of You", "Ed Sheeran", "÷", "Pop", 2017, 233, datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
    ]
    
    insert_sql = '''
    INSERT INTO music (title, artist, album, genre, release_year, duration, added_date)
    VALUES (?, ?, ?, ?, ?, ?, ?)
    '''
    
    try:
        c = conn.cursor()
        c.executemany(insert_sql, sample_data)
        conn.commit()
        print("Sample data inserted successfully")
    except sqlite3.Error as e:
        print(f"Error inserting data: {e}")

We're inserting three sample songs to test our database. Notice how we use ? placeholders to prevent SQL injection attacks.

Step 8: Create a search function

Add this function to search for music by different criteria:

def search_music(conn, search_term, search_by="title"):
    search_sql = f"SELECT * FROM music WHERE {search_by} LIKE ?"
    try:
        c = conn.cursor()
        c.execute(search_sql, (f"%{search_term}%",))
        results = c.fetchall()
        return results
    except sqlite3.Error as e:
        print(f"Error searching music: {e}")
        return []

This function allows you to search by title, artist, album, or genre. The %{search_term}% pattern finds partial matches, making it flexible for user searches.

Step 9: Build the main program

Now add this main code to your file:

def main():
    conn = create_connection()
    if conn is not None:
        create_music_table(conn)
        insert_sample_data(conn)
        
        # Test search functionality
        print("\nSearching for 'Queen':")
        results = search_music(conn, "Queen")
        for row in results:
            print(row)
        
        print("\nSearching for 'Pop':")
        results = search_music(conn, "Pop", "genre")
        for row in results:
            print(row)
        
        conn.close()
        print("\nDatabase connection closed")
    else:
        print("Error! Cannot create database connection.")

if __name__ == '__main__':
    main()

This runs all our functions in order. It creates the database, adds sample data, and tests the search functionality.

Step 10: Run your program

Save your file and run it from the command line:

python music_db.py

You should see output showing successful database creation, data insertion, and search results.

Summary

In this tutorial, you've built a searchable music database using Python and SQLite. You learned how to create database connections, define table structures, insert data, and perform searches. This simple database demonstrates the basic principles behind the large music datasets used to train AI models, similar to what the Atlantic reporter discovered. The skills you've learned form the foundation for working with larger, more complex databases that could handle millions of music tracks like those mentioned in the news article.