python3 爬虫连接mysql

来源：互联网发布：淘宝客关闭返利编辑：程序博客网时间：2024/05/21 06:24

进入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 jsonimport urllib.requestimport reimport sysimport pymysqlurl = '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 |......

0 0