省市区数据在很多系统中都有应用,5月国家民政部发布了最新的国家行政区划代码,参见行政区划,本着分享共赢的精神,我把我的表设计、数据清洗脚本奉献给大家,以资共饟
Python现在很火啊,10年前刚开始用就爱上她了,但没有现在这么火热,之前的小家碧玉都快成大众情人了!闲话少说,还是拿她开搞,首先将民政部表格复制保存为“行政区划.xls”表格,用以下代码将表格转换成SQL语句。
import xlrd
import re
book = xlrd.open_workbook('行政区划.xls')
print('sheet页名称:',book.sheet_names())
sheet = book.sheet_by_index(0)
rows = sheet.nrows
cols = sheet.ncols
level_1 = 0
level_2 = 0
for row in range(1, rows+1):
area = sheet.cell_value(row-1, 1)
code = sheet.cell_value(row-1, 0)
code = repr(code).split(".")[0]
if re.match("^[\u4E00-\u9FA5]+", area) is not None:
level_1 = row
if area.endswith(u"北京市") or area.endswith(u"上海市") or area.endswith(u"深圳市") or area.endswith(u"重庆市") or area.endswith(u"天津市"):
level_2 = row
print('''INSERT INTO `area` VALUES (%d, 0, '%s', '%s', '%s', 1);'''%(row, area.lstrip(), area.lstrip()[:2], code))
if re.match("^\s+", area) is not None and re.match("^\s{3}", area) is None:
level_2 = row
print('''INSERT INTO `area` VALUES (%d, %d, '%s', '%s', '%s', 2);'''%(row, level_1, area.lstrip(), area.lstrip()[:2], code))
if re.match("^\s{3}", area) is not None:
print('''INSERT INTO `area` VALUES (%d, %d, '%s', '%s', '%s', 3);'''%(row, level_2, area.lstrip(), area.lstrip()[:2], code))
print('该工作表有%d行,%d列.'%(rows,cols))
差点忘了,数据库结构是这样的:
--
-- Table structure for table `region`
--
DROP TABLE IF EXISTS `region`;
CREATE TABLE `region` (
`id` bigint NOT NULL AUTO_INCREMENT,
`parent_id` bigint DEFAULT NULL COMMENT '上级区划ID,一级区划为0',
`name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '区划名称',
`short_name` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '区划简称',
`code` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '区划行政编号',
`level` int NOT NULL DEFAULT 0 COMMENT '区划级别:1-省、自治区、直辖市 2-地级市、地区、自治州、盟 3-市辖区、县级市、县,详见RegionLevel枚举',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '行政区划表' ROW_FORMAT = Dynamic;
三级区划树形结构,有简称、行政编码、行政级别