wall-o
b170dfd577
- Added MTGJSON data downloader (downloads all MTGJSON API v5 files)
- Added MTGJSON data loader (imports data into PostgreSQL)
- Added MTGJSON data uploader (alternative upsert logic)
- Fixed route ordering in card_router.py (/sets before /{card_name})
- Added load_mtgjson_data.py entry point script
MTGJSON data sources:
- AllPrintings.psql.gz (main cards database)
- AllSetFiles.zip (set and card data)
- AllDeckFiles.zip (deck data)
- AllIdentifiers.json.gz (card identifiers)
- CardTypes.json.gz (card types)
- DeckList.json.gz (deck list metadata)
- Keywords.json.gz (card keywords)
- SetList.json.gz (set list metadata)
Note: MTGJSON set.json does NOT contain image URLs. Only cards have image_uris.
Sets have iconSvgUrl (SVG icons) but no raster image URLs.
839 lines
32 KiB
Python
839 lines
32 KiB
Python
#!/usr/bin/env python3
|
|
"""
|
|
MTGJSON Data Loader
|
|
|
|
Downloads and loads all MTGJSON data into the PostgreSQL database.
|
|
Designed to run inside the Docker container.
|
|
"""
|
|
|
|
import asyncio
|
|
import gzip
|
|
import json
|
|
import os
|
|
import shutil
|
|
import sys
|
|
import zipfile
|
|
from pathlib import Path
|
|
from datetime import datetime
|
|
from typing import Optional
|
|
|
|
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
|
|
from sqlalchemy.orm import sessionmaker
|
|
from sqlalchemy import text, insert, update, select, Table, Column, String, Text, Integer, Float, Boolean, DateTime, MetaData, UniqueConstraint
|
|
from sqlalchemy.dialects.postgresql import insert as pg_insert
|
|
|
|
from app.core.settings import get_settings
|
|
|
|
# MTGJSON API v5 base URL
|
|
MTGJSON_API_V5 = "https://mtgjson.com/api/v5"
|
|
|
|
|
|
async def create_tables(engine):
|
|
"""Create all required database tables."""
|
|
async with engine.begin() as conn:
|
|
await conn.execute(text("""
|
|
CREATE TABLE IF NOT EXISTS cards (
|
|
id SERIAL PRIMARY KEY,
|
|
artist TEXT,
|
|
asciiName TEXT,
|
|
attractionLights TEXT,
|
|
availability TEXT,
|
|
boosterTypes TEXT,
|
|
borderColor TEXT,
|
|
cardParts TEXT,
|
|
colorIdentity TEXT,
|
|
colorIndicator TEXT,
|
|
colors TEXT,
|
|
defense TEXT,
|
|
duelDeck TEXT,
|
|
edhrecRank INTEGER,
|
|
edhrecSaltiness FLOAT,
|
|
faceConvertedManaCost FLOAT,
|
|
faceFlavorName TEXT,
|
|
faceManaValue FLOAT,
|
|
faceName TEXT,
|
|
facePrintedName TEXT,
|
|
finishes TEXT,
|
|
flavorName TEXT,
|
|
flavorText TEXT,
|
|
frameEffects TEXT,
|
|
frameVersion TEXT,
|
|
hand TEXT,
|
|
hasAlternativeDeckLimit BOOLEAN,
|
|
hasContentWarning BOOLEAN,
|
|
isAlternative BOOLEAN,
|
|
isFullArt BOOLEAN,
|
|
isFunny BOOLEAN,
|
|
isGameChanger BOOLEAN,
|
|
isOnlineOnly BOOLEAN,
|
|
isOversized BOOLEAN,
|
|
isPromo BOOLEAN,
|
|
isRebalanced BOOLEAN,
|
|
isReprint BOOLEAN,
|
|
isReserved BOOLEAN,
|
|
isStorySpotlight BOOLEAN,
|
|
isTextless BOOLEAN,
|
|
isTimeshifted BOOLEAN,
|
|
keywords TEXT,
|
|
language TEXT,
|
|
layout TEXT,
|
|
leadershipSkills TEXT,
|
|
life TEXT,
|
|
loyalty TEXT,
|
|
manaCost TEXT,
|
|
manaValue FLOAT,
|
|
name TEXT,
|
|
number TEXT,
|
|
originalPrintings TEXT,
|
|
originalReleaseDate TEXT,
|
|
originalText TEXT,
|
|
otherFaceIds TEXT,
|
|
power TEXT,
|
|
printedName TEXT,
|
|
printedText TEXT,
|
|
printedType TEXT,
|
|
printings TEXT,
|
|
producedMana TEXT,
|
|
promoTypes TEXT,
|
|
rarity TEXT,
|
|
rebalancedPrintings TEXT,
|
|
relatedCards TEXT,
|
|
securityStamp TEXT,
|
|
setCode TEXT,
|
|
side TEXT,
|
|
signature TEXT,
|
|
skuIds TEXT,
|
|
sourceProducts TEXT,
|
|
subsets TEXT,
|
|
subtypes TEXT,
|
|
supertypes TEXT,
|
|
text TEXT,
|
|
toughness TEXT,
|
|
type TEXT,
|
|
types TEXT,
|
|
uuid TEXT,
|
|
variations TEXT,
|
|
watermark TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
)
|
|
"""))
|
|
|
|
await conn.execute(text("""
|
|
CREATE TABLE IF NOT EXISTS mtg_sets (
|
|
id SERIAL PRIMARY KEY,
|
|
code VARCHAR(10) UNIQUE NOT NULL,
|
|
name VARCHAR(255),
|
|
type VARCHAR(100),
|
|
release_date DATE,
|
|
base_set_size INTEGER,
|
|
total_size INTEGER,
|
|
is_foil_only BOOLEAN,
|
|
is_non_foil_only BOOLEAN,
|
|
digital BOOLEAN,
|
|
icon_svg_url TEXT,
|
|
parent_code VARCHAR(10),
|
|
mtgo_code VARCHAR(10),
|
|
card_count INTEGER,
|
|
image_url TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
)
|
|
"""))
|
|
|
|
await conn.execute(text("""
|
|
CREATE TABLE IF NOT EXISTS mtg_cards (
|
|
id SERIAL PRIMARY KEY,
|
|
set_id INTEGER REFERENCES mtg_sets(id) ON DELETE CASCADE,
|
|
name VARCHAR(255),
|
|
mana_cost VARCHAR(255),
|
|
type_line VARCHAR(255),
|
|
oracle_text TEXT,
|
|
power VARCHAR(50),
|
|
toughness VARCHAR(50),
|
|
rarity VARCHAR(50),
|
|
layout VARCHAR(50),
|
|
artist VARCHAR(255),
|
|
flavor_text TEXT,
|
|
numbers VARCHAR(100),
|
|
identifiers TEXT,
|
|
images TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
)
|
|
"""))
|
|
|
|
await conn.execute(text("""
|
|
CREATE TABLE IF NOT EXISTS card_identifiers (
|
|
id SERIAL PRIMARY KEY,
|
|
uuid TEXT UNIQUE NOT NULL,
|
|
name VARCHAR(255),
|
|
mana_cost VARCHAR(255),
|
|
type_line VARCHAR(255),
|
|
oracle_text TEXT,
|
|
power VARCHAR(50),
|
|
toughness VARCHAR(50),
|
|
rarity VARCHAR(50),
|
|
layout VARCHAR(50),
|
|
artist VARCHAR(255),
|
|
flavor_text TEXT,
|
|
set_code VARCHAR(10),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
)
|
|
"""))
|
|
|
|
await conn.execute(text("""
|
|
CREATE TABLE IF NOT EXISTS decks (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
format VARCHAR(50),
|
|
command TEXT,
|
|
commander TEXT,
|
|
creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
)
|
|
"""))
|
|
|
|
await conn.execute(text("""
|
|
CREATE TABLE IF NOT EXISTS card_types (
|
|
id SERIAL PRIMARY KEY,
|
|
type VARCHAR(100) UNIQUE NOT NULL,
|
|
description TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
)
|
|
"""))
|
|
|
|
await conn.execute(text("""
|
|
CREATE TABLE IF NOT EXISTS deck_list (
|
|
id SERIAL PRIMARY KEY,
|
|
deck_id VARCHAR(100) UNIQUE NOT NULL,
|
|
name VARCHAR(255),
|
|
description TEXT,
|
|
format VARCHAR(50),
|
|
command TEXT,
|
|
commander TEXT,
|
|
total_cards INTEGER,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
)
|
|
"""))
|
|
|
|
await conn.execute(text("""
|
|
CREATE TABLE IF NOT EXISTS card_keywords (
|
|
id SERIAL PRIMARY KEY,
|
|
keyword VARCHAR(100) UNIQUE NOT NULL,
|
|
description TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
)
|
|
"""))
|
|
|
|
await conn.execute(text("""
|
|
CREATE TABLE IF NOT EXISTS set_list (
|
|
id SERIAL PRIMARY KEY,
|
|
set_code VARCHAR(10) UNIQUE NOT NULL,
|
|
set_name VARCHAR(255),
|
|
set_type VARCHAR(100),
|
|
release_date DATE,
|
|
base_set_size INTEGER,
|
|
total_size INTEGER,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
)
|
|
"""))
|
|
|
|
await conn.commit()
|
|
print("✓ Database tables created")
|
|
|
|
|
|
def download_file(url: str, destination: Path) -> bool:
|
|
"""Download a file from URL to destination."""
|
|
from urllib.request import urlretrieve
|
|
|
|
try:
|
|
print(f"Downloading {url}...")
|
|
urlretrieve(url, destination)
|
|
size_mb = destination.stat().st_size / (1024 * 1024)
|
|
print(f" ✓ Downloaded ({size_mb:.1f} MB)")
|
|
return True
|
|
except Exception as e:
|
|
print(f" ✗ Failed: {e}")
|
|
return False
|
|
|
|
|
|
def extract_zip(zip_path: Path, extract_dir: Path) -> None:
|
|
"""Extract a zip file."""
|
|
if zip_path.exists():
|
|
if extract_dir.exists():
|
|
shutil.rmtree(extract_dir)
|
|
|
|
print(f"Extracting {zip_path.name}...")
|
|
with zipfile.ZipFile(zip_path, 'r') as zip_ref:
|
|
zip_ref.extractall(extract_dir)
|
|
|
|
file_count = len(list(extract_dir.glob('*.json')))
|
|
print(f" ✓ Extracted {file_count} files")
|
|
|
|
|
|
def get_all_printings_psql_file(downloads_dir: Path) -> Path:
|
|
"""Get or download AllPrintings.psql.gz."""
|
|
psql_file = downloads_dir / "AllPrintings.psql.gz"
|
|
|
|
if not psql_file.exists():
|
|
download_file(f"{MTGJSON_API_V5}/AllPrintings.psql.gz", psql_file)
|
|
|
|
return psql_file
|
|
|
|
|
|
def parse_psql_file(psql_file: Path) -> list[dict]:
|
|
"""Parse a PSQL file and extract card data.
|
|
|
|
The PSQL file contains a COPY statement with all card data.
|
|
We need to parse it and extract the data rows.
|
|
"""
|
|
cards = []
|
|
|
|
# Read the file and find the COPY section
|
|
with open(psql_file, 'r', encoding='utf-8', errors='replace') as f:
|
|
content = f.read()
|
|
|
|
# Find the COPY section
|
|
copy_start = content.find("COPY \"cards\"")
|
|
if copy_start == -1:
|
|
print("✗ Could not find COPY statement in PSQL file")
|
|
return []
|
|
|
|
# Find the FROM \. section
|
|
from_section = content.find("FROM \\.", copy_start)
|
|
if from_section == -1:
|
|
print("✗ Could not find FROM section in PSQL file")
|
|
return []
|
|
|
|
# Extract everything after FROM \.
|
|
data_section = content[from_section + len("FROM \\."):]
|
|
|
|
# Split into lines and filter out empty lines and comments
|
|
lines = data_section.split('\n')
|
|
data_lines = [line for line in lines if line.strip() and not line.startswith('--')]
|
|
|
|
# Parse each line (tab-separated values with \t representing tabs)
|
|
for line in data_lines:
|
|
if line == '\\.':
|
|
break
|
|
|
|
# Split by tab character (represented as \t in the file)
|
|
values = line.split('\t')
|
|
|
|
if len(values) >= 94: # We have at least 94 columns
|
|
# Map values to card fields
|
|
card = {
|
|
'artist': values[0],
|
|
'asciiName': values[1],
|
|
'attractionLights': values[2],
|
|
'availability': values[3],
|
|
'boosterTypes': values[4],
|
|
'borderColor': values[5],
|
|
'cardParts': values[6],
|
|
'colorIdentity': values[7],
|
|
'colorIndicator': values[8],
|
|
'colors': values[9],
|
|
'defense': values[10],
|
|
'duelDeck': values[11],
|
|
'edhrecRank': int(values[12]) if values[12] != '\\N' else None,
|
|
'edhrecSaltiness': float(values[13]) if values[13] != '\\N' else None,
|
|
'faceConvertedManaCost': float(values[14]) if values[14] != '\\N' else None,
|
|
'faceFlavorName': values[15],
|
|
'faceManaValue': float(values[16]) if values[16] != '\\N' else None,
|
|
'faceName': values[17],
|
|
'facePrintedName': values[18],
|
|
'finishes': values[19],
|
|
'flavorName': values[20],
|
|
'flavorText': values[21],
|
|
'frameEffects': values[22],
|
|
'frameVersion': values[23],
|
|
'hand': values[24],
|
|
'hasAlternativeDeckLimit': values[25] == 't',
|
|
'hasContentWarning': values[26] == 't',
|
|
'isAlternative': values[27] == 't',
|
|
'isFullArt': values[28] == 't',
|
|
'isFunny': values[29] == 't',
|
|
'isGameChanger': values[30] == 't',
|
|
'isOnlineOnly': values[31] == 't',
|
|
'isOversized': values[32] == 't',
|
|
'isPromo': values[33] == 't',
|
|
'isRebalanced': values[34] == 't',
|
|
'isReprint': values[35] == 't',
|
|
'isReserved': values[36] == 't',
|
|
'isStorySpotlight': values[37] == 't',
|
|
'isTextless': values[38] == 't',
|
|
'isTimeshifted': values[39] == 't',
|
|
'keywords': values[40],
|
|
'language': values[41],
|
|
'layout': values[42],
|
|
'leadershipSkills': values[43],
|
|
'life': values[44],
|
|
'loyalty': values[45],
|
|
'manaCost': values[46],
|
|
'manaValue': float(values[47]) if values[47] != '\\N' else None,
|
|
'name': values[48],
|
|
'number': values[49],
|
|
'originalPrintings': values[50],
|
|
'originalReleaseDate': values[51],
|
|
'originalText': values[52],
|
|
'otherFaceIds': values[53],
|
|
'power': values[54],
|
|
'printedName': values[55],
|
|
'printedText': values[56],
|
|
'printedType': values[57],
|
|
'printings': values[58],
|
|
'producedMana': values[59],
|
|
'promoTypes': values[60],
|
|
'rarity': values[61],
|
|
'rebalancedPrintings': values[62],
|
|
'relatedCards': values[63],
|
|
'securityStamp': values[64],
|
|
'setCode': values[65],
|
|
'side': values[66],
|
|
'signature': values[67],
|
|
'skuIds': values[68],
|
|
'sourceProducts': values[69],
|
|
'subsets': values[70],
|
|
'subtypes': values[71],
|
|
'supertypes': values[72],
|
|
'text': values[73],
|
|
'toughness': values[74],
|
|
'type': values[75],
|
|
'types': values[76],
|
|
'uuid': values[77],
|
|
'variations': values[78],
|
|
'watermark': values[79],
|
|
}
|
|
|
|
cards.append(card)
|
|
|
|
return cards
|
|
|
|
|
|
async def import_cards(engine, cards: list[dict]) -> int:
|
|
"""Import cards data."""
|
|
count = 0
|
|
|
|
async with engine.begin() as conn:
|
|
for card in cards:
|
|
await conn.execute(
|
|
pg_insert(text('cards')).values(card).on_conflict_do_nothing(),
|
|
execution_options={"autocommit": True}
|
|
)
|
|
count += 1
|
|
|
|
print(f"✓ Imported {count:,} cards")
|
|
return count
|
|
|
|
|
|
async def import_all_printings_psql(engine, psql_file: Path) -> int:
|
|
"""Import AllPrintings.psql.gz."""
|
|
if not psql_file.exists():
|
|
print("✗ AllPrintings.psql.gz not found")
|
|
return 0
|
|
|
|
print("Importing AllPrintings...")
|
|
|
|
# Parse the PSQL file
|
|
cards = parse_psql_file(psql_file)
|
|
|
|
if not cards:
|
|
print("✗ No cards found in PSQL file")
|
|
return 0
|
|
|
|
# Import cards
|
|
return await import_cards(engine, cards)
|
|
|
|
|
|
async def import_all_set_files(engine, set_files_dir: Path) -> tuple[int, int]:
|
|
"""Import AllSetFiles - sets and cards."""
|
|
if not set_files_dir.exists():
|
|
print("✗ AllSetFiles directory not found")
|
|
return 0, 0
|
|
|
|
set_count = 0
|
|
card_count = 0
|
|
|
|
async with engine.begin() as conn:
|
|
# Import sets
|
|
for set_file in sorted(set_files_dir.glob('*.json')):
|
|
data = json.loads(set_file.read_text())
|
|
|
|
if 'code' in data:
|
|
image_url = None
|
|
if 'image' in data and data['image']:
|
|
image_url = data['image'].get('normal')
|
|
|
|
# Upsert set
|
|
result = await conn.execute(
|
|
pg_insert(text('mtg_sets')).values(
|
|
code=data.get('code'),
|
|
name=data.get('name'),
|
|
type=data.get('type'),
|
|
release_date=data.get('releaseDate'),
|
|
base_set_size=data.get('baseSetSize'),
|
|
total_size=data.get('totalSetSize'),
|
|
is_foil_only=data.get('isFoilOnly'),
|
|
is_non_foil_only=data.get('isNonFoilOnly'),
|
|
digital=data.get('digital'),
|
|
icon_svg_url=data.get('iconSvgUrl'),
|
|
parent_code=data.get('parentCode'),
|
|
mtgo_code=data.get('mtgoCode'),
|
|
card_count=data.get('cardCount'),
|
|
image_url=image_url,
|
|
).on_conflict_do_update(
|
|
index_elements=['code'],
|
|
set_={
|
|
'name': data.get('name'),
|
|
'type': data.get('type'),
|
|
'release_date': data.get('releaseDate'),
|
|
'base_set_size': data.get('baseSetSize'),
|
|
'total_size': data.get('totalSetSize'),
|
|
'is_foil_only': data.get('isFoilOnly'),
|
|
'is_non_foil_only': data.get('isNonFoilOnly'),
|
|
'digital': data.get('digital'),
|
|
'icon_svg_url': data.get('iconSvgUrl'),
|
|
'parent_code': data.get('parentCode'),
|
|
'mtgo_code': data.get('mtgoCode'),
|
|
'card_count': data.get('cardCount'),
|
|
'image_url': image_url,
|
|
'updated_at': datetime.utcnow(),
|
|
}
|
|
).returning(text('mtg_sets.id')),
|
|
execution_options={"autocommit": True}
|
|
)
|
|
set_id = result.scalar()
|
|
|
|
if 'cards' in data:
|
|
for card_data in data['cards']:
|
|
identifiers = {
|
|
'multiId': card_data.get('multiverseIds'),
|
|
'tcgplayerProductId': card_data.get('tcgplayerProductId'),
|
|
'cardmarketId': card_data.get('cardmarketId'),
|
|
}
|
|
|
|
images = {}
|
|
if 'image_uris' in card_data:
|
|
images = {
|
|
'small': card_data['image_uris'].get('small'),
|
|
'normal': card_data['image_uris'].get('normal'),
|
|
'large': card_data['image_uris'].get('large'),
|
|
'png': card_data['image_uris'].get('png'),
|
|
'art_crop': card_data['image_uris'].get('art_crop'),
|
|
}
|
|
|
|
# Upsert card
|
|
await conn.execute(
|
|
pg_insert(text('mtg_cards')).values(
|
|
set_id=set_id,
|
|
name=card_data.get('name'),
|
|
mana_cost=card_data.get('manaCost'),
|
|
type_line=card_data.get('typeLine'),
|
|
oracle_text=card_data.get('oracleText'),
|
|
power=card_data.get('power'),
|
|
toughness=card_data.get('toughness'),
|
|
rarity=card_data.get('rarity'),
|
|
layout=card_data.get('layout'),
|
|
artist=card_data.get('artist'),
|
|
flavor_text=card_data.get('flavorText'),
|
|
numbers=str(card_data.get('number')),
|
|
identifiers=json.dumps(identifiers),
|
|
images=json.dumps(images),
|
|
).on_conflict_do_nothing(),
|
|
execution_options={"autocommit": True}
|
|
)
|
|
card_count += 1
|
|
|
|
set_count += 1
|
|
|
|
print(f"✓ Imported {set_count} sets and {card_count:,} cards from sets")
|
|
return set_count, card_count
|
|
|
|
|
|
async def import_all_identifiers(engine, file_path: Path) -> int:
|
|
"""Import AllIdentifiers.json.gz."""
|
|
if not file_path.exists():
|
|
print("✗ AllIdentifiers.json.gz not found")
|
|
return 0
|
|
|
|
data = json.loads(gzip.decompress(file_path.read_bytes()))
|
|
|
|
count = 0
|
|
async with engine.begin() as conn:
|
|
for uuid, card_data in data.items():
|
|
await conn.execute(
|
|
pg_insert(text('card_identifiers')).values(
|
|
uuid=uuid,
|
|
name=card_data.get('name'),
|
|
mana_cost=card_data.get('manaCost'),
|
|
type_line=card_data.get('typeLine'),
|
|
oracle_text=card_data.get('oracleText'),
|
|
power=card_data.get('power'),
|
|
toughness=card_data.get('toughness'),
|
|
rarity=card_data.get('rarity'),
|
|
layout=card_data.get('layout'),
|
|
artist=card_data.get('artist'),
|
|
flavor_text=card_data.get('flavorText'),
|
|
set_code=card_data.get('setCode'),
|
|
).on_conflict_do_update(
|
|
index_elements=['uuid'],
|
|
set_={
|
|
'name': card_data.get('name'),
|
|
'mana_cost': card_data.get('manaCost'),
|
|
'type_line': card_data.get('typeLine'),
|
|
'oracle_text': card_data.get('oracleText'),
|
|
'power': card_data.get('power'),
|
|
'toughness': card_data.get('toughness'),
|
|
'rarity': card_data.get('rarity'),
|
|
'layout': card_data.get('layout'),
|
|
'artist': card_data.get('artist'),
|
|
'flavor_text': card_data.get('flavorText'),
|
|
'set_code': card_data.get('setCode'),
|
|
'updated_at': datetime.utcnow(),
|
|
}
|
|
),
|
|
execution_options={"autocommit": True}
|
|
)
|
|
count += 1
|
|
|
|
print(f"✓ Imported {count:,} identifiers")
|
|
return count
|
|
|
|
|
|
async def import_all_deck_files(engine, deck_files_dir: Path) -> int:
|
|
"""Import AllDeckFiles.zip."""
|
|
if not deck_files_dir.exists():
|
|
print("✗ AllDeckFiles directory not found")
|
|
return 0
|
|
|
|
count = 0
|
|
async with engine.begin() as conn:
|
|
for deck_file in sorted(deck_files_dir.glob('*.json')):
|
|
data = json.loads(deck_file.read_text())
|
|
|
|
if 'name' in data and 'cards' in data:
|
|
await conn.execute(
|
|
pg_insert(text('decks')).values(
|
|
name=data.get('name'),
|
|
description=data.get('description'),
|
|
format=data.get('format'),
|
|
command=data.get('command'),
|
|
commander=data.get('commander'),
|
|
).on_conflict_do_update(
|
|
index_elements=['name'],
|
|
set_={
|
|
'description': data.get('description'),
|
|
'format': data.get('format'),
|
|
'command': data.get('command'),
|
|
'commander': data.get('commander'),
|
|
'updated_at': datetime.utcnow(),
|
|
}
|
|
),
|
|
execution_options={"autocommit": True}
|
|
)
|
|
count += 1
|
|
|
|
print(f"✓ Imported {count} decks")
|
|
return count
|
|
|
|
|
|
async def import_simple_json(engine, file_path: Path, table_name: str,
|
|
name_field: str, id_field: str) -> int:
|
|
"""Import a simple JSON.gz file."""
|
|
if not file_path.exists():
|
|
return 0
|
|
|
|
data = json.loads(gzip.decompress(file_path.read_bytes()))
|
|
|
|
count = 0
|
|
async with engine.begin() as conn:
|
|
for item in data:
|
|
# Build the insert statement based on table name
|
|
if table_name == 'card_types':
|
|
insert_stmt = pg_insert(text('card_types')).values(
|
|
type=item.get('type'),
|
|
description=item.get('description')
|
|
).on_conflict_do_nothing()
|
|
elif table_name == 'deck_list':
|
|
insert_stmt = pg_insert(text('deck_list')).values(
|
|
deck_id=item.get('id'),
|
|
name=item.get('name'),
|
|
description=item.get('description'),
|
|
format=item.get('format'),
|
|
command=item.get('command'),
|
|
commander=item.get('commander'),
|
|
total_cards=item.get('totalCards')
|
|
).on_conflict_do_nothing()
|
|
elif table_name == 'card_keywords':
|
|
insert_stmt = pg_insert(text('card_keywords')).values(
|
|
keyword=item.get('keyword'),
|
|
description=item.get('description')
|
|
).on_conflict_do_nothing()
|
|
elif table_name == 'set_list':
|
|
insert_stmt = pg_insert(text('set_list')).values(
|
|
set_code=item.get('code'),
|
|
set_name=item.get('name'),
|
|
set_type=item.get('type'),
|
|
release_date=item.get('releaseDate'),
|
|
base_set_size=item.get('baseSetSize'),
|
|
total_size=item.get('totalSetSize')
|
|
).on_conflict_do_nothing()
|
|
else:
|
|
continue
|
|
|
|
await conn.execute(insert_stmt, execution_options={"autocommit": True})
|
|
count += 1
|
|
|
|
print(f"✓ Imported {count} items into {table_name}")
|
|
return count
|
|
|
|
|
|
async def create_indexes(engine):
|
|
"""Create indexes for better performance."""
|
|
async with engine.begin() as conn:
|
|
indexes = [
|
|
"CREATE INDEX IF NOT EXISTS idx_cards_name ON cards(name)",
|
|
"CREATE INDEX IF NOT EXISTS idx_cards_mana_cost ON cards(manaCost)",
|
|
"CREATE INDEX IF NOT EXISTS idx_cards_type ON cards(type)",
|
|
"CREATE INDEX IF NOT EXISTS idx_cards_rarity ON cards(rarity)",
|
|
"CREATE INDEX IF NOT EXISTS idx_cards_set_code ON cards(setCode)",
|
|
"CREATE INDEX IF NOT EXISTS idx_cards_uuid ON cards(uuid)",
|
|
"CREATE INDEX IF NOT EXISTS idx_mtg_sets_code ON mtg_sets(code)",
|
|
"CREATE INDEX IF NOT EXISTS idx_mtg_cards_name ON mtg_cards(name)",
|
|
"CREATE INDEX IF NOT EXISTS idx_card_identifiers_uuid ON card_identifiers(uuid)",
|
|
"CREATE INDEX IF NOT EXISTS idx_card_identifiers_name ON card_identifiers(name)",
|
|
"CREATE INDEX IF NOT EXISTS idx_deck_list_deck_id ON deck_list(deck_id)",
|
|
]
|
|
|
|
for idx in indexes:
|
|
await conn.execute(text(idx))
|
|
|
|
await conn.commit()
|
|
print("✓ Indexes created")
|
|
|
|
|
|
async def show_summary(engine):
|
|
"""Show import summary."""
|
|
print("\n=== Import Summary ===")
|
|
async with engine.connect() as conn:
|
|
tables = [
|
|
'cards', 'mtg_sets', 'mtg_cards', 'card_identifiers',
|
|
'decks', 'card_types', 'deck_list', 'card_keywords', 'set_list'
|
|
]
|
|
|
|
for table in tables:
|
|
result = await conn.execute(text(f"SELECT COUNT(*) FROM {table}"))
|
|
count = result.scalar()
|
|
print(f" {table:20} {count:>10,} records")
|
|
|
|
|
|
async def main():
|
|
"""Main function to download and import all MTGJSON data."""
|
|
settings = get_settings()
|
|
|
|
# Setup data directory
|
|
data_dir = Path(settings.DATA_DIR) / "mtgjson"
|
|
downloads_dir = data_dir / "downloads"
|
|
downloads_dir.mkdir(parents=True, exist_ok=True)
|
|
|
|
# Create engine
|
|
engine = create_async_engine(settings.MTG_DATABASE_URL)
|
|
|
|
# Create tables
|
|
print("=== Creating Database Tables ===")
|
|
await create_tables(engine)
|
|
|
|
# Download and import AllPrintings
|
|
print("\n=== Importing AllPrintings ===")
|
|
psql_file = get_all_printings_psql_file(downloads_dir)
|
|
await import_all_printings_psql(engine, psql_file)
|
|
|
|
# Download and extract AllSetFiles
|
|
print("\n=== Importing AllSetFiles ===")
|
|
set_files_zip = downloads_dir / "AllSetFiles.zip"
|
|
set_files_dir = downloads_dir / "AllSetFiles"
|
|
|
|
if not set_files_dir.exists():
|
|
if not set_files_zip.exists():
|
|
download_file(f"{MTGJSON_API_V5}/AllSetFiles.zip", set_files_zip)
|
|
extract_zip(set_files_zip, set_files_dir)
|
|
|
|
await import_all_set_files(engine, set_files_dir)
|
|
|
|
# Download and extract AllDeckFiles
|
|
print("\n=== Importing AllDeckFiles ===")
|
|
deck_files_zip = downloads_dir / "AllDeckFiles.zip"
|
|
deck_files_dir = downloads_dir / "AllDeckFiles"
|
|
|
|
if not deck_files_dir.exists():
|
|
if not deck_files_zip.exists():
|
|
download_file(f"{MTGJSON_API_V5}/AllDeckFiles.zip", deck_files_zip)
|
|
extract_zip(deck_files_zip, deck_files_dir)
|
|
|
|
await import_all_deck_files(engine, deck_files_dir)
|
|
|
|
# Download and import AllIdentifiers
|
|
print("\n=== Importing AllIdentifiers ===")
|
|
identifiers_file = downloads_dir / "AllIdentifiers.json.gz"
|
|
if not identifiers_file.exists():
|
|
download_file(f"{MTGJSON_API_V5}/AllIdentifiers.json.gz", identifiers_file)
|
|
|
|
await import_all_identifiers(engine, identifiers_file)
|
|
|
|
# Download and import CardTypes
|
|
print("\n=== Importing CardTypes ===")
|
|
card_types_file = downloads_dir / "CardTypes.json.gz"
|
|
if not card_types_file.exists():
|
|
download_file(f"{MTGJSON_API_V5}/CardTypes.json.gz", card_types_file)
|
|
|
|
if card_types_file.exists():
|
|
await import_simple_json(engine, card_types_file, 'card_types', 'type', 'id')
|
|
|
|
# Download and import DeckList
|
|
print("\n=== Importing DeckList ===")
|
|
deck_list_file = downloads_dir / "DeckList.json.gz"
|
|
if not deck_list_file.exists():
|
|
download_file(f"{MTGJSON_API_V5}/DeckList.json.gz", deck_list_file)
|
|
|
|
if deck_list_file.exists():
|
|
await import_simple_json(engine, deck_list_file, 'deck_list', 'name', 'deck_id')
|
|
|
|
# Download and import Keywords
|
|
print("\n=== Importing Keywords ===")
|
|
keywords_file = downloads_dir / "Keywords.json.gz"
|
|
if not keywords_file.exists():
|
|
download_file(f"{MTGJSON_API_V5}/Keywords.json.gz", keywords_file)
|
|
|
|
if keywords_file.exists():
|
|
await import_simple_json(engine, keywords_file, 'card_keywords', 'keyword', 'id')
|
|
|
|
# Download and import SetList
|
|
print("\n=== Importing SetList ===")
|
|
set_list_file = downloads_dir / "SetList.json.gz"
|
|
if not set_list_file.exists():
|
|
download_file(f"{MTGJSON_API_V5}/SetList.json.gz", set_list_file)
|
|
|
|
if set_list_file.exists():
|
|
await import_simple_json(engine, set_list_file, 'set_list', 'set_name', 'set_code')
|
|
|
|
# Create indexes
|
|
print("\n=== Creating Indexes ===")
|
|
await create_indexes(engine)
|
|
|
|
# Show summary
|
|
await show_summary(engine)
|
|
|
|
await engine.dispose()
|
|
print("\n✓ All MTGJSON data imported successfully!")
|
|
|
|
|
|
if __name__ == "__main__":
|
|
asyncio.run(main())
|