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 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 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
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))