↓ Skip to main content
www.FoolishLee.com
  1. Projects/

PostgreSQL Practice

223 words·2 mins
Author
FoolishLee TheWise
CREATE TABLE movies (
id SERIAL PRIMARY KEY,
timestamp TIMESTAMPTZ DEFAULT NOW(),
filename TEXT,
size INT,
details JSONB
);

CREATE TABLE music (
id SERIAL PRIMARY KEY,
timestamp TIMESTAMPTZ DEFAULT NOW(),
filename TEXT,
artist VARCHAR(64),
title VARCHAR(64),
file_size INT,
details JSONB,
is_valid BOOL
);

CREATE INDEX idx_music_artist ON music (artist);
CREATE INDEX idx_music_title ON music (title);

Now For Some Python Code:
#

conn = psycopg2.connect(**DB_CONFIG)

cur = conn.cursor()
cur.execute("""
    INSERT INTO music (timestamp, filename, artist, title, file_size, is_falid)
    VALUES (%s, %s, %s, %s, %s, %s)
""", (
    timestamp or datetime.utcnow(),
    filename,
    artist,
    title,
    file_size,
    is_valid
))
conn.commit()
cur.close()

Let’s mix it up and throw some Bash in there!
#

#!/bin/bash

# Configuration
EXTENSION="mp3"           # Change to your target extension
OUTPUT_FILE="mp3s.csv"
DB_NAME="archgodot"
TABLE_NAME="music_tbl"

# 1. Find files and format as CSV (Full Path, Filename, Size in Bytes)
# %p: full path, %f: filename, %s: size in bytes
echo "Searching for *.$EXTENSION files..."
find . -type f -name "*.$EXTENSION" -printf "%p,%f,%s\n" > "$OUTPUT_FILE"

# 2. Upload to PostgreSQL
# This creates the table if it doesn't exist and uses \copy for fast import
echo "Importing into database..."
psql -d "$DB_NAME" -c "CREATE TABLE IF NOT EXISTS $TABLE_NAME (file_path TEXT, file_name TEXT, size_bytes BIGINT);"
psql -d "$DB_NAME" -c "\copy $TABLE_NAME FROM '$OUTPUT_FILE' WITH (FORMAT csv);"

echo "Done. Results saved in $OUTPUT_FILE and imported to table '$TABLE_NAME'."