def test_get(self):
# Create and insert two stations and two sensors into sqlite
se1 = Sensor(reads='Humidity')
se2 = Sensor(reads='Temprature')
st1 = Station(name='Jx05t', active=True, sensors=[se1, se2])
st2 = Station(name='Zy99p', active=True)
db.session.add_all([st1, st2])
db.session.commit()
a1 = db.session.query(Station).filter_by(name=st1.name).one()
a2 = db.session.query(Station).filter_by(name=st2.name).one()
# Check if they are inserted
self.assertEqual(a1, st1)
self.assertEqual(a2, st2)
# Check the relationship Stations -< Sensor
self.assertEqual(a1.sensors.all(), [se1, se2])
self.assertEqual(a2.sensors.all(), [])
# Send an API request to get stations
response = self.app.test_client().get('/stations')
self.assertEqual(response.status_code, 200)
data = json.loads(response.get_data(as_text=True))
# Check if they exist in the response
item_ids_returned = [item["id"] for item in data['data']]
self.assertIn(a1.id, item_ids_returned)
self.assertIn(a2.id, item_ids_returned)
for item in data['data']:
if (item["id"] == a1.id):
self.assertEqual(item["name"], a1.name)
def test_delete(self):
# Create and insert stations and sensors into sqlite
se1 = Sensor(reads='Humidity')
se2 = Sensor(reads='Temprature')
st1 = Station(name='Jx05t', active=True, sensors=[se1, se2])
st2 = Station(name='Zy99p', active=True)
db.session.add_all([st1, st2])
db.session.commit()
a1 = db.session.query(Station).filter_by(name=st1.name).one()
a2 = db.session.query(Station).filter_by(name=st2.name).one()
# Check if correctly inserted
self.assertEqual(a1.name, st1.name)
self.assertEqual(a2.name, st2.name)
self.assertEqual(a1.sensors.count(), 2)
# Request delete through the API
response = self.app.test_client().delete('/stations/' + str(a1.id))
# Retrieve items through sqlite
a1 = db.session.query(Station).filter_by(name=st1.name).scalar()
a2 = db.session.query(Station).filter_by(name=st2.name).scalar()
ses = db.session.query(Sensor).count()
# Check if correctly deleted
self.assertEqual(a1, None)
self.assertNotEqual(a2, None)
# Check if deleting cascaddes to sensors correctly
self.assertEqual(ses, 0)
def handle(self, *args, **options):
file = 'moscow_bus_stations.csv'
with open(os.path.join(settings.BASE_DIR, file),
'r',
encoding='cp1251') as csvfile:
reader = csv.reader(csvfile, delimiter=';')
next(reader)
# count = 0
for line in reader:
# count += 1
station = Station()
station.name = line[1]
station.longitude = float(line[2])
station.latitude = float(line[3])
station.save()
for route in line[7].split('; '):
try:
new_route = Route.objects.get(name=route)
except Route.DoesNotExist:
new_route = Route.objects.create(name=route)
station.routes.add(new_route)
print(f'доабвлена станция {station.name}')
print(
f'latitude {station.latitude} : longitude {station.longitude}'
)
print(f'Всего доабавлено станций: {Station.objects.count()}')
print(f'Всего добоавлено маршрутов: {Route.objects.count()}')
def addstation():
form = StationForm()
validation_data = [
form.coor_lat_input.data, form.coor_lng_input.data,
form.kWh_price_input.data
]
if form.validate_on_submit():
if is_int(form.id.data) and is_float(
validation_data) and look_for_duplicates(
form.id.data) == False:
new_station = Station(id=form.id.data,
coor_lat=form.coor_lat_input.data,
coor_lng=form.coor_lng_input.data,
kWh_price=form.kWh_price_input.data)
db.session.add(new_station)
db.session.commit()
db.session.close()
flash('Station added succesfully.')
elif is_int(form.id.data) and look_for_duplicates(form.id.data):
flash(
f'There already is a station with id "{form.id.data}" in database.'
)
else:
flash('All inputs should be numbers.')
return redirect(url_for('stations.addstation'))
return render_template('addstation.html', form=form)
def updateListOfStation():
logger.debug("Open DB session")
s = crud.Session()
stations_from_tmp = s.query(TmpStation).all()
stations_from_main = s.query(Station).all()
logging.debug(
"Summary before update of Station table. Stations in tmp: {}, station in db: {}"
.format(len(stations_from_tmp), len(stations_from_main)))
add_row = True
for _tmp in stations_from_tmp:
add_row = True
for _main in stations_from_main:
if _tmp.stationName == _main.stationName: add_row = False
if add_row == True:
logging.warning("Adding station {}, id: {}".format(
_tmp.stationName, _tmp.id))
c1 = Station(id=_tmp.id, stationName=_tmp.stationName, gegrLat=_tmp.gegrLat, gegrLon=_tmp.gegrLon, cityId=_tmp.cityId, cityName=_tmp.cityName, \
communeName=_tmp.communeName, districtName=_tmp.districtName, provinceName=_tmp.provinceName, addressStreet=_tmp.addressStreet)
s.add(c1)
s.commit
logging.debug("Number of stations in main table: {}".format(
s.query(Station).count()))
logger.debug("Close DB session")
s.close()
def _station_new():
"""
Creates a station:
params:
name: station name to create
"""
if request.form:
data = request.form
else:
data = request.args
station_name = data.get('name', '', type=str).upper()
station = Station(name=station_name)
data = ''
try:
db.session.add(station)
db.session.commit()
# Alert the clients that we have a new alert
socketio.emit('unit_update', namespace='/afd')
return jsonify(result='success', \
message='Station ' + station_name + ' added.')
except exc.IntegrityError:
db.session.rollback()
return jsonify(result='error', \
message='Unable to add station ' + station_name + '.')
def station_save_to_db(item, station):
# Если записи в БД нет - создаем запись
item = Station(latitude=station['latitude'],
longitude=station['longitude'],
name=station['name'], )
item.save()
# Делаем привязку к routes
for route in station['routes']:
item.routes.add(Route.objects.filter(name=route).values('id')[0]['id'])
item.save()
def getStations():
if request.args.get('lat') == None or request.args.get(
'lng') == None or request.args.get('dst') == None:
abort(400)
# get query string params
lat = float(request.args.get('lat'))
lng = float(request.args.get('lng'))
dst = float(request.args.get('dst'))
places_id = list()
stations = list()
places_tree = ET.parse(
'/home/gasway/Gasway-WebService-Python-Flask-/app/static/places.xml')
places = places_tree.getroot()
for place in places:
data = place.find('location')
#for data in place.findall('location'):
x = float(data.find('x').text)
y = float(data.find('y').text)
formula = (6371 * acos(
cos(radians(y)) * cos(radians(lat)) *
cos(radians(lng) - radians(x)) +
sin(radians(y)) * sin(radians(lat))))
if (formula < dst):
s = Station()
s.place_id = place.get('place_id')
s.name = place.find('name').text
s.brand = place.find('brand').text
s.cre_id = place.find('cre_id').text
s.category = place.find('category').text
s.address = data.find('address_street').text
s.lat = y
s.lng = x
stations.append(s)
places_id.append(place.attrib)
prices_tree = ET.parse(
'/home/gasway/Gasway-WebService-Python-Flask-/app/static/prices.xml')
prices = prices_tree.getroot()
for price in prices:
if price.attrib in places_id:
s_i = places_id.index(price.attrib)
for p in price.findall('gas_price'):
po = Price()
po.type = p.get('type')
po.price = p.text
stations[s_i].prices.append(po)
response = app.response_class(response=jsonpickle.encode(
stations, unpicklable=False),
status=200,
mimetype='application/json')
return response
def create_station():
data = request.get_json() or {}
if 'name' not in data or 'active' not in data:
return bad_request('Missing fields -name -active')
if Station.query.filter_by(name=data['name']).first():
return bad_request('Station with that name already exists.')
station = Station()
station.unpack(data, new_station=True)
db.session.add(station)
db.session.commit()
response = jsonify(station.packin())
response.status_code = 201
response.headers['Location'] = url_for('get_station', id=station.id)
return response
def handle(self, *args, **options):
with open(CSV_FILENAME, 'rt') as csv_file:
csv_reader = csv.DictReader(csv_file, delimiter=';')
for row in csv_reader:
print(row['Latitude_WGS84'], row['Longitude_WGS84'], row['Name'])
print(row['RouteNumbers'].split(';'))
station = Station(
latitude=row['Latitude_WGS84'],
longitude=row['Longitude_WGS84'],
name=row['Name'],
)
station.save()
for route_name in row['RouteNumbers'].split(';'):
route, created = Route.objects.get_or_create(name=route_name)
station.routes.add(route)
def handle(self, *args, **options):
with open('moscow_bus_stations.csv', newline='',
encoding='cp1251') as csvfile:
reader = csv.DictReader(csvfile, delimiter=';')
for row in reader:
station = Station()
station.id = row['ID']
station.name = row['Name']
station.latitude = row['Latitude_WGS84']
station.longitude = row['Longitude_WGS84']
station.save()
for route in row['RouteNumbers'].split('; '):
obj, created = Route.objects.get_or_create(name=route)
station.routes.add(obj)
print('All done')
def add_station(station: str, country: str,key_db):
"""adds a new station
Parameters
----------
station : str
station name
country : [type]
[description]
key_db : [type]
[description]
"""
newstation = Station(station=station, country=country, key=key_db)
db.session.add(newstation)
db.session.commit()
db.session.close()
def handle(self, *args, **options):
with open('moscow_bus_stations.csv', 'r') as csvfile:
csv_reader = csv.DictReader(csvfile, delimiter=';')
for line in csv_reader:
station = Station()
station.latitude = line["Latitude_WGS84"]
station.longitude = line["Longitude_WGS84"]
station.name = line["Name"]
string = line["RouteNumbers"].split(";")
station.save()
for i in range(len(string)):
route = Route()
route.name = string[i].replace(" ", "")
route.save()
station.routes.add(route)
station.save()
del string
def handle(self, *args, **options):
with open(CSV_FILE, 'r', encoding=ENCODING) as csv_file:
reader = csv.DictReader(csv_file, delimiter=';')
for line in reader:
station_value = Station(
latitude=line['Latitude_WGS84'],
longitude=line['Longitude_WGS84'],
name=line['Name'],
)
station_value.save()
route_values = [
i.strip() for i in line['RouteNumbers'].split(';')
]
for value in route_values:
route = Route.objects.get_or_create(name=value)
station_value.routes.add(route[0])
station_value.save()
def setUp():
app.config.from_object(Config)
db.create_all()
# Possible sensor names
sensorref = ['Temprature', 'Humidity', 'WindSpeed', 'UV']
# Add 100 stations as example
for x in range(100):
# Create random number of random types of sensors
sensors = [
Sensor(reads=sample)
for sample in random.sample(sensorref, random.randint(0, 4))
]
# Random name assigned to station
st = Station(name=str(uuid.uuid1()), active=True, sensors=sensors)
db.session.add(st)
db.session.commit()
def test_update(self):
# Create and insert an item into sqlite
st2 = Station(name='Zy99p', active=True)
db.session.add_all([st2])
db.session.commit()
# Payload to update the item
station = {"name": "HGp78", "active": False}
station_payload = json.dumps(station)
a2 = db.session.query(Station).filter_by(name=st2.name).one()
# Request to update the item through the API
response = self.app.test_client().put(
'/stations/' + str(a2.id),
headers={"content-type": "application/json"},
data=station_payload)
# Retrieve the item through sqlite
a2 = db.session.query(Station).filter_by(id=a2.id).one()
# Check if correctly updated
self.assertEqual(a2.name, station["name"])
def handle(self, *args, **options):
with open('moscow_bus_stations.csv', newline='', encoding='cp1251') as csvfile:
stations_reader = csv.DictReader(csvfile, delimiter=';')
for row in tqdm(stations_reader):
station = Station()
station.name = row['Name']
station.longitude = row['Longitude_WGS84']
station.latitude = row['Latitude_WGS84']
station.save()
routes = [route for route in row['RouteNumbers'].split('; ')]
for route in routes:
object = Route.objects.filter(name=route).first()
if object:
station.routes.add(object)
else:
route = Route(name=route)
route.save()
station.routes.add(route)
station.save()
def handle(self, *args, **options):
with open('moscow_bus_stations.csv', 'r') as csvfile:
reader = csv.DictReader(csvfile, delimiter=';')
for line in reader:
station = Station(
latitude=line['Latitude_WGS84'],
longitude=line['Longitude_WGS84'],
name=line['Name']
)
station.save()
route_numbers = [i.strip() for i in line['RouteNumbers'].split(';')]
for route_number in route_numbers:
route = Route.objects.get_or_create(
name=route_number
)
station.routes.add(route[0])
station.save()
def loadSTATION():
"""Load Stations."""
url = "https://www.aviationweather.gov/adds/dataserver_current/httpparam?dataSource=stations&requestType=retrieve&format=xml"
content = urllib.request.urlopen(url).read()
root = ET.fromstring(content)
for station in root.iter('Station'):
if station.find('station_id') is None:
print("Skipping")
continue
stationId = station.find('station_id').text
latitude = station.find('latitude').text
longitude = station.find('longitude').text
elevation = station.find('elevation_m').text
site = station.find('site').text
if station.find('wmo_id') is not None:
wmo_id = station.find('wmo_id').text
else:
wmo_id = None
if station.find('state') is not None:
state = station.find('state').text
else:
state = None
country = station.find('country').text
if station.find('site_type') is None:
metar = None
rawinsonde = None
taf = None
nexrad = None
wind_profiler = None
wfo_office = None
synops = None
else:
for site_type in station.iter('site_type'):
METAR = site_type.find('METAR')
TAF = site_type.find('TAF')
raw = site_type.find('rawinsonde')
NEXRAD = site_type.find('NEXRAD')
wind = site_type.find('wind_profiler')
wfo = site_type.find('WFO_office')
syn = site_type.find('SYNOPS')
if METAR is not None:
METAR = True
else:
METAR = False
if TAF is not None:
TAF = True
else:
TAF = False
if raw is not None:
raw = True
else:
raw = False
if NEXRAD is not None:
NEXRAD = True
else:
NEXRAD = False
if wind is not None:
wind = True
else:
wind = False
if wfo is not None:
wfo = True
else:
wfo = False
if syn is not None:
syn = True
else:
syn = False
stationDB = Station.query.filter_by(station_id=stationId).first()
if stationDB is None:
stationDB = Station(station_id=stationId, wmo_id=wmo_id, latitude=latitude, longitude=longitude, elevation_m=elevation, site=site, state=state, country=country, metar=METAR, taf=TAF, rawinsonde=raw, nexrad=NEXRAD, wind_profiler=wind, wfo_office=wfo, synops=syn)
db.session.add(stationDB)
db.session.commit()
elif stationDB.station_id==stationId and (stationDB.wmo_id!=wmo_id or stationDB.latitude!=latitude or stationDB.longitude!=longitude or stationDB.elevation_m!=elevation or stationDB.site!=site or stationDB.state!=state or stationDB.country!=country or stationDB.metar!=METAR or stationDB.taf!=TAF or stationDB.rawinsonde!=raw or stationDB.nexrad!=NEXRAD or stationDB.wind_profiler!=wind or stationDB.wfo_office!=wfo or stationDB.synops!=syn):
print(stationDB.station_id)
stationDB.wmo_id = wmo_id
stationDB.latitude = latitude
stationDB.longitude = longitude
stationDB.elevation_m = elevation
stationDB.site = site
stationDB.state = state
stationDB.country = country
stationDB.metar = METAR
stationDB.taf = TAF
stationDB.synops = syn
stationDB.wfo_office = wfo
stationDB.wind_profiler = wind
stationDB.nexrad = NEXRAD
stationDB.rawinsonde = raw
db.session.commit()
import csv
import os
import django
os.environ.setdefault("DJANGO_SETTINGS_MODULE", "project.settings")
django.setup()
from app.models import Route, Station
with open('moscow_bus_stations.csv', newline='', encoding='cp1251') as csvfile:
reader = csv.DictReader(csvfile, delimiter=';')
for row in reader:
station = Station()
station.id = row['ID']
station.name = row['Name']
station.latitude = row['Latitude_WGS84']
station.longitude = row['Longitude_WGS84']
station.save()
for route in row['RouteNumbers'].split('; '):
obj, created = Route.objects.get_or_create(name=route)
station.routes.add(obj)
print('All done')
def init_stations():
station_data = get_ubike_json()
for key in station_data["retVal"].keys():
station = Station(station_no=key)
db.session.add(station)
db.session.commit()
db.session.add(new_row)
######################
df = m_df_sing
for index, row in df.iterrows():
x = [str(row[column]) for column in df]
new_row = Michelin(name=x[0],
year=x[1],
latitude=x[2],
longitude=x[3],
city=x[4],
cuisine=x[5],
price=x[6],
url=x[7],
star=x[8])
db.session.add(new_row)
#######################
df = train_stat_df
for index, row in df.iterrows():
x = [str(row[column]) for column in df]
new_row = Station(station_name=x[0],
_type=x[1],
latitude=x[2],
longitude=x[3])
db.session.add(new_row)
db.session.commit()