import xml.etree.ElementTree as ET
import numpy as np
import pyodbc
import requests
 
headers = {
   'Accept': 'application/xml',
}
r = requests.get('https://xml.meteoservice.ru/export/gismeteo/point/30861.xml', headers=headers)
tree = ET.parse(r.content)
#print(r.content)
#FORECAST_tag=[]
#FORECAST_items=[]
#TEMPERATURE_tag=[]
#TEMPERATURE_items=[]
#PHENOMENA_tag=[]
#PHENOMENA_items=[]
#tree = ET.parse('z.xml')
 
#tree = ET.ElementTree(file='z.xml')
root = tree.getroot() 
# все данные
print('Expertise Data:')
#for child_of_root in root.iter(): 
#    if child_of_root.tag=='FORECAST':
#       FORECAST_tag.append(child_of_root.tag)
#       FORECAST_items.append(child_of_root.items())
#    if child_of_root.tag=='TEMPERATURE':
#       TEMPERATURE_tag.append(child_of_root.tag)
#       TEMPERATURE_items.append(child_of_root.items())
#    if child_of_root.tag=='PHENOMENA':
#       PHENOMENA_tag.append(child_of_root.tag)
#       PHENOMENA_items.append(child_of_root.items())
#z=np.append(FORECAST_items,TEMPERATURE_items,PHENOMENA_items)   
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 = pyodbc.connect('DRIVER={SQL Server};SERVER=skp-db2;DATABASE=ARCH_BF_MDM_Light;')
cursor = conn.cursor()
cursor.executemany("""INSERT INTO [dbo].[pogodas]
           ([day]
           ,[month]
           ,[year]
           ,[hour]
           ,[Tmins]
           ,[Tmaxs]
           ,[cloudiness]
           ,[precipitation]
           ,[rpower]
           ,[spower]
           ,datas)
     VALUES
           (?
           ,?
           ,?
           ,?
           ,?
           ,?
           ,?
           ,?
           ,?
           ,?,getdate())""", z)
conn.commit()
print('Готово')