萌新流泪之POI爬取、写入数据库、pandas的sql坑

poi爬取全代码

作为半小白，在认真学习了python基础半年之后，私以为可以不费力解决基础爬虫的运行bug,呵！转眼就被抽了一记响亮的大嘴巴子，经过一晚上的调整、运行、报错、调整、运行。。。。终于成功了。鸡冻的我赶紧记录下来自己在python之旅上踏下的一个小脚印。

不废话，先上爬取写入数据库的全代码

import json
import os
import requests
import sys
import time
import pandas as pd
from sqlalchemy import create_engine

class BaiDuPOI(object):
    def __init__(self, itemy, loc):
        self.itemy = itemy
        self.loc = loc

    def urls(self):
        api_key = baidu_api
        urls = []
        for pages in range(0, 20):
            url = 'https://api.map.baidu.com/place/v2/search?query=' + self.itemy + '&bounds=' + self.loc + '&page_size=20&page_num=' + str(
                pages) + '&output=json&ak=' + api_key
            urls.append(url)
        return urls

    def baidu_search(self):
        try:
            global url
            for i, url in enumerate(self.urls()):
                print(i, url)
                json_obj = requests.get(url).text
                data = json.loads(json_obj)
                if data['total'] != 0:
                    for item in data['results']:
                        json_sel = {}
                        json_sel['h1'] = h1
                        json_sel['h2'] = h2
                        json_sel['name'] = item["name"]
                        json_sel['lat'] = item["location"]["lat"]
                        json_sel['lng'] = item["location"]["lng"]
                        yield json_sel
                else:
                    print('本页及以后无数据')
                    break
        except:
            print('one error')
            with open('./log.txt', 'a', encoding='utf-8') as fl:
                fl.write(url + '\n')


class LocaDiv(object):
    def __init__(self, loc_all, divd):
        self.loc_all = loc_all
        self.divd = divd

    def lat_all(self):
        lat_sw = float(self.loc_all.split(',')[0])
        lat_ne = float(self.loc_all.split(',')[2])
        lat_list = [str(lat_ne)]
        while lat_ne - lat_sw >= 0:
            m = lat_ne - self.divd
            lat_ne = lat_ne - self.divd
            lat_list.append('%.2f' % m)
        return sorted(lat_list)

    def lng_all(self):
        lng_sw = float(self.loc_all.split(',')[1])
        lng_ne = float(self.loc_all.split(',')[3])
        lng_list = [str(lng_ne)]
        while lng_ne - lng_sw >= 0:
            m = lng_ne - self.divd
            lng_ne = lng_ne - self.divd
            lng_list.append('%.2f' % m)
        return sorted(lng_list)

    def ls_com(self):
        l1 = self.lat_all()
        l2 = self.lng_all()
        ab_list = []
        for i in range(0, len(l1)):
            a = str(l1[i])
            for i2 in range(0, len(l2)):
                b = str(l2[i2])
                ab = a + ',' + b
                ab_list.append(ab)
        return ab_list

    def ls_row(self):
        l1 = self.lat_all()
        l2 = self.lng_all()
        ls_com_v = self.ls_com()
        ls = []
        for n in range(0, len(l1) - 1):
            for i in range(len(l2) * n, len(l2) * (n + 1) - 1):
                a = ls_com_v[i]
                b = ls_com_v[i + len(l2) + 1]
                ab = a + ',' + b
                ls.append(ab)
        return ls


if __name__ == '__main__':
    engine = create_engine('mssql+pymssql://数据库用户名:密码@数据库服务器名/数据库?charset=utf8')
    #这里填入自己数据库的参数，我用的是sql sever
    pois = {'商业': ['酒店', '购物', '金融'],
            '公共服务': ['生活服务', '丽人', '运动健身', '生活服务', '留学中介机构', '培训机构', '汽车销售', '汽车维修',
                     '汽车美容', '汽车配件', '汽车租赁', '汽车检测场'],
            '行政机构': ['政府机构'],
            '停车场': ['停车场'],
            '旅游景点': ['旅游景点'],
            '工业': ['公司企业'],
            '餐饮': ['美食'],
            '娱乐': ['休闲娱乐'],
            '教育': ['高等院校', '中学', '小学', '幼儿园', '成人教育', '亲子教育', '特殊教育学校'],
            '医疗': ['医疗'],
            '文化设施': ['图书馆', '科技馆', '新闻出版', '广播电视', '艺术团体', '美术馆', '展览馆', '文化宫'],
            '商务办公': ['科研机构', '写字楼'],
            '交通服务设施': ['飞机场', '火车站', '地铁站', '长途汽车站', '公交车站', '港口', '加油加气站', '服务区', '收费站', '桥', '充电站', '路侧停车位']}
    baidu_api = 'axyMyKpbY9remLczpfBrSlvlwh10kwD9'  #记得更新api，输入自己的密钥，在百度控制台里面新建应用获取
    print("开始爬数据，请稍等...")
    start_time = time.time()
    loc = LocaDiv('38.0056477858,114.4360955007,38.0742058822,114.5822123353', 0.02)  # 填入爬取区域坐标、子区域长宽，两个坐标分别是区域的左下角和右上角
    locs_to_use = loc.ls_row()
    for h1, v in pois.items():
        print('爬取：', h1)
        file_name = './data/baidu_poi_{}'.format(h1) # format字符格式化，在数据库中生成13个表
        for loc_to_use in locs_to_use:
            for h2 in v:
                par = BaiDuPOI(h2, loc_to_use)  # 如果下载单个类型的POI，可以修改这里的类型参数，h2是小类
                dt = par.baidu_search()
                df = pd.DataFrame(dt)
                if len(df) != 0:
                    print(df)
                   
                    #这里附上一开始运行的写入数据库代码
                    #df.to_sql(file_name, con=engine, if_exists='append',index=False)
                    #pd.io.sql.to_sql(df,name=file_name,con=engine,schema='w_analysis',if_exists='append',index=False)
  
                    pd.io.sql.to_sql(df,name=file_name,con=engine,if_exists='append',index=False)#这里用io模块写入数据库而不是传统的data to sql
                    time.sleep(1)
                else:
                    pass
    end_time = time.time()
    print("数据爬取完毕，用时%.2f秒" % (end_time - start_time))

上面的代码是完整可运行的，下面，是此次过程流过的泪

1. 数据库不存在或访问被拒绝 ，一开始没有用pymssql，而是傻瓜式的import pyodbc，运行过后提总是提示连接不上，后来读了官方文档发现人家根本不是这么用的，在博客上搜搜搜搜搜才写对了这么一行

engine = create_engine('mssql+pymssql://数据库用户名:密码@数据库服务器名/数据库?charset=utf8')

这里的参数没有最后的driver=sql sever,建议初次运行可以单独连接数据库测试成功再放进全代码中，就比如下面

from sqlalchemy import create_engine

HOSTNAME = "localhost"
PORT = "3306"
DATABASE = "你的数据库名称"
USERNAME = "用户名"
PASSWORD = "密码"
 
DB_URI = "mssql+pymssql://{username}:{password}@{host}/{db}?charset=utf8".\
    format(username=USERNAME,password=PASSWORD,host=HOSTNAME,db=DATABASE)

engine = create_engine(DB_URI)

conn = engine.connect()
result = conn.execute("select 1")
print(result.fetchone()) 

这也是我在博客上找到的测试代码，运行发现连接成功，进行下一步

2. 创建表被拒绝 运行后来发现错误从create_engine那一行变成了pd.to_sql这一行

  #这里附上一开始运行的写入数据库代码
 df.to_sql(file_name, con=engine, if_exists='append',index=False,driver=sql sever)               	  

这一行代码搞得我焦头烂额，总是卡在这里，网上看看写写改成了这样，是因为to sql 不是pandas库里而是在pandas库里的io模块中

 pd.io.sql.to_sql(df,name=file_name,con=engine,schema='w_analysis',if_exists='append',index=False)   

那么最后进阶去掉schema参数

pd.io.sql.to_sql(df,name=file_name,con=engine,if_exists='append',index=False)

这就大功告成了
这里还需要注意几个问题

• from sqlalchemy import create_engine 要用pandas库的 pd to sql 就不能用 import pymssql。
• create engine里面用户名：密码@数据库，有的是用户名：密码@接口/数据库,接口一般默认3306，在连接数据库时不要写，不然会连接不上。
• if_exists='append，这里是如果存在就继续追加数据进去，还有的if_exists='replace/append/fail',注意这里的清空和关闭。

大概就这么多，祝各位好运！