Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- import requests
- import sqlite3
- import time
- import logging
- import datetime
- from datetime import datetime
- API_KEY = "URL KEY"
- API_URL = "API URL"
- API_HEADERS = {
- "User-Agent": "ComicVine Metadata Downloader"
- }
- DATABASE_FILE = "comic_metadata.db"
- OFFSET_INCREMENT = 100
- RATE_LIMIT = 750 # Number of API calls per hour
- RATE_PERIOD = 3600 # Time period in seconds
- RATE_DELAY = RATE_PERIOD / RATE_LIMIT # Time to wait between API requests in seconds
- def setup_database():
- conn = sqlite3.connect(DATABASE_FILE)
- cur = conn.cursor()
- cur.execute('''
- CREATE TABLE IF NOT EXISTS issues (
- id INTEGER PRIMARY KEY,
- title TEXT,
- issue_number TEXT,
- issue_name TEXT,
- publisher TEXT,
- cover_date TEXT,
- writers TEXT,
- synopsis TEXT,
- image_url TEXT,
- aliases TEXT,
- characters TEXT,
- story_arcs TEXT,
- teams TEXT,
- volume_id INTEGER
- )
- ''')
- cur.execute('''
- CREATE TABLE IF NOT EXISTS volumes (
- id INTEGER PRIMARY KEY,
- name TEXT,
- publisher TEXT,
- start_year INTEGER,
- count_of_issues INTEGER,
- completed INTEGER DEFAULT 0,
- aliases TEXT,
- genres TEXT,
- last_scan_date TEXT
- )
- ''')
- cur.execute("CREATE INDEX IF NOT EXISTS idx_issues_id ON issues (id);")
- cur.execute('''
- CREATE TABLE IF NOT EXISTS last_offset (
- id INTEGER PRIMARY KEY,
- data_type TEXT NOT NULL,
- offset INTEGER NOT NULL
- )
- ''')
- cur.execute('''
- CREATE TABLE IF NOT EXISTS last_update (
- id INTEGER PRIMARY KEY,
- data_type TEXT NOT NULL,
- timestamp TEXT NOT NULL
- )
- ''')
- # Update the 'completed' field of all records in the 'volumes' table where 'completed' is NULL, setting it to 0.
- cur.execute('''
- UPDATE volumes
- SET completed = 0
- WHERE completed IS NULL
- ''')
- conn.commit()
- return conn
- def setup_logging():
- current_date = datetime.now().strftime("%Y-%m-%d")
- log_filename = f"comic_downloader_{current_date}.log"
- # Create a file handler
- file_handler = logging.FileHandler(log_filename)
- file_handler.setLevel(logging.INFO)
- # Create a console handler
- console_handler = logging.StreamHandler()
- console_handler.setLevel(logging.INFO)
- # Create a formatter and set it for both handlers
- formatter = logging.Formatter('%(asctime)s [%(levelname)s] %(message)s', datefmt='%Y-%m-%d %H:%M:%S')
- file_handler.setFormatter(formatter)
- console_handler.setFormatter(formatter)
- # Get the root logger and add both handlers to it
- logger = logging.getLogger()
- logger.setLevel(logging.INFO) # Set the minimum logging level to INFO
- logger.addHandler(file_handler)
- logger.addHandler(console_handler)
- def get_data_from_api(api_key, data_type, offset, limit, field_list=None, filter=None):
- params = {
- "api_key": api_key,
- "format": "json",
- "offset": offset,
- "limit": limit
- }
- if field_list:
- params["field_list"] = field_list
- if filter:
- params["filter"] = filter
- url = f"{API_URL}/{data_type}"
- try:
- response = requests.get(url, headers=API_HEADERS, params=params)
- response.raise_for_status()
- time.sleep(RATE_DELAY) # Add delay here
- except requests.exceptions.RequestException as e:
- logging.error(f"Failed to get {data_type} data from API. Error: {e}")
- return None
- if response.status_code == 200:
- return response.json()
- else:
- logging.error(f"Failed to get {data_type} data from API. Status code: {response.status_code}")
- return None
- def save_issues_to_database(conn, issues, log_file):
- cur = conn.cursor()
- for issue in issues:
- # Set default values if keys do not exist
- volume_id = issue["volume"].get("id", "")
- title = issue["volume"].get("name", "")
- issue_number = issue.get("issue_number", "")
- issue_name = issue.get("name", "")
- volume_publisher = issue["volume"].get("publisher", {})
- publisher_name = volume_publisher.get("name", "")
- cover_date = issue.get("cover_date", "") # getting cover_date from issue dictionary
- # Fields added to match the structure of issues table
- writers = ""
- synopsis = ""
- image_url = ""
- aliases = ""
- characters = ""
- story_arcs = ""
- teams = ""
- # Log information
- log_file.write(f"Saving issue {issue['id']} - {title} #{issue_number} - {issue_name} ({publisher_name})\n")
- # If issue exists, update it. Otherwise, insert new record.
- cur.execute("""
- INSERT OR REPLACE INTO issues
- (id, volume_id, title, issue_number, issue_name, publisher, cover_date, writers, synopsis, image_url, aliases, characters, story_arcs, teams)
- VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
- """,
- (issue["id"], volume_id, title, issue_number, issue_name, publisher_name, cover_date, writers, synopsis, image_url, aliases, characters, story_arcs, teams))
- # This will only update fields that are not null
- cur.execute("""
- UPDATE issues
- SET volume_id = COALESCE(NULLIF(?, ''), volume_id),
- title = COALESCE(NULLIF(?, ''), title),
- issue_number = COALESCE(NULLIF(?, ''), issue_number),
- issue_name = COALESCE(NULLIF(?, ''), issue_name),
- publisher = COALESCE(NULLIF(?, ''), publisher),
- cover_date = COALESCE(NULLIF(?, ''), cover_date),
- writers = COALESCE(NULLIF(?, ''), writers),
- synopsis = COALESCE(NULLIF(?, ''), synopsis),
- image_url = COALESCE(NULLIF(?, ''), image_url),
- aliases = COALESCE(NULLIF(?, ''), aliases),
- characters = COALESCE(NULLIF(?, ''), characters),
- story_arcs = COALESCE(NULLIF(?, ''), story_arcs),
- teams = COALESCE(NULLIF(?, ''), teams)
- WHERE id = ?
- """,
- (volume_id, title, issue_number, issue_name, publisher_name, cover_date, writers, synopsis, image_url, aliases, characters, story_arcs, teams, issue["id"]))
- conn.commit()
- def save_volumes_to_database(conn, volumes, log_file):
- cur = conn.cursor()
- for volume in volumes:
- # Set default values if keys do not exist
- publisher_name = volume.get("publisher", {}).get("name", "unknown") if volume.get("publisher") else "unknown"
- genres = volume.get("genres", [])
- genre_names = ', '.join([genre.get("name") for genre in genres]) if genres else None
- # Handling None values in 'completed' field
- completed = volume.get("completed")
- if completed is None:
- completed = 0 # replace None with 0
- # Log information
- log_file.write(f"Saving volume {volume['id']} - {volume['name']} ({publisher_name})\n")
- # If volume exists, update it. Otherwise, insert new record.
- cur.execute("""
- INSERT OR REPLACE INTO volumes
- (id, name, publisher, start_year, count_of_issues, completed, aliases, genres, last_scan_date)
- VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
- """,
- (volume["id"], volume["name"], publisher_name, volume.get("start_year"), volume.get("count_of_issues"),
- completed, volume.get("aliases"), genre_names, volume.get("date_last_updated")))
- # This will only update fields that are not null
- cur.execute("""
- UPDATE volumes
- SET name = COALESCE(NULLIF(?, ''), name),
- publisher = COALESCE(NULLIF(?, ''), publisher),
- start_year = COALESCE(NULLIF(?, ''), start_year),
- count_of_issues = COALESCE(NULLIF(?, ''), count_of_issues),
- completed = COALESCE(NULLIF(?, ''), completed),
- aliases = COALESCE(NULLIF(?, ''), aliases),
- genres = COALESCE(NULLIF(?, ''), genres),
- last_scan_date = COALESCE(NULLIF(?, ''), last_scan_date)
- WHERE id = ?
- """,
- (volume["name"], publisher_name, volume.get("start_year"), volume.get("count_of_issues"),
- completed, volume.get("aliases"), genre_names, volume.get("date_last_updated"), volume["id"]))
- conn.commit()
- def get_last_offset(conn, data_type):
- cur = conn.cursor()
- cur.execute("SELECT offset FROM last_offset WHERE data_type = ? ORDER BY id DESC LIMIT 1", (data_type,))
- result = cur.fetchone()
- return result[0] if result else 0
- def save_last_offset(conn, data_type, offset):
- cur = conn.cursor()
- cur.execute("INSERT INTO last_offset (data_type, offset) VALUES (?, ?)", (data_type, offset))
- conn.commit()
- def get_last_fetched_volume_id(conn):
- cur = conn.cursor()
- cur.execute("SELECT id FROM volumes ORDER BY id DESC LIMIT 1")
- result = cur.fetchone()
- return result[0] if result else 0
- def get_volumes_to_fetch_issues(conn):
- cur = conn.cursor()
- cur.execute("SELECT volume_id, MAX(cover_date) FROM issues WHERE volume_id NOT IN (SELECT id FROM volumes WHERE completed = 1) GROUP BY volume_id")
- result = cur.fetchall()
- volume_ids_and_latest_dates = {volume_id: latest_date for volume_id, latest_date in result}
- return volume_ids_and_latest_dates
- def get_last_updated(conn, data_type):
- cur = conn.cursor()
- cur.execute("SELECT timestamp FROM last_update WHERE data_type = ? ORDER BY id DESC LIMIT 1", (data_type,))
- result = cur.fetchone()
- return result[0] if result else "1970-01-01T00:00:00"
- def save_last_updated(conn, data_type, timestamp):
- cur = conn.cursor()
- cur.execute("INSERT INTO last_update (data_type, timestamp) VALUES (?, ?)", (data_type, timestamp))
- conn.commit()
- logging.info(f"Updated last_update for {data_type} to {timestamp}")
- def main():
- setup_logging()
- conn = setup_database()
- current_date = datetime.now().strftime("%Y-%m-%d")
- log_filename = f"changes_{current_date}.txt"
- with open(log_filename, "w", encoding='utf-8') as log_file:
- # Step 1 - Fetch only volumes that are not marked as completed
- volumes_filter = "completed:0"
- volume_offset = 0
- while True:
- volumes = get_data_from_api(API_KEY, "volumes", volume_offset, OFFSET_INCREMENT, field_list="id,name,publisher,start_year,count_of_issues,completed,aliases,genres", filter=volumes_filter)
- if volumes is None or not volumes["results"]:
- break
- # Filter out volumes that are marked as completed in the local database
- cur = conn.cursor()
- cur.execute("SELECT id FROM volumes WHERE completed = 1")
- completed_volume_ids = {row[0] for row in cur.fetchall()}
- volumes_to_save = [volume for volume in volumes["results"] if volume["id"] not in completed_volume_ids]
- save_volumes_to_database(conn, volumes_to_save, log_file)
- volume_offset += OFFSET_INCREMENT
- # Step 2 - Fetch new ID/volumes/series
- last_updated = get_last_updated(conn, "volumes")
- volume_offset = get_last_offset(conn, "volumes")
- while True:
- volumes = get_data_from_api(API_KEY, "volumes", volume_offset, OFFSET_INCREMENT, field_list="id,name,publisher,start_year,count_of_issues,completed,aliases,genres", filter=f"date_last_updated>{last_updated}")
- if volumes is None or not volumes["results"]:
- break
- # Filter out volumes that are marked as completed in the local database
- volumes_to_save = [volume for volume in volumes["results"] if volume["id"] not in completed_volume_ids]
- save_volumes_to_database(conn, volumes_to_save, log_file)
- volume_offset += OFFSET_INCREMENT
- save_last_offset(conn, "volumes", volume_offset)
- volume_ids_and_latest_dates = get_volumes_to_fetch_issues(conn)
- for volume_id, latest_issue_date in volume_ids_and_latest_dates.items():
- last_scan_date = datetime.now() - datetime.strptime(latest_issue_date, "%Y-%m-%d") if latest_issue_date else None
- # Skip scanning the volume if it has been scanned within the last week
- if last_scan_date and last_scan_date.days < 7:
- continue
- last_updated = get_last_updated(conn, f"issues_{volume_id}")
- issue_offset = get_last_offset(conn, f"issues_{volume_id}")
- while True:
- logging.info(f"Fetching issues for volume {volume_id} starting from offset {issue_offset}")
- issues = get_data_from_api(API_KEY, "issues", issue_offset, OFFSET_INCREMENT, filter=f"volume:{volume_id};date_last_updated>{last_updated}")
- if issues is None or not issues["results"]:
- break
- save_issues_to_database(conn, issues["results"], log_file)
- issue_offset += OFFSET_INCREMENT
- save_last_offset(conn, f"issues_{volume_id}", issue_offset)
- # Update the last_scan_date for the volume after scanning its issues
- cur = conn.cursor()
- cur.execute("UPDATE volumes SET last_scan_date = ? WHERE id = ?", (datetime.now().strftime("%Y-%m-%d"), volume_id))
- log_file.write(f"Updated last_scan_date for volume {volume_id} to {datetime.now().strftime('%Y-%m-%d')}\n")
- conn.commit()
- # Mark the volume as completed if the latest issue date is older than 13 months
- if latest_issue_date:
- latest_issue_date = datetime.strptime(latest_issue_date, "%Y-%m-%d")
- if (datetime.now() - latest_issue_date).days > 30 * 13:
- cur = conn.cursor()
- cur.execute("UPDATE volumes SET completed = 1 WHERE id = ?", (volume_id,))
- conn.commit()
- logging.info("Data collection completed")
- conn.close()
- if __name__ == "__main__":
- main()
Advertisement
Add Comment
Please, Sign In to add comment