python学习笔记-mysql数据库操作

现有一个需求，调用高德api获取全国县级以上行政区数据并保存为json文件，使用python获取：

import requests
import json

# 高德API Key
api_key = "your_api_key"

# 调用行政区域查询API
def fetch_districts():
    url = f"https://restapi.amap.com/v3/config/district?key={api_key}&subdistrict=3&extensions=base"
    response = requests.get(url)
    if response.status_code == 200:
        return response.json()
    else:
        print("请求失败，状态码：", response.status_code)
        return None

# 提取省市县区数据
def extract_regions(districts):
    regions = []
    for district in districts:
        # 提取当前节点
        if district["level"] in ["province", "city", "district"]:
            regions.append({
                "name": district["name"],
                "level": district["level"],
                "adcode": district["adcode"],
                "citycode": district["citycode"],
                "center": district["center"]
            })
        # 递归提取子节点
        if "districts" in district:
            regions.extend(extract_regions(district["districts"]))
    return regions

# 保存数据为JSON文件
def save_to_json(data, filename):
    with open(filename, "w", encoding="utf-8") as f:
        json.dump(data, f, ensure_ascii=False, indent=4)
    print(f"数据已保存到 {filename}")

# 主函数
def main():
    # 获取数据
    data = fetch_districts()
    if data:
        # 提取省市县区数据
        regions = extract_regions(data["districts"])
        # 保存为JSON文件
        save_to_json(regions, "regions.json")

if __name__ == "__main__":
    main()

获得的行政区数据结构如下(部分数据)：

[
    {
        "name": "河南省",
        "level": "province",
        "adcode": "410000",
        "citycode": [],
        "center": "113.753094,34.767052"
    },
    {
        "name": "洛阳市",
        "level": "city",
        "adcode": "410300",
        "citycode": "0379",
        "center": "112.453895,34.619702"
    },
    {
        "name": "新安县",
        "level": "district",
        "adcode": "410323",
        "citycode": "0379",
        "center": "112.13246,34.728909"
    }
]

在mysql中新建一张表，存储这些数据：

CREATE TABLE administrative_regions (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    name VARCHAR(100) NOT NULL COMMENT '行政区名称',
    level VARCHAR(50) NOT NULL COMMENT '行政区级别（province/city/district）',
    adcode VARCHAR(20) NOT NULL COMMENT '行政区编码',
    citycode VARCHAR(20) COMMENT '城市编码',
    center VARCHAR(50) COMMENT '行政区中心点坐标',
    parent_id INT COMMENT '上级行政区ID',
    first_letter_1 CHAR(1)  COMMENT '行政区名称第1个字的首字母',
    first_letter_2 CHAR(1)  COMMENT '行政区名称第2个字的首字母',
    CONSTRAINT fk_parent_id FOREIGN KEY (parent_id) REFERENCES administrative_regions(id)
) COMMENT='全国省市县区数据表';

使用navicat将json数据导入本地数据库，生成表中name,level,adcode,citycode,center的值。

如果想实现按行政区名称第1个字和第2个字拼音首字母返回，可借助python的拼音库为表中first_letter_1和first_letter_2字段赋值，下面说下具体实现步骤：

1.安装pymysql和pypinyin库:

pip install pymysql

pip install pypinyin

2.写一个python脚本，连上mysql数据库，使用pypinyin库获得行政区名称第1个字和第2个字拼音首字母，然后批量修改first_letter_1和first_letter_2字段的值：

import pymysql
from pypinyin import pinyin, Style

# 数据库连接配置
db_config = {
    'host': 'localhost',
    'port': 3306,
    'user': 'root',
    'password': 'root',
    'database': 'area',
    'charset': 'utf8mb4'
}

# 获取汉字的拼音首字母
def get_first_letter(chinese_char):
    return pinyin(chinese_char, style=Style.FIRST_LETTER)[0][0].upper()
# 连接数据库
connection = pymysql.connect(**db_config)
cursor = connection.cursor()

# 查询所有行政区名称
cursor.execute("SELECT id, name FROM administrative_regions")
regions = cursor.fetchall()

# 更新 first_letter_1 和 first_letter_2
for region in regions:
    region_id, name = region
    if len(name) >= 1:
        first_letter_1 = get_first_letter(name[0])
    else:
        first_letter_1 = ''
    if len(name) >= 2:
        first_letter_2 = get_first_letter(name[1])
    else:
        first_letter_2 = ''
    # 更新数据库
    cursor.execute(
        "UPDATE administrative_regions SET first_letter_1 = %s, first_letter_2 = %s WHERE id = %s",
        (first_letter_1, first_letter_2, region_id)
    )

# 提交事务并关闭连接
connection.commit()
cursor.close()
connection.close()

更新后的表数据为：

最后，编写sql语句，实现按名称首字母分类返回，本人服务器端程序使用Java开发，下面是示例：

查询语句：

 SELECT name, first_letter_1 FROM administrative_regions  ORDER BY first_letter_1, first_letter_2

 service代码：

public Map<String, List<String>> getAreaGroupedByFirstLetter1() {
	// 查询所有数据
	List<AdministrativeRegions> regions = baseMapper.selectAllRegions();

	// 按 first_letter_1 分组
	return regions.stream().collect(Collectors.groupingBy(AdministrativeRegions::getFirstLetter1,
					Collectors.mapping(AdministrativeRegions::getName, // 提取 name 字段
							Collectors.toList() // 转换为 List
					)
			));
}

最后，得到类似下面的行政区数据：

{

    "msg": "操作成功",

    "code": 200,

    "data": {

        "A": [

            "昂昂溪区",

            "阿巴嘎旗",

            "阿坝藏族羌族自治州",

            "阿坝县",

            "阿城区",

               ..............

            ],

       ....

   }