src/utils/saveSQL.py · DXnima/pythonData

代码拉取完成,页面将自动刷新

richgiteeai

Watch

不关注关注所有动态仅关注版本发行动态关注但不提醒动态

1 Star 3 Fork 0

DXnima/pythonData

代码 Issues 0 Pull Requests 0 Wiki 统计流水线

服务

加入 Gitee

与超过 1400万开发者一起发现、参与优秀开源项目,私有仓库也完全免费 :)

免费加入

已有帐号? 立即登录

文件

master

分支 (1)

管理

master

克隆/下载

HTTPS SSH SVN SVN+SSH 下载ZIP

提示

下载代码请复制以下命令到终端执行

为确保你提交的代码身份被 Gitee 正确识别,请执行以下命令完成配置

git config --global user.name userName 
git config --global user.email userEmail

初次使用 SSH 协议进行代码克隆、推送等操作时,需按下述提示完成 SSH 配置

1 生成 RSA 密钥

2 获取 RSA 公钥内容,并配置到 SSH公钥中

在 Gitee 上使用 SVN,请访问使用指南

使用 HTTPS 协议时,命令行会出现如下账号密码验证步骤。基于安全考虑,Gitee 建议配置并使用私人令牌替代登录密码进行克隆、推送等操作

Username for 'https://gitee.com': userName

Password for 'https://userName@gitee.com': # 私人令牌

分支 1

标签 0

pythonData

src

utils

saveSQL.py

saveSQL.py 5.17 KB

# -*- coding:utf-8 -*-
from bs4 import BeautifulSoup
import requests
import re
import MySQLdb
import requests
import json
import time
import xlwt
# 解决出现的写入错误
import sys
import importlib
importlib.reload(sys)

#台风轨迹点
start_time = [] #台风到达时间
lon = [] #台风到达地经度
lat = [] #台风到达地纬度
central_pressure = [] #台风中心气压
wind = [] #台风风速风力(米/秒)
direction = [] #未来移向
feature_speed = [] #未来移速
number_new=[] #台风编号
#台风登陆时的轨迹点
start_time_1 = [] #台风到达时间
lon_1 = [] #台风到达地经度
lat_1 = [] #台风到达地纬度
central_pressure_1 = [] #台风中心气压
wind_1 = [] #台风风速风力(米/秒)
direction_1 = [] #未来移向
feature_speed_1 = [] #未来移速
denglu = [] #台风登陆描述
number_newDL=[] #台风编号

#year = input("输入台风的年份") # 输入台风的年份
Year=[2018,2019]
#number = input("输入台风的编号") # 输入台风的编号
Number=[]
Number18=[1801,1802,1803,1804,1805,1806,1807,1808,1809,1810,1811,1812,1813,1814,1815,1816,1817,1818,1819,1820,1821,1822,1823,1824,1825,1826,1827,1828,1829] #,1800018,1800019
Number19=[1901,1902,1903,1904,1905,1906,1907,1908,1909,1910,1911,1912,1913,1914,190003,190003,190005]

#连接数据库
print('连接到mysql服务器...')
conn = MySQLdb.connect(host='127.0.0.1', port = 3306, user='root', passwd='123456', db='test', charset='utf8')
print('连接上了!')
cur = conn.cursor()

for index in range(len(Year)):
 year=str(Year[index])
 if year=="2018":
 Number=Number18
 else :
 Number = Number19
 for index in range(len(Number)):
 number=str(Number[index])
 #Python爬虫天气预报(小白入门) | Coder·码农网 https://www.codercto.com/a/9087.html
 #含时间戳的url,请求头
 url = "http://d1.weather.com.cn/typhoon/typhoon_data/"+year+"/"+number+".json?callback=getData&_="+str(int(round((time.time()*1000))))
 headers = {
 "User-Agent": "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/55.0.2883.87 Safari/537.36",
 "Referer": "http://typhoon.weather.com.cn/gis/typhoon_p.shtml",
 }
 r = requests.get(url,headers=headers)
 print(r.text)
 a = json.loads(r.text[8:-1])#解析json文件
 print(a["typhoon"][8])
 print(len(a["typhoon"][8]))

#print(a["typhoon"][8][21])
 for i in range(len(a["typhoon"][8])):
 #b = a["typhoon"][8][i]
 #print(b[1],b[4],b[5],b[6],b[7],b[8],b[9])
 start_time.append(a["typhoon"][8][i][1])
 lon.append(a["typhoon"][8][i][4])
 lat.append(a["typhoon"][8][i][5])
 central_pressure.append(a["typhoon"][8][i][6])
 wind.append(a["typhoon"][8][i][7])
 direction.append(a["typhoon"][8][i][8])
 feature_speed.append(a["typhoon"][8][i][9])
 number_new.append(number)
 if len(a["typhoon"][8][i]) > 13:
 start_time_1.append(a["typhoon"][8][i][1])
 lon_1.append(a["typhoon"][8][i][4])
 lat_1.append(a["typhoon"][8][i][5])
 central_pressure_1.append(a["typhoon"][8][i][6])
 wind_1.append(a["typhoon"][8][i][7])
 direction_1.append(a["typhoon"][8][i][8])
 feature_speed_1.append(a["typhoon"][8][i][9])
 denglu.append(a["typhoon"][8][i][13])
 number_newDL.append(number)
 print(a["typhoon"][8][i][13])
 # 写入数据库

j=0
 q=0
 for start_time1,lon1,lat1,central_pressure1,wind1,direction1,feature_speed1,number_new1\
 in zip(start_time,lon,lat,central_pressure,wind,direction,feature_speed,number_new):
 into = "INSERT INTO tf_geo(tf_geo_number,tf_geo_time,tf_geo_lon,tf_geo_lat,tf_geo_central_pressure,tf_geo_wind,tf_geo_direction,tf_geo_feature_speed) VALUES (%s,%s, %s,%s, %s,%s, %s,%s)"
 values = (number_new1,start_time1,lon1,lat1,central_pressure1,wind1,direction1,feature_speed1)
 cur.execute(into, values)
 conn.commit()
 j = j+1
 for start_time1,lon1,lat1,central_pressure1,wind1,direction1,feature_speed1,denglu1,number_newDL1\
 in zip(start_time_1,lon_1,lat_1,central_pressure_1,wind_1,direction_1,feature_speed_1,denglu,number_newDL):
 into = "INSERT INTO tf_loacl(tf_loacl_number,tf_loacl_time,tf_loacl_lon,tf_loacl_lat,tf_loacl_text) VALUES (%s,%s,%s, %s,%s)"
 values = (number_newDL1,start_time1,lon1,lat1,denglu1)
 cur.execute(into, values)
 conn.commit()
 q = q+1

# # 判断表是否存在,若存在则删除此表
# cur.execute("DROP TABLE xiaoshuo")
#
# # 创建表
# # cursor(): 使用该链接创建并返回的游标
# # execute():执行一个数据库查询和命令
# # commit():提交但前事物(写入数据时也会用到
# sql = """CREATE TABLE xiaoshuo(
# title CHAR(20),
# sec_title CHAR(20),
# content VARCHAR(6499))"""
# cur.execute(sql)
# conn.commit()
# print('表创建成功!')

conn.close() #关闭数据库

一键复制编辑原始数据按行查看历史

DXnima 提交于 2022年10月24日 19:12 +08:00 . 添加获取谷歌瓦片

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128

# -*- coding:utf-8 -*-
from bs4 import BeautifulSoup
import requests
import re
import MySQLdb
import requests
import json
import time
import xlwt
# 解决出现的写入错误
import sys
import importlib
importlib.reload(sys)

#台风轨迹点
start_time = [] #台风到达时间
lon = [] #台风到达地经度
lat = [] #台风到达地纬度
central_pressure = [] #台风中心气压
wind = [] #台风风速风力(米/秒)
direction = [] #未来移向
feature_speed = [] #未来移速
number_new=[] #台风编号
#台风登陆时的轨迹点
start_time_1 = [] #台风到达时间
lon_1 = [] #台风到达地经度
lat_1 = [] #台风到达地纬度
central_pressure_1 = [] #台风中心气压
wind_1 = [] #台风风速风力(米/秒)
direction_1 = [] #未来移向
feature_speed_1 = [] #未来移速
denglu = [] #台风登陆描述
number_newDL=[] #台风编号


#year = input("输入台风的年份") # 输入台风的年份
Year=[2018,2019]
#number = input("输入台风的编号") # 输入台风的编号
Number=[]
Number18=[1801,1802,1803,1804,1805,1806,1807,1808,1809,1810,1811,1812,1813,1814,1815,1816,1817,1818,1819,1820,1821,1822,1823,1824,1825,1826,1827,1828,1829] #,1800018,1800019
Number19=[1901,1902,1903,1904,1905,1906,1907,1908,1909,1910,1911,1912,1913,1914,190003,190003,190005]

#连接数据库
print('连接到mysql服务器...')
conn = MySQLdb.connect(host='127.0.0.1', port = 3306, user='root', passwd='123456', db='test', charset='utf8')
print('连接上了!')
cur = conn.cursor()

for index in range(len(Year)):
 year=str(Year[index])
 if year=="2018":
 Number=Number18
 else :
 Number = Number19
 for index in range(len(Number)):
 number=str(Number[index])
 #Python爬虫天气预报(小白入门) | Coder·码农网 https://www.codercto.com/a/9087.html
 #含时间戳的url,请求头
 url = "http://d1.weather.com.cn/typhoon/typhoon_data/"+year+"/"+number+".json?callback=getData&_="+str(int(round((time.time()*1000))))
 headers = {
 "User-Agent": "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/55.0.2883.87 Safari/537.36",
 "Referer": "http://typhoon.weather.com.cn/gis/typhoon_p.shtml",
 }
 r = requests.get(url,headers=headers)
 print(r.text)
 a = json.loads(r.text[8:-1])#解析json文件
 print(a["typhoon"][8])
 print(len(a["typhoon"][8]))


 #print(a["typhoon"][8][21])
 for i in range(len(a["typhoon"][8])):
 #b = a["typhoon"][8][i]
 #print(b[1],b[4],b[5],b[6],b[7],b[8],b[9])
 start_time.append(a["typhoon"][8][i][1])
 lon.append(a["typhoon"][8][i][4])
 lat.append(a["typhoon"][8][i][5])
 central_pressure.append(a["typhoon"][8][i][6])
 wind.append(a["typhoon"][8][i][7])
 direction.append(a["typhoon"][8][i][8])
 feature_speed.append(a["typhoon"][8][i][9])
 number_new.append(number)
 if len(a["typhoon"][8][i]) > 13:
 start_time_1.append(a["typhoon"][8][i][1])
 lon_1.append(a["typhoon"][8][i][4])
 lat_1.append(a["typhoon"][8][i][5])
 central_pressure_1.append(a["typhoon"][8][i][6])
 wind_1.append(a["typhoon"][8][i][7])
 direction_1.append(a["typhoon"][8][i][8])
 feature_speed_1.append(a["typhoon"][8][i][9])
 denglu.append(a["typhoon"][8][i][13])
 number_newDL.append(number)
 print(a["typhoon"][8][i][13])
 # 写入数据库

 j=0
 q=0
 for start_time1,lon1,lat1,central_pressure1,wind1,direction1,feature_speed1,number_new1\
 in zip(start_time,lon,lat,central_pressure,wind,direction,feature_speed,number_new):
 into = "INSERT INTO tf_geo(tf_geo_number,tf_geo_time,tf_geo_lon,tf_geo_lat,tf_geo_central_pressure,tf_geo_wind,tf_geo_direction,tf_geo_feature_speed) VALUES (%s,%s, %s,%s, %s,%s, %s,%s)"
 values = (number_new1,start_time1,lon1,lat1,central_pressure1,wind1,direction1,feature_speed1)
 cur.execute(into, values)
 conn.commit()
 j = j+1
 for start_time1,lon1,lat1,central_pressure1,wind1,direction1,feature_speed1,denglu1,number_newDL1\
 in zip(start_time_1,lon_1,lat_1,central_pressure_1,wind_1,direction_1,feature_speed_1,denglu,number_newDL):
 into = "INSERT INTO tf_loacl(tf_loacl_number,tf_loacl_time,tf_loacl_lon,tf_loacl_lat,tf_loacl_text) VALUES (%s,%s,%s, %s,%s)"
 values = (number_newDL1,start_time1,lon1,lat1,denglu1)
 cur.execute(into, values)
 conn.commit()
 q = q+1

# # 判断表是否存在,若存在则删除此表
# cur.execute("DROP TABLE xiaoshuo")
#
# # 创建表
# # cursor(): 使用该链接创建并返回的游标
# # execute():执行一个数据库查询和命令
# # commit():提交但前事物(写入数据时也会用到
# sql = """CREATE TABLE xiaoshuo(
# title CHAR(20),
# sec_title CHAR(20),
# content VARCHAR(6499))"""
# cur.execute(sql)
# conn.commit()
# print('表创建成功!')

conn.close() #关闭数据库