def data2db(file_name, response):
last_modified = response.getheader("last-modified")
bytes_data = response.read()
wb = load_workbook(filename=BytesIO(bytes_data))
ws = wb[wb.sheetnames[0]]
# lga_name = " ".join(ws.cell(row=5, column=1).value.split()[:-3])
number_of_year = (ws.max_column - 5) // 2
years = []
if ws.cell(row=1, column=3).value is None:
t = 0
for i in range(number_of_year):
str1 = ws.cell(row=6, column=3 + i * 2).value
year = ''.join(list(filter(str.isdigit, str1)))
rate = get_column(ws, 4 + i * 2)
s = None
for x in rate:
if x != 'nc':
if s is None:
s = float(x)
else:
s += float(x)
if s is not None:
total_rate = s
else:
total_rate = 'Null'
number_of_year -= 1
if total_rate != 'Null':
t += total_rate
years.append(Year(year=str(year),crime_rate=str(total_rate)))
if number_of_year != 0:
average = t / number_of_year
else:
average = 'Null'
else:
t = 0
for i in range(number_of_year):
end_year = int(ws.cell(row=1, column=3).value)
year = str(end_year - 4 + i)
rate = get_column(ws, 4 + i * 2)
s = None
for x in rate:
if x != 'nc':
if s is None:
s = float(x)
else:
s += float(x)
if s is not None:
total_rate = s
else:
total_rate = 'Null'
number_of_year -= 1
if total_rate != 'Null':
t += total_rate
years.append(Year(year=str(year), crime_rate=str(total_rate)))
if number_of_year != 0:
average = t / number_of_year
else:
average = 'Null'
crime_data = LGA(file_name, years, str(average), last_modified)
crime_data.save()
def years(request):
verses = Verse.category().instance.all()
authors = Author.category().instance.all()
years = Year.category().instance.all()
if request.method == 'GET':
years = Year.category().instance.all()
return render(request, 'jinja2/years.html', {'verses': verses,
'authors': authors,
'years': years})
elif request.method == 'POST':
if request.POST.get('year') is not None:
Year(year=unicode(request.POST['year'])).save()
years = Year.category().instance.all()
return render(request, 'jinja2/years.html', {'verses': verses,
'authors': authors,
'years': years})
def entity_browser_committee_part(self, *args):
entity_key = args[0]
entity = db.get(entity_key)
committee_result = Committee.gql("where entiti=:entity_key",
entity_key=entity.key())
param = self.render_parameters()
committee = None
committee_list = []
for cmte in committee_result:
if cmte.year.is_current:
committee = cmte
param["president"] = committee.president()
else:
committee_list.append(cmte)
param["committee_list"] = committee_list
param["committee"] = committee
param["entity"] = entity
year = Year.all()
if committee:
year.filter("__key__ !=", committee.year.key())
for cmte in committee_list:
year.filter("__key__ !=", cmte.year.key())
if year.count > 0:
year_list = []
for y in year:
year_list.append(y)
param["year_list"] = year_list
#Temporary
return self.render("entity_browser_committee_part", param)
def test_dictify_year(self):
expected = {
'year': "2000",
'top_album_name': 'Top Album',
'top_album_id': 'tgvjs',
'top_genre_name': 'Top Genre',
'top_album_artist_id': 'sadsd',
'top_songs': ['Top Song 1', 'Top Song 2']
}
year = Year(year="2000",
top_album_name='Top Album',
top_album_id='tgvjs',
top_genre_name='Top Genre',
top_album_artist_id='sadsd')
song1 = Song(song_id='abcd', song_name='Top Song 1')
assoc_s1 = YearsSongsAssociation(year_num="2000",
rank=2,
song_id="abcd")
assoc_s1.song = song1
song2 = Song(song_id='efgh', song_name='Top Song 2')
assoc_s2 = YearsSongsAssociation(year_num="2000",
rank=1,
song_id="efgh")
assoc_s2.song = song2
year.top_songs.append(assoc_s1)
year.top_songs.append(assoc_s2)
self.session.add_all([year, song1, song2, assoc_s1, assoc_s2])
self.session.commit()
returned_year = self.session.query(Year) \
.filter_by(year="2000").first()
actual_dict = returned_year.dictify()
self.assertEqual(actual_dict['year'], expected['year'])
self.assertEqual(actual_dict['top_album_name'],
expected['top_album_name'])
self.assertEqual(actual_dict['top_album_id'], expected['top_album_id'])
self.assertEqual(actual_dict['top_genre_name'],
expected['top_genre_name'])
self.assertEqual(actual_dict['top_album_artist_id'],
expected['top_album_artist_id'])
self.assertTrue('Top Song 1' in actual_dict['top_songs'])
self.assertTrue('Top Song 2' in actual_dict['top_songs'])
def names(request):
verses = Verse.category().instance.all()
authors = Author.category().instance.all()
years = Year.category().instance.all()
auth_names = [author.name for author in Author.category().instance.all()]
year_list = [year.year for year in Year.category().instance.all()]
if request.method == 'GET':
return render(request, 'jinja2/names.html', {'verses': verses,
'authors': authors,
'years': years})
elif request.method == 'POST':
if request.POST.get('name') and request.POST.get('text'):
verse = Verse(
name=unicode(request.POST['name']), text=unicode(request.POST['text'])).save()
if request.POST.get('author'):
if unicode(request.POST.get('author')) in auth_names:
verse.author.connect(
Author.index.get(name=request.POST['author']))
else:
Author(name=unicode(request.POST['author'])).save()
verse.author.connect(
Author.index.get(name=unicode(request.POST['author'])))
if request.POST.get('year'):
if int(request.POST.get('year')) in year_list:
verse.year.connect(
Year.index.get(year=int(request.POST['year'])))
else:
Year(year=request.POST['year']).save()
verse.year.connect(
Year.index.get(year=int(request.POST['year'])))
verses = Verse.category().instance.all()
authors = Author.category().instance.all()
years = Year.category().instance.all()
return render(request, 'jinja2/names.html', {'verses': verses,
'authors': authors,
'years': years})
def authors(request):
verses = Verse.category().instance.all()
authors = Author.category().instance.all()
years = Year.category().instance.all()
if request.method == 'GET':
return render(request, 'jinja2/index.html', {'verses': verses,
'authors': authors,
'years': years})
elif request.method == 'POST':
if request.POST.get('author_name'):
Author(name=unicode(request.POST['author_name'])).save()
authors = Author.category().instance.all()
return render(request, 'jinja2/index.html', {'verses': verses,
'authors': authors,
'years': years})
def test_genre_1(self):
name = "The Genre"
description = "Some description of the genre."
test_genre = Genre(name=name, description=description)
artist_1 = Artist(artist_id="FirstArtist", name="The First Artist")
test_genre.artists.append(artist_1)
year_1 = Year(year="2015")
test_genre.years_on_top.append(year_1)
genre_1 = Genre(name="Related 1", description="Related 1 description")
test_genre.related_genres.append(genre_1)
self.session.add_all([artist_1, year_1, genre_1, test_genre])
self.session.commit()
self.assertTrue(test_genre in self.session)
actual_genre = self.session.query(Genre).filter_by(
name="The Genre").first()
genre_artists = actual_genre.artists
self.assertEqual(len(genre_artists), 1)
self.assertTrue(artist_1 in genre_artists)
actual_artist = self.session.query(Artist).filter_by(
name="The First Artist").first()
artist_genres = actual_artist.genres
self.assertEqual(len(artist_genres), 1)
self.assertTrue(test_genre in artist_genres)
genre_years = actual_genre.years_on_top
self.assertEqual(len(genre_years), 1)
self.assertTrue(year_1 in genre_years)
actual_year = self.session.query(Year).filter_by(year="2015").first()
year_genre = actual_year.top_genre
self.assertEqual(test_genre, year_genre)
genre_relatedgenres = test_genre.related_genres
self.assertEqual(len(genre_relatedgenres), 1)
self.assertTrue(genre_1, genre_relatedgenres)
self.assertTrue(test_genre in self.session)
actual_genre = self.session.query(Genre).filter_by(
name="The Genre").first()
actual_name = actual_genre.name
self.assertEqual(actual_name, name)
def test_year_1(self):
year = "2001"
top_album_name = "Some Album"
top_album_id = "1234"
top_genre_name = "Top Genre Name"
top_album_artist_id = "artist123"
top_genre = Genre(name="Top Genre Name", description="Generic genre.")
self.session.add(top_genre)
self.session.commit()
test_year = Year(year=year,
top_album_name=top_album_name,
top_album_id=top_album_id,
top_genre_name=top_genre_name,
top_album_artist_id=top_album_artist_id)
top_song = Song(song_id="same_id", song_name="Some Song")
top_songb = Song(song_id="other_id", song_name="Other Song")
test_assoc = YearsSongsAssociation(rank=1)
test_assoc.song = top_song
test_year.top_songs.append(test_assoc)
test_assocb = YearsSongsAssociation(rank=85)
test_assocb.song = top_songb
test_year.top_songs.append(test_assocb)
self.session.add(top_song)
self.session.add(top_songb)
self.session.commit()
self.session.add(test_year)
self.session.commit()
self.assertTrue(test_year in self.session)
actual_year = self.session.query(Year).filter_by(year="2001").first()
actual_top_genre = actual_year.top_genre
self.assertEqual("Top Genre Name", actual_top_genre.name)
self.assertEqual("Generic genre.", actual_top_genre.description)
self.assertEqual(test_year, actual_top_genre.years_on_top[0])
all_songs = actual_year.top_songs
self.assertEqual(len(all_songs), 2)
def test_song_1(self):
song_id = "99999"
song_name = "A Song"
artist_name = "Some Artist"
artist_id = "1234"
album_name = "Some Album"
explicit = True
popularity = 20
test_song = Song(song_id=song_id,
song_name=song_name,
artist_name=artist_name,
artist_id=artist_id,
album_name=album_name,
explicit=explicit,
popularity=popularity)
year_charted_1 = Year(year="1990")
assoc_1 = YearsSongsAssociation(year_num="1990",
rank=20,
song_id=song_id)
assoc_1.song = test_song
assoc_1.year = year_charted_1
year_charted_1.top_songs.append(assoc_1)
test_song.years_charted.append(assoc_1)
self.assertEqual(assoc_1.song_id, test_song.song_id)
self.session.add_all([year_charted_1, assoc_1, test_song])
self.session.commit()
self.assertTrue(test_song in self.session)
this_song = self.session.query(Song).filter_by(song_id="99999").first()
self.assertTrue(this_song.explicit)
self.assertEqual(this_song.years_charted[0].year.year, "1990")
self.assertEqual(year_charted_1.top_songs[0].song.song_name, "A Song")
def create_year(universe, year):
y = Year(universe=universe,
year=year)
y.save()
return y
def test_dictify_song(self):
expected = {
'song_id': 'abcd',
'song_name': 'A Song Name',
'artist_name': 'Artist Name',
'artist_id': 'efgh',
'album_name': 'The Album Name',
'explicit': True,
'popularity': 20,
'years_charted': ["1990", "1991", "1992", "2015"]
}
some_song = Song(song_id="abcd",
song_name="A Song Name",
artist_name="Artist Name",
artist_id="efgh",
album_name="The Album Name",
explicit=True,
popularity=20)
artist = Artist(name="Artist Name", artist_id="efgh")
year1 = Year(year="1990")
year2 = Year(year="1991")
year3 = Year(year="1992")
year4 = Year(year="2015")
assoc_y1 = YearsSongsAssociation(year_num="1990",
rank=1,
song_id="abcd")
assoc_y1.year = year1
assoc_y2 = YearsSongsAssociation(year_num="1991",
rank=1,
song_id="abcd")
assoc_y2.year = year2
assoc_y3 = YearsSongsAssociation(year_num="1992",
rank=1,
song_id="abcd")
assoc_y3.year = year3
assoc_y4 = YearsSongsAssociation(year_num="2015",
rank=1,
song_id="abcd")
assoc_y4.year = year4
some_song.years_charted.append(assoc_y1)
some_song.years_charted.append(assoc_y2)
some_song.years_charted.append(assoc_y3)
some_song.years_charted.append(assoc_y4)
self.session.add_all([
some_song, artist, year1, year2, year3, year4, assoc_y1, assoc_y2,
assoc_y3, assoc_y4
])
self.session.commit()
returned_song = self.session.query(Song) \
.filter_by(song_name="A Song Name").first()
actual_dict = returned_song.dictify()
self.assertEqual(actual_dict['song_id'], expected['song_id'])
self.assertEqual(actual_dict['song_name'], expected['song_name'])
self.assertEqual(actual_dict['artist_name'], expected['artist_name'])
self.assertEqual(actual_dict['artist_id'], expected['artist_id'])
self.assertEqual(actual_dict['album_name'], expected['album_name'])
self.assertEqual(actual_dict['explicit'], expected['explicit'])
self.assertEqual(actual_dict['popularity'], expected['popularity'])
self.assertTrue("1990" in actual_dict['years_charted'])
self.assertTrue("1991" in actual_dict['years_charted'])
self.assertTrue("1992" in actual_dict['years_charted'])
self.assertTrue("2015" in actual_dict['years_charted'])
def test_year_3(self):
year = "2015"
top_album_name = "The Biggest Album of 2015"
top_album_id = "2193810"
top_genre_name = "Rap"
top_album_artist_id = "TopAlbumArtist2015"
top_genre = Genre(name="Rap", description="Rap music.")
self.session.add(top_genre)
self.session.commit()
test_year = Year(year=year,
top_album_name=top_album_name,
top_album_id=top_album_id,
top_genre_name=top_genre_name,
top_album_artist_id=top_album_artist_id)
top_song = Song(song_id="2015A", song_name="The Biggest Song of 2015")
top_songb = Song(song_id="2015B",
song_name="The Second Biggest Song of 2015")
test_assoc = YearsSongsAssociation(rank=1)
test_assoc.song = top_song
test_year.top_songs.append(test_assoc)
test_assocb = YearsSongsAssociation(rank=2)
test_assocb.song = top_songb
test_year.top_songs.append(test_assocb)
self.session.add(top_song)
self.session.add(top_songb)
self.session.commit()
self.session.add(test_year)
self.session.commit()
year_2 = "2014"
top_album_name_2 = "The Biggest Album of 2014"
top_album_id_2 = "999999"
top_genre_name_2 = "Rap"
top_album_artist_id_2 = "TopAlbumArtist2014"
test_year_2 = Year(year=year_2,
top_album_name=top_album_name_2,
top_album_id=top_album_id_2,
top_genre_name=top_genre_name_2,
top_album_artist_id=top_album_artist_id_2)
top_song_2 = Song(song_id="2014A",
song_name="The Biggest Song of 2014")
top_song_2b = Song(song_id="2014B",
song_name="The Second Biggest Song of 2014")
test_assoc_2 = YearsSongsAssociation(rank=1)
test_assoc_2.song = top_song_2
test_year_2.top_songs.append(test_assoc_2)
test_assoc_2b = YearsSongsAssociation(rank=2)
test_assoc_2b.song = top_song_2b
test_year_2.top_songs.append(test_assoc_2b)
self.session.add(top_song_2)
self.session.add(top_song_2b)
self.session.commit()
self.session.add(test_year_2)
self.session.commit()
year_3 = "2013"
top_album_name_3 = "The Biggest Album of 2013"
top_album_id_3 = "3333333333"
top_genre_name_3 = "Not Rap"
top_album_artist_id_3 = "TopAlbumArtist2013"
top_genre = Genre(name="Not Rap", description="Not rap music.")
self.session.add(top_genre)
self.session.commit()
test_year_3 = Year(year=year_3,
top_album_name=top_album_name_3,
top_album_id=top_album_id_3,
top_genre_name=top_genre_name_3,
top_album_artist_id=top_album_artist_id_3)
top_song_3 = Song(song_id="2013A",
song_name="The Biggest Song of 2013")
top_song_3b = Song(song_id="2013B",
song_name="The Second Biggest Song of 2013")
test_assoc_3 = YearsSongsAssociation(rank=1)
test_assoc_3.song = top_song_3
test_year_3.top_songs.append(test_assoc_3)
test_assoc_3b = YearsSongsAssociation(rank=2)
test_assoc_3b.song = top_song_2b
test_year_3.top_songs.append(test_assoc_3b)
self.session.add(top_song_3)
self.session.add(top_song_3b)
self.session.commit()
self.session.add(test_year_3)
self.session.commit()
years_list = self.session.query(Year).filter_by(
top_genre_name="Rap").all()
self.assertTrue(len(years_list) == 2)
self.assertTrue(test_year in years_list and test_year_2 in years_list
and test_year_3 not in years_list)
not_rap_list = self.session.query(Year).filter_by(
top_genre_name="Not Rap").all()
self.assertTrue(len(not_rap_list) == 1)
self.assertTrue(test_year not in not_rap_list
and test_year_2 not in not_rap_list
and test_year_3 in not_rap_list)
self.assertEqual(not_rap_list[0], test_year_3)
self.assertEqual(not_rap_list[0].year, "2013")
def test_year_2(self):
year = "2015"
top_album_name = "The Biggest Album of 2015"
top_album_id = "2193810"
top_genre_name = "Pop"
top_album_artist_id = "TopAlbumArtist2015"
top_genre = Genre(name="Pop", description="Pop music.")
self.session.add(top_genre)
self.session.commit()
test_year = Year(year=year,
top_album_name=top_album_name,
top_album_id=top_album_id,
top_genre_name=top_genre_name,
top_album_artist_id=top_album_artist_id)
top_song = Song(song_id="2015A", song_name="The Biggest Song of 2015")
top_songb = Song(song_id="2015B",
song_name="The Second Biggest Song of 2015")
test_assoc = YearsSongsAssociation(rank=1)
test_assoc.song = top_song
test_year.top_songs.append(test_assoc)
test_assocb = YearsSongsAssociation(rank=2)
test_assocb.song = top_songb
test_year.top_songs.append(test_assocb)
self.session.add(top_song)
self.session.add(top_songb)
self.session.commit()
self.session.add(test_year)
self.session.commit()
year_2 = "2014"
top_album_name_2 = "The Biggest Album of 2014"
top_album_id_2 = "999999"
top_genre_name_2 = "Pop"
top_album_artist_id_2 = "TopAlbumArtist2014"
test_year_2 = Year(year=year_2,
top_album_name=top_album_name_2,
top_album_id=top_album_id_2,
top_genre_name=top_genre_name_2,
top_album_artist_id=top_album_artist_id_2)
top_song_2 = Song(song_id="2014A",
song_name="The Biggest Song of 2014")
top_song_2b = Song(song_id="2014B",
song_name="The Second Biggest Song of 2014")
test_assoc_2 = YearsSongsAssociation(rank=1)
test_assoc_2.song = top_song_2
test_year_2.top_songs.append(test_assoc_2)
test_assoc_2b = YearsSongsAssociation(rank=2)
test_assoc_2b.song = top_song_2b
test_year_2.top_songs.append(test_assoc_2b)
self.session.add(top_song_2)
self.session.add(top_song_2b)
self.session.commit()
self.session.add(test_year_2)
self.session.commit()
fifteen = self.session.query(Year).filter_by(year="2015").first()
fourteen = self.session.query(Year).filter_by(year="2014").first()
self.assertTrue(fifteen is not None)
self.assertTrue(fourteen is not None)
fifteen_top_album = fifteen.top_album_name
fourteen_top_album = fourteen.top_album_name
fifteen_top_album_artist = fifteen.top_album_artist_id
fourteen_top_album_artist = fourteen.top_album_artist_id
correct_albums = fifteen_top_album == "The Biggest Album of 2015" and \
fourteen_top_album == "The Biggest Album of 2014"
self.assertTrue(correct_albums)
correct_album_artists = fifteen_top_album_artist == \
"TopAlbumArtist2015" and \
fourteen_top_album_artist == \
"TopAlbumArtist2014"
self.assertTrue(correct_album_artists)