從 JSON 到資料庫：使用 Django ORM 建構音樂活動管理系統

{
  "version": "1.4",
  "UID": "669eab3226b3240c380463f1",
  "title": "新觀點．新世界～Kimball Gallagher 2024台灣巡迴音樂會",
  "category": "1",
  "showInfo": [
    {
      "time": "2025/02/19 19:30:00",
      "location": "高雄市鳳山區三多一路1號",
      "locationName": "衛武營國家藝術文化中心表演廳",
      "onSales": "Y",
      "price": "",
      "latitude": "22.6230179238508",
      "longitude": "120.342434118507",
      "endTime": "2025/02/19 21:05:00"
    }
  ],
  "webSales": "https://www.opentix.life/program/1811063063948378113",
  "startDate": "2025/02/19",
  "endDate": "2025/02/19",
  "hitRate": 34
}

class Event(models.Model):
    UID = models.CharField(max_length=255, primary_key=True)  # 活動唯一識別碼
    version = models.CharField(max_length=10)  # 數據版本
    title = models.CharField(max_length=255)  # 活動名稱
    category = models.CharField(max_length=10)  # 活動分類（原本是 IntegerField，JSON 為字串）
    showUnit = models.CharField(max_length=255, blank=True, null=True)  # 演出單位
    webSales = models.URLField(blank=True, null=True)  # 購票網址
    startDate = models.DateField()  # 活動開始日期
    endDate = models.DateField()  # 活動結束日期
    hitRate = models.IntegerField(default=0)  # 點擊率

    def __str__(self):
        return self.title

class Location(models.Model):
    locationID = models.AutoField(primary_key=True)  # 自動遞增 ID
    location = models.CharField(max_length=255)  # 具體地址
    locationName = models.CharField(max_length=255)  # 場地名稱
    latitude = models.DecimalField(max_digits=10, decimal_places=6, blank=True, null=True)  # 經度
    longitude = models.DecimalField(max_digits=10, decimal_places=6, blank=True, null=True)  # 緯度

    def __str__(self):
        return self.locationName

class ShowInfo(models.Model):
    showID = models.AutoField(primary_key=True)  # 場次唯一識別碼，自動遞增
    event = models.ForeignKey(Event, on_delete=models.CASCADE, related_name="show_info")  # 關聯到 events 表
    show_time = models.DateTimeField()  # 演出時間
    endTime = models.DateTimeField(blank=True, null=True)  # 演出結束時間
    location = models.ForeignKey(Location, on_delete=models.CASCADE, related_name="show_info")  # 關聯到 locations 表
    onSales = models.CharField(max_length=1, choices=[("Y", "Yes"), ("N", "No")], default="N")  # 是否開放售票
    price = models.CharField(max_length=255, blank=True, null=True)  # 票價資訊

    def __str__(self):
        return f"{self.event.title} - {self.show_time}"

class ShowInfo(models.Model):
    event = models.ForeignKey(Event, on_delete=models.CASCADE, related_name="show_info")

class ShowInfo(models.Model): location = models.ForeignKey(Location, on_delete=models.CASCADE, related_name="show_info")

ALTER TABLE show_info ADD CONSTRAINT fk_location FOREIGN KEY (location_id) REFERENCES locations (locationID);

class ShowInfo(models.Model):
    event = models.ForeignKey(Event, on_delete=models.CASCADE, related_name="show_info")
    location = models.ForeignKey(Location, on_delete=models.CASCADE, related_name="show_info")

Event (活動)  1 ----- * ShowInfo (場次) * ----- 1 Location (場地)
   ↑                ↑
  父物件           子物件

python manage.py makemigrations

python manage.py migrate

import json
from myapp.models import Event, ShowInfo, Location
from datetime import datetime

# 讀取 JSON 文件
with open("music_events.json", "r", encoding="utf-8") as file:
    data = json.load(file)

# 解析 JSON 並存入資料庫
for event_data in data:
    event, created = Event.objects.get_or_create(
        UID=event_data["UID"],
        defaults={
            "version": event_data["version"],
            "title": event_data["title"],
            "category": event_data["category"],
            "webSales": event_data.get("webSales", ""),
            "startDate": datetime.strptime(event_data["startDate"], "%Y/%m/%d").date(),
            "endDate": datetime.strptime(event_data["endDate"], "%Y/%m/%d").date(),
            "hitRate": event_data.get("hitRate", 0),
        },
    )

    for show in event_data["showInfo"]:
        location, _ = Location.objects.get_or_create(
            location=show["location"],
            locationName=show["locationName"],
            latitude=show.get("latitude"),
            longitude=show.get("longitude"),
        )

        ShowInfo.objects.create(
            event=event,
            show_time=datetime.strptime(show["time"], "%Y/%m/%d %H:%M:%S"),
            endTime=datetime.strptime(show["endTime"], "%Y/%m/%d %H:%M:%S"),
            location=location,
            onSales=show["onSales"],
            price=show.get("price", ""),
        )