def get(self): if settings['TEST_MODE']: conn = db.get_connection() server_md.drop_all(bind=conn) server_md.create_all(bind=conn) user_t = db.get_table('user') conn.execute(user_t.insert().values(id=TEST_ID)) conn.execute(user_t.insert()) conn.execute(user_t.insert()) query = user_t.select() for t in conn.execute(query): logging.debug(t) logging.debug('base users added') words_t = db.get_table('words') conn.execute(words_t.insert(), {'user_id': TEST_ID, 'word': 'admin', 'raw_data': '{"translations":{"nouns":["админ"]}}'}) conn.execute(words_t.insert(), {'user_id': TEST_ID, 'word': 'wolf', 'raw_data': '{"translations":{"nouns":["волк"]}}'}) conn.execute(words_t.insert(), {'user_id': TEST_ID, 'word': 'nature', 'raw_data': '{"translations":{"nouns":["природа"]}}'}) conn.execute(words_t.insert(), {'user_id': TEST_ID, 'word': 'internet', 'raw_data': '{"translations":{"nouns":["интернет"]}}'}) conn.execute(words_t.insert(), {'user_id': TEST_ID, 'word': 'computer', 'raw_data': '{"translations":{"nouns":["компьютер"]}}'}) conn.execute(words_t.insert(), {'user_id': TEST_ID, 'word': 'data', 'raw_data': '{"translations":{"nouns":["данные"]}}'}) conn.execute(words_t.insert(), {'user_id': TEST_ID, 'word': 'logic', 'raw_data': '{"translations":{"nouns":["логика"]}}'}) conn.execute(words_t.insert(), {'user_id': TEST_ID, 'word': 'differ', 'raw_data': '{"translations":{"nouns":["разница"]}}'}) conn.execute(words_t.insert(), {'user_id': TEST_ID, 'word': 'clock', 'raw_data': '{"translations":{"nouns":["часы"]}}'}) conn.execute(words_t.insert(), {'user_id': TEST_ID, 'word': 'hackaton', 'raw_data': '{"translations":{"nouns":["хакатон"]}}'}) query = words_t.select(words_t.c.user_id == TEST_ID)#.where(words_t.c.user_id==2019) for t in conn.execute(query): logging.debug(t) logging.debug('step2') packs_t = db.get_table('word_package') for file_name in glob.glob('word_packs/*.json'): logging.warning(file_name) with open(file_name) as f_in: data = json.load(f_in) conn.execute(packs_t.insert(), {'name': data['name'], 'avatar': data['avatar'], 'description': data['description'], 'words': json.dumps(data['data'])}) with open('dirty_words/translated_words.json') as f_in: data = json.load(f_in) for elem in data: conn.execute(words_t.insert(), {'user_id': None, 'word': elem['word'], 'raw_data': json.dumps(elem['translation'])}) conn.close() logging.warning("correct update db") self.write({'result': 'ok'}) else: logging.warning("updating db is forbiden") self.write({'error': 'disabled'})
async def query_stop_ids(self, system: gtfs.TransitSystem, route_id: str): trips = db.get_table("trips") stop_times = db.get_table("stop_times") async with db.acquire_conn() as conn: res = await conn.execute( sa.select([stop_times.c.stop_id]).distinct().where( sa.and_( trips.c.system == system.value, trips.c.route_id == route_id, trips.c.trip_id == stop_times.c.trip_id, ))) rows = await res.fetchall() return [row["stop_id"] for row in rows]
def create(data): """ Creates a new artist from the supplied data, and saves to database. Parameters ---------- data: dict Dictionary of attribute values. Returns ------- The new Artist instance. """ artist = Artist(id=ids.new_id(), **data) artist.normalized_name = names.safe_obj_name(artist.name) artist.image_url = '' if name_is_taken(artist.normalized_name): raise exceptions.NameIsTaken item = artist_to_item(artist) table = db.get_table() table.put_item(Item=item) return artist
def get(self): try: user_id = int(self.get_argument('vk_user_id')) except ValueError: logging.warning( f"incorrect user_id: {self.get_argument('vk_user_id')}") self.send_error(403) return except MissingArgumentError: logging.warning(f"no user_id") self.send_error(403) return user_t = get_table('user') with db.get_connection() as conn: # TODO: separate thread q = sa.select([user_t ]).select_from(user_t).where(user_t.c.id == user_id) rows = conn.execute(q).fetchall() if not rows: q = user_t.insert().values(id=user_id) conn.execute(q) token = token_issuer.get_token(user_id) logging.debug( f"send index with paramtrs: token->{token}, user_id->{user_id}, " f"vk_param_sting->{self._get_vk_params_string()}, sign->{self.get_argument('sign')}" ) self.render('index.html', token=token, user_id=user_id, vk_param_string=self._get_vk_params_string(), sign=self.get_argument('sign'))
def update(artist, data): """ Updates attributes of the artist from the supplied data. Parameters ---------- artist: Artist The Artist instance to update. data: dict Dictionary of attribute values. Returns ------- The modified Artist instance. """ print('updating artist', artist.id) # if neither of these attributes changed, just return if data['name'] == artist.name and data["bio"] == artist.bio: return artist if data['name'] == artist.name: # just update bio table = db.get_table() table.update_item(Key={ 'PK': artist.id, 'SK': artist.id }, UpdateExpression='set Bio = :bio', ExpressionAttributeValues={':bio': data["bio"]}) artist.bio = data['bio'] return artist else: raise Exception("can't change artist names yet.")
async def process_feed(feed_id: str): feed_message = await nyc.get_data(feed_id) request_time = datetime.now(timezone.utc) if not feed_message.IsInitialized(): raise Exception( "Unable to parse NYC MTA feed {}: (FeedMessage not initialized)". format(feed_id)) timestamp = feed_message.header.timestamp json_str = google.protobuf.json_format.MessageToJson(feed_message) logging.info( "NYC MTA feed {}: {} ({:.3f} seconds ago), {} JSON bytes".format( feed_id, timestamp, time.time() - timestamp, len(json_str))) async with db.acquire_conn() as conn: table = db.get_table("realtime_raw") await conn.execute( insert(table).values( system=gtfs.TransitSystem.NYC_MTA.value, feed_id=feed_id, time=datetime.fromtimestamp(timestamp, timezone.utc), json=json.loads(json_str), raw=feed_message.SerializeToString(), update_time=request_time, ).on_conflict_do_update(table.primary_key, set_=dict(update_time=request_time)))
async def get_trip_row_from_id(self, trip_id: str): trips_table = db.get_table("trips") async with db.acquire_conn() as conn: res = await conn.execute(trips_table.select().where( trips_table.c.system == self.system.value).where( trips_table.c.trip_id == trip_id)) return await res.fetchone()
def get(self): with db.get_connection() as conn: user_id = self._extract_user_id() words_t = db.get_table('words') # query = sa.select([sa.func.count()]).select_from(words_t).where(words_t.c.user_id==user_id) # logging.warning(query) # word_sum = conn.execute(query).fetchone()[0] query = sa.select([words_t]).select_from(words_t).where( words_t.c.user_id == user_id) #.where( # words_t.c.correct_tested + words_t.c.wrong_tested > 0 # ) rows = conn.execute(query).fetchall() word_sum = 0 known_words_cnt = 0 correct = 0 wrong = 0 for row in rows: correct += row[words_t.c.correct_tested] wrong += row[words_t.c.wrong_tested] word_sum += 1 right_cnt = row[words_t.c.correct_tested] cnt = row[words_t.c.correct_tested] + row[ words_t.c.wrong_tested] if cnt > 0 and right_cnt / cnt > 0.8: known_words_cnt += 1 # users_t = db.get_table('user') # query = sa.select([users_t.c.correct_tested, users_t.c.wrong_tested]).where(users_t.c.id==user_id) # correct, wrong = conn.execute(query).fetchone() logging.debug(word_sum) logging.debug(known_words_cnt) logging.debug(f"{correct}, {wrong}") if correct + wrong == 0: percent = 0 else: percent = correct / (correct + wrong) logging.debug({ 'result': 'ok', 'data': { 'all_words': word_sum, 'known_words': known_words_cnt, 'rating': int(known_words_cnt * percent * 100) } }) self.write( json.dumps({ 'result': 'ok', 'data': { 'all_words': word_sum, 'known_words': known_words_cnt, 'rating': int(known_words_cnt * percent * 100) } }))
def list_for_artist(artist_id): table = db.get_table() res = table.query(IndexName='IX_ARTIST_CONTENT', ScanIndexForward=True, KeyConditionExpression=Key('AC_PK').eq(artist_id) & Key('AC_SK').begins_with(DEFAULT_SORT[0])) return list(map(item_to_single, res['Items']))
def get_by_id(id): table = db.get_table() res = table.query(KeyConditionExpression=Key('PK').eq(id) & Key('SK').eq(id)) if len(res['Items']) == 0: return None item = res['Items'][0] track = { 'id': item['PK'], 'artist_id': item['ArtistID'], 'artist_name': item['ArtistName'], 'track_title': item['TrackTitle'], 'audio_url': item['AudioURL'] } album_id = item.get('AA_PK') album_title = item.get('AlbumTitle') if album_id and album_title: track['album_id'] = album_id track['album_title'] = album_title license = item.get('License') if license: track["license"] = license track["license_name"] = licenses.names[license] release_date = item.get('ReleaseDate') if release_date: track['year'] = dateutil.parser.parse(release_date).strftime('%Y') return track
def create(artist, single_title, audio_url): existing_singles = list_for_artist(artist.id) if len(existing_singles) > 0: if any(ex.title == single_title for ex in existing_singles): dedupe_num = 0 test_title = single_title while any(ex.title == test_title for ex in existing_singles): dedupe_num += 1 test_title = single_title + ' ' + str(dedupe_num) single_title = test_title last = existing_singles[-1] last_sort = int(last.sort) sort = str(last_sort + 1) else: sort = DEFAULT_SORT single = Single( id=ids.new_id(), title=single_title, audio_url=audio_url, sort=sort, artist=artist, license=DEFAULT_LICENSE, release_date=datetime.datetime.utcnow().strftime('%Y-%m-%d')) item = single_to_item(single) table = db.get_table() table.put_item(Item=item) return single
def name_is_taken(test_name, exclude_id=None): """ Used to determine if an artist name is already used. Parameters ---------- test_name : str the name to check exclude_id : str optional, used in edit scenarios to not check the current owner Returns ------- boolean, if true name is already taken and artist should not be persisted with this name. """ table = db.get_table() result = table.query( IndexName='IX_ARTISTS_NAMES', KeyConditionExpression=Key('NormalizedName').eq(test_name)) if len(result['Items']) == 0: return False if exclude_id is None: return True return len(filter(lambda item: item['PK'] != exclude_id, result['Items'])) != 0
def list_all(): table = db.get_table() response = table.query(IndexName='IX_ARTISTS_ALBUMS', ScanIndexForward=True, KeyConditionExpression=Key('AA_PK').eq('ARTISTS'), ProjectionExpression='PK, AA_SK, ImageURL') return map(map_list_item, response['Items'])
def unfeature_item(artist_id, item_id): table = db.get_table() # get track res = table.query(KeyConditionExpression=Key('PK').eq(item_id) & Key('SK').eq(item_id)) if len(res['Items']) == 0: raise exceptions.InvalidData('invalid item id') track = res['Items'][0] if track['ArtistID'] != artist_id: raise exceptions.InvalidData('track not in artist') # define update key = {'PK': item_id, 'SK': item_id} update_exp = 'remove Featured, FeatureSort' # if album track, remove from AC if 'AA_PK' in track: update_exp += ', AC_SK, AC_PK' # update print('update_exp', update_exp) table.update_item(Key=key, UpdateExpression=update_exp)
def create_resource_types(ctx): ctx.status("creating resource types") tbl = db.get_table('resource_types') recs = [ dict( code="runm.cpu.dedicated", description="A logical CPU processor associated with a " "single dedicated host CPU processor" ), dict( code="runm.cpu.shared", description="A logical CPU processor that may be executed on " "a host CPU processor along with other shared logical " "CPUs" ), dict(code='runm.memory', description='Bytes of RAM'), dict(code='runm.storage.block', description='Bytes of block storage'), dict(code='runm.gpu.virtual', description='virtual GPU context'), ] try: _insert_records(tbl, recs) ctx.status_ok() except Exception as err: ctx.status_fail(err)
def create_partitions(ctx): ctx.status("creating partitions") part_tbl = db.get_table('partitions') sess = db.get_session() created = set() try: for p in ctx.deployment_config.providers.values(): part_uuid = p.partition.uuid if part_uuid in created: continue metadata.create_object( sess, 'runm.partition', part_uuid, p.partition.name) # Create the base provider group record part_rec = dict( uuid=part_uuid, ) ins = part_tbl.insert().values(**part_rec) sess.execute(ins) created.add(part_uuid) sess.commit() ctx.status_ok() except Exception as err: sess.rollback() ctx.status_fail(err)
def create_capabilities(ctx): ctx.status("creating capabilities") tbl = db.get_table('capabilities') recs = [ dict( code="cpu.x86.avx", description="Intel x86 CPU instruction set extensions for AVX", ), dict( code="cpu.x86.avx2", description="Intel x86 CPU instruction set extensions for AVX2", ), dict( code="cpu.x86.vmx", description="Intel x86 CPU instruction set extensions for VMX", ), dict( code="storage.disk.hdd", description="Block storage is on traditional spinning rust", ), dict( code="storage.disk.ssd", description="Block storage is on a solid-state drive", ), ] try: _insert_records(tbl, recs) ctx.status_ok() except Exception as err: ctx.status_fail(err)
async def write_trip_paths(routes_for_shape_id, paths): values = [] for (shape_id, path) in paths.items(): values.append( { "system": TRANSIT_SYSTEM.value, "shape_id": shape_id, "routes": routes_for_shape_id.get(shape_id), "shape": "LINESTRING({})".format( ", ".join(["{} {}".format(p.x, p.y) for p in path]) ), } ) table = db.get_table("trip_paths") stmt = insert(table).values(values) stmt = stmt.on_conflict_do_update( index_elements=[table.c.system, table.c.shape_id], set_={"shape": stmt.excluded.shape}, ) async with db.acquire_conn() as conn: res = await conn.execute( table.delete().where(table.c.system == TRANSIT_SYSTEM.value) ) logging.info("Deleted %d rows from %s", res.rowcount, table.name) await conn.execute(stmt) logging.info("Inserted %d rows into %s", len(values), table.name)
def get(self): try: user_id = self._extract_user_id() except ValueError: logging.warning('incorrect user_id') self.write(json.dumps({'error': 'incorrect-format'})) return words_t = db.get_table('words') with db.get_connection() as conn: words = conn.execute( select([words_t.c.id, words_t.c.word, words_t.c.raw_data ]).where(words_t.c.user_id == user_id).order_by( words_t.c.word)) data = [{ 'id': word['id'], 'word': word['word'], 'translations': json.loads(word['raw_data'])['translations'] } for word in words] self.write(json.dumps({'result': 'ok', 'data': data}))
def add(artist, data): """ Adds an album to the artist. """ album = Album(id=ids.new_id(), artist=artist, image_url='', **data) if not hasattr(album, 'license'): setattr(album, 'license', '') if not hasattr(album, 'description'): setattr(album, 'description', '') table = db.get_table() # get existing albums to check name and get next sort res = table.query(IndexName='IX_ARTIST_CONTENT', ScanIndexForward=True, KeyConditionExpression=Key('AC_PK').eq(artist.id) & Key('AC_SK').begins_with('2'), ProjectionExpression='PK, AA_PK, AC_SK, AlbumTitle') existing_albums = list(map(item_to_album, res['Items'])) if any(album.title == existing.title for existing in existing_albums): raise exceptions.AlbumTitleExists if len(existing_albums) > 0: last_album = existing_albums[-1] last_sort = int(last_album.sort) album.sort = str(last_sort + 1) else: album.sort = '200' print("creating '{0}', id '{1}', sort {2}".format(album.title, album.id, album.sort)) table.put_item(Item=album_to_item(album)) return album
def _get_random_user_words(self, max_count=10): user_id = self._extract_user_id() logging.info(f"get {max_count} random words with their translations from user {user_id}") user_words = list() is_enough = False with db.get_connection() as conn: words_t = db.get_table('words') query = words_t.select(words_t.c.user_id == user_id).order_by(func.random()) for row in conn.execute(query): if len(user_words) >= max_count: is_enough = True break if 'raw_data' not in row or not row['raw_data']: logging.warning(f"got incorrect row from database {row}") continue translations = list() logging.warning(json.loads(row['raw_data'])) for _, vals in json.loads(row['raw_data'])['translations'].items(): for elem in vals: translations.append(elem) user_words.append((row['word'], translations)) if not is_enough: logging.warning(f"found not enough user words(need {max_count}, got {len(user_words)}") logging.debug(f"extract words: {user_words}") return user_words
def create_track(album, track_title, audio_url): if len(album.tracks) > 0: if any(existing_track.title == track_title for existing_track in album.tracks): dedupe_num = 0 test_title = track_title while any(existing_track.title == test_title for existing_track in album.tracks): dedupe_num += 1 test_title = track_title + ' ' + str(dedupe_num) track_title = test_title last_track = album.tracks[-1] last_sort = int(last_track.sort) sort = str(last_sort + 1) else: sort = '100' track = Track(id=ids.new_id(), title=track_title, audio_url=audio_url, sort=sort, album=album) item = track_to_item(track) table = db.get_table() table.put_item(Item=item) return track
def update(album, data): for key, val in data.items(): setattr(album, key, val) # get existing albums to check name table = db.get_table() res = table.query(IndexName='IX_ARTIST_CONTENT', ScanIndexForward=True, KeyConditionExpression=Key('AC_PK').eq(album.artist.id) & Key('AC_SK').begins_with('2'), ProjectionExpression='PK, AA_PK, AC_SK, AlbumTitle') existing_albums = list(map(item_to_album, res['Items'])) if any((album.title == existing.title and album.id != existing.id) for existing in existing_albums): raise exceptions.AlbumTitleExists table.update_item( Key={ 'PK': album.artist.id, 'SK': album.id }, UpdateExpression= 'set AlbumTitle = :title, ReleaseDate = :release_date, License = :license, Description = :description', ExpressionAttributeValues={ ':title': album.title, ':release_date': album.release_date, ':license': album.license, ':description': album.description }) return album
async def query_timezone(self, system: TransitSystem): agency = db.get_table("agency") async with db.acquire_conn() as conn: res = await conn.execute( agency.select().where(agency.c.system == system.value)) row = await res.fetchone() return gettz(row.agency_timezone)
def create_object_types(ctx): ctx.status("creating object types") tbl = db.get_table('object_types') recs = [ dict( code="runm.partition", description="A division of resources. A deployment unit for runm", ), dict( code="runm.provider", description="A provider of some resources, e.g. a compute node or " "an SR-IOV NIC", ), dict( code="runm.provider_group", description="A group of providers", ), dict( code="runm.image", description="A bootable bunch of bits", ), dict( code="runm.machine", description="Created by a user, a machine consumes compute " "resources from one of more providers", ), ] try: _insert_records(tbl, recs) ctx.status_ok() except Exception as err: ctx.status_fail(err)
async def get_stop_exists(self, stop_id: str) -> bool: table = db.get_table("stops") async with db.acquire_conn() as conn: res = await conn.scalar( sa.select([table.c.stop_id ]).where(table.c.system == self.system.value).where( table.c.stop_id == stop_id)) return res is not None
def __init__(self): table = db.get_table("realtime_raw") super().__init__( "realtime_raw", ("update_time", "feed_id"), table.c.system == self.transit_system.value, ) self.writer = RealtimeWriter(self.transit_system)
def load_logged_in_user(): user_id = session.get('user_id') if user_id is None: g.user = None else: g.user = database.get_table().find_one({'_id': ObjectId(user_id)})['username']
def write_checkpoint(self, engine, job_name: str, checkpoint: Checkpoint): table = db.get_table("batch_checkpoints") with engine.connect() as conn: conn.execute(table.insert().values( job_name=job_name, time=datetime.now(timezone.utc), checkpoint=checkpoint.dumps(), ))
async def query_route(self, system: gtfs.TransitSystem, route_id: str): routes = db.get_table("routes") async with db.acquire_conn() as conn: res = await conn.execute( routes.select().where(routes.c.system == system.value).where( routes.c.route_id == route_id)) route = await res.fetchone() assert route is not None return route
def _forget_project(name, conn): db.get_table().filter(r.row['name'] == name).delete().run(conn)