-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathdrive_sheet.py
232 lines (204 loc) · 9.41 KB
/
drive_sheet.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
__author__ = 'jpatdalton'
import columns
from my_models import Artist, Track, Current_Spreadsheet
from sqlalchemy.sql import func
from datetime import datetime
import oauth
import db_setup
def update_hourly():
try:
session = db_setup.get_session()
worksheet = oauth.open_spreadsheet()
end = str(int(session.query(func.max(Current_Spreadsheet.indice)).all()[0][0]) + 2)
track_ids = session.query(Current_Spreadsheet).all()
col_youtube = columns.youtube
col_popularity = columns.spotify_popularity
cell_list_popularity = worksheet.range(col_popularity+'2:'+col_popularity+end)
cell_list_views = worksheet.range(col_youtube+'2:'+col_youtube+end)
col = columns.shazams
cell_list_zams = worksheet.range(col+'2:'+col+end)
col = columns.shazam_chart_pos
cell_list_chart_pos = worksheet.range(col+'2:'+col+end)
for track_id in track_ids:
track = session.query(Track).get(track_id.id)
i = track_id.indice
cell_list_views[i].value = track.youtube_views
cell_list_popularity[i].value = track.spotify_popularity
cell_list_chart_pos[i].value = track.shazam_chart_pos
cell_list_zams[i].value = track.shazams
worksheet.update_cells(cell_list_popularity)
worksheet.update_cells(cell_list_views)
worksheet.update_cells(cell_list_zams)
worksheet.update_cells(cell_list_chart_pos)
except Exception, e:
print e, 'error in update worksheet hourly'
session.close()
finally:
session.close()
def update_artists():
try:
session = db_setup.get_session()
worksheet = oauth.open_spreadsheet()
end = str(int(session.query(func.max(Current_Spreadsheet.indice)).all()[0][0]) + 7)
col = columns.artists
cell_list_artists = worksheet.range(col+'2:'+col+str(end))
col = columns.fb_likes
cell_list_fb_likes = worksheet.range(col+'2:'+col+end)
col = columns.instagram
cell_list_insta_likes = worksheet.range(col+'2:'+col+end)
col = columns.vine
cell_list_vine_likes = worksheet.range(col+'2:'+col+end)
col = columns.soundcloud
cell_list_sc_likes = worksheet.range(col+'2:'+col+end)
col = columns.twitter
cell_list_twitter_likes = worksheet.range(col+'2:' + col+end)
n=0
track_ids = session.query(Current_Spreadsheet).all()
for track_id in track_ids:
track = session.query(Track).get(track_id.id)
#i = track_id.indice
for artist in track.artists:
cell_list_artists[n].value = artist.name
cell_list_fb_likes[n].value = artist.fb_likes
cell_list_insta_likes[n].value = artist.instagram
cell_list_vine_likes[n].value = artist.vine
cell_list_sc_likes[n].value = artist.soundcloud
cell_list_twitter_likes[n].value = artist.twitter
n+=1
worksheet.update_cells(cell_list_artists)
worksheet.update_cells(cell_list_fb_likes)
worksheet.update_cells(cell_list_insta_likes)
worksheet.update_cells(cell_list_vine_likes)
worksheet.update_cells(cell_list_sc_likes)
worksheet.update_cells(cell_list_twitter_likes)
except Exception, e:
print e, 'error in update worksheet artists'
session.close()
finally:
session.close()
def update_weekly():
session = db_setup.get_session()
try:
worksheet = oauth.open_spreadsheet()
end = int(session.query(func.max(Current_Spreadsheet.indice)).all()[0][0]) + 2
col = columns.song_title
cell_list_titles = worksheet.range(col+'2:'+col+str(end))
col = columns.chart_position
cell_list_nums = worksheet.range(col+'2:'+col+str(end))
col = columns.chart_movement
cell_list_chart_movement = worksheet.range(col+'2:'+col+str(end))
col_writers = columns.writers
col_producers = columns.producers
col_label = columns.label
cell_list_writers = worksheet.range(col_writers+'2:'+col_writers+str(end))
cell_list_producers = worksheet.range(col_producers+'2:'+col_producers+str(end))
cell_list_labels = worksheet.range(col_label+'2:'+col_label+str(end))
n=0
track_ids = session.query(Current_Spreadsheet).all()
for track_id in track_ids:
track = session.query(Track).get(track_id.id)
i = track_id.indice
cell_list_titles[i].value = track.title
cell_list_chart_movement[i].value = track.chart_movement
cell_list_nums[i].value = n+1
cell_list_writers[i].value = track.writers
cell_list_producers[i].value = track.producers
cell_list_labels[i].value = track.label
n+=1
# Update in batch
worksheet.update_cells(cell_list_nums)
worksheet.update_cells(cell_list_titles)
worksheet.update_cells(cell_list_chart_movement)
worksheet.update_cells(cell_list_writers)
worksheet.update_cells(cell_list_producers)
worksheet.update_cells(cell_list_labels)
except Exception, e:
print e, 'error in update worksheet weekly'
session.close()
finally:
session.close()
def update_spotify():
session = db_setup.get_session()
try:
worksheet = oauth.open_spreadsheet()
today = datetime.today()
end = str(int(session.query(func.max(Current_Spreadsheet.indice)).all()[0][0]) + 2)
track_ids = session.query(Current_Spreadsheet).all()
col = columns.spotify_streams
cell_list_spotify_streams = worksheet.range(col+'2:'+col+end)
n = 0
for track_id in track_ids:
track = session.query(Track).get(track_id.id)
i = track_id.indice
cell_list_spotify_streams[i].value = track.spotify_streams
n+=1
worksheet.update_cells(cell_list_spotify_streams)
except Exception, e:
print e, 'error in update worksheet spoitfy'
finally:
session.close()
def update_daily():
session = db_setup.get_session()
try:
worksheet = oauth.open_spreadsheet()
today = datetime.today()
end = str(int(session.query(func.max(Current_Spreadsheet.indice)).all()[0][0]) + 2)
col = columns.spins
col_audience = columns.audience
col_spins_pos = columns.spins_pos
col_spins_days = columns.spins_days
col_spins_pop_pos = columns.spins_pop_pos
col_spins_pop = columns.spins_pop
col_spins_rhythmic = columns.spins_rhythmic
col_spins_urban = columns.spins_urban
col_spins_itunes = columns.itunes_chart_pos
cell_list_spins = worksheet.range(col+'2:'+col+end)
cell_list_aud = worksheet.range(col_audience+'2:'+col_audience+end)
cell_list_spins_pos = worksheet.range(col_spins_pos+'2:'+col_spins_pos+end)
cell_list_spins_days = worksheet.range(col_spins_days+'2:'+col_spins_days+end)
cell_list_spins_pop_pos = worksheet.range(col_spins_pop_pos+'2:'+col_spins_pop_pos+end)
cell_list_pop = worksheet.range(col_spins_pop+'2:'+col_spins_pop+end)
cell_list_rhythmic = worksheet.range(col_spins_rhythmic+'2:'+col_spins_rhythmic+end)
cell_list_urban = worksheet.range(col_spins_urban+'2:'+col_spins_urban+end)
cell_list_itunes = worksheet.range(col_spins_itunes+'2:'+col_spins_itunes+end)
col_spins_lw = columns.spins_last_week
col_spins_diff = columns.spins_diff
cell_list_spins_lw = worksheet.range(col_spins_lw+'2:'+col_spins_lw+end)
cell_list_spins_diff = worksheet.range(col_spins_diff+'2:'+col_spins_diff+end)
col = columns.release_date
cell_list_dates = worksheet.range(col+'2:'+col+end)
n=0
track_ids = session.query(Current_Spreadsheet).all()
for track_id in track_ids:
track = session.query(Track).get(track_id.id)
i = track_id.indice
cell_list_spins[i].value = track.spins
cell_list_aud[i].value = track.radio_audience
cell_list_spins_pos[i].value = track.radio_position
cell_list_spins_days[i].value = track.days_from_release
cell_list_spins_pop_pos[i].value = track.spins_pop_pos
cell_list_pop[i].value = track.spins_pop
cell_list_rhythmic[i].value = track.spins_rhythmic
cell_list_urban[i].value = track.spins_urban
cell_list_itunes[i].value = track.itunes_chart_pos
cell_list_spins_lw[i].value = track.spins_lw
cell_list_spins_diff[i].value = track.spins_diff
cell_list_dates[i].value = (today - track.itunes_release_date).days
n+=1
# Update in batch
worksheet.update_cells(cell_list_spins)
worksheet.update_cells(cell_list_aud)
worksheet.update_cells(cell_list_spins_pos)
worksheet.update_cells(cell_list_spins_days)
worksheet.update_cells(cell_list_spins_diff)
worksheet.update_cells(cell_list_spins_lw)
worksheet.update_cells(cell_list_spins_pop_pos)
worksheet.update_cells(cell_list_pop)
worksheet.update_cells(cell_list_rhythmic)
worksheet.update_cells(cell_list_urban)
worksheet.update_cells(cell_list_itunes)
worksheet.update_cells(cell_list_dates)
except Exception, e:
print e, 'error in update worksheet daily'
finally:
session.close()