在CodeIgniter中创建推特粉丝追踪表并实现每日数据插入

Got it, let's walk through building this daily Twitter follower tracking system that integrates with your existing Projects and Twitter Tracking tables, plus generates that Instagram-style growth chart you want. Here's a practical, step-by-step breakdown tailored to your setup:

Let's align on the core fields we'll work with to avoid missteps:

• Projects Table: Should have ico_id (unique project identifier) and twitter_url (direct link to the project's Twitter profile)
• Twitter Tracking Table: Needs ico_id (foreign key linking to Projects), follower_count (numeric value of followers), and recorded_at (timestamp of when the count was captured). You might also want an auto-incrementing id as the primary key.

2.1 Pull Target Data from the Projects Table #

First, we'll fetch all projects that have a valid Twitter URL to avoid wasted requests. Use this SQL query:

SELECT ico_id, twitter_url 
FROM Projects 
WHERE twitter_url IS NOT NULL AND twitter_url != '';

2.2 Extract Twitter Follower Counts #

You have two reliable options here—pick the one that fits your access level:

Option 1: Use Twitter's Official API (Recommended) #

This is the most stable, accurate method. You'll need a Twitter Developer account to get a Bearer Token. Here's a Python snippet to fetch follower counts:

import requests
import os

# Store your Bearer Token in an environment variable for security
BEARER_TOKEN = os.getenv("TWITTER_BEARER_TOKEN")

def get_follower_count_from_api(twitter_url):
    # Extract the username from the URL (e.g., "OpenAI" from "https://twitter.com/OpenAI")
    username = twitter_url.split("/")[-1]
    api_url = f"https://api.twitter.com/2/users/by/username/{username}"
    
    headers = {"Authorization": f"Bearer {BEARER_TOKEN}"}
    params = {"user.fields": "public_metrics"}
    
    response = requests.get(api_url, headers=headers, params=params)
    if response.status_code == 200:
        data = response.json()
        return data["data"]["public_metrics"]["followers_count"]
    else:
        print(f"Failed to fetch data for {username}: {response.text}")
        return None

Option 2: Web Scraping (Fallback if API Access Isn't Available) #

Note: Twitter has anti-scraping measures, so this might break unexpectedly. Use requests-html to render dynamic content:

from requests_html import HTMLSession

def scrape_follower_count(twitter_url):
    session = HTMLSession()
    try:
        response = session.get(twitter_url)
        response.html.render()  # Render JavaScript-loaded content
        
        # Target the follower count element (selector might change over time)
        follower_element = response.html.find('a[href$="/followers"] span', first=True)
        if not follower_element:
            return None
        
        # Convert formatted counts (e.g., 12.5K → 12500, 2.3M → 2300000)
        follower_text = follower_element.text.replace(",", "")
        if "K" in follower_text:
            return int(float(follower_text.replace("K", "")) * 1000)
        elif "M" in follower_text:
            return int(float(follower_text.replace("M", "")) * 1000000)
        else:
            return int(follower_text)
    except Exception as e:
        print(f"Scraping failed for {twitter_url}: {str(e)}")
        return None
    finally:
        session.close()

2.3 Insert Data into the Twitter Tracking Table #

Once you have the follower count, insert it into your tracking table. Here's a Python example using SQLite (adapt for PostgreSQL/MySQL with the appropriate library):

import sqlite3
from datetime import datetime

def insert_tracking_record(ico_id, follower_count):
    conn = sqlite3.connect("your_database.db")
    cursor = conn.cursor()
    
    # Check if we already have a record for this project today to avoid duplicates
    check_query = """
    SELECT COUNT(*) 
    FROM Twitter_Tracking 
    WHERE ico_id = ? AND DATE(recorded_at) = DATE('now')
    """
    cursor.execute(check_query, (ico_id,))
    if cursor.fetchone()[0] > 0:
        conn.close()
        print(f"Record already exists for project {ico_id} today")
        return
    
    # Insert the new record
    insert_query = """
    INSERT INTO Twitter_Tracking (ico_id, follower_count, recorded_at)
    VALUES (?, ?, ?)
    """
    current_timestamp = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    cursor.execute(insert_query, (ico_id, follower_count, current_timestamp))
    
    conn.commit()
    conn.close()

2.4 Set Up a 24-Hour Scheduled Task #

To automate daily updates:

• Linux/macOS: Use cron. Run crontab -e and add this line (adjust paths to match your setup):

  0 0 * * * /usr/bin/python3 /path/to/your/tracking_script.py >> /path/to/logs/twitter_tracking.log 2>&1
  

  This runs the script at midnight every day and logs output.
• Windows: Use Task Scheduler. Create a basic task with a daily trigger, and set the action to run your Python script.

2.5 Generate Instagram-Style Growth Charts #

Use matplotlib to create a clean, Instagram-like line chart. Here's how:

import matplotlib.pyplot as plt
import sqlite3
from datetime import datetime

def generate_growth_chart(ico_id):
    conn = sqlite3.connect("your_database.db")
    cursor = conn.cursor()
    
    # Fetch historical data for the project
    query = """
    SELECT recorded_at, follower_count
    FROM Twitter_Tracking
    WHERE ico_id = ?
    ORDER BY recorded_at ASC
    """
    cursor.execute(query, (ico_id,))
    data = cursor.fetchall()
    
    dates = [datetime.strptime(row[0], "%Y-%m-%d %H:%M:%S") for row in data]
    followers = [row[1] for row in data]
    
    # Style the chart to match Instagram's aesthetic
    plt.figure(figsize=(10, 6))
    plt.plot(dates, followers, marker="o", linestyle="-", color="#E1306C", linewidth=2)
    plt.title(f"Twitter Follower Growth: Project {ico_id}", fontsize=14)
    plt.xlabel("Date", fontsize=12)
    plt.ylabel("Follower Count", fontsize=12)
    plt.xticks(rotation=45)
    plt.tight_layout()
    plt.savefig(f"project_{ico_id}_growth_chart.png", dpi=300)
    plt.close()
    
    conn.close()

• API Rate Limits: If using the Twitter API, add small delays between requests to avoid hitting rate limits.
• Error Handling: Expand the scripts with try/except blocks to handle network drops, database errors, or missing data gracefully.
• Data Retention: Decide how long to keep historical data—older records can be archived if needed to keep queries fast.

内容的提问来源于stack exchange，提问作者Vainius Mykolaitis