def application_init():
"""
Bootstrap server application
"""
# Setup logging
logging.basicConfig(format=LOG_FORMAT)
logger = logging.getLogger()
logger.setLevel(logging.INFO)
# Make database connection
engine.connect("sqlite:///var/db.sqlite3")
def database_exists():
try:
engine.connect()
return True
except OperationalError:
print('The database "sqlalchemy_tutorial" did not exist')
print('Please run commands below')
print('--------------------------------------')
print('createdb sqlalchemy_tutorial')
print('psql sqlalchemy_tutorial < world.sql')
print('--------------------------------------')
return False
def query_db(self):
query = self.text_edit.toPlainText()
with engine.connect() as db:
try:
results = db.execute(sqlalchemy.text(query))
columns = results.keys()
results = np.array([r for r in results])
except BaseException as e:
print(e, type(e))
if type(e) == AttributeError:
results = []
else:
return
if len(results) == 0:
return
self.table\
.setRowCount(len(results) + 1)
self.table\
.setColumnCount(results.shape[1])
for i, column in enumerate(columns):
self.table\
.setItem(0, i, QTableWidgetItem(str(column)))
for i, row in enumerate(results):
for j, item in enumerate(row):
self.table\
.setItem(i + 1, j, QTableWidgetItem(str(item)))
def get_campaign_name():
conn = engine.connect()
campaign_name = conn.execute(
sa.select([campaigns.c.campaign_name
]).where(campaigns.c.campaign_id == None)).fetchone()
if campaign_name:
return campaign_name[0]
def init_db():
with engine.connect() as conn:
with open('schema.sql') as f:
schema = f.read()
conn.execute(schema)
logging.info('Created new database')
test_sermons()
def sermon_answerer():
defaut_sermon = 'Namo tassa bhagavato arahato sammāsambuddhassā.'
sermon = ''
with engine.connect() as conn:
ids = conn.execute(select([sermons.c.id])).fetchall()
if ids:
sermon = conn.execute(
select([
sermons.c.sermon_text
]).where(sermons.c.id == random.choice(ids)[0])).fetchone()
if not sermon or not sermon[0].strip():
sermon = defaut_sermon
else:
sermon = sermon[0]
if len(sermon) > MAX_MESSAGE_LENGTH:
sermon = sermon[:MAX_MESSAGE_LENGTH - 3] + '...'
return [
InlineQueryResultArticle(
id=f'{uuid.uuid4()}',
title='SERMON',
input_message_content=InputTextMessageContent(sermon))
]
def get_audience_name_mk():
conn = engine.connect()
audience_name = conn.execute(
sa.select([campaigns.c.adgroup, campaigns.c.custom_audience_id]).where(
sa.and_(campaigns.c.custom_audience_id != None,
campaigns.c.add_users_mk == False))).fetchone()
if audience_name:
return audience_name
def get_audience_name():
conn = engine.connect()
audience_name = conn.execute(
sa.select([campaigns.c.adgroup]).where(
sa.and_(campaigns.c.campaign_id != None,
campaigns.c.custom_audience_id == None))).fetchone()
if audience_name:
return audience_name[0]
def get_playlists_for_user(user):
playlists = Playlist.__table__
conn = engine.connect()
s = select([playlists.c.id, playlists.c.name]) \
.where(playlists.c.user == user) \
.order_by(playlists.c.name)
res = [{'id': row[0], 'name': row[1]} for row in conn.execute(s)]
conn.close()
return {'user': user, 'playlists': res}
def get_playlists_for_user(user):
playlists = Playlist.__table__
conn = engine.connect()
s = select([playlists.c.id, playlists.c.name]) \
.where(playlists.c.user == user) \
.order_by(playlists.c.name)
res = [{'id': row[0], 'name': row[1]} for row in conn.execute(s)]
conn.close()
return {'user': user, 'playlists': res}
def top_artists(limit=20):
songs = Song.__table__
play_history = PlayHistory.__table__
conn = engine.connect()
cols = [songs.c.artist, func.count(play_history.c.id).label('play_count')]
s = (select(cols).select_from(songs.join(play_history)).group_by(
songs.c.artist).order_by('play_count DESC').limit(limit))
res = [{'artist': row[0], 'play_count': row[1]} for row in conn.execute(s)]
conn.close()
return {'limit': limit, 'results': res}
def get_data_for_adset():
conn = engine.connect()
data_for_adset = conn.execute(
sa.select([
campaigns.c.campaign_id, campaigns.c.custom_audience_id,
campaigns.c.adgroup
]).where(
sa.and_(campaigns.c.ad_set_id == None,
campaigns.c.custom_audience_id != None))).fetchone()
if data_for_adset:
return data_for_adset
def get_data_video_upload():
conn = engine.connect()
data_for_video = conn.execute(
sa.select([
campaigns.c.adgroup, campaigns.c.custom_audience_id,
campaigns.c.link_video
]).where(
sa.and_(campaigns.c.video_id == None,
campaigns.c.ad_set_id != None))).fetchone()
if data_for_video:
return data_for_video
def get_creative_group():
conn = engine.connect()
creative_group_data = conn.execute(
sa.select([
campaigns.c.ad_set_id, campaigns.c.video_creation_id,
campaigns.c.adgroup
]).where(
sa.and_(campaigns.c.creative_group_id == None,
campaigns.c.video_creation_id != None))).fetchone()
if creative_group_data:
return creative_group_data
def get_data_video_creation():
conn = engine.connect()
data_video_creation = conn.execute(
sa.select([
campaigns.c.adgroup, campaigns.c.video_id,
campaigns.c.destination_url, campaigns.c.description
]).where(
sa.and_(campaigns.c.video_creation_id == None,
campaigns.c.video_id != None))).fetchone()
if data_video_creation:
return data_video_creation
def top_artists(limit=20):
songs = Song.__table__
play_history = PlayHistory.__table__
conn = engine.connect()
s = select([songs.c.artist, func.count(play_history.c.id).label('play_count')]) \
.select_from(songs.join(play_history)) \
.group_by(songs.c.artist) \
.order_by('play_count DESC') \
.limit(limit)
res = [{'artist': row[0], 'play_count': row[1]} for row in conn.execute(s)]
conn.close()
return {'limit': limit, 'results': res}
def get_albums_for_artist(artist):
songs = Song.__table__
albums = []
if artist:
conn = engine.connect()
s = select([songs.c.album, func.count(songs.c.album).label('num_songs')]) \
.where(songs.c.artist == artist) \
.group_by(songs.c.album) \
.order_by(songs.c.album)
res = conn.execute(s)
albums = [{'name': row[0], 'num_songs': row[1]} for row in res]
conn.close()
return {'query': artist, 'results': albums}
def top_songs(limit=20):
songs = Song.__table__
play_history = PlayHistory.__table__
conn = engine.connect()
cols = [songs.c.id, func.count(play_history.c.id).label('play_count')]
s = (select(cols).select_from(songs.join(play_history)).group_by(
songs.c.id).order_by('play_count DESC').limit(limit))
res = conn.execute(s)
conn.close()
session = Session()
songs = [session.query(Song).get(song[0]).dictify() for song in res]
session.commit()
return {'limit': limit, 'results': songs}
def get_albums_for_artist(artist):
songs = Song.__table__
albums = []
if artist:
conn = engine.connect()
cols = [songs.c.album, func.count(songs.c.album).label('num_songs'), songs.c.artist]
s = (select(cols)
.where(songs.c.artist == artist)
.group_by(songs.c.album)
.order_by(songs.c.album))
res = conn.execute(s)
albums = [{'name': row[0], 'num_songs': row[1], 'art_uri': art.get_art(row[2], row[0])} for row in res]
conn.close()
return {'query': artist, 'results': albums}
def get_albums_for_artist(artist):
songs = Song.__table__
albums = []
if artist:
conn = engine.connect()
cols = [songs.c.album, func.count(songs.c.album).label('num_songs'), songs.c.artist]
s = (select(cols)
.where(songs.c.artist == artist)
.group_by(songs.c.album)
.order_by(songs.c.album))
res = conn.execute(s)
albums = [{'name': row[0], 'num_songs': row[1], 'art_uri': art.get_art(row[2], row[0])} for row in res]
conn.close()
return {'query': artist, 'results': albums}
def top_songs(limit=20):
songs = Song.__table__
play_history = PlayHistory.__table__
conn = engine.connect()
s = select([songs.c.id, func.count(play_history.c.id).label('play_count')]) \
.select_from(songs.join(play_history)) \
.group_by(songs.c.id) \
.order_by('play_count DESC') \
.limit(limit)
res = conn.execute(s)
conn.close()
session = Session()
songs = [session.query(Song).get(song[0]).dictify() for song in res]
session.commit()
return {'limit': limit, 'results': songs}
def test_sermons():
with engine.connect() as conn:
res = conn.execute(
select([
sermons.c.id
])
).fetchone()
if not res:
if os.path.isfile(sermon_file):
with open(sermon_file) as f:
sermons_lst = f.read().split('***')
conn.execute(
sermons.insert(),
[
{'sermon_text': s} for s in sermons_lst
]
)
def sidewalks():
# Update construction info from the construction table
# FIXME: this should be handled by proper schema definitions and migrations
print('Updating sidewalks table...')
with engine.connect() as conn:
has_column = conn.execute("""
SELECT column_name
FROM information_schema.columns
WHERE table_name='sidewalks'
AND column_name='construction'
""")
if not has_column.first():
conn.execute('''
ALTER TABLE sidewalks
ADD COLUMN construction boolean
DEFAULT FALSE
''')
# Note: checking within 1e-6 degrees, which is ~10 cm
# This method is used (rather than reprojecting) because it's fast, at the
# expense of having non-equal lat/lon units.
with engine.begin() as conn:
conn.execute('''
UPDATE sidewalks s
SET construction=TRUE
FROM (SELECT s2.gid
FROM construction c
JOIN sidewalks s2
ON ST_DWithin(c.geom, s2.geom, {})
WHERE c.start_date <= current_timestamp
AND c.end_date >= current_timestamp) q
WHERE q.gid = s.gid
'''.format(NODE_DIST))
print('Done')
def add_songs_in_dir(path, store_checksum=False):
"""Update database to reflect the contents of the given directory.
store_checksum: Whether or not to store an MD5 file checksum in order to
update the song metadata in the database if the file is modified. Disabled
by default because it makes scanning a lot slower.
"""
already_added = _prune_dir(path, prune_modified=store_checksum)
table = Song.__table__
conn = engine.connect()
num_songs = 0
for root, _, files in walk(path):
for f in files:
ext = splitext(f)[1]
filepath = join(root, f).decode('utf-8')
if ext in {'.mp3', '.flac', '.ogg', '.m4a', '.mp4'}:
if filepath in already_added:
print 'Already added: ' + filepath
continue
try:
if ext == '.mp3':
song = EasyMP3(filepath)
elif ext == '.flac':
song = FLAC(filepath)
elif ext == '.ogg':
song = OggVorbis(filepath)
elif ext in {'.m4a', '.mp4'}:
song = MP4(filepath)
except IOError, e:
print e
continue
# Required tags
try:
if ext in {'.m4a', '.mp4'}:
title = song.tags['\xa9nam'][0]
artist = song.tags['\xa9ART'][0]
else:
title = song.tags['title'][0]
artist = song.tags['artist'][0]
except Exception:
print 'Missing tags: ' + filepath
continue
song_obj = {
'title': title,
'artist': artist,
'length': song.info.length,
'path': filepath,
}
# Calculate and store file checksum
if store_checksum:
with open(filepath, 'rb') as song_file:
song_obj['checksum'] = md5_for_file(song_file)
try: # Album optional for singles
if ext in {'.m4a', '.mp4'}:
song_obj['album'] = song.tags['\xa9alb'][0]
else:
song_obj['album'] = song.tags['album'][0]
except Exception:
song_obj['album'] = None
try: # Track number optional
if ext in {'.m4a', '.mp4'}:
song_obj['tracknumber'] = song.tags['trkn'][0][0]
else:
song_obj['tracknumber'] = (
int(song.tags['tracknumber'][0]))
except Exception:
song_obj['tracknumber'] = None
# Album art added on indexing
if not art.get_art(song_obj['artist'], song_obj['album']):
art.index_art(song_obj)
print 'Added: ' + filepath
conn.execute(table.insert().values(song_obj))
num_songs += 1
def add_songs_in_dir(path, store_checksum=False):
"""Update database to reflect the contents of the given directory.
store_checksum: Whether or not to store an MD5 file checksum in order to
update the song metadata in the database if the file is modified. Disabled
by default because it makes scanning a lot slower.
"""
already_added = _prune_dir(path, prune_modified=store_checksum)
table = Song.__table__
conn = engine.connect()
num_songs = 0
for root, _, files in walk(path):
for f in files:
ext = splitext(f)[1]
filepath = join(root, f).decode('utf-8')
if ext in {'.mp3', '.flac', '.ogg', '.m4a', '.mp4'}:
if filepath in already_added:
print 'Already added: ' + filepath
continue
try:
if ext == '.mp3':
song = EasyMP3(filepath)
elif ext == '.flac':
song = FLAC(filepath)
elif ext == '.ogg':
song = OggVorbis(filepath)
elif ext in {'.m4a', '.mp4'}:
song = MP4(filepath)
except IOError, e:
print e
continue
# Required tags
try:
if ext in {'.m4a', '.mp4'}:
title = song.tags['\xa9nam'][0]
artist = song.tags['\xa9ART'][0]
else:
title = song.tags['title'][0]
artist = song.tags['artist'][0]
except Exception:
print 'Missing tags: ' + filepath
continue
song_obj = {
'title': title,
'artist': artist,
'length': song.info.length,
'path': filepath,
}
# Calculate and store file checksum
if store_checksum:
with open(filepath, 'rb') as song_file:
song_obj['checksum'] = md5_for_file(song_file)
try: # Album optional for singles
if ext in {'.m4a', '.mp4'}:
song_obj['album'] = song.tags['\xa9alb'][0]
else:
song_obj['album'] = song.tags['album'][0]
except Exception:
song_obj['album'] = None
try: # Track number optional
if ext in {'.m4a', '.mp4'}:
song_obj['tracknumber'] = song.tags['trkn'][0][0]
else:
song_obj['tracknumber'] = (
int(song.tags['tracknumber'][0]))
except Exception:
song_obj['tracknumber'] = None
# Album art added on indexing
if not art.get_art(song_obj['artist'], song_obj['album']):
art.index_art(song_obj)
print 'Added: ' + filepath
conn.execute(table.insert().values(song_obj))
num_songs += 1
import os
import sys
sys.path.append(os.path.dirname(os.path.dirname(os.path.realpath(__file__))))
from db import engine;
from clean_tokens.keywords import locations;
results = []
for location in locations:
conn = engine.connect()
query = f"SELECT COUNT(*) FROM derived_posts WHERE stage_3 ILIKE '%%{location}%%'"
count = conn.execute(query).fetchall()[0][0];
results.append([location, count])
results.sort(key=lambda i: i[1])
print(*results[::-1], sep=',\n')
def set_video_creation_id(video_id, video_creation_id):
conn = engine.connect()
return conn.execute(campaigns.update().where(
campaigns.c.video_id.match(video_id)).values(
video_creation_id=str(video_creation_id)))
def set_video_id(custom_audience_id, video_id):
conn = engine.connect()
return conn.execute(campaigns.update().where(
campaigns.c.custom_audience_id.match(custom_audience_id)).values(
video_id=str(video_id)))
def recover_art(path):
"""Recovers missing artwork for songs already in database.
Useful for disaster recovery.
"""
table = Song.__table__
conn = engine.connect()
num_songs = 0
for root, _, files in walk(path):
for f in files:
ext = splitext(f)[1]
filepath = join(root, f).decode('utf-8')
if ext in {'.mp3', '.flac', '.ogg', '.m4a', '.mp4'}:
try:
if ext == '.mp3':
song = EasyMP3(filepath)
elif ext == '.flac':
song = FLAC(filepath)
elif ext == '.ogg':
song = OggVorbis(filepath)
elif ext in {'.m4a', '.mp4'}:
song = MP4(filepath)
except IOError, e:
print e
continue
# Required tags
try:
if ext in {'.m4a', '.mp4'}:
title = song.tags['\xa9nam'][0]
artist = song.tags['\xa9ART'][0]
else:
title = song.tags['title'][0]
artist = song.tags['artist'][0]
except Exception:
print 'Missing tags: ' + filepath
continue
song_obj = {
'title': title,
'artist': artist,
'length': song.info.length,
'path': filepath,
}
try: # Album optional for singles
if ext in {'.m4a', '.mp4'}:
song_obj['album'] = song.tags['\xa9alb'][0]
else:
song_obj['album'] = song.tags['album'][0]
except Exception:
song_obj['album'] = None
try: # Track number optional
if ext in {'.m4a', '.mp4'}:
song_obj['tracknumber'] = song.tags['trkn'][0][0]
else:
song_obj['tracknumber'] = (
int(song.tags['tracknumber'][0]))
except Exception:
song_obj['tracknumber'] = None
# Album art added on indexing
if not art.get_art(song_obj['artist'], song_obj['album']):
art.index_art(song_obj)
print 'Recovered artwork for: ' + filepath
num_songs += 1
def set_adset_id(custom_audience_id, ad_set_id):
conn = engine.connect()
conn.execute(campaigns.update().where(
campaigns.c.custom_audience_id.match(custom_audience_id)).values(
ad_set_id=ad_set_id))
def recover_art(path):
"""Recovers missing artwork for songs already in database.
Useful for disaster recovery.
"""
table = Song.__table__
conn = engine.connect()
num_songs = 0
for root, _, files in walk(path):
for f in files:
ext = splitext(f)[1]
filepath = join(root, f).decode('utf-8')
if ext in {'.mp3', '.flac', '.ogg', '.m4a', '.mp4'}:
try:
if ext == '.mp3':
song = EasyMP3(filepath)
elif ext == '.flac':
song = FLAC(filepath)
elif ext == '.ogg':
song = OggVorbis(filepath)
elif ext in {'.m4a', '.mp4'}:
song = MP4(filepath)
except IOError, e:
print e
continue
# Required tags
try:
if ext in {'.m4a', '.mp4'}:
title = song.tags['\xa9nam'][0]
artist = song.tags['\xa9ART'][0]
else:
title = song.tags['title'][0]
artist = song.tags['artist'][0]
except Exception:
print 'Missing tags: ' + filepath
continue
song_obj = {
'title': title,
'artist': artist,
'length': song.info.length,
'path': filepath,
}
try: # Album optional for singles
if ext in {'.m4a', '.mp4'}:
song_obj['album'] = song.tags['\xa9alb'][0]
else:
song_obj['album'] = song.tags['album'][0]
except Exception:
song_obj['album'] = None
try: # Track number optional
if ext in {'.m4a', '.mp4'}:
song_obj['tracknumber'] = song.tags['trkn'][0][0]
else:
song_obj['tracknumber'] = (int(
song.tags['tracknumber'][0]))
except Exception:
song_obj['tracknumber'] = None
# Album art added on indexing
if not art.get_art(song_obj['artist'], song_obj['album']):
art.index_art(song_obj)
print 'Recovered artwork for: ' + filepath
num_songs += 1
def set_creative_group_id(ad_creative_id, creative_group_id):
conn = engine.connect()
conn.execute(campaigns.update().where(
campaigns.c.video_creation_id.match(ad_creative_id)).values(
creative_group_id=str(creative_group_id)))
def set_audience_mk(custom_audience_id):
conn = engine.connect()
conn.execute(campaigns.update().where(
campaigns.c.custom_audience_id.match(custom_audience_id)).values(
add_users_mk=True))
def get_custom_audience(audience_name):
conn = engine.connect()
return conn.execute(audience.select().where(
audience.c.audience_name == audience_name)).fetchall()
def set_audience_id(audience_name, custom_audience_id):
conn = engine.connect()
return conn.execute(campaigns.update().where(
campaigns.c.adgroup.match(audience_name)).values(
custom_audience_id=custom_audience_id))
def get_currency_rates(**kwargs):
text = ''
res = {}
current_date = datetime.utcnow().date()
params = {
'base': kwargs.get('base', 'EUR'),
'date': kwargs.get('date', current_date),
'symbols': kwargs.get('currencies')
}
with engine.connect() as conn:
query = conn.execute(
select([
currency_rates.c.rates,
currency_rates.c.lookup_date,
currency_rates.c.base,
]).where(
and_(
currency_rates.c.base == params['base'],
currency_rates.c.lookup_date == params['date'],
)))
result = query.fetchone()
if result:
res = {
'rates': result[0],
'date': result[1],
'base': result[2],
}
else:
url = currency_rates_url
if params.get('date') != current_date:
url += f'/{params["date"]}'
else:
params.pop('date', None)
url += '/latest'
resp = requests.get(url, params)
if resp.ok:
res = resp.json()
with engine.connect() as conn:
# sometimes the api sends a previous date
# for /latest with querystrings
existing_record = conn.execute(
select([currency_rates.c.base]).where(
and_(
currency_rates.c.base == res['base'],
currency_rates.c.lookup_date == res['date'],
))).fetchone()
if not existing_record:
conn.execute(currency_rates.insert().values(
base=res['base'],
lookup_date=res['date'],
rates=res['rates']))
else:
error = resp.json()['error']
return f'Sorry, unable to answer your query. Reason: {error}'
text = f'Date: {res["date"]}\nBase: {res["base"]}\n'
symbols = params.get('symbols')
for cur, rate in res['rates'].items():
if symbols and cur not in symbols:
continue
text += f'{cur}: {rate}\n'
return text
async def startup():
engine.connect()
def set_campaign_id(camp_name, camp_id):
conn = engine.connect()
return conn.execute(campaigns.update().where(
campaigns.c.campaign_name.match(camp_name)).values(
campaign_id=camp_id))
