CentraleSupélec LMF, UMR CNRS 9021
Département informatique Laboratoire Méthodes Formelles
Bât Breguet, 3 rue Joliot-Curie Bât 650 Ada Lovelace, Université Paris Sud
91190 Gif-sur-Yvette, France Rue Noetzlin, 91190 Gif-sur-Yvette, France
TD n°8 et 9 SIP

Le sujet SIP-TD08-09.pdf

Éléments de corrigé

Le site de SQLAlchemy

Exercices 1 à 4

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy import Table, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.orm import sessionmaker

engine = create_engine('sqlite:///:memory:', echo = False)
Base = declarative_base()

affiliation = Table(
    'Affiliation', Base.metadata,
    Column('asso_id', Integer, ForeignKey('Association.asso_id'), nullable=False),
    Column('student_id', Integer, ForeignKey('Student.number'), nullable=False)
)

class Student(Base):
    __tablename__ = 'Student'
    number = Column(Integer, primary_key=True)
    firstName = Column(String, nullable=False)
    lastName = Column(String, nullable=False)
    nickName = Column(String)
    country = Column(String)
    associations = relationship('Association', secondary = affiliation, back_populates = 'students')

class Association(Base):
    __tablename__ = 'Association'
    asso_id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    description = Column(String)
    students = relationship('Student', secondary = affiliation, back_populates = 'associations')


Base.metadata.create_all(engine)
print("Tables created!")

Session = sessionmaker(bind = engine)
session = Session()

nox = Student(number = 1, firstName = "Thomas", lastName = "Adier", nickName = "nox", country = "France")
session.add(nox)
print("Student nox added.")

rezo = Association(asso_id = 1, name = "ViaRezo", description = "Bringing Internet to the campus")
session.add(rezo)
print("Association ViaRezo added.")

enizor = Student(number=2, firstName='Remi', lastName='Garde',
                 nickName='enizor', country='France')
gossex = Student(number=3, firstName='David', lastName='Gosset',
                 nickName='gossex', country='France')
xix = Student(number=4, firstName='Julen', lastName='Dixneuf',
              nickName='xix', country='France')
session.add_all([enizor, gossex, xix])
print("enizor, gossex, xix added!")

rezo.students.append(nox)
rezo.students.append(enizor)
rezo.students.append(gossex)
rezo.students.append(xix)

session.commit()

# [asso.name for asso in enizor.associations]
# [stud.firstName + " " + stud.lastName for stud in session.query(Student)]
# [stud.nickName for stud in session.query(Student).filter(Student.lastName == 'Adier')]
# [stud.nickName for stud in session.query(Student).filter(Student.lastName == 'Adier').filter(Student.firstName.startswith('Tho'))]
# session.query(Association).one().name
# session.query(Student).get(1).lastName

Exercices 5 à 13

import csv

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy import Table, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.orm import sessionmaker

engine = create_engine('sqlite:///:memory:', echo = False)
Base = declarative_base()

class Student(Base):
    __tablename__ = 'Student'
    number = Column(Integer, primary_key=True)
    firstName = Column(String, nullable=False)
    lastName = Column(String, nullable=False)
    nickName = Column(String)
    country = Column(String)
    associations = relationship('Affiliation', back_populates = 'student')
    addresses = relationship('Address', back_populates = 'student')

class Association(Base):
    __tablename__ = 'Association'
    asso_id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    description = Column(String)
    students = relationship('Affiliation', back_populates = 'asso',
                            cascade = "all, delete, delete-orphan")

class Address(Base):
    __tablename__ = 'Address'
    email = Column(String, primary_key=True)
    student_id = Column(Integer, ForeignKey('Student.number'))
    student = relationship('Student', back_populates = 'addresses')

class Affiliation(Base):
    __tablename__ = 'Affiliation'
    asso_id = Column(Integer, ForeignKey('Association.asso_id'), primary_key=True)
    student_id = Column(Integer, ForeignKey('Student.number'), primary_key=True)
    role = Column(String, nullable=False)
    asso = relationship('Association', back_populates='students')
    student = relationship('Student', back_populates='associations')

Base.metadata.create_all(engine)
print("Tables created!")

##########
# Session = sessionmaker(bind = engine)
# session = Session()
#
# nox = Student(number = 1, firstName = "Thomas", lastName = "Adier", nickName = "nox", country = "France")
# session.add(nox)
# print("Student nox added.")
#
# rezo = Association(asso_id = 1, name = "ViaRezo", description = "Bringing Internet to the campus")
# session.add(rezo)
# print("Association ViaRezo added.")
#
# enizor = Student(number=2, firstName='Remi', lastName='Garde',
#                  nickName='enizor', country='France')
# gossex = Student(number=3, firstName='David', lastName='Gosset',
#                  nickName='gossex', country='France')
# xix = Student(number=4, firstName='Julien', lastName='Dixneuf',
#               nickName='xix', country='France')
# session.add_all([enizor, gossex, xix])
# print("enizor, gossex, xix added!")
#
# session.add(Affiliation(asso_id = rezo.asso_id, student_id = enizor.number, role="President"))
# session.add(Address(student_id = xix.number, email = 'Julien.Dixneuf@provider.com'))
# session.commit()
#
# # [af.student.nickName for af in rezo.students]
# # [asso.name for asso in [affi.asso for affi in enizor.associations]]
# # [addr.email for addr in xix.addresses]
##########

def populates() :
    Session = sessionmaker(bind=engine)
    session = Session()

    # Load student data
    with open('../data/students.csv') as studfile :
        reader = csv.reader(studfile, delimiter=';')
        next(reader)  # skip headers
        students = {} # dictionary for storing student_id/student pairs
        for line in reader :
            newstud = Student(number=int(line[0]),
                              firstName=line[1],
                              lastName=line[2],
                              nickName=line[3],
                              country=line[4])
            students[newstud.number] = newstud
            session.add(newstud)
    # Load association data
    with open('../data/associations.csv') as assofile :
        reader = csv.reader(assofile, delimiter=';')
        next(reader)      # skip headers
        associations = {} # dictionary for storing asso_id/association pairs
        for line in reader :
            newasso = Association(asso_id=int(line[0]),
                                  name=line[1],
                                  description=line[2])
            associations[newasso.asso_id] = newasso
            session.add(newasso)
    # Load affiliation data
    with open('../data/affiliation.csv') as affifile :
        reader = csv.reader(affifile, delimiter=';')
        next(reader)  # skip headers
        for line in reader :
            session.add(Affiliation(asso_id=int(line[0]),
                                    student_id=int(line[1]),
                                    role=line[2]))
    # Load address data
    with open('../data/adresses.csv') as emailfile :
        reader = csv.reader(emailfile, delimiter=';')
        next(reader)  # skip headers
        for line in reader :
            session.add(Address(student_id=int(line[0]),
                                email=line[1]))
    # Commit data
    session.commit()

# Session = sessionmaker(bind=engine)
# session = Session()

## Students from Brazil
# session.query(Student).filter(Student.country=='Brazil').count()
# [stud.firstName + " " + stud.lastName for stud in session.query(Student).filter(Student.country=='Brazil')]

## Students in association ViaRezo
## X.any(P) tels if any item in collection X has property P
## X.has(P) tels if item X has property P
# session.query(Student).filter(Student.associations.any(Affiliation.asso.has(Association.name=='ViaRezo'))).count()
# [stud.firstName + " " + stud.lastName for stud in session.query(Student).filter(Student.associations.any(Affiliation.asso.has(Association.name=='ViaRezo')))]

## Students in ViaRezo by country
# [s for s in session.query(func.count(Student.number), Student.country).filter(Student.associations.any(Affiliation.asso.has(Association.name=='ViaRezo'))).group_by(Student.country)]

## Presidents of associations
# [s.firstName + " " + s.lastName for s in session.query(Student).filter(Student.associations.any(Affiliation.role == 'president'))]

## Delete association BDI
# bdi = session.query(Association).filter(Association.name=='BDI').one()
# session.delete(bdi)
# session.commit()

## Add an email to Julie Rose (sudent number 8)
# julie = session.query(Student).filter(Student.number == 8).one()
# julie.addresses.append(Address(email="julier@gmail.com"))
# session.commit()

Deuxième partie (OpenWeatherMap)

À propos des API RESTful : Wikipedia

# 7878656358ddfb28fb9b904b2242fd2a
import requests
import json

WeatherAPIKey = "7878656358ddfb28fb9b904b2242fd2a"

req = requests.get("http://api.openweathermap.org/data/2.5/weather?q=Paris,France&APPID=" + WeatherAPIKey)
rawData = req.content.decode('utf-8')

data = json.loads(rawData)

# Ex 14
print(data["weather"][0]["description"])

# Ex 15
print(data["main"]["temp"]-273.15)

# Ex 16
req = requests.get("http://api.openweathermap.org/data/2.5/find?lat=48.86&lon=2.35&cnt=50&units=metric&APPID=" + WeatherAPIKey)
rawData = req.content.decode('utf-8')

places = json.loads(rawData)
print([(p['name'], p['main']['temp']) for p in places['list']])

request = requests.get("http://api.openweathermap.org/data/2.5/forecast?q=Paris,France&units=metric&APPID=" + WeatherAPIKey)
rawData = request.content.decode('utf-8')
forecast = json.loads(rawData)['list']
print(forecast)

import time
print(["day={}, weather={}, temp={}°C".format(time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(f['dt'])),
                                              f['weather'][0]['description'],
                                              f['main']['temp'])
       for f in forecast
      ])


def avg_temperature(lon_left, lat_bot, lon_right, lat_top, nb_loc) :
    req = requests.get(
        'http://api.openweathermap.org/data/2.5/box/city?bbox={},{},{},{},{}&unit=metric&APPID={}'.format(
            lon_left, lat_bot, lon_right, lat_top, nb_loc, WeatherAPIKey
        )
    )
    rawData = req.content.decode('utf-8')
    data = json.loads(rawData)
    locations = data['list']
    nbLoc = data['cnt']
    average_temp = 0
    for loc in locations :
        average_temp += loc['main']['temp']
    return average_temp / nbLoc

print(avg_temperature(-4.9,42.34,8.43,51.09, 9))

def get_country(loc_id) :
    req = requests.get(
        'http://api.openweathermap.org/data/2.5/weather?id={}&unit=metric&APPID={}'.format(
            loc_id, WeatherAPIKey
        )
    )
    rawData = req.content.decode('utf-8')
    data = json.loads(rawData)
    return data['sys']['country']

import time
def avg_temperature2(lon_left, lat_bot, lon_right, lat_top, nb_loc, country) :
    req = requests.get(
        'http://api.openweathermap.org/data/2.5/box/city?bbox={},{},{},{},{}&unit=metric&APPID={}'.format(
            lon_left, lat_bot, lon_right, lat_top, nb_loc, WeatherAPIKey
        )
    )
    rawData = req.content.decode('utf-8')
    data = json.loads(rawData)
    locations = data['list']
    nbLoc = 0
    average_temp = 0
    for loc in locations :
        print("Checking " + loc['name'] + "...")
        if country == get_country(loc['id']) :
            average_temp += loc['main']['temp']
            nbLoc += 1
        time.sleep(1)  # Wait 1s to avoid hitting the 60 requests per minute limit
    if nbLoc > 0
        return average_temp / nbLoc
    else:
        print("Error: no location in country, check your bounding box and country code.")
        return 0

print(avg_temperature2(-4.9,42.34,8.43,51.09, 9, 'FR'))