json2sqlite.py

import sqlite3
import json
import os
import glob

# データベース作成
conn = sqlite3.connect('fish.db')
cursor = conn.cursor()

# reportテーブル作成
def create_tables():
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS report (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            facility TEXT,
            date TEXT,
            weather TEXT,
            water_temp REAL,
            tide TEXT,
            visitors INTEGER,
            sentence TEXT
        )
    ''')
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS catch (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            report_id INTEGER,
            fish_name TEXT,
            min_size REAL,
            max_size REAL,
            unit TEXT,
            count INTEGER,
            place TEXT,
            FOREIGN KEY(report_id) REFERENCES report(id)
        )
    ''')
    conn.commit()

def insert_report(item):
    cursor.execute(
        'INSERT INTO report (facility, date, weather, water_temp, tide, visitors, sentence) VALUES (?, ?, ?, ?, ?, ?, ?)',
        (
            item.get('facility'),
            item.get('date'),
            item.get('weather'),
            float(item.get('waterTemp')) if item.get('waterTemp') else None,
            item.get('tide'),
            item.get('visitors'),
            item.get('sentence')
        )
    )
    return cursor.lastrowid

def insert_catches(item, report_id):
    for i in range(1, 31):
        name = item.get(f'fish{i}Name')
        if not name:
            continue
        min_size = item.get(f'fish{i}MinSize')
        max_size = item.get(f'fish{i}MaxSize')
        unit = item.get(f'fish{i}Unit')
        count = item.get(f'fish{i}Count')
        place = item.get(f'fish{i}Place')
        place_str = ','.join(place) if place else None
        cursor.execute(
            'INSERT INTO catch (report_id, fish_name, min_size, max_size, unit, count, place) VALUES (?, ?, ?, ?, ?, ?, ?)',
            (report_id, name, min_size, max_size, unit, count, place_str)
        )

def process_json_file(filepath):
    with open(filepath, 'r', encoding='utf-8') as f:
        data = json.load(f)
        # もしdataがstr型なら再度デコード
        if isinstance(data, str):
            data = json.loads(data)
        items = data['data']['lastPostsByFacilityAndDate']['items']
        for item in items:
            report_id = insert_report(item)
            insert_catches(item, report_id)
    conn.commit()

def main():
    create_tables()
    base = 'location'
    for facility in os.listdir(base):
        dir_path = os.path.join(base, facility)
        if not os.path.isdir(dir_path):
            continue
        for json_file in glob.glob(os.path.join(dir_path, '*.json')):
            process_json_file(json_file)
    print('Done!')

if __name__ == '__main__':
    main()