Guest User

comicmd

a guest
May 26th, 2023
123
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 14.34 KB | None | 0 0
  1. import requests
  2. import sqlite3
  3. import time
  4. import logging
  5. import datetime
  6. from datetime import datetime
  7.  
  8. API_KEY = "URL KEY"
  9. API_URL = "API URL"
  10. API_HEADERS = {
  11. "User-Agent": "ComicVine Metadata Downloader"
  12. }
  13.  
  14. DATABASE_FILE = "comic_metadata.db"
  15. OFFSET_INCREMENT = 100
  16.  
  17. RATE_LIMIT = 750 # Number of API calls per hour
  18. RATE_PERIOD = 3600 # Time period in seconds
  19. RATE_DELAY = RATE_PERIOD / RATE_LIMIT # Time to wait between API requests in seconds
  20.  
  21.  
  22. def setup_database():
  23. conn = sqlite3.connect(DATABASE_FILE)
  24. cur = conn.cursor()
  25.  
  26. cur.execute('''
  27. CREATE TABLE IF NOT EXISTS issues (
  28. id INTEGER PRIMARY KEY,
  29. title TEXT,
  30. issue_number TEXT,
  31. issue_name TEXT,
  32. publisher TEXT,
  33. cover_date TEXT,
  34. writers TEXT,
  35. synopsis TEXT,
  36. image_url TEXT,
  37. aliases TEXT,
  38. characters TEXT,
  39. story_arcs TEXT,
  40. teams TEXT,
  41. volume_id INTEGER
  42. )
  43. ''')
  44.  
  45. cur.execute('''
  46. CREATE TABLE IF NOT EXISTS volumes (
  47. id INTEGER PRIMARY KEY,
  48. name TEXT,
  49. publisher TEXT,
  50. start_year INTEGER,
  51. count_of_issues INTEGER,
  52. completed INTEGER DEFAULT 0,
  53. aliases TEXT,
  54. genres TEXT,
  55. last_scan_date TEXT
  56. )
  57. ''')
  58.  
  59. cur.execute("CREATE INDEX IF NOT EXISTS idx_issues_id ON issues (id);")
  60.  
  61. cur.execute('''
  62. CREATE TABLE IF NOT EXISTS last_offset (
  63. id INTEGER PRIMARY KEY,
  64. data_type TEXT NOT NULL,
  65. offset INTEGER NOT NULL
  66. )
  67. ''')
  68.  
  69. cur.execute('''
  70. CREATE TABLE IF NOT EXISTS last_update (
  71. id INTEGER PRIMARY KEY,
  72. data_type TEXT NOT NULL,
  73. timestamp TEXT NOT NULL
  74. )
  75. ''')
  76.  
  77. # Update the 'completed' field of all records in the 'volumes' table where 'completed' is NULL, setting it to 0.
  78. cur.execute('''
  79. UPDATE volumes
  80. SET completed = 0
  81. WHERE completed IS NULL
  82. ''')
  83.  
  84. conn.commit()
  85. return conn
  86.  
  87.  
  88.  
  89. def setup_logging():
  90. current_date = datetime.now().strftime("%Y-%m-%d")
  91. log_filename = f"comic_downloader_{current_date}.log"
  92.  
  93. # Create a file handler
  94. file_handler = logging.FileHandler(log_filename)
  95. file_handler.setLevel(logging.INFO)
  96.  
  97. # Create a console handler
  98. console_handler = logging.StreamHandler()
  99. console_handler.setLevel(logging.INFO)
  100.  
  101. # Create a formatter and set it for both handlers
  102. formatter = logging.Formatter('%(asctime)s [%(levelname)s] %(message)s', datefmt='%Y-%m-%d %H:%M:%S')
  103. file_handler.setFormatter(formatter)
  104. console_handler.setFormatter(formatter)
  105.  
  106. # Get the root logger and add both handlers to it
  107. logger = logging.getLogger()
  108. logger.setLevel(logging.INFO) # Set the minimum logging level to INFO
  109. logger.addHandler(file_handler)
  110. logger.addHandler(console_handler)
  111.  
  112.  
  113.  
  114. def get_data_from_api(api_key, data_type, offset, limit, field_list=None, filter=None):
  115. params = {
  116. "api_key": api_key,
  117. "format": "json",
  118. "offset": offset,
  119. "limit": limit
  120. }
  121.  
  122. if field_list:
  123. params["field_list"] = field_list
  124.  
  125. if filter:
  126. params["filter"] = filter
  127.  
  128. url = f"{API_URL}/{data_type}"
  129. try:
  130. response = requests.get(url, headers=API_HEADERS, params=params)
  131. response.raise_for_status()
  132. time.sleep(RATE_DELAY) # Add delay here
  133. except requests.exceptions.RequestException as e:
  134. logging.error(f"Failed to get {data_type} data from API. Error: {e}")
  135. return None
  136.  
  137. if response.status_code == 200:
  138. return response.json()
  139. else:
  140. logging.error(f"Failed to get {data_type} data from API. Status code: {response.status_code}")
  141. return None
  142.  
  143.  
  144.  
  145. def save_issues_to_database(conn, issues, log_file):
  146. cur = conn.cursor()
  147. for issue in issues:
  148. # Set default values if keys do not exist
  149. volume_id = issue["volume"].get("id", "")
  150. title = issue["volume"].get("name", "")
  151. issue_number = issue.get("issue_number", "")
  152. issue_name = issue.get("name", "")
  153. volume_publisher = issue["volume"].get("publisher", {})
  154. publisher_name = volume_publisher.get("name", "")
  155. cover_date = issue.get("cover_date", "") # getting cover_date from issue dictionary
  156.  
  157. # Fields added to match the structure of issues table
  158. writers = ""
  159. synopsis = ""
  160. image_url = ""
  161. aliases = ""
  162. characters = ""
  163. story_arcs = ""
  164. teams = ""
  165.  
  166. # Log information
  167. log_file.write(f"Saving issue {issue['id']} - {title} #{issue_number} - {issue_name} ({publisher_name})\n")
  168.  
  169. # If issue exists, update it. Otherwise, insert new record.
  170. cur.execute("""
  171. INSERT OR REPLACE INTO issues
  172. (id, volume_id, title, issue_number, issue_name, publisher, cover_date, writers, synopsis, image_url, aliases, characters, story_arcs, teams)
  173. VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  174. """,
  175. (issue["id"], volume_id, title, issue_number, issue_name, publisher_name, cover_date, writers, synopsis, image_url, aliases, characters, story_arcs, teams))
  176. # This will only update fields that are not null
  177. cur.execute("""
  178. UPDATE issues
  179. SET volume_id = COALESCE(NULLIF(?, ''), volume_id),
  180. title = COALESCE(NULLIF(?, ''), title),
  181. issue_number = COALESCE(NULLIF(?, ''), issue_number),
  182. issue_name = COALESCE(NULLIF(?, ''), issue_name),
  183. publisher = COALESCE(NULLIF(?, ''), publisher),
  184. cover_date = COALESCE(NULLIF(?, ''), cover_date),
  185. writers = COALESCE(NULLIF(?, ''), writers),
  186. synopsis = COALESCE(NULLIF(?, ''), synopsis),
  187. image_url = COALESCE(NULLIF(?, ''), image_url),
  188. aliases = COALESCE(NULLIF(?, ''), aliases),
  189. characters = COALESCE(NULLIF(?, ''), characters),
  190. story_arcs = COALESCE(NULLIF(?, ''), story_arcs),
  191. teams = COALESCE(NULLIF(?, ''), teams)
  192. WHERE id = ?
  193. """,
  194. (volume_id, title, issue_number, issue_name, publisher_name, cover_date, writers, synopsis, image_url, aliases, characters, story_arcs, teams, issue["id"]))
  195. conn.commit()
  196.  
  197.  
  198.  
  199.  
  200.  
  201. def save_volumes_to_database(conn, volumes, log_file):
  202. cur = conn.cursor()
  203.  
  204. for volume in volumes:
  205. # Set default values if keys do not exist
  206. publisher_name = volume.get("publisher", {}).get("name", "unknown") if volume.get("publisher") else "unknown"
  207. genres = volume.get("genres", [])
  208. genre_names = ', '.join([genre.get("name") for genre in genres]) if genres else None
  209.  
  210. # Handling None values in 'completed' field
  211. completed = volume.get("completed")
  212. if completed is None:
  213. completed = 0 # replace None with 0
  214.  
  215. # Log information
  216. log_file.write(f"Saving volume {volume['id']} - {volume['name']} ({publisher_name})\n")
  217.  
  218. # If volume exists, update it. Otherwise, insert new record.
  219. cur.execute("""
  220. INSERT OR REPLACE INTO volumes
  221. (id, name, publisher, start_year, count_of_issues, completed, aliases, genres, last_scan_date)
  222. VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
  223. """,
  224. (volume["id"], volume["name"], publisher_name, volume.get("start_year"), volume.get("count_of_issues"),
  225. completed, volume.get("aliases"), genre_names, volume.get("date_last_updated")))
  226.  
  227. # This will only update fields that are not null
  228. cur.execute("""
  229. UPDATE volumes
  230. SET name = COALESCE(NULLIF(?, ''), name),
  231. publisher = COALESCE(NULLIF(?, ''), publisher),
  232. start_year = COALESCE(NULLIF(?, ''), start_year),
  233. count_of_issues = COALESCE(NULLIF(?, ''), count_of_issues),
  234. completed = COALESCE(NULLIF(?, ''), completed),
  235. aliases = COALESCE(NULLIF(?, ''), aliases),
  236. genres = COALESCE(NULLIF(?, ''), genres),
  237. last_scan_date = COALESCE(NULLIF(?, ''), last_scan_date)
  238. WHERE id = ?
  239. """,
  240. (volume["name"], publisher_name, volume.get("start_year"), volume.get("count_of_issues"),
  241. completed, volume.get("aliases"), genre_names, volume.get("date_last_updated"), volume["id"]))
  242. conn.commit()
  243.  
  244.  
  245.  
  246. def get_last_offset(conn, data_type):
  247. cur = conn.cursor()
  248. cur.execute("SELECT offset FROM last_offset WHERE data_type = ? ORDER BY id DESC LIMIT 1", (data_type,))
  249. result = cur.fetchone()
  250. return result[0] if result else 0
  251.  
  252. def save_last_offset(conn, data_type, offset):
  253. cur = conn.cursor()
  254. cur.execute("INSERT INTO last_offset (data_type, offset) VALUES (?, ?)", (data_type, offset))
  255. conn.commit()
  256.  
  257. def get_last_fetched_volume_id(conn):
  258. cur = conn.cursor()
  259. cur.execute("SELECT id FROM volumes ORDER BY id DESC LIMIT 1")
  260. result = cur.fetchone()
  261. return result[0] if result else 0
  262.  
  263. def get_volumes_to_fetch_issues(conn):
  264. cur = conn.cursor()
  265. 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")
  266. result = cur.fetchall()
  267. volume_ids_and_latest_dates = {volume_id: latest_date for volume_id, latest_date in result}
  268. return volume_ids_and_latest_dates
  269.  
  270.  
  271.  
  272.  
  273.  
  274. def get_last_updated(conn, data_type):
  275. cur = conn.cursor()
  276. cur.execute("SELECT timestamp FROM last_update WHERE data_type = ? ORDER BY id DESC LIMIT 1", (data_type,))
  277. result = cur.fetchone()
  278. return result[0] if result else "1970-01-01T00:00:00"
  279.  
  280. def save_last_updated(conn, data_type, timestamp):
  281. cur = conn.cursor()
  282. cur.execute("INSERT INTO last_update (data_type, timestamp) VALUES (?, ?)", (data_type, timestamp))
  283. conn.commit()
  284. logging.info(f"Updated last_update for {data_type} to {timestamp}")
  285.  
  286. def main():
  287. setup_logging()
  288. conn = setup_database()
  289.  
  290. current_date = datetime.now().strftime("%Y-%m-%d")
  291. log_filename = f"changes_{current_date}.txt"
  292.  
  293. with open(log_filename, "w", encoding='utf-8') as log_file:
  294. # Step 1 - Fetch only volumes that are not marked as completed
  295. volumes_filter = "completed:0"
  296. volume_offset = 0
  297. while True:
  298. 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)
  299. if volumes is None or not volumes["results"]:
  300. break
  301.  
  302. # Filter out volumes that are marked as completed in the local database
  303. cur = conn.cursor()
  304. cur.execute("SELECT id FROM volumes WHERE completed = 1")
  305. completed_volume_ids = {row[0] for row in cur.fetchall()}
  306. volumes_to_save = [volume for volume in volumes["results"] if volume["id"] not in completed_volume_ids]
  307.  
  308. save_volumes_to_database(conn, volumes_to_save, log_file)
  309. volume_offset += OFFSET_INCREMENT
  310.  
  311. # Step 2 - Fetch new ID/volumes/series
  312. last_updated = get_last_updated(conn, "volumes")
  313. volume_offset = get_last_offset(conn, "volumes")
  314. while True:
  315. 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}")
  316. if volumes is None or not volumes["results"]:
  317. break
  318.  
  319. # Filter out volumes that are marked as completed in the local database
  320. volumes_to_save = [volume for volume in volumes["results"] if volume["id"] not in completed_volume_ids]
  321.  
  322. save_volumes_to_database(conn, volumes_to_save, log_file)
  323. volume_offset += OFFSET_INCREMENT
  324. save_last_offset(conn, "volumes", volume_offset)
  325.  
  326. volume_ids_and_latest_dates = get_volumes_to_fetch_issues(conn)
  327. for volume_id, latest_issue_date in volume_ids_and_latest_dates.items():
  328. last_scan_date = datetime.now() - datetime.strptime(latest_issue_date, "%Y-%m-%d") if latest_issue_date else None
  329.  
  330. # Skip scanning the volume if it has been scanned within the last week
  331. if last_scan_date and last_scan_date.days < 7:
  332. continue
  333.  
  334. last_updated = get_last_updated(conn, f"issues_{volume_id}")
  335. issue_offset = get_last_offset(conn, f"issues_{volume_id}")
  336. while True:
  337. logging.info(f"Fetching issues for volume {volume_id} starting from offset {issue_offset}")
  338. issues = get_data_from_api(API_KEY, "issues", issue_offset, OFFSET_INCREMENT, filter=f"volume:{volume_id};date_last_updated>{last_updated}")
  339. if issues is None or not issues["results"]:
  340. break
  341.  
  342. save_issues_to_database(conn, issues["results"], log_file)
  343. issue_offset += OFFSET_INCREMENT
  344. save_last_offset(conn, f"issues_{volume_id}", issue_offset)
  345.  
  346. # Update the last_scan_date for the volume after scanning its issues
  347. cur = conn.cursor()
  348. cur.execute("UPDATE volumes SET last_scan_date = ? WHERE id = ?", (datetime.now().strftime("%Y-%m-%d"), volume_id))
  349. log_file.write(f"Updated last_scan_date for volume {volume_id} to {datetime.now().strftime('%Y-%m-%d')}\n")
  350. conn.commit()
  351.  
  352. # Mark the volume as completed if the latest issue date is older than 13 months
  353. if latest_issue_date:
  354. latest_issue_date = datetime.strptime(latest_issue_date, "%Y-%m-%d")
  355. if (datetime.now() - latest_issue_date).days > 30 * 13:
  356. cur = conn.cursor()
  357. cur.execute("UPDATE volumes SET completed = 1 WHERE id = ?", (volume_id,))
  358. conn.commit()
  359.  
  360. logging.info("Data collection completed")
  361. conn.close()
  362.  
  363.  
  364.  
  365.  
  366. if __name__ == "__main__":
  367. main()
  368.  
  369.  
Advertisement
Add Comment
Please, Sign In to add comment