urlopen ElementTree pymssql cursor.executemany MSSQL

python > urlopen ElementTree pymssql cursor.executemany MSSQL
25.10.2019 11:39:59


Наиболее часто встречающиеся слова в статье:

[myArrayFORECAST] [myArrayPHENOMENA] [myArrayTEMPERATURE] ['month'] [cloudiness] ['cloudiness'] ['precipitation'] ['rpower'] ['spower'] [ElementTree]


Статья:

import xml.etree.ElementTree as ET
import numpy as np
import pymssql
import requests
from urllib.request import urlopen
 
url = urlopen("https://xml.meteoservice.ru/export/gismeteo/point/30861.xml")
f = url.read()
open("z.xml","wb").write(f)
 
tree = ET.parse('z.xml')
 
#tree = ET.ElementTree(file='z.xml')
root = tree.getroot()
# все данные
 
 
myArrayFORECAST = []
for elem in root.iter('FORECAST'):
day = elem.get('day')
month=elem.get('month')
year=elem.get('year')
hour=elem.get('hour')
myArrayFORECAST.append({'day':day,'month':month,'year':year,'hour':hour})
myArrayTEMPERATURE = []
for elem in root.iter('TEMPERATURE'):
maxs = elem.get('max')
mins=elem.get('min')
myArrayTEMPERATURE.append({'maxs':maxs,'mins':mins})
myArrayPHENOMENA = []
for elem in root.iter('PHENOMENA'):
cloudiness = elem.get('cloudiness')
precipitation=elem.get('precipitation')
rpower=elem.get('rpower')
spower=elem.get('spower')
myArrayPHENOMENA.append({'cloudiness':cloudiness,'precipitation':precipitation,'rpower':rpower,'spower':spower})
# соберём всё в один массив для sql
z=[]
i=0
for c in myArrayFORECAST:
z.append((myArrayFORECAST[i]['day'], myArrayFORECAST[i]['month'], myArrayFORECAST[i]['year'], myArrayFORECAST[i]['hour'],myArrayTEMPERATURE[i]['mins'],myArrayTEMPERATURE[i]['maxs'],myArrayPHENOMENA[i]['cloudiness'],myArrayPHENOMENA[i]['precipitation'],myArrayPHENOMENA[i]['rpower'],myArrayPHENOMENA[i]['spower']))
i=i+1
 
conn = pymssql.connect(server="i7", user="Пользователь", password="Пароль", database="ti")
cursor = conn.cursor()
cursor.executemany("""INSERT INTO [dbo].[pogodas]
([day]
,[month]
,[year]
,[hour]
,[Tmins]
,[Tmaxs]
,[cloudiness]
,[precipitation]
,[rpower]
,[spower]
,datas)
VALUES
(%s
,%s
,%s
,%s
,%s
,%s
,%s
,%s
,%s
,%s
,getdate())""", z)
conn.commit()