本地创建数据库，将 excel 数据存储到 city 表中，再取 | 湖北省 | 的所有地级市和县、县级市、区数据作为样表数据记录在样表中。利用 python 的 xlrd 包，定义 process_data 包来存放操作 excel 数据，生成 sql 语句的类，定义 op_postgresql 包来存放数据库的操作对象，定义各种方法

城市距离爬取 – 任务计划

本地创建数据库，将 excel 数据存储到 city 表中，再取 | 湖北省 | 的所有地级市和县、县级市、区数据作为样表数据记录在样表中。

本地创建数据库，将 excel 数据存储到 city 表中，再取 | 湖北省 | 的所有地级市和县、县级市、区数据作为样表数据记录在样表中。准备工作创建好 public/config.py 扩展包，到时候，利用 python 的 xlrd 包，定义 process_data 包来存放操作 excel 数据，生成 sql 语句的类，
定义 op_postgresql 包来存放数据库的操作对象，定义各种方法
创建 crwler 包，来存放爬虫的操作对象 -> 发现对方网站调用的地图 api -> 更改为调用德地图 api 的包 – 存放操作对象
创建 log 文件夹，存放数据库操作的日志
创建 data 文件夹，存放初始 excel 数据

数据库基本构造：

样本数据表格式：
表名：sample_table

样本 1-1 地点 route 表的格式

表名:sample_route

创建配置信息接口

方便存储我们需要的特定变量和配置信息。

public/config.py

#!/usr/bin/python
# -*- coding: utf-8 -*-
#__author__: stray_camel

import os,sys
#当前package所在目录的上级目录
src_path = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))

创建读取 excel 数据的接口

利用 python 的 xlrd 包，定义 process_data 包来存放操作 excel 数据，生成 sql 语句的类

参考 github 源码 readme 文档
并没有发现在 PyPI 上有 document，所以只能去 github 上找源码了，xlrd 处理 excel 基础 guide

import xlrd
book = xlrd.open_workbook("myfile.xls")
print("The number of worksheets is {0}".format(book.nsheets))
print("Worksheet name(s): {0}".format(book.sheet_names()))
sh = book.sheet_by_index(0)
print("{0} {1} {2}".format(sh.name, sh.nrows, sh.ncols))
print("Cell D30 is {0}".format(sh.cell_value(rowx=29, colx=3)))
for rx in range(sh.nrows):
print(sh.row(rx))

创建 process_data/excel2sql.py 扩展包，方便后面 import
获取 excel 的数据构造 sql 语句，创建 city 表（湖北省）样表

process_data/excel2sql.py

#!/usr/bin/python
# -*- coding: utf-8 -*-
#__author__: stray_camel

import xlrd,sys,os,logging
from public import config
class Excel2Sql(object):
    def __init__(
        self, 
        url:"str类型的文件路径", 
        sheet:"excel中的表单名"):
        self.f_name = url
        # 将excel中特定表单名数据存储起来
        self.sh_data = xlrd.open_workbook(self.f_name).sheet_by_name(sheet)
        self.rows = self.sh_data.nrows
        self.cols = self.sh_data.ncols

当我们生成这个 Excel2Sql 对象的时候，我们希望按照类似

excel_data = excel2sql.Excel2Sql("fiel_name","sheet_name")

的代码形式来直接读取 excel 文件并获取某个表单的数据。所以在初始化对象的时候我们希望对其属性进行赋值。

excel 表中，我们按照下面的形式进行存储数据：

 

之后我们希望通过调用这个类（接口）地时候能够访问其中一个函数，只获取某个省 / 或者直辖市的所有数据，类似湖北省，我们指向获取奇中 103 个县、区。

在类 Excel2Sql 中定义方法：

def init_SampleViaProvince_name(
        self, 
        Province_name:"省名"
        ) ->"insert的数据，列表形式[(\'地域名1\',\'1\',\'经纬度\'),(\'地域名2\',\'1\',\'经纬度\')]":
        geo_app = Geo_mapInterface(config.geo_key)
        all_data = [self.sh_data.row_values(i) for i in range(self.rows)]

        cities_data=[[["".join(i),1],["".join(i[1:len(i)]),1]][i[0]==i[1]] for i in all_data if i[0] == Province_name]
        for i in cities_data:
            i.append(geo_app.get_coordinatesViaaddress("".join(i[0])))
        # cities_data=[[["".join(i),1,\'test1\'],["".join(i[1:len(i)]),1,\'test2\']][i[0]==i[1]] for i in all_data if i[0] == Province_name]
        return cities_data

之后我们可以测试类的构造是否正确，或进行调试：
在文件末端编写：

if __name__ == "__main__":
    test = Excel2Sql(config.src_path+"\\data\\2019最新全国城市省市县区行政级别对照表(194).xls","全国城市省市县区域列表")
    print(test.init_SampleViaProvince_name("北京市"))

测试结果：

(env) PS F:\览众数据> & f:/览众数据/env/Scripts/python.exe f:/览众数据/城市距离爬取/process_data/excel2sql.py
[[\'北京市东城区\', 1, \'116.416357,39.928353\'], [\'北京市西城区\', 1, \'116.365868,39.912289\'], [\'北京市崇文区\', 1,
\'116.416357,39.928353\'], [\'北京市宣武区\', 1, \'116.365868,39.912289\'], [\'北京市朝阳区\', 1, \'116.601144,39.948574\'], [\'北京市丰台区\', 1, \'116.287149,39.858427\'], [\'北京市石景山区\', 1, \'116.222982,39.906611\'], [\'北京市海淀区\', 1, \'116.329519,39.972134\'], [\'北京市门头沟区\', 1, \'116.102009,39.940646\'], [\'北京市房山区\', 1, \'116.143267,39.749144\'], [\'北京市通州区\', 1, \'116.656435,39.909946\'], [\'北京市顺义区\', 1, \'116.654561,40.130347\'], [\'北京市昌
平区\', 1, \'116.231204,40.220660\'], [\'北京市大兴区\', 1, \'116.341014,39.784747\'], [\'北京市平谷区\', 1, \'117.121383,40.140701\'], [\'北京市怀柔区\', 1, \'116.642349,40.315704\'], [\'北京市密云县\', 1, \'116.843177,40.376834\'], [\'北京
市延庆县\', 1, \'115.974848,40.456951\']]

创建 OP 数据库 postgresql（其他数据库也都一样啦～）接口

定义 op_postgresql 包来存放数据库的操作对象，定义各种方法

数据库的 curd 真的是从大二写到大四。
访问 postgresql 数据库一般用的包：psycopg2
访问官网
在这个操作文档网站中，使用的思路已经很清楚的写出来了 http://initd.org/psycopg/docs/usage.html

 

希望大小少在网上走弯路（少看一些翻译过来的文档）。。。
http://initd.org/psycopg/

模式还是一样，调用 postgresql 的驱动 / 接口，设置参数登陆，访问数据库。设置光标，注入 sql 数据，fetch 返回值。

• 这里需要注意的几点是，默认防 xss 注入，写代码时一般设置参数访问。
• 注意生成日志文件，打印日志

具体过程不赘述，直接上代码

op_postgresql/opsql.py:

#!/usr/bin/python
# -*- coding: utf-8 -*-
#__author__: stray_camel

\'\'\'
定义对mysql数据库基本操作的封装
1.数据插入
2.表的清空
3.查询表的所有数据
\'\'\'
import logging
import psycopg2
from public import config
class OperationDbInterface(object):
    #定义初始化连接数据库
    def __init__(self, 
    host_db : \'数据库服务主机\' = \'localhost\', 
    user_db: \'数据库用户名\' = \'postgres\', 
    passwd_db: \'数据库密码\' = \'1026shenyang\', 
    name_db: \'数据库名称\' = \'linezone\', 
    port_db: \'端口号，整型数字\'=5432):
        try:
            self.conn=psycopg2.connect(database=name_db, user=user_db, password=passwd_db, host=host_db, port=port_db)#创建数据库链接
        except psycopg2.Error as e:
            print("创建数据库连接失败|postgresql Error %d: %s" % (e.args[0], e.args[1]))
            logging.basicConfig(stream=open(config.src_path + \'/log/syserror.log\', encoding="utf-8", mode="a"), level = logging.DEBUG,format=\'%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s\')
            logger = logging.getLogger(__name__)
            logger.exception(e)
        self.cur=self.conn.cursor()

    #定义在样本表中插入数据操作
    def insert_sample_data(self, 
    condition : "insert语句" = "insert into sample_data(address,ad_type,coordinates) values (%s,%s,%s)", 
    params : "insert数据，列表形式[(\'地域名1\',\'1\',\'经纬度\'),(\'地域名2\',\'1\',\'经纬度\')]" = [(\'地域名1\',\'1\',\'经纬度\'),(\'地域名2\',\'1\',\'经纬度\')]
    ) -> "字典形式的批量插入数据结果" :
        try:
            self.cur.executemany(condition,params)
            self.conn.commit()
            result={\'code\':\'0000\',\'message\':\'执行批量插入操作成功\',\'data\':len(params)}
            logging.basicConfig(stream=open(config.src_path + \'/log/syserror.log\', encoding="utf-8", mode="a"), level = logging.DEBUG, format=\'%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s\')
            logger = logging.getLogger(__name__)
            logger.info("在样本表sample_data中插入数据{}条，操作：{}!".format(result[\'data\'],result[\'message\']))
        except psycopg2.Error as e:
            self.conn.rollback()  # 执行回滚操作
            result={\'code\':\'9999\',\'message\':\'执行批量插入异常\',\'data\':[]}
            print