python-sqlite3 写入省城市区编码

城市编码信息

资源引用的链接地址是: https://download.csdn.net/download/Ly_Word/87506253 现在是免费下载的 要是收费了,评论下 我改改.

# -*- coding: utf-8 -*-
# @time    : 2023/2/27 14:59
# @file    : par_city.py
"""
文件说明:
"""
import json
import sqlite3
import time

sqlite_conn = sqlite3.connect('city.db')
sqlite_cur = sqlite_conn.cursor()
sqlite_sql = """
DROP TABLE IF EXISTS `city`;
CREATE TABLE `city`  (
  `id` INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY ,
  `province` varchar(32) NULL DEFAULT NULL,
   `province_code` varchar(32) NULL DEFAULT NULL,
  `city` varchar(32)  NULL DEFAULT NULL,
   `city_code` varchar(32)  NULL DEFAULT NULL,
  `area` varchar(32)  NULL DEFAULT NULL,
   `area_code` varchar(32)  NULL DEFAULT NULL
) ;
"""
for item in sqlite_sql.split(";"):
    sqlite_cur.execute(item + ';')
sqlite_conn.commit()

with open("city.json", 'r+', encoding='utf-8')as f:
    json_data = json.load(f)
i = 1
for one_item in json_data:
    province_code = one_item['code']
    province = one_item['name']
    for two_item in one_item['cityList']:
        city_code = two_item['code']
        city = two_item['name']
        for three_item in two_item['areaList']:
            area_code = three_item['code']
            area = three_item['name']
            print({
                "province_code": province_code,
                "province": province,
                "city_code": city_code,
                "city": city,
                "area_code": area_code,
                "area": area,
            })
            time.sleep(0.1)
            sql_insert = "INSERT INTO city(id,province_code,province,city_code,city,area_code,area)VALUES (?,?,?,?,?,?,?)"
            try:
                sqlite_cur.execute(sql_insert, (i, province_code, province, city_code, city, area_code, area))
                # 提交事务
                sqlite_conn.commit()
                print('插入成功')
            except Exception as e:
                print(e)
                print('插入失败')
                sqlite_conn.rollback()
            i += 1