Adding a Database

Working in text and JSON files is great for experimentation and speed, but I’ve been itching to put some of what we’ve been retrieving and generating into something more permanent like a database.

ChatGPT advised me to use Postgres for this and I have no reason to disagree. My goal for QCStacks is to make it a wealth of local Cincinnati news so we want to design our database to be something that’s reusable and expandable in the near future.

I wanted to use Svelte for this project as it’s something I also wanted to learn. I found that Vercel has a hosted Postgres DB available and I can use that for little-to-no cost. After setting it up and downloading the Svelte starter kit, I’m ready to go.

I generated the following script with ChatGPT to help me move my JSON and text files:

import os
import json
import psycopg2
from datetime import datetime
from dotenv import load_dotenv

# Load environment variables from .env.development.local
dotenv_path = './.env.development.local'
load_dotenv(dotenv_path=dotenv_path)

# Environment variable names for database connection
DB_NAME = os.getenv("POSTGRES_DATABASE")
DB_USER = os.getenv("POSTGRES_USER")
DB_PASSWORD = os.getenv("POSTGRES_PASSWORD")
DB_HOST = os.getenv("POSTGRES_HOST")

# Function to read a JSON file
def read_json(file_path):
    with open(file_path, 'r') as file:
        return json.load(file)  # Parse the JSON file

# Function to insert data into the database
def insert_into_db(conn, summary_data, metadata_data):
    print("Summary Data:", summary_data)  # Debugging print

    # Check if summary_data is a string and convert it to a dictionary if necessary
    if isinstance(summary_data, str):
        summary_data = json.loads(summary_data)

    # Now extract the fields
    content = summary_data['content']
    top_topics = summary_data['top_topics']
    top_actions = summary_data['top_actions']
    meeting_date = metadata_data['date']
    title = metadata_data['title']

    # Adjusting for the public_date conversion
    iso_format_date = metadata_data['publicdate']
    if iso_format_date.endswith('Z'):  # Check if the date string ends with 'Z'
        iso_format_date = iso_format_date[:-1] + '+00:00'  # Replace 'Z' with '+00:00'

    # Extracting additional fields from metadata_data
    identifier = metadata_data['identifier']
    creator = metadata_data['creator']
    description = metadata_data['description']
    language = metadata_data['language']
    public_date = datetime.fromisoformat(iso_format_date)
    closed_captioning = metadata_data['closed_captioning'] == 'yes'  # Converts to boolean

    # Convert Python list to PostgreSQL array literal
    top_topics_array = "{" + ",".join(f'"{item}"' for item in top_topics) + "}"
    top_actions_array = "{" + ",".join(f'"{item}"' for item in top_actions) + "}"

    with conn.cursor() as cur:
        # SQL command for inserting data with new fields
        sql = """
        INSERT INTO city_council_meetings (meeting_date, title, content, top_topics, top_actions, identifier, creator, description, language, public_date, closed_captioning)
        VALUES (%s, %s, %s, %s::text[], %s::text[], %s, %s, %s, %s, %s, %s)
        """
        cur.execute(sql, (meeting_date, title, content, top_topics_array, top_actions_array, identifier, creator, description, language, public_date, closed_captioning))
        conn.commit()

# Function to process each Council_Meetings directory
def process_directory(root_dir, conn):
    for dirpath, _, filenames in os.walk(root_dir):
        if 'metadata.json' in filenames and any(fname.endswith('_summary.json') for fname in filenames):
            metadata = read_json(os.path.join(dirpath, 'metadata.json'))
            summary_file = next((f for f in filenames if f.endswith('_summary.json')), None)
            if summary_file:
                summary = read_json(os.path.join(dirpath, summary_file))
                insert_into_db(conn, summary, metadata)

# Main execution function
def main():
    # Database connection
    conn = psycopg2.connect(dbname=DB_NAME, user=DB_USER, password=DB_PASSWORD, host=DB_HOST)

    # Root directory for your data (adjust as needed)
    root_dir = 'output/'

    process_directory(root_dir, conn)

    conn.close()
    print("Data inserted successfully into the database.")

if __name__ == "__main__":
    main()

Now that the data is in the database I’ll be able to turn my attention to building out the front-end of the application. This should be really simple to do and I’m not looking for a ton of style here. I’ll be using Tailwind.css wherever I can and a simple Svelte setup to render the articles.