使用 python 世界港口插入到 mysql

使用 python 将世界港口插入到 mysql

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

__author__ = "leo"
__time__ = "2018-07-05"

import requests
from lxml import etree

import pymysql

# 打开数据库连接
db = pymysql.connect("localhost", "root", "", "hey_star")

# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()

page = 118
base = "https://www.gangkoudaima.com/Search/%s?keywords="

for i in range(1, page + 1):
    url = base % str(i)
    html = requests.get(url).text
    doc = etree.HTML(html)
    data = doc.xpath('//*[@id="wrap"]/div[2]/div[2]/table//td//text()')[6:]
    res = [data[i:i + 10] for i in range(0, len(data), 10)]

    for item in res:
        code = item[0].strip()
        port_name_zh = item[1].strip()
        port_name_en = item[2].strip()
        country_name_zh = item[4].strip()
        sql = """INSERT INTO reference_worldport
            (code, port_name_zh, port_name_en, country_name_zh)
                     VALUES ('%s', '%s', '%s', '%s');"""
        sql = sql % (code, port_name_zh, port_name_en, country_name_zh)
        print(sql)
        try:
            cursor.execute(sql)
            db.commit()
        except:
            db.rollback()

db.close()