import sqlalchemy
from jieba.analyse import textrank

engine = sqlalchemy.create_engine(
    "mysql+pymysql://user:pass@host/db?charset=utf8mb4"
)

with engine.connect() as conn:
    # Kiểm tra và tạo cột từ khóa
    conn.execute("""
        ALTER TABLE du_an 
        ADD COLUMN IF NOT EXISTS tu_khoa VARCHAR(500)
    """)
    
    # Lấy dữ liệu mô tả dự án
    results = conn.execute(
        "SELECT id, mo_ta FROM du_an WHERE mo_ta IS NOT NULL"
    ).fetchall()
    
    for record in results:
        keywords = textrank(
            record.mo_ta, 
            topK=8, 
            allowPOS=('n', 'nz', 'v')
        )
        keyword_str = ";".join(keywords)
        
        conn.execute(
            sqlalchemy.text(
                "UPDATE du_an SET tu_khoa = :kw WHERE id = :id"
            ),
            {"kw": keyword_str, "id": record.id}
        )

import requests
import pandas as pd

API_KEY = "ma_bao_mat"

def lay_ma_hanh_chinh(ten_tinh):
    params = {
        "key": API_KEY,
        "keywords": ten_tinh,
        "subdistrict": "0",
        "extensions": "base"
    }
    response = requests.get(
        "https://restapi.amap.com/v3/config/district", 
        params=params
    )
    if response.status_code == 200:
        data = response.json()
        if data["status"] == "1" and data["districts"]:
            return data["districts"][0]["adcode"]
    return None

# Cập nhật mã cho toàn bộ bản ghi
conn = sqlalchemy.create_engine(...)
df = pd.read_sql("SELECT id, tinh_thanh FROM du_an", conn)

for _, row in df.iterrows():
    ma = lay_ma_hanh_chinh(row.tinh_thanh)
    if ma:
        conn.execute(
            sqlalchemy.text(
                "UPDATE du_an SET ma_hanh_chinh = :ma WHERE id = :id"
            ),
            {"ma": ma, "id": row.id}
        )