#### 分析
微信认证名中一般都会附带地区名，通过数据库的省市县名和微信名做存在判断（```注意:此方法存在一定概率的误判```）
#### 准备
需要省、市、县数据表的数据，需要的话请联系博主
#### 步骤
1.判断对应的省名是否在微信认证名中，如果在，则匹配出数据,并且判断如果是直辖市，获取对应的市级名字,id
2.如果省没匹配成功，则匹配市级数据的名字，如果成功，则获取对应的省名及对应省id
3.如果省市都没匹配成功，则匹配县区级数据的名字，如果成功，则获取对应的省市名及对应省市id
##### 数据表结构
```sql
CREATE TABLE `province` (
`id` bigint(19) NOT NULL AUTO_INCREMENT,
`name` varchar(32) NOT NULL COMMENT '省份名称',
`short_name` varchar(32) DEFAULT NULL COMMENT '省份简称',
`remark` varchar(255) DEFAULT NULL COMMENT '备注',
`created_at` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8;
CREATE TABLE `city` (
`id` bigint(19) NOT NULL AUTO_INCREMENT COMMENT '标识',
`name` varchar(32) NOT NULL COMMENT '城市名称',
`short_name` varchar(32) DEFAULT NULL COMMENT '城市简称',
`province_id` bigint(19) NOT NULL COMMENT '所属省份标识',
`level` int(10) NOT NULL COMMENT '城市等级（0未知，1:一线，2：二线，3：三线，4：四线）',
`remark` varchar(255) DEFAULT NULL COMMENT '备注',
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`,`province_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=7471 DEFAULT CHARSET=utf8;
CREATE TABLE `district` (
`id` bigint(19) NOT NULL AUTO_INCREMENT COMMENT '标识',
`name` varchar(32) NOT NULL COMMENT '区县名称',
`short_name` varchar(32) DEFAULT NULL COMMENT '区县简称',
`city_id` bigint(19) NOT NULL COMMENT '所属城市标识',
`remark` varchar(255) DEFAULT NULL COMMENT '备注',
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`,`city_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10069 DEFAULT CHARSET=utf8;
```
##### python脚本
```python
vim get_loc.py
```
```python
#coding:utf-8
import pymysql
db_host="xx.xx.xx.xx"
db_user="root"
db_pass="xxxxx"
db_port=3306
db_name="xxxx"
#微信认证名
file=open("weixin_auth.txt",'r',encoding="utf-8")
def execute_query_sql(sql):
#循环读取数据库状态是0的关键字100个
db= pymysql.connect(host=db_host,port=db_port,user=db_user, passwd=db_pass, db=db_name)
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
#执行sql
cursor.execute(sql)
results=cursor.fetchall()
# 关闭数据库连接
db.close()
return results
n=1
for f in file.readlines():
#获取省
pro_sql='select id,name from province;'
result=execute_query_sql(pro_sql)
is_has_pro=False
pro_id=-1
city_id=-1
district_id=-1
pro_name=""
for result_entry in result:
if result_entry[1][0:2] in f:
is_has_pro=True
pro_name=result_entry[1]
pro_id=result_entry[0]
#这里写死id
if pro_name=="北京市":
city_id=7151
if pro_name=="上海市":
city_id=7122
if pro_name=="天津市":
city_id=7182
if pro_name=="重庆市":
city_id=7430
break
#获取市
is_has_city=False
city_name=""
if not is_has_pro:
city_sql='select id,name from city where province_id>0;'
result=execute_query_sql(city_sql)
for result_entry in result:
if result_entry[1].replace("市","").replace("县","") in f:
is_has_city=True
city_name=result_entry[1]
city_id=result_entry[0]
#获取省id
pro_id_sql='select province_id from city where id='+str(city_id)+';'
result=execute_query_sql(pro_id_sql)
pro_id=result[0][0]
break
#获取区县
is_has_district=False
district_name=""
if not is_has_city and not is_has_pro:
district_sql='select id,name from district where city_id>0;'
result=execute_query_sql(district_sql)
for result_entry in result:
if result_entry[1].replace("市","").replace("县","") in f:
is_has_district=True
district_name=result_entry[1]
district_id=result_entry[0]
#获取市id
city_id_sql='select city_id from district where id='+str(district_id)+';'
result=execute_query_sql(city_id_sql)
city_id=result[0][0]
#获取省id
pro_id_sql='select province_id from city where id='+str(city_id)+';'
result=execute_query_sql(pro_id_sql)
pro_id=result[0][0]
break
#打印对应的id
#获取名字
if pro_id>0:
sql='select name from province where id='+str(pro_id)+';'
result=execute_query_sql(sql)
pro_name=result[0][0]
if city_id>0:
sql='select name from city where id='+str(city_id)+';'
result=execute_query_sql(sql)
city_name=result[0][0]
if district_id >0:
sql='select name from district where id='+str(district_id)+';'
result=execute_query_sql(sql)
district_name=result[0][0]
n+=1
print("当前执行到第"+str(n)+"行")
print(f.strip()+","+str(pro_id)+","+str(city_id)+","+str(district_id)+","+pro_name+","+city_name+","+district_name+"\n")
#把数据保存在文件中
file2=open("weixin_auth_loc.txt",'a',encoding="utf-8")
file2.write(f.strip()+","+str(pro_id)+","+str(city_id)+","+str(district_id)+","+pro_name+","+city_name+","+district_name+"\n")
file2.close()
```
