İl İlçe Listesi Excel, CSV ve SQL Olarak Hazırlama

Bir rapor şablonu, bir yönetim paneli ya da veritabanı tohumlama (seed) betiği için il ilçe listesi Excel ya da SQL biçiminde sık sık gerekir. Çoğu ekip bu listeyi bir kez bir yerden kopyalar, depoya koyar ve unutur. Bu yazıda listeyi API'den çekip Excel'de sorunsuz açılan bir CSV dosyasına ve SQL INSERT ifadelerine dönüştürmeyi, ardından bu dosyayı düzenli olarak nasıl yenileyeceğinizi adım adım gösteriyoruz.

Örneklerde API Türkiye'nin /v1/provinces ve /v1/provinces/{code}/districts uçlarını kullanıyoruz. Tüm istekler sunucu tarafında, X-API-Key başlığıyla yapılır. Anahtarı ortam değişkeninde tutun; tarayıcı koduna veya Git deposuna yazmayın.

Statik il ilçe listesi Excel dosyaları neden eskir?

Elle hazırlanmış bir dosya, hazırlandığı günün fotoğrafıdır. Zamanla şu sorunlar birikir:

• Yeni kurulan ya da adı değişen ilçeler dosyaya yansımaz.
• Farklı ekiplerin elindeki kopyalar birbirinden ayrışır; aynı ilçe iki sistemde farklı yazılır.
• Kayıtlarda kalıcı bir kimlik olmadığı için eşleştirme ada göre yapılır ve "Merkez" gibi tekrarlanan adlarda hata riski artar.

Dosyayı her ihtiyaçta API'den yeniden üretirseniz bu sorunların çoğu ortadan kalkar. Dosyanın kendisi yine statiktir, ancak onu üreten süreç güncel veriye bağlıdır.

1. Adım: İl ve ilçe listesini API'den alın

Önce yanıtın biçimini görmek için iki isteği elle deneyin:

curl "https://api.apiturkiye.com/v1/provinces" -H "X-API-Key: $APITURKIYE_KEY"
curl "https://api.apiturkiye.com/v1/provinces/34/districts" -H "X-API-Key: $APITURKIYE_KEY"

İl listesi şu biçimde döner:

{ "data": [ { "stableId": "apt_v1_prv_...", "code": 1, "name": "Adana" } ] }

Bilmeniz gereken birkaç ayrıntı var:

• İl ve ilçe listeleri ilgili kapsamın tamamını tek yanıtta döner. Bu uçlarda sayfalama (limit, cursor) yoktur.
• İl code değeri sayısaldır ve araç plaka koduyla aynıdır: 01 Adana, 34 İstanbul, 81 Düzce gibi. Ayrıntılar için il plaka kodları ve il kodu kullanımı yazısına bakabilirsiniz.
• İlçe code değerini metin olarak ele alın. Kalıcı referans olarak ise stableId saklayın; bu kimlik veri seti sürümlerinden bağımsızdır.

Yanıtı hiç dönüştürmeden kaydetmek de bir seçenektir. Ham il ilçe JSON verisiyle çalışmayı Türkiye il ilçe listesini JSON olarak alma yazısında ele almıştık. Burada odak noktamız Excel ve SQL çıktısı.

2. Adım: CSV olarak kaydedin

Aşağıdaki Python betiği tüm illeri alır, her il için ilçe listesini çeker ve iki dosya yazar: Excel için il_ilce.csv, yedek olarak da ham il_ilce.json.

import csv
import json
import os
import time

import requests

BASE = "https://api.apiturkiye.com/v1"
HEADERS = {"X-API-Key": os.environ["APITURKIYE_KEY"]}


def get(path):
    for _ in range(5):
        res = requests.get(f"{BASE}{path}", headers=HEADERS, timeout=10)
        retry_after = res.headers.get("Retry-After")
        if res.status_code == 429 and retry_after:
            time.sleep(int(retry_after))
            continue
        res.raise_for_status()
        return res.json()["data"]
    raise RuntimeError(f"Tekrar denemeler tükendi: {path}")


def fetch_all():
    provinces = sorted(get("/provinces"), key=lambda p: int(p["code"]))
    districts = []
    for p in provinces:
        for d in get(f"/provinces/{p['code']}/districts"):
            districts.append({**d, "provinceCode": int(p["code"]), "provinceName": p["name"]})
    return provinces, districts


def write_csv(districts, path="il_ilce.csv"):
    fields = ["il_kodu", "plaka", "il_adi", "ilce_kodu", "ilce_adi", "ilce_stable_id"]
    with open(path, "w", encoding="utf-8-sig", newline="") as f:
        writer = csv.writer(f, delimiter=";")
        writer.writerow(fields)
        for d in districts:
            writer.writerow([
                d["provinceCode"],
                f"{d['provinceCode']:02d}",
                d["provinceName"],
                d["code"],
                d["name"],
                d["stableId"],
            ])


if __name__ == "__main__":
    provinces, districts = fetch_all()
    write_csv(districts)
    with open("il_ilce.json", "w", encoding="utf-8") as f:
        json.dump({"provinces": provinces, "districts": districts}, f, ensure_ascii=False, indent=2)
    print(f"{len(provinces)} il, {len(districts)} ilçe yazıldı.")

Betik 429 yanıtında yalnızca Retry-After başlığı varsa bekleyip yeniden dener. Paket kotası dolduğunda (quota_exceeded) ya da 401/403 aldığınızda hata fırlatır. Bu durumlarda otomatik tekrar denemek işe yaramaz; önce anahtarı, yetkiyi veya kotayı kontrol etmeniz gerekir.

Excel'de Türkçe karakterlerin doğru açılması

"İ, ş, ğ" gibi harflerin "Ä°" ya da "ÅŸ" olarak görünmesinin nedeni çoğunlukla kodlamadır. Betikteki iki ayar bunu önler:

Bir uyarı daha: Excel plaka sütunundaki "01" değerini sayıya çevirip "1" olarak gösterir. Baştaki sıfırın korunması gerekiyorsa dosyayı çift tıklayarak açmak yerine Veri sekmesindeki metin/CSV içe aktarma seçeneğini kullanın ve bu sütunun türünü "Metin" olarak ayarlayın. Yalnızca bir Türkiye il listesi Excel tablosuna ihtiyacınız varsa aynı betikte ilçe döngüsünü atlayıp yalnızca provinces listesini yazdırmanız yeterlidir.

Aynı işi Node.js ile yapmak

Node 18 ve üzeri sürümlerde yerleşik fetch bulunur. Aşağıdaki kodu il-ilce.mjs adıyla kaydedip node il-ilce.mjs komutuyla çalıştırabilirsiniz:

import { writeFile } from "node:fs/promises";

const BASE = "https://api.apiturkiye.com/v1";
const headers = { "X-API-Key": process.env.APITURKIYE_KEY };

async function get(path) {
  const res = await fetch(`${BASE}${path}`, { headers });
  if (!res.ok) throw new Error(`HTTP ${res.status} ${path}`);
  return (await res.json()).data;
}

const cell = (v) => `"${String(v).replace(/"/g, '""')}"`;

const provinces = await get("/provinces");
const lines = [["il_kodu", "il_adi", "ilce_kodu", "ilce_adi", "ilce_stable_id"].map(cell).join(";")];

for (const p of provinces) {
  for (const d of await get(`/provinces/${p.code}/districts`)) {
    lines.push([p.code, p.name, d.code, d.name, d.stableId].map(cell).join(";"));
  }
}

await writeFile("il_ilce.csv", "\uFEFF" + lines.join("\r\n") + "\r\n", "utf8");

Buradaki \uFEFF karakteri Python'daki utf-8-sig ayarının karşılığıdır, yani dosyanın başına BOM ekler.

3. Adım: SQL tablo ve INSERT ifadeleri üretin

İl ilçe SQL dosyası hazırlarken iki ilkeye dikkat edin: kalıcı referans olarak stableId kullanın ve metinlerdeki tek tırnakları kaçırın. Aşağıdaki fonksiyonu yukarıdaki Python betiğine ekleyebilirsiniz:

def q(value):
    return "'" + str(value).replace("'", "''") + "'"


def write_sql(provinces, districts, path="il_ilce.sql"):
    lines = [
        "CREATE TABLE IF NOT EXISTS provinces (",
        "  stable_id VARCHAR(64) PRIMARY KEY,",
        "  code SMALLINT NOT NULL UNIQUE,",
        "  name VARCHAR(100) NOT NULL",
        ");",
        "CREATE TABLE IF NOT EXISTS districts (",
        "  stable_id VARCHAR(64) PRIMARY KEY,",
        "  code VARCHAR(20) NOT NULL,",
        "  province_code SMALLINT NOT NULL REFERENCES provinces(code),",
        "  name VARCHAR(100) NOT NULL",
        ");",
    ]
    for p in provinces:
        lines.append(
            "INSERT INTO provinces (stable_id, code, name) VALUES "
            f"({q(p['stableId'])}, {int(p['code'])}, {q(p['name'])});"
        )
    for d in districts:
        lines.append(
            "INSERT INTO districts (stable_id, code, province_code, name) VALUES "
            f"({q(d['stableId'])}, {q(d['code'])}, {d['provinceCode']}, {q(d['name'])});"
        )
    with open(path, "w", encoding="utf-8") as f:
        f.write("\n".join(lines) + "\n")

Ana bloğa write_sql(provinces, districts) satırını eklediğinizde betik CSV ile birlikte SQL dosyasını da üretir. Dosyayı yüklemeden önce veritabanınızın ve bağlantınızın UTF-8 kullandığından emin olun; aksi hâlde Türkçe karakterler bu aşamada bozulur.

İlk yüklemeden sonraki güncellemelerde düz INSERT ifadeleri birincil anahtar çakışmasına yol açar. PostgreSQL kullanıyorsanız ifadeyi bir upsert'e dönüştürebilirsiniz:

INSERT INTO districts (stable_id, code, province_code, name)
VALUES ('apt_v1_dst_...', '1421', 34, 'Kadıköy')
ON CONFLICT (stable_id) DO UPDATE
SET code = EXCLUDED.code,
    province_code = EXCLUDED.province_code,
    name = EXCLUDED.name;

Başka bir veritabanı kullanıyorsanız kendi upsert söz dizimini tercih edin. Dikkat edilecek nokta aynıdır: eşleştirmeyi ada göre değil, stable_id üzerinden yapın.

4. Adım: Listeyi düzenli olarak yenileyin

Dosyayı üreten betiğiniz hazır olduğuna göre geriye onu düzenli çalıştırmak kalıyor. İki yaklaşım öneriyoruz.

Zamanlanmış yenileme. Betiği bir cron görevi ya da CI zamanlayıcısı ile çalıştırın. Örneğin her pazartesi sabahı:

0 4 * * 1 cd /opt/adres-export && APITURKIYE_KEY=... python export_il_ilce.py

Anahtarı crontab'a açık metin olarak yazmak yerine sunucunuzun gizli değişken yönetimini kullanmanız daha güvenlidir.

Sürüm değişince yenileme. Anahtarınızda datasets:read yetkisi varsa aktif veri seti sürümünü sorgulayabilir ve dosyaları yalnızca sürüm değiştiğinde yeniden üretebilirsiniz:

def current_version():
    res = requests.get(f"{BASE}/datasets/current", headers=HEADERS, timeout=10)
    if res.status_code == 403:
        return None  # Bu anahtarda datasets:read yetkisi yok
    res.raise_for_status()
    data = res.json()["data"]
    return data["datasetVersion"], data["counts"]

Son işlenen datasetVersion değerini bir dosyada saklayın ve yeni değer farklıysa dışa aktarmayı tetikleyin. counts.provinces ve counts.districts değerlerini ürettiğiniz satır sayılarıyla karşılaştırmak da yararlı bir tutarlılık kontrolüdür. Katalogda şu anda 81 il ve 973 ilçe bulunuyor. Yine de bu sayıları koda sabitlemeyin; karşılaştırmayı her seferinde API'den gelen değerle yapın.

Yenileme sırasında bir ilçe listeden çıkarsa ilgili satırı veritabanından hemen silmeyin. O ilçeye bağlı sipariş veya kullanıcı kayıtları olabilir. Kaydı pasif olarak işaretleyip incelemeye almak daha güvenli bir yaklaşımdır.

Kontrol listesi

• [ ] API anahtarı ortam değişkeninde tutuluyor ve yalnızca sunucuda kullanılıyor.
• [ ] CSV dosyası UTF-8 BOM ile ve noktalı virgülle yazılıyor.
• [ ] Baştaki sıfırın önemli olduğu sütunlar Excel'e metin olarak aktarılıyor.
• [ ] SQL tablolarında kalıcı referans olarak stable_id kullanılıyor; ilçe kodu metin olarak tutuluyor.
• [ ] Tek tırnaklar kaçırılıyor ve veritabanı bağlantısı UTF-8 kullanıyor.
• [ ] Betik zamanlanmış olarak ya da sürüm değişikliğinde yeniden çalışıyor.
• [ ] 401, 403 ve kota hataları otomatik tekrar denemeye sokulmuyor.

Uç noktaların ayrıntılı açıklaması ve hata kodları için API dokümantasyonuna göz atabilirsiniz. Betiği kendi verinizle denemek için ücretsiz kayıt olup API anahtarınızı alabilirsiniz.