To establish a database connection and create a table, use the following Python script with sqlite3.

import sqlite3

db_conn = sqlite3.connect('playlist.db')
cursor = db_conn.cursor()

cursor.execute('DROP TABLE IF EXISTS Songs')
cursor.execute('CREATE TABLE Songs (song_name TEXT, play_count INTEGER)')

db_conn.close()

The connect() method opens a database file, creating it if it doesn't exist. This connection is represented by a sqlite3.Connection object. A cursor object, analogous to a file handle, is created from this connection to execute commands on the database. The execute() method runs Structured Query Language (SQL) commands. The first command drops the Songs table if it exists, preventing errors on repeated runs. The second creates the Songs table with two columns: song_name (TEXT) and play_count (INTEGER).

Insert data into the table using the SQL INSERT command. The example passes data as a tuple into parameter placeholders (?).

import sqlite3

db_conn = sqlite3.connect('playlist.db')
cursor = db_conn.cursor()

cursor.execute('INSERT INTO Songs (song_name, play_count) VALUES (?, ?)',
               ('Bohemian Rhapsody', 50))
cursor.execute('INSERT INTO Songs (song_name, play_count) VALUES (?, ?)',
               ('Imagine', 30))
db_conn.commit()

print('Songs:')
cursor.execute('SELECT song_name, play_count FROM Songs')
for record in cursor:
    print(record)

cursor.execute('DELETE FROM Songs WHERE play_count < 100')
db_conn.commit()

cursor.close()

Output:

Songs:
('Bohemian Rhapsody', 50)
('Imagine', 30)

The commit() method persists the insersions to the database file. The SELECT command retrieves specified columns from the Songs table. Iterating over the cursor yields each row as a tuple. Finally, a DELETE command with a WHERE clause removes rows where play_count is less than 100.

Core SQL Operations

SQL provides four fundamental commands for data manipulation.

• CREATE TABLE: Defines a new table and its columns.

CREATE TABLE Songs (song_name TEXT, play_count INTEGER)

• INSERT: Adds a new row to a table.

INSERT INTO Songs (song_name, play_count) VALUES ('Yesterday', 25)

• SELECT: Retrieves data from a table. It can include WHERE for filtering and ORDER BY for sorting.

SELECT * FROM Songs WHERE song_name = 'Yesterday'
SELECT song_name, play_count FROM Songs ORDER BY song_name

• UPDATE: Modifies existing rows.

UPDATE Songs SET play_count = 26 WHERE song_name = 'Yesterday'

• DELETE: Removes rows from a table.

DELETE FROM Songs WHERE song_name = 'Yesterday'

Storing Social Graph Data with Normalization

To store data like social connections efficiently, a normalized database design with multiple tablles is used. This avoids duplicating string data.

import sqlite3

conn = sqlite3.connect('network.db')
cur = conn.cursor()

# People table with an auto-incrementing primary key
cur.execute('''CREATE TABLE IF NOT EXISTS Users
               (user_id INTEGER PRIMARY KEY, handle TEXT UNIQUE, fetched INTEGER)''')
# Follows table linking users via foreign keys
cur.execute('''CREATE TABLE IF NOT EXISTS Follows
               (follower_id INTEGER, followed_id INTEGER,
                UNIQUE(follower_id, followed_id))''')

The Users table uses INTEGER PRIMARY KEY for an auto-generated user_id. The UNIQUE constraint on handle prevents duplicate usernames. The Follows table stores relationships using integer IDs, with a uniqueness constraint on the pair.

Data insertion respects these constraints using INSERT OR IGNORE.

# Add a new user, ignoring if the handle already exists
cur.execute('''INSERT OR IGNORE INTO Users (handle, fetched)
               VALUES (?, 0)''', (account_name,))
conn.commit()
if cur.rowcount == 1:
    new_id = cur.lastrowid  # Retrieve the auto-generated ID

The rowcount attribute checks if an insert occurred. lastrowid retrieves the primary key for the new row.

To record a follow relationship:

cur.execute('''INSERT OR IGNORE INTO Follows (follower_id, followed_id)
               VALUES (?, ?)''', (user_id, friend_id))

The UNIQUE constraint and OR IGNORE prevent duplicate entries.

Querying Across Tables with JOIN

The JOIN clause combines rows from related tables.

# Find all accounts followed by the user with user_id = 1
cur.execute('''SELECT * FROM Follows
               JOIN Users ON Follows.followed_id = Users.user_id
               WHERE Follows.follower_id = ?''', (1,))
for meta_row in cur:
    print(meta_row)  # Contains columns from both Follows and Users

This query produces "metarows" containing fields from both tables where the followed_id matches a user_id.

Database Locking Consideration

When using tools like the SQLite Database Browser concurrently with a Python script, ensure the browser is not actively locking the database file (e.g., by having unsaved changes), as this will prevent Python from accessing it.

Example: Social Media Data Retrieval

The following script populates the normalized database by retrieving data from a hypothetical API.

import json
import sqlite3
import urllib.request
import api_helper  # Hypothetical module for API URL construction

API_ENDPOINT = 'https://api.social.com/1.1/friends/list.json'
db_conn = sqlite3.connect('network.db')
cursor = db_conn.cursor()

cursor.execute('''CREATE TABLE IF NOT EXISTS Users
                  (user_id INTEGER PRIMARY KEY, handle TEXT UNIQUE, fetched INTEGER)''')
cursor.execute('''CREATE TABLE IF NOT EXISTS Follows
                  (follower_id INTEGER, followed_id INTEGER,
                   UNIQUE(follower_id, followed_id))''')

while True:
    account = input('Enter an account name, or press enter to quit: ')
    if account == 'quit':
        break
    if not account.strip():
        # Retrieve an account not yet processed
        cursor.execute('''SELECT user_id, handle FROM Users WHERE fetched = 0 LIMIT 1''')
        try:
            user_id, account = cursor.fetchone()
        except:
            print('No unprocessed accounts found.')
            continue
    else:
        # Check if account exists or insert it
        cursor.execute('SELECT user_id FROM Users WHERE handle = ? LIMIT 1', (account,))
        row = cursor.fetchone()
        if row:
            user_id = row[0]
        else:
            cursor.execute('''INSERT OR IGNORE INTO Users (handle, fetched)
                              VALUES (?, 0)''', (account,))
            db_conn.commit()
            if cursor.rowcount != 1:
                print(f'Error inserting account: {account}')
                continue
            user_id = cursor.lastrowid

    # Fetch data for the account
    url = api_helper.construct_url(API_ENDPOINT, {'screen_name': account, 'count': '20'})
    print(f'Fetching {url}')
    try:
        response = urllib.request.urlopen(url)
        data = json.loads(response.read())
    except Exception as e:
        print(f'Failed to retrieve data: {e}')
        continue

    cursor.execute('UPDATE Users SET fetched=1 WHERE handle = ?', (account,))

    new_users = 0
    existing_users = 0
    for member in data.get('users', []):
        friend_handle = member['screen_name']
        cursor.execute('SELECT user_id FROM Users WHERE handle = ? LIMIT 1', (friend_handle,))
        row = cursor.fetchone()
        if row:
            friend_id = row[0]
            existing_users += 1
        else:
            cursor.execute('''INSERT OR IGNORE INTO Users (handle, fetched)
                              VALUES (?, 0)''', (friend_handle,))
            db_conn.commit()
            if cursor.rowcount != 1:
                print(f'Error inserting friend: {friend_handle}')
                continue
            friend_id = cursor.lastrowid
            new_users += 1
        # Record the follow relationship
        cursor.execute('''INSERT OR IGNORE INTO Follows (follower_id, followed_id)
                          VALUES (?, ?)''', (user_id, friend_id))

    print(f'New accounts added: {new_users}. Existing accounts found: {existing_users}')
    db_conn.commit()

cursor.close()

This script maintains two tables: Users for account information and Follows for relationships. It uses unique constraints and INSERT OR IGNORE to handle duplicates gracefully.