def nname(self):
if self._nname is not None:
return self._nname
session = Session()
ytchannel = session.query(YTChannelSA).filter(YTChannelSA.ytchannelid == self.ytchannelid).first()
if ytchannel:
self._nname = ytchannel.nname
session.close()
if self._nname is None:
return 'w/inf'
return self._nname
def write_item_to_db(self):
session = Session()
videoitem = session.\
query(YTVideoItemInfoSA).\
filter(YTVideoItemInfoSA.ytvideoid == self.ytvideoid).\
first()
if videoitem:
bool_ret = self.update_videoitem(videoitem, session)
else:
bool_ret = self.insert_videoitem(session)
session.close()
return bool_ret
def get_days_n_subscribers_within_desc_up_to_n_rows(
self, n_of_date_desc_rows=20):
session = Session()
subs = session.\
query(YTDailySubscribersSA).\
filter(YTDailySubscribersSA.ytchannelid == self.ytchannelid). \
order_by(desc(YTDailySubscribersSA.infodate)). \
limit(n_of_date_desc_rows)
days_n_subscribers_tuplelist = []
for sub in subs:
days_n_subscribers_tuplelist.append(
(sub.infodate, sub.subscribers))
session.close()
return days_n_subscribers_tuplelist
def db_insert_or_update_if_needed(deputados):
session = Session()
n_inserts = 0
n_inserts_or_updates = 0
for i, deput_excel in enumerate(deputados):
seq = i + 1
nomeparlamentar = deput_excel.nomeparlamentar
uf = deput_excel.uf
partido = deput_excel.partido
do_commit = False
deput_sa = session.query(SADeput) \
.filter(
SADeput.nomeparlamentar == nomeparlamentar, SADeput.uf == uf, SADeput.partido == partido
) \
.first()
if deput_sa:
print(seq, 'exists deput_sa (updating if needed)', deput_sa)
else:
deput_sa = SADeput()
do_commit = True
print(seq, 'does not exist deput_sa (inserting)', deput_sa)
deput_sa.name = deput_excel.nomeparlamentar
deput_sa.uf = deput_excel.uf
deput_sa.partido = deput_excel.partido
n_inserts += 1
session.add(deput_sa)
if deput_sa.nomeparlamentar != deput_excel.nomeparlamentar:
deput_sa.nomeparlamentar = deput_excel.nomeparlamentar
do_commit = True
if deput_sa.nomecivil != deput_excel.nomecivil:
deput_sa.nomecivil = deput_excel.nomecivil
do_commit = True
if deput_sa.fixphone != deput_excel.fixphone:
deput_sa.fixphone = deput_excel.fixphone
do_commit = True
if deput_sa.titular_outro != deput_excel.titular_outro:
deput_sa.titular_outro = deput_excel.titular_outro
do_commit = True
if deput_sa.ngabinete != deput_excel.ngabinete:
deput_sa.ngabinete = deput_excel.ngabinete
do_commit = True
if deput_sa.anexopredio != deput_excel.anexopredio:
deput_sa.anexopredio = deput_excel.anexopredio
do_commit = True
if deput_sa.emlmidstr != deput_excel.emlmidstr:
deput_sa.emlmidstr = deput_excel.emlmidstr
do_commit = True
if do_commit:
n_inserts_or_updates += 1
print('updating', deput_sa.nomeparlamentar)
session.commit()
print('n_inserts_or_updates', n_inserts_or_updates, 'n_inserts', n_inserts)
session.close()
def loop_vitems_to_correct_publishdatetime():
filename = 'z_pubdate_analysis.log'
fp = open(filename, 'w', encoding='utf8')
session = Session()
vitems = session.query(YTVideoItemInfoSA).all()
n_need_change = 0
for i, vi in enumerate(vitems):
recalc_publishdtime = vi.recalc_n_return_publishdtime_from_infodtime_n_calendarstr()
recalc_publishdt = dtfs.convert_datetime_to_date(recalc_publishdtime)
if recalc_publishdt != vi.publishdate:
n_need_change += 1
line = str(n_need_change) + '/' + str(i+1) + ' infdt ' + str(vi.infodate) + ' pubdt ' \
+ str(vi.publishdate) + ' calend ' + str(vi.published_time_ago) + '\n'
line += ' recalc ' + str(recalc_publishdt) + ' [ needs updating ]' + '\n'
fp.write(line)
print(line)
vi.publishdatetime = recalc_publishdtime
session.commit()
line = 'Number of corrected records = ' + str(n_need_change) + ' // Total video items records = '\
+ str(len(vitems)) + '\n'
fp.write(line)
print(line)
fp.close()
session.close()
print('Written', filename)
def db_insert_or_update_if_needed(phonesdict):
session = Session()
n_not_inserted = 0
n_updates = 0
for i, cellphone in enumerate(phonesdict):
seq = i + 1
dw = phonesdict[cellphone]
do_commit = False
sadeput = session.query(SADeput) \
.filter(
SADeput.nomeparlamentar == dw.nomeparlamentar,
# SADeput.uf == dw.uf,
# SADeput.partido == dw.partido,
) \
.first()
if sadeput is None:
n_not_inserted += 1
print(
n_not_inserted, seq, 'this script does not insert '
'(either nomeparlamentar is wrong or outdated or needs polishing) =>',
str(dw))
continue
if sadeput.cellphone != dw.cellphone:
do_commit = True
sadeput.cellphone = dw.cellphone
if do_commit:
n_updates += 1
print('n_updates', n_updates, 'committing', sadeput)
session.commit()
print('n_updates', n_updates, 'n_not_inserted', n_not_inserted)
session.close()
def insert_into_db(phonesdict):
session = Session()
for i, phone in enumerate(phonesdict):
seq = i + 1
dw = phonesdict[phone]
existent = session.query(SADeput) \
.filter(
SADeput.name == dw.name, SADeput.uf == dw.uf, SADeput.partido == dw.partido, SADeput.phone == dw.cellphone
) \
.first()
if existent:
print(seq, 'exists', str(dw))
continue
sadeput = SADeput()
sadeput.name = dw.name
sadeput.uf = dw.uf
sadeput.partido = dw.partido
sadeput.phone = dw.cellphone
session.add(sadeput)
session.commit()
print(seq, 'inserted', str(dw))
session.close()
def dbsave_subscribers_number(self, subscribers_number):
pdate, ptime = dtfs.split_date_n_time_from_datetime(
self.videopagedatetime)
if pdate is None:
pdate = self.refdate
if ptime is None:
infotime = datetime.datetime.now()
else:
infotime = ptime
session = Session()
subs = session.\
query(YTDailySubscribersSA).\
filter(YTDailySubscribersSA.infodate == pdate). \
filter(YTDailySubscribersSA.ytchannelid == self.ytchannelid). \
first()
updated = False
created = False
if not subs:
created = True
updated = True
subs = YTDailySubscribersSA()
session.add(subs)
subs.ytchannelid = self.ytchannelid
if created or subs.subscribers != subscribers_number:
subs.subscribers = subscribers_number
self.n_subscribers = subscribers_number
updated = True
if created or subs.infodate != pdate:
subs.infodate = pdate
self.refdate = pdate
updated = True
if created or subs.infotime != infotime:
subs.infotime = infotime
updated = True
if updated:
session.commit()
print(' * Subscribers DB-written *')
else:
print(' *** Subscribers NOT DB-written ***')
session.close()
return updated
def write_views_to_db(self):
session = Session()
vviews = session.query(YTVideoViewsSA). \
filter(YTVideoViewsSA.ytvideoid == self.ytvideoid). \
filter(YTVideoViewsSA.infodate == self.infodate). \
first()
if vviews:
was_changed = False
if self.n_views is not None and vviews.views != self.n_views:
was_changed = True
vviews.views = self.n_views
if self.infotime is not None and vviews.infotime != self.infotime:
was_changed = True
vviews.infotime = self.infotime
if was_changed:
session.commit()
session.close()
if was_changed:
return True
else:
return False
vviews = YTVideoViewsSA()
vviews.ytvideoid = self.ytvideoid
vviews.views = self.n_views
vviews.infodate = self.infodate
vviews.infotime = self.infotime
session.add(vviews)
session.commit()
log_msg = 'Committed videoviews object ' + str(vviews)
print(log_msg)
logger.info(log_msg)
session.close()
return True