python3 爬虫连接mysql

进入mysql创建一个数据库

create database places;
use places;

创建一张表

create table place(
	id int primary key auto_increment,
	name varchar(20),
	longitude varchar(20),
	latitude varchar(20)
	) auto_increment = 1;

编写爬虫文件file.py

import json
import urllib.request
import re
import sys
import pymysql

url = 'http://api.sou-yun.com/api/Biography?author=&scope=&beginYear=0&endYear=0'
html = urllib.request.urlopen(url)
s = json.loads(html.read().decode('utf-8'))

db = pymysql.connect(
	host = 'localhost',
	port = 3306,
	user = 'root',
	password = '******',
	db = 'places',
	charset = 'utf8')

cur = db.cursor()

print ("地里位置：")
place = s["Traces"][0]["Markers"]
for x in place:
	try:
		insert_sql = "insert into place (name, longitude, latitude) values ('%s', '%s', '%s')"
		data = (x["Title"], x["Latitude"], x["Longitude"])
		cur.execute(insert_sql % data)
		db.commit()
		print (x["Title"])
		print ("纬度： %s" %(x["Latitude"]))
		print ("经度： %s" %(x["Longitude"]))
		print ()
	except:
		print ("error")
		db.rollback()

编译运行这个py文件

python3 file.py

去数据库中查看

mysql> select * from place;
+-----+--------------------------------+------------+------------+
| id  | name                           | longitude  | latitude   |
+-----+--------------------------------+------------+------------+
|   1 | 濮阳                           | 35.7675858 | 115.035919 |
|   2 | 邯郸                           | 36.63122   | 114.545807 |
|   3 | 运城                           | 35.0325851 | 111.013382 |
|   4 | 平顶山                         | 33.7720528 | 113.198936 |
|   5 | 绍兴                           | 30.0365181 | 120.58667  |
|   6 | 鹤壁                           | 35.752655  | 114.304047 |
|   7 | 西安                           | 34.347435  | 108.946304 |
|   8 | 渭南                           | 34.5056877 | 109.516739 |
|   9 | 安阳                           | 36.10365   | 114.3996   |
|  10 | 潍坊                           | 36.71321   | 119.168137 |
|  11 | 三门峡                         | 34.7784424 | 111.206833 |
|  12 | 扬州                           | 32.39986   | 119.419106 |
|  13 | 宜春                           | 27.82009   | 114.422684 |
|  14 | 九江                           | 29.7113285 | 116.007996 |
|  15 | 保定                           | 38.8800545 | 115.471054 |
|  16 | 六安                           | 31.7412262 | 116.529648 |
|  17 | 襄阳                           | 32.01479   | 112.129089 |
|  18 | 武威                           | 37.93408   | 102.644524 |
|  19 | 张掖                           | 38.9321861 | 100.456223 |
|  20 | 蒙古                           | 47.966156  | 107.149559 |
......