def get_data_from_table(table_name): # Connect to account table_service = TableService( account_name='soilhumiditydata293s', account_key= '4PSsEO1xBAIdq3/MppWm+t6eYHi+CWhVn6xNZ6i4mLVgm50K8+NK6lA94v8MxG0bvVEfYCvsv1suxCyCnUYd0A==' ) # Check if table exists if not table_service.exists(table_name): print("Table does NOT exist.") return -1 # Retrieve all values from table table = table_service.query_entities(table_name) data = [] for entry in table: # Format timestamp eTime = entry['enqueuedTime'] eTime = datetime.strptime( str(eTime[:10]) + " " + str(eTime[11:-8]), '%Y-%m-%d %H:%M:%S') entry['enqueuedTime'] = find_closest_15th_minute( eTime) # Round to closest 15th minute entry['hour'] = float(entry['enqueuedTime'].hour) data.append(entry) # Sort by time of reading data = sorted(data, key=lambda k: k['enqueuedTime']) return data
def main(req: func.HttpRequest) -> func.HttpResponse: logging.info('Python HTTP trigger function processed a request.') # Connect to Azure Table Storage table_service = TableService( connection_string=os.environ['AzureWebJobsStorage']) table_service.create_table( 'intents') if not table_service.exists('intents') else None req_body = req.get_json() if req_body: # Create row to be saved on Azure Table Storage print(req_body.get('ConversationId')) data = req_body data["PartitionKey"] = req_body.get('ConversationId') data["RowKey"] = req_body.get('MessageId') # Save row on Azure Table Storage table_service.insert_or_replace_entity('intents', data) return func.HttpResponse( f"Row {req_body.get('MessageId')} for {req_body.get('ConversationId')} added" ) else: return func.HttpResponse("Please pass valid request body", status_code=400)
def setup_table_service(conn_str: str, target_table: str) -> TableService: """Setup a Table Service for a the target_table. Parameters ---------- conn_str Connection string to an Azure storage account target_table Name of the table we want to create the table service for. Raise ----- Raises an exceptions.HttpError if the table was not found in the storage account. """ storage_params = extract_storage_parameters(conn_str) table_service = TableService( account_name=storage_params["AccountName"], account_key=storage_params["AccountKey"], ) if not table_service.exists(target_table): msg = f"Table {target_table} to store request info did not exist." raise exceptions.HttpError( msg, func.HttpResponse(msg, status_code=500), ) return table_service
def sync_excel_blobs_and_az_tables(): table_service = TableService(account_name=Config.TABLE_ACCOUNT_NAME, account_key=Config.TABLE_KEY) if not table_service.exists(Config.PRODUCT_TABLE_NAME): create_table(table_service, Config.PRODUCT_TABLE_NAME) # TODO: Consider insert_or_merge_entity() here instead https://docs.microsoft.com/en-us/python/api/azure-cosmosdb-table/azure.cosmosdb.table.tableservice.tableservice?view=azure-python delete_all_entries_in_table(table_service, Config.PRODUCT_TABLE_NAME) batch = TableBatch() table_data = get_metadata_blob_data() products_data = get_product_blobs_data() for p in table_data: try: p["cumulative"] = str(products_data[p["id"]]["cumulative"]) except KeyError: p["cumulative"] = None batch.insert_entity(p) try: table_service.commit_batch(table_name=Config.PRODUCT_TABLE_NAME, batch=batch) print( f"Uploaded products data to '{Config.PRODUCT_TABLE_NAME}' table in '{Config.TABLE_ACCOUNT_NAME}' storage account" ) except HeaderParsingError as e: print(e)
def create_if_missing(self, table_service: TableService) -> None: try: self.disable_storage_client_logging() if not table_service.exists(TABLE_NAME): table_service.create_table(TABLE_NAME) finally: self.enable_storage_client_logging()
def tableStorage(table_name, partition_key, row_key, hins_processed, timesaved, time_by_system, time_by_user, requests): try: table_service = TableService( account_name=config.AZURE['STORAGE_ACCOUNT_NAME'], account_key=config.AZURE['STORAGE_ACCOUNT_KEY']) entity = { 'PartitionKey': partition_key, 'RowKey': row_key, 'HinsProcessed': hins_processed, 'TimeSaved': timesaved, 'TimeBySystem': time_by_system, 'TimeByUser': time_by_user, 'Requests': requests } if not table_service.exists(table_name, timeout=None): table_service.create_table(table_name, fail_on_exist=False) try: table_service.insert_entity(table_name, entity) print("Entity Doesn't Exist") print("Creating Entity\n") except Exception as e: print("Entity Exists") print("Updating entity\n") currentEntity = table_service.get_entity(table_name, partition_key, row_key) tempHinProcessed = currentEntity.HinsProcessed + hins_processed tempTimeSaved = currentEntity.TimeSaved + timesaved tempTimeBySystem = currentEntity.TimeBySystem + time_by_system tempTimeByUser = currentEntity.TimeByUser + time_by_user tempRequest = currentEntity.Requests + requests entity = { 'PartitionKey': partition_key, 'RowKey': row_key, 'HinsProcessed': tempHinProcessed, 'TimeSaved': tempTimeSaved, 'TimeBySystem': tempTimeBySystem, 'TimeByUser': tempTimeByUser, 'Requests': tempRequest } table_service.update_entity(table_name, entity, if_match='*', timeout=None) except Exception as e: print(e)
def create_table(table_service: TableService, table_name: str): if table_service.exists(table_name): """ When a table is successfully deleted, it is immediately marked for deletion and is no longer accessible to clients. The table is later removed from the Table service during garbage collection. Note that deleting a table is likely to take at least 40 seconds to complete. """ table_service.delete_table(table_name) print(f"Deleted table '{table_name}'") _wait_for_table_to_be_created(table_service, table_name)
def main(req: func.HttpRequest) -> func.HttpResponse: logging.info('Python HTTP trigger function processed a request.') KeyVault_DNS = os.environ["KeyVault_DNS"] SecretName = os.environ["SecretName"] table_name = req.headers.get('name') value = req.get_json() if table_name: try: # Try with managed identity, otherwise to with Service Principal creds = ManagedIdentityCredential() client = SecretClient(vault_url=KeyVault_DNS, credential=creds) retrieved_secret = client.get_secret(SecretName) except: creds = ClientSecretCredential( client_id=os.environ["SP_ID"], client_secret=os.environ["SP_SECRET"], tenant_id=os.environ["TENANT_ID"]) client = SecretClient(vault_url=KeyVault_DNS, credential=creds) retrieved_secret = client.get_secret(SecretName) table_service = TableService(connection_string=retrieved_secret.value) if table_service.exists(table_name): if 'PartitionKey' not in value.keys(): #This is mandatory value['PartitionKey'] = 'reference' if 'RowKey' not in value.keys(): #This is mandatory too value['RowKey'] = '001' try: table_service.update_entity(table_name=table_name, entity=value) except: table_service.insert_entity(table_name=table_name, entity=value) else: ret = dict() ret['result'] = "Please create the table!" return func.HttpResponse(json.dumps(ret), status_code=400) ret = dict() ret['result'] = "Success" return func.HttpResponse(json.dumps(ret), status_code=200) else: ret = dict() ret['result'] = "Please pass a name!!" return func.HttpResponse(json.dumps(ret), status_code=400)
def put_object(obj, table_name='default', account_name='cloudmaticafunc9b4c', account_key='YOUR_KEY', partition_key='default', row_key=None): table_service = TableService(account_name=account_name, account_key=account_key) if not table_service.exists(table_name): table_service.create_table(table_name) if not row_key: row_key = str(uuid.uuid4()) obj['PartitionKey'] = partition_key obj['RowKey'] = row_key table_service.insert_or_replace_entity(table_name, obj) return obj
class AzureTable(): def __init__(self, account_name, account_key): self.table_service = TableService(account_name=account_name, account_key=account_key) def create_table(self, table_name): return self.table_service.create_table(table_name) def exists_table(self, table_name): return self.table_service.exists(table_name) def insert_or_replace_entity(self, table_name, partition_key, row_key, **kwargs): try: entity = self.table_service.get_entity(table_name, partition_key, row_key) except Exception: # Insert a new entity entity = {'PartitionKey': partition_key, 'RowKey': row_key} for (k, v) in kwargs.items(): entity[k] = v return self.table_service.insert_or_replace_entity(table_name, entity) def insert_or_replace_entity2(self, table_name, entity): return self.table_service.insert_or_replace_entity(table_name, entity) def insert_entity(self, table_name, entity): return self.table_service.insert_entity(table_name, entity) def update_entity(self, table_name, entity): return self.table_service.update_entity(table_name, entity) def get_entity(self, table_name, partition_key, row_key): return self.table_service.get_entity(table_name, partition_key, row_key) def delete_entity(self, table_name, partition_key, row_key): self.table_service.delete_entity(table_name, partition_key, row_key) def delete_table(self, table_name): return self.table_service.delete_table(table_name) def get_entities(self, table_name, partition_key): filter = "PartitionKey eq '{0}'".format(partition_key) return self.table_service.query_entities(table_name, filter)
class AzureTableStorageHandler(logging.Handler): """ A handler class which writes formatted logging records to Azure Table Storage. """ def __init__(self, account_name, account_key, table_name, *, level=logging.NOTSET): """ Setup TableService and the specified table for logging. """ super().__init__(level=level) self.table_service = TableService(account_name=account_name, account_key=account_key) self.table_name = table_name if not self.table_service.exists(self.table_name): self.table_service.create_table(self.table_name) self.formatter = logging.Formatter("%(message)s") self.executor = ThreadPoolExecutor(max_workers=1, thread_name_prefix="AzHndlr") self.epoch_max = datetime(MAXYEAR, 12, 31, 23, 59, 59, 999999, timezone.utc).timestamp() def insert_log(self, record): """ Insert log to Azure Table Storage. """ entity = { "PartitionKey": record.name, "RowKey": str(self.epoch_max - time()), "LocalTimestamp": self.formatter.formatTime(record), "LevelName": record.levelname, "Level": record.levelno, "Message": self.format(record) } self.table_service.insert_entity(self.table_name, entity) def emit(self, record): """ Emit a record. This method just submit the logging task to worker thread and return immediately. """ self.executor.submit(self.insert_log, record)
def main(req: func.HttpRequest) -> func.HttpResponse: logging.info('Starting insert row.') table_name = req.headers.get('name') if not table_name: #If name wasnt added as header, search for it in the parameters table_name = req.params.get('name') value = req.get_json() if table_name: retrieved_secret = getConnectionString() table_service = TableService(connection_string=retrieved_secret.value) if table_service.exists(table_name): if 'PartitionKey' not in value.keys(): #This is mandatory value['PartitionKey'] = 'reference' if 'RowKey' not in value.keys(): #This is mandatory too value['RowKey'] = '001' try: table_service.update_entity(table_name=table_name, entity=value) except: table_service.insert_entity(table_name=table_name, entity=value) else: ret = dict() ret['result'] = "Please create the table!" return func.HttpResponse(json.dumps(ret), status_code=400) ret = dict() ret['result'] = "Success" return func.HttpResponse(json.dumps(ret), status_code=200) else: ret = dict() ret['result'] = "Please pass a name!!" return func.HttpResponse(json.dumps(ret), status_code=400)
def main(req: func.HttpRequest) -> func.HttpResponse: logging.info('Python HTTP trigger function processed a request.') # Connect to Azure Table Storage table_service = TableService( connection_string=os.environ['AzureWebJobsStorage']) table_service.create_table( 'intents') if not table_service.exists('intents') else None # Retrieve entities from Azure Table Storage entries = list(table_service.query_entities('intents')) # Convert to pandas dataframe and output as csv dataframe = pd.DataFrame(entries) list_of_dictionaries = dataframe.to_dict('records') # Set HTTP Response header and mimetype mimetype = 'text/csv' headers = {'Content-Disposition': 'attachment; filename="export.csv"'} # Set response body body = dataframe.to_csv(index=False) return func.HttpResponse(body=body, mimetype=mimetype, headers=headers)
def main(mytimer: func.TimerRequest) -> None: utc_timestamp = datetime.datetime.utcnow().replace( tzinfo=datetime.timezone.utc).isoformat() if mytimer.past_due: logging.info('The timer is past due...') logging.info('Fetching data from IdentityNow at %s', utc_timestamp) url = f'https://{tenant_id}.api.identitynow.com/oauth/token' new_checkpoint_time = (datetime.datetime.utcnow() - datetime.timedelta(minutes=60)).isoformat() + "Z" checkpoint_table_name = 'checkpointTable' table_service = TableService(account_name=storage_account_name, account_key=access_key) task = { 'PartitionKey': 'checkpointTime', 'RowKey': '001', 'createdTime': new_checkpoint_time } table_exists = table_service.exists(checkpoint_table_name) # Check if table already exists, if yes- get existing checkpoint time from the table entry. # If not then create table and insert the row containing new checkpoint time. if not table_exists: table_service.create_table(checkpoint_table_name) table_service.insert_entity(checkpoint_table_name, task) checkpoint_time = new_checkpoint_time else: returned_entity = table_service.get_entity(checkpoint_table_name, 'checkpointTime', '001') checkpoint_time = returned_entity.createdTime if use_current(new_checkpoint_time, checkpoint_time): checkpoint_time = new_checkpoint_time tokenparams = { 'grant_type': grant_type, 'client_id': client_id, 'client_secret': client_secret } oauth_response = requests.request("POST", url=url, params=tokenparams) if oauth_response is not None: try: oauth_response.raise_for_status() access_token = oauth_response.json()["access_token"] headers = { 'Content-Type': 'application/json', 'Authorization': "Bearer " + access_token } except (HTTPError, KeyError, ValueError): logging.error("No access token received..." + str(oauth_response.status_code)) return 0 partial_set = False audit_events = [] # Search API results are slightly delayed, allow for 5 minutes though in reality. # This time will be much shorter. Cap query at checkpoint time to 5 minutes ago. search_delay_time = (datetime.datetime.utcnow() - datetime.timedelta(minutes=60)).isoformat() + "Z" # Number of Events to return per call to the search API. limit = int(os.environ["LIMIT"]) while True: if partial_set == True: break # Standard query params, but include limit for result set size. queryparams = {"count": "true", "offset": "0", "limit": limit} query_checkpoint_time = checkpoint_time.replace('-', '\\-').replace( '.', '\\.').replace(':', '\\:') query_search_delay_time = search_delay_time.replace( '-', '\\-').replace('.', '\\.').replace(':', '\\:') logging.info( f'checkpoint_time {query_checkpoint_time} search_delay_time {query_search_delay_time}' ) # Search criteria - retrieve all audit events since the checkpoint time, sorted by created date searchpayload = { "queryType": "SAILPOINT", "query": { "query": f"created:>{query_checkpoint_time} AND created:<{query_search_delay_time}" }, "queryResultFilter": {}, "sort": ["created"], "searchAfter": [] } audit_url = f'https://{tenant_id}.api.identitynow.com/v3/search/events' # Initiate request audit_events_response = requests.request("POST", url=audit_url, params=queryparams, json=searchpayload, headers=headers) # API Gateway saturated / rate limit encountered. Delay and try again. Delay will either be dictated by IdentityNow server response or 5000 seconds if audit_events_response.status_code == 429: retryDelay = 5000 retryAfter = audit_events_response.headers['Retry-After'] if retryAfter is not None: retryDelay = int(retryAfter) logging.warning( f'429 - Rate Limit Exceeded, retrying in: {retryDelay}') time.sleep(retryDelay) elif audit_events_response.ok: # Check response headers to get toal number of search results - if this value is 0 there is nothing to parse, if it is less than the limit value then we are caught up to most recent, and can exit the query loop x_total_count = int(audit_events_response.headers['X-Total-Count']) if x_total_count > 0: try: if x_total_count < int(limit): # Less than limit returned, caught up so exit. partial_set = True results = audit_events_response.json() # Add this set of results to the audit events array audit_events.extend(results) current_last_event = audit_events[-1] checkpoint_time = current_last_event['created'] except KeyError: logging.info("Response does not contain items...") break else: # Set partial_set to True to exit loop (no results) partial_set = True else: logging.info( f'Failure from server... " {audit_events_response.status_code}' ) # Forced Exit return 0 # Iterate the audit events array and create events for each one. if len(audit_events) > 0: for audit_event in audit_events: data_json = json.dumps(audit_event) table_name = "SailPointIDN_Events" try: post_data(customer_id, shared_key, data_json, table_name, logAnalyticsUri) except Exception as error: logging.error("Unable to send data to Azure Log...") logging.error(error) # Get the created date of the last AuditEvent in this run and save it as the checkpoint time in the table. last_event = audit_events[-1] new_checkpoint_time = last_event['created'] # Create an entry with new checkpoint time. task = { 'PartitionKey': 'checkpointTime', 'RowKey': '001', 'createdTime': new_checkpoint_time } # Write new checkpoint time back to the table. table_service.insert_or_replace_entity(checkpoint_table_name, task) logging.info("Table successfully updated...") else: logging.info("No Events were returned...")
class Db(object): ts = None def __init__(self): """Init connection with cosmosdb""" self.ts = TableService(account_name=ACCOUNT_NAME, account_key=ACCOUNT_KEY) def migrate(self): """ Create tabel if not exists""" if not self.ts.exists(USER_TABLE_NAME): self.ts.create_table(USER_TABLE_NAME) if not self.ts.exists(MESSAGE_TABLE_NAME): self.ts.create_table(MESSAGE_TABLE_NAME) def get_all_users(self): """select email from user""" return [ i['PartitionKey'] for i in self.ts.query_entities(USER_TABLE_NAME) ] def create_user(self, data=None): bjson = ep( EdmType.BINARY, dumps({ 'email': data['email'], 'password': sha224(bytes(data['password'], encoding='utf-8')).hexdigest(), 'full_name': data['full_name'] })) user = { 'PartitionKey': data['email'], 'RowKey': row_key, 'info': bjson } if (self.ts.insert_or_replace_entity(USER_TABLE_NAME, user)): return {'success': True} def delete_user(self, email=None): if (self.ts.delete_entity(USER_TABLE_NAME, email, row_key)): return {'success': True} def create_message(self, email=None, message=None): """ Create message in protobuf""" proto_message = message_pb2.Message() proto_message.title = message['title'] proto_message.content = message['content'] proto_message.magic_number = message['magic_number'] details = ep(EdmType.BINARY, str(proto_message)) bmessage = { 'PartitionKey': email, 'RowKey': row_key, 'details': details, } if (self.ts.insert_or_replace_entity(MESSAGE_TABLE_NAME, bmessage)): return {'success': True} def get_user(self, email=''): return self.ts.get_entity(USER_TABLE_NAME, email, row_key) def get_message(self, email=''): return self.ts.get_entity(MESSAGE_TABLE_NAME, email, row_key) def get_messages(self): messages = self.ts.query_entities(MESSAGE_TABLE_NAME) return list(messages)
from azure.cosmosdb.table.tableservice import TableService from azure.cosmosdb.table.models import Entity table_service = TableService( account_name='az532rg1diag572', account_key= 'coixsNqJU6lbm2w9yCWh+fODv+NqAFjgV+jHRy4zkZX8ywkrJ+nfawSPNCF0tgzOh8FstVAG4tUu/pOeDwfLEQ==' ) if table_service.exists('tasktable'): task = { 'PartitionKey': 'tasksSeattle', 'RowKey': '001', 'description': 'Take out the garbage.', 'priority': 250 } table_service.update_entity('tasktable', task) # Replace the entity created earlier task = { 'PartitionKey': 'tasksSeattle', 'RowKey': '001', 'description': 'Take out the garbage again...', 'priority': 250 } table_service.insert_or_replace_entity('tasktable', task) #Insert a new entity task = { 'PartitionKey': 'tasksTigard', 'RowKey': '001',
class DataControl: def __init__(self): self.table_service = TableService(account_name='toonestorage01', account_key='sIo3TKwG40eH2a9MpjZdGgWwetkGDV3NcgFhwZN2sFerhrj3kWLTiKQO3wGO7bd9sjmGoBnPl2CbqrIJcsnG8g==') #table_service = TableService(connection_string='DefaultEndpointsProtocol=https;AccountName=toonestorage01;AccountKey=sIo3TKwG40eH2a9MpjZdGgWwetkGDV3NcgFhwZN2sFerhrj3kWLTiKQO3wGO7bd9sjmGoBnPl2CbqrIJcsnG8g==;EndpointSuffix=core.windows.net') #SOURCE_TABLE = "stockday" ########################################################################################################################### def set_table(self, tabletype, tooneday): if tabletype == 'D': table_name = 'stock' + tabletype + tooneday #오늘 날짜 분석 테이블이 존재 하면 삭제 하고 새로 생성 / Azure Table은 삭제 후 재 생성 불가 (delay 발생) print(table_name) if self.table_service.exists(table_name) == False: self.table_service.create_table(table_name) print(">> table created : " + table_name ) else : print(">> table exists " + table_name) elif tabletype =='5M': print(">>> 5M table") return table_name def set_target(self, targetdf): prep_df = preprocess(targetdf) print ( prep_df ) ########################################################################################################################### def set_stock_day(self, targettable, stockdf): # print('>> insert data to azure table') ''' for data in stockdata: task = {'PartitionKey': 'tasksSeattle', 'RowKey': '001','description': 'Take out the trash', 'priority': 200} self.table_service.insert_entity('stockday', task) ''' # dataframe에서 partitionkey 및 row_id 로 칼럼명 변경 필요 # key 값을 두개만 가질 수 있음 # particionkey = code / date = row_id #print (stockdf.head()) stockdf_table = stockdf.rename(columns={"code": "PartitionKey", "date": "RowKey"}) #print (stockdf_table) for index, row in stockdf_table.iterrows(): #print(row) #print(row['PartitionKey']) #print(">> start row") #print(row) task = Entity() task.PartitionKey = row.to_dict()['PartitionKey'] task.RowKey = str(row.to_dict()['RowKey']) task.open = row.to_dict()['open'] task.high = row.to_dict()['high'] task.low = row.to_dict()['low'] task.close = row.to_dict()['close'] task.volume = row.to_dict()['volume'] self.table_service.insert_or_merge_entity(targettable, task, timeout=None) #print('>> end set stockday') def get_stock_day(self, code): print('>> start get data ') # 여러 데이타 추출 target_table = 'stockD' + time.strftime('%Y%m%d') filter_target = "PartitionKey eq '" + code+ "'" rows = self.table_service.query_entities( target_table , filter=filter_target, select='PartitionKey,RowKey,open,high,low,volume,close') df_stock_day = pd.DataFrame(rows) #print(df_stock_day.head()) return df_stock_day def set_stock_min(self, stockdf): # print('>> insert data to azure table') #print (stockdf.head()) stockdf_table = stockdf.rename(columns={"code": "PartitionKey", "date": "RowKey"}) #print (stockdf_table) stockdf_table = stockdf_table.astype({"time":str, "RowKey": str}) # date column을 time 컬럼과 합성 if len(str(stockdf_table["time"])) <= 3: stockdf_table["time"] = '0' + str(stockdf_table["time"]) print(stockdf_table.head()) stockdf_table["RowKey"] = stockdf_table["RowKey"] + stockdf_table["time"] print(stockdf_table.head()) stockdf_last = pd.DataFrame() stockdf_last = stockdf_table[ ['PartitionKey', 'RowKey', 'time', 'open', 'high', 'low', 'close', 'volume'] ] print(stockdf_last) for index, row in stockdf_last.iterrows(): task = Entity() task.PartitionKey = row.to_dict()['PartitionKey'] task.RowKey = str(row.to_dict()['RowKey']) task.time = row.to_dict()['time'] task.open = row.to_dict()['open'] task.high = row.to_dict()['high'] task.low = row.to_dict()['low'] task.close = row.to_dict()['close'] task.volume = row.to_dict()['volume'] #print(task) self.table_service.insert_or_merge_entity('stockM', task, timeout=None) ########################################################################################################################### def get_max_date(self, stockcode): filter_str = "PartitionKey eq '" + stockcode +"'" rows = self.table_service.query_entities( 'stockday', filter=filter_str, select='open,close') #print (rows.RowKey) for row in rows: print (row) return row def get_max_time(self, stockcode): filter_str = "RowKey eq '" + stockcode +"'" rows = self.table_service.query_entities( 'stockM', filter=filter_str, select='open,close') #print (rows.RowKey) for row in rows: print (row) return row def set_target_stock(self,df_target): # ['price','volume', 'per','eps'] df_target["date"]=time.strftime('%Y%m%d') stockdf_table = df_target.rename(columns={"date": "PartitionKey", "code": "RowKey"}) for index, row in stockdf_table.iterrows(): #print(row) #print(row['PartitionKey']) #print(">> start row") #print(row) task = Entity() task.PartitionKey = row.to_dict()['PartitionKey'] task.RowKey = str(row.to_dict()['RowKey']) task.price = row.to_dict()['price'] task.volume = row.to_dict()['volume'] task.per = row.to_dict()['per'] task.eps = row.to_dict()['eps'] self.table_service.insert_or_merge_entity('stocktarget', task) print(">> set target stock..." + str(row.to_dict()['RowKey']) ) def get_target_stock(self): filter_target = "PartitionKey eq '" + time.strftime('%Y%m%d')+ "'" rows = self.table_service.query_entities( 'stocktarget', filter= filter_target, select='RowKey, status') #print (rows.RowKey) df_target = pd.DataFrame(rows) return df_target
def main(req: func.HttpRequest) -> func.HttpResponse: logging.info('Python HTTP trigger function processed a request.') startDate = req.params.get('startDate') endDate = req.params.get('endDate') currency = req.params.get('currency') if not (startDate and endDate): try: req_body = req.get_json() except ValueError: return func.HttpResponse('error') else: startDate = req_body.get('startDate') endDate = req_body.get('endDate') if (startDate and endDate): positives = [ 'top', 'rise', 'stable', 'bullish', 'rally', 'spike', 'bull', 'surge', 'surges' ] negatives = ['fall', 'drop', 'unstable', 'tank', 'panic', 'bearish'] table_service = TableService( account_name='sauokgp', account_key= '113mdwUqIiqt4K2HonK80HakIOplxYZINmQME5KB1IZfP+v3JHZK64wpoTP5NBFaG0MaO/TVqA0nW4KuCINTow==' ) #creates Reddit table if one doesn't already exist if not (table_service.exists('Reddit')): table_service.create_table('Reddit', fail_on_exist=False) reddit = praw.Reddit(client_id='sCanLl76vO0ExA', client_secret='54qOmHpy2PBRLTVs8soyBhif42A', user_agent='CryptoCollector') api = PushshiftAPI(reddit) startDate = datetime.strptime(startDate, "%Y-%m-%d") endDate = datetime.strptime(endDate, "%Y-%m-%d") posts = [] while startDate < endDate: d1 = int(time.mktime(startDate.timetuple())) d2 = int(time.mktime((startDate + timedelta(days=1)).timetuple())) gen = api.search_submissions(before=d2, after=d1, subreddit=currency, limit=5, sort_type='score') results = list(gen) #creates Reddit table if one doesn't already exist if not (table_service.exists('Reddit')): table_service.create_table('Reddit', fail_on_exist=False) highest_polarity = -1 lowest_polarity = 1 total_polarity = 0 total_subjectivity = 0 count = 0 for x in results: submission = reddit.submission(id=x.id) sentiment = TextBlob(submission.title) polarity = sentiment.sentiment.polarity subjectivity = sentiment.sentiment.subjectivity print(submission.title) for x in positives: positive = submission.title.count(x) for x in negatives: negative = submission.title.count(x) if positive > negative: polarity = 1 if negative > positive: polarity = -1 if polarity != 0: total_polarity += polarity total_subjectivity += subjectivity count += 1 print('=============================') print('Total Polarity: ' + str(total_polarity)) print('Number of Posts: ' + str(count)) print('=============================') if count != 0: total_polarity = total_polarity / count print('Reddit Polarity Rating: ' + str(total_polarity)) print('=============================') post = { 'PartitionKey': currency, 'RowKey': str(datetime.now()), 'Polarity': total_polarity, 'Subjectivity': total_subjectivity, 'Date': str(startDate) } posts.append(post) table_service.insert_entity('Reddit', post) startDate = startDate + timedelta(days=1) #return func.HttpResponse(posts) return str(posts) else: return func.HttpResponse( "Please pass the correct parameters on the query string or in the request body", status_code=400)
def configure_front_end(): try: table_service = TableService(account_name=STORAGE_ACCT_NAME, account_key=STORAGE_ACCT_KEY) except: print( "Error: Could not connect to table service. Check STORAGE_ACCT_NAME and STORAGE_ACCT_KEY" ) return if (not table_service.exists(TABLE_NAME_CONFIGURATION)): print("Error: Could not find configuration table: %s" % (TABLE_NAME_CONFIGURATION)) return taps = table_service.query_entities(TABLE_NAME_CONFIGURATION) for tap in taps: print("") print("%s scale-%s" % (tap['PartitionKey'], tap['RowKey'])) print("---------------------------------") for key in tap: if key in table_data and table_data[key][ 'type'] != 'drop' and table_data[key]['type'] != 'hide': if key in tap: print(" %s: %s" % (table_data[key]['description'], tap[key])) else: print(" %s:" % (table_data[key]['description'])) answer = input("Update tap info [y/N]? ") if not answer: answer = 'N' if answer.lower() == "y" or answer.lower == "yes": newdata = Entity() for key in table_data: prompt = True if table_data[key]['type'] != 'drop': update = None if table_data[key]['type'] == 'hide': update = tap[key] prompt = False else: if key in tap: existing = tap[key] else: existing = '' if prompt: update = input( " %s: [%s]: " % (table_data[key]['description'], existing)) if (not update): update = existing if table_data[key]['type'] == 'hide': newdata[key] = update elif table_data[key]['type'] == 'int': newdata[key] = int(update) elif table_data[key]['type'] == 'double': newdata[key] = float(update) elif table_data[key]['type'] == 'string': newdata[key] = str(update) else: print("Error: uknown data type: %s" % table_data[key]['type']) print("Updated record: ") print(newdata) table_service.update_entity(TABLE_NAME_CONFIGURATION, newdata)
import logging import azure.functions as func from azure.cosmosdb.table.tableservice import TableService import requests import json import os imageScannerTable = "IoTEdgeImageClassifier" storageConnectionString = os.environ['StorageConnectionString'] partitionKey = os.environ['PartitionKey'] signalrUrl = os.environ['SignalrUrl'] table_service = TableService(connection_string=storageConnectionString) if not table_service.exists(imageScannerTable): table_service.create_table(imageScannerTable) def main(event: func.EventHubEvent): messages = json.loads(event.get_body().decode('utf-8')) for msg in messages: sortResponse = sorted(msg, key=lambda k: k['probability'], reverse=True)[0] sortResponse['PartitionKey'] = partitionKey sortResponse['RowKey'] = sortResponse['tagName']
# TODO only debug ''' os.environ["ACCOUNT_NAME"] = "account_name" os.environ["ACCOUNT_KEY"] = "account_key" os.environ["TABLE_NAME"] = "table_name" os.environ["SECRET_KEY"] = "secret_key" ''' # initiate the table service table_service = TableService(account_name=os.environ['ACCOUNT_NAME'], account_key=os.environ['ACCOUNT_KEY']) # if the table does not exsits than create it tableName = os.environ['TABLE_NAME'] if not table_service.exists(tableName): table_service.create_table(tableName) # read unsure data (used by feedback engine) unsureDf = pd.read_csv("api/data/unsure_data/to_provide_examples.tsv", sep="\t", dtype={"CODE": "string"}) query_terms = "SELECT termCode, termExtendedName FROM term WHERE termCode IN (%s) LIMIT 100" query_attrs = "SELECT code, label FROM attribute WHERE code IN (%s) LIMIT 100" def join_intepretation(codes, descs): ''' return the interpretation joined by delimiters ''' res = "" for index, code in enumerate(codes): if index == 0:
class AzureOperationsStorage(BasicOperationStorage): """ Implementation of :class:`.interface.IOperationStorage` with Azure Table Storage using the default implementation :class:`.interface.BasicOperationStorage` On creating a connection to the storage is initialized and all needed tables are created. If a purge is necessary, tables are not deleted but simple the content removed. Table creation can take a while with Azure Table Storage. As Azure Table Storage only supports two indices, the operations are inserted multiple times in different tables to enable multi-index queries. """ def get_retry_exceptions(self): return (NewConnectionError) @retry_auto_reconnect def __init__(self, azure_config, purge=False): super(AzureOperationsStorage, self).__init__() if not azure_config: raise Exception("No azure table storage configuration provided!") self._azure_config = azure_config # ensure defaults self._azure_config["operation_table"] = self._azure_config.get( "operation_table", "operations") self._azure_config["address_table"] = self._azure_config.get( "address_table", "address") self._azure_config["status_table"] = self._azure_config.get( "status_table", "status") self._azure_config["balances_table"] = self._azure_config.get( "balances_table", "balances") if not self._azure_config["account"]: raise Exception( "Please include the azure account name in the config") if not self._azure_config["key"]: raise Exception( "Please include the azure account key in the config") self._service = TableService( account_name=self._azure_config["account"], account_key=self._azure_config["key"]) # if tables doesnt exist, create it self._create_operations_storage(purge) self._create_status_storage(purge) self._create_address_storage(purge) self._create_balances_storage(purge) def _debug_print(self, operation): from pprint import pprint pprint(operation) def _create_address_storage(self, purge): _varients = ["balance", "historyfrom", "historyto"] for variant in _varients: tablename = self._azure_config["address_table"] + variant if purge: try: for item in self._service.query_entities(tablename): self._service.delete_entity(tablename, item["PartitionKey"], item["RowKey"]) except AzureHttpError: pass except AzureMissingResourceHttpError: pass while not self._service.exists(tablename): self._service.create_table(tablename) time.sleep(0.1) def _create_status_storage(self, purge): if purge: try: tablename = self._azure_config["status_table"] for item in self._service.query_entities(tablename): self._service.delete_entity(tablename, item["PartitionKey"], item["RowKey"]) except AzureMissingResourceHttpError: pass while not self._service.exists(self._azure_config["status_table"]): self._service.create_table(self._azure_config["status_table"]) time.sleep(0.1) def _create_balances_storage(self, purge): if purge: try: tablename = self._azure_config["balances_table"] for item in self._service.query_entities(tablename): self._service.delete_entity(tablename, item["PartitionKey"], item["RowKey"]) except AzureMissingResourceHttpError: pass while not self._service.exists(self._azure_config["balances_table"]): self._service.create_table(self._azure_config["balances_table"]) time.sleep(0.1) def _create_operations_storage(self, purge): self._operation_varients = [ "incident", "statuscompleted", "statusfailed", "statusinprogress" ] # "customer" self._operation_tables = {} for variant in self._operation_varients: self._operation_tables[ variant] = self._azure_config["operation_table"] + variant self._operation_prep = { "statusinprogress": lambda op: { "PartitionKey": self._short_digit_hash(op["chain_identifier"]), "RowKey": op["chain_identifier"] }, "statuscompleted": lambda op: { "PartitionKey": self._short_digit_hash(op["chain_identifier"]), "RowKey": op["chain_identifier"] }, "statusfailed": lambda op: { "PartitionKey": self._short_digit_hash(op["chain_identifier"]), "RowKey": op["chain_identifier"] }, "customer": lambda op: { "PartitionKey": op["customer_id"], "RowKey": op["chain_identifier"] }, "incident": lambda op: { "PartitionKey": self._short_digit_hash(op["incident_id"]), "RowKey": op["incident_id"] } } for variant in self._operation_varients: if purge: try: tablename = self._operation_tables[variant] for item in self._service.query_entities(tablename): self._service.delete_entity(tablename, item["PartitionKey"], item["RowKey"]) except AzureMissingResourceHttpError: pass while not self._service.exists(self._operation_tables[variant]): self._service.create_table(self._operation_tables[variant]) time.sleep(0.1) def _get_with_ck(self, variant, operation): with_ck = operation.copy() with_ck.update(self._operation_prep[variant](with_ck)) return with_ck def _short_digit_hash(self, value): hash_type = Config.get("operation_storage", "key_hash", "type", default="crc32") if hash_type == "crc32": short_hash = hex(zlib.crc32(value.encode(encoding='UTF-8'))) short_hash = short_hash[2:len(short_hash)] elif hash_type == "sha256": checker = hashlib.sha256() checker.update(value.encode(encoding='UTF-8')) short_hash = checker.hexdigest() return short_hash[0:Config. get("operation_storage", "key_hash", "digits", 3)] @retry_auto_reconnect def track_address(self, address, usage="balance"): address = ensure_address_format(address) try: short_hash = self._short_digit_hash(address) logging.getLogger(__name__).debug("track_address with " + str(address) + ", hash " + str(short_hash)) self._service.insert_entity( self._azure_config["address_table"] + usage, { "PartitionKey": short_hash, "RowKey": address, "address": address, "usage": usage }) except AzureConflictHttpError: raise AddressAlreadyTrackedException @retry_auto_reconnect def untrack_address(self, address, usage="balance"): address = ensure_address_format(address) try: short_hash = self._short_digit_hash(address) logging.getLogger(__name__).debug("untrack_address with " + str(address) + ", hash " + str(short_hash)) self._service.delete_entity( self._azure_config["address_table"] + usage, short_hash, address) try: self._delete_balance(address) except AzureMissingResourceHttpError: pass except AzureMissingResourceHttpError: raise AddressNotTrackedException() @retry_auto_reconnect def _get_address(self, address, usage="balance"): try: short_hash = self._short_digit_hash(address) logging.getLogger(__name__).debug("_get_address with " + str(address) + ", hash " + str(short_hash)) return self._service.get_entity( self._azure_config["address_table"] + usage, short_hash, address) except AzureMissingResourceHttpError: raise AddressNotTrackedException() def _update(self, operation, status=None): try: mapping = { "in_progress": "statusinprogress", "completed": "statuscompleted", "failed": "statusfailed" } operation = self._get_with_ck("incident", operation.copy()) new_operation = operation if status: tmp = self.get_operation(operation["incident_id"]) new_operation["timestamp"] = tmp["timestamp"] new_operation["status"] = status new_operation = self._get_with_ck("incident", new_operation) logging.getLogger(__name__).debug( "_update: Table " + self._operation_tables["incident"] + " PartitionKey " + new_operation["PartitionKey"] + " " + new_operation["RowKey"]) self._service.update_entity(self._operation_tables["incident"], new_operation) operation = self._get_with_ck("statuscompleted", operation.copy()) new_operation = operation if status: tmp = self.get_operation(operation["incident_id"]) new_operation["timestamp"] = tmp["timestamp"] new_operation["status"] = status new_operation = self._get_with_ck("statuscompleted", new_operation) self._service.update_entity( self._operation_tables["statuscompleted"], new_operation) logging.getLogger(__name__).debug( "_update: Table " + self._operation_tables["statuscompleted"] + " PartitionKey " + new_operation["PartitionKey"] + " " + new_operation["RowKey"]) if status: # needs delete and insert try: self._service.delete_entity( self._operation_tables[mapping[operation["status"]]], operation["PartitionKey"], operation["RowKey"]) except AzureMissingResourceHttpError: pass try: self._service.insert_entity( self._operation_tables[mapping[ new_operation["status"]]], new_operation) except AzureConflictHttpError: # already exists, try update self._service.update_entity( self._operation_tables[mapping[ new_operation["status"]]], new_operation) else: self._service.update_entity( self._operation_tables[mapping[new_operation["status"]]], new_operation) except AzureMissingResourceHttpError: raise OperationNotFoundException() def _insert(self, operation): try: for variant in self._operation_varients: to_insert = operation.copy() to_insert.update(self._operation_prep[variant](to_insert)) if not to_insert["PartitionKey"]: raise AzureMissingResourceHttpError() if not to_insert["RowKey"]: raise AzureMissingResourceHttpError() logging.getLogger(__name__).debug( "_insert: Table " + self._operation_tables[variant] + " PartitionKey " + to_insert["PartitionKey"] + " " + to_insert["RowKey"]) self._service.insert_entity(self._operation_tables[variant], to_insert) except AzureConflictHttpError: raise DuplicateOperationException() def _delete(self, operation): try: for variant in self._operation_varients: to_delete = operation.copy() to_delete.update(self._operation_prep[variant](to_delete)) self._service.delete_entity(self._operation_tables[variant], to_delete["PartitionKey"], to_delete["RowKey"]) except AzureMissingResourceHttpError: raise OperationNotFoundException() @retry_auto_reconnect def flag_operation_completed(self, operation): # do basics operation = super(AzureOperationsStorage, self).flag_operation_completed(operation) self._update(operation, status="completed") self._ensure_balances(operation) @retry_auto_reconnect def flag_operation_failed(self, operation, message=None): # do basics operation = super(AzureOperationsStorage, self).flag_operation_failed(operation) operation["message"] = message self._update(operation, status="failed") @retry_auto_reconnect def insert_operation(self, operation): # do basics operation = super(AzureOperationsStorage, self).insert_operation(operation) error = None try: self._insert(operation) except DuplicateOperationException as e: error = e try: # always check if balances are ok if operation["status"] == "completed": self._ensure_balances(operation) except BalanceConcurrentException as e: if error is None: error = e if error is not None: raise error @retry_auto_reconnect def _delete_balance(self, address, if_match='*'): self._service.delete_entity(self._azure_config["balances_table"], self._short_digit_hash(address), address, if_match=if_match) @retry_auto_reconnect def _ensure_balances(self, operation): affected_address = get_tracking_address(operation) logging.getLogger(__name__).debug("_ensure_balances: with " + operation["chain_identifier"] + " for address " + str(affected_address)) try: self._get_address(affected_address) except AddressNotTrackedException: # delte if exists and return try: self._delete_balance(affected_address) except AzureMissingResourceHttpError: pass return try: balance_dict = self._service.get_entity( self._azure_config["balances_table"], self._short_digit_hash(affected_address), affected_address) insert = False except AzureMissingResourceHttpError as e: balance_dict = {"address": affected_address} balance_dict["PartitionKey"] = self._short_digit_hash( balance_dict["address"]) balance_dict["RowKey"] = balance_dict["address"] insert = True if operation["block_num"] < balance_dict.get("blocknum", 0): raise BalanceConcurrentException() elif operation["block_num"] == balance_dict.get("blocknum", 0) and\ operation["txnum"] < balance_dict.get("txnum", 0): raise BalanceConcurrentException() elif operation["block_num"] == balance_dict.get("blocknum", 0) and\ operation["txnum"] == balance_dict.get("txnum", 0) and\ operation["opnum"] <= balance_dict.get("opnum", 0): raise BalanceConcurrentException() balance_dict["blocknum"] = max(balance_dict.get("blocknum", 0), operation["block_num"]) balance_dict["txnum"] = max(balance_dict.get("txnum", 0), operation["tx_in_block"]) balance_dict["opnum"] = max(balance_dict.get("opnum", 0), operation["op_in_tx"]) total = 0 addrs = split_unique_address(affected_address) asset_id = "balance" + operation["amount_asset_id"].split("1.3.")[1] if addrs["account_id"] == operation["from"]: # internal transfer and withdraw # negative balance = balance_dict.get(asset_id, 0) balance_dict[asset_id] = balance - operation["amount_value"] # fee as well asset_id = operation["fee_asset_id"] balance = balance_dict.get(asset_id, 0) balance_dict[asset_id] = balance - operation["fee_value"] elif addrs["account_id"] == operation["to"]: # deposit # positive balance = balance_dict.get(asset_id, 0) balance_dict[asset_id] = balance + operation["amount_value"] # fees were paid by someone else else: raise InvalidOperationException() for key, value in balance_dict.items(): if key.startswith("balance"): total = total + value if total == 0: if not insert: try: self._delete_balance(affected_address, if_match=balance_dict.etag) except AzureMissingResourceHttpError: pass return # may be updated or inserted, total > 0 if (insert): try: self._service.insert_entity( self._azure_config["balances_table"], balance_dict) except AzureMissingResourceHttpError: raise OperationStorageException( "Critical error in database consistency") else: try: self._service.update_entity( self._azure_config["balances_table"], balance_dict, if_match=balance_dict.etag) except AzureConflictHttpError: raise OperationStorageException( "Critical error in database consistency") @retry_auto_reconnect def insert_or_update_operation(self, operation): # do basics operation = super(AzureOperationsStorage, self).insert_operation(operation) # check if this is from in_progress to complete (for withdrawals we need to find incident id as its # not stored onchain) try: logging.getLogger(__name__).debug( "insert_or_update_operation: check if in_progress with " + str(operation["chain_identifier"]) + " exists") existing_operation = self.get_operation_by_chain_identifier( "in_progress", operation["chain_identifier"]) logging.getLogger(__name__).debug( "insert_or_update_operation: found existing in_progress operation" ) if not existing_operation["incident_id"] == operation["incident_id"] and\ operation["incident_id"] == operation["chain_identifier"]: logging.getLogger(__name__).debug( "insert_or_update_operation: using preset incident_id " + str(existing_operation["incident_id"])) operation["incident_id"] = existing_operation["incident_id"] except OperationNotFoundException: existing_operation = None if existing_operation is None: try: logging.getLogger(__name__).debug( "insert_or_update_operation: attempting insert") error = None try: self._insert(operation) except DuplicateOperationException as e: error = e try: # always check if balances are ok if operation["status"] == "completed": self._ensure_balances(operation) except BalanceConcurrentException as e: if error is None: error = e if error is not None: raise error except DuplicateOperationException as ex: logging.getLogger(__name__).debug( "insert_or_update_operation: fallback to update") # could be an update to completed ... if operation.get("block_num"): try: operation.pop("status") self.flag_operation_completed(operation) except OperationNotFoundException: raise ex else: raise ex else: logging.getLogger(__name__).debug( "insert_or_update_operation: attempting update") if operation.get("block_num"): try: operation.pop("status") self.flag_operation_completed(operation) except OperationNotFoundException: raise ex @retry_auto_reconnect def delete_operation(self, operation_or_incident_id): # do basics operation = super(AzureOperationsStorage, self).delete_operation(operation_or_incident_id) if type(operation_or_incident_id) == str: operation = self.get_operation(operation_or_incident_id) else: operation = operation_or_incident_id self._delete(operation) @retry_auto_reconnect def get_operation_by_chain_identifier(self, status, chain_identifier): mapping = { "in_progress": "statusinprogress", "completed": "statuscompleted", "failed": "statusfailed" } try: operation = self._service.get_entity( self._operation_tables[mapping[status]], self._short_digit_hash(chain_identifier), chain_identifier) operation.pop("PartitionKey") operation.pop("RowKey") operation.pop("Timestamp") operation.pop("etag") except AzureMissingResourceHttpError: raise OperationNotFoundException() return operation @retry_auto_reconnect def get_operation(self, incident_id): try: short_hash = self._short_digit_hash(incident_id) logging.getLogger(__name__).debug("get_operation with " + str(incident_id) + ", hash " + str(short_hash)) operation = self._service.get_entity( self._operation_tables["incident"], short_hash, incident_id) operation.pop("PartitionKey") operation.pop("RowKey") operation.pop("Timestamp") operation.pop("etag") except AzureMissingResourceHttpError: raise OperationNotFoundException() return operation @retry_auto_reconnect def get_balances(self, take, continuation=None, addresses=None, recalculate=False): if recalculate: raise Exception( "Currently not supported due to memo change on withdraw") return self._get_balances_recalculate(take, continuation, addresses) else: if continuation is not None: try: continuation_marker = json.loads(continuation) except TypeError: raise InputInvalidException() except JSONDecodeError: raise InputInvalidException() balances = self._service.query_entities( self._azure_config["balances_table"], num_results=take, marker=continuation_marker) else: balances = self._service.query_entities( self._azure_config["balances_table"], num_results=take) return_balances = {} for address_balance in balances: return_balances[address_balance["address"]] = { "block_num": address_balance["blocknum"] } for key, value in address_balance.items(): if key.startswith("balance"): asset_id = "1.3." + key.split("balance")[1] return_balances[ address_balance["address"]][asset_id] = value return_balances["continuation"] = None if balances.next_marker: return_balances["continuation"] = json.dumps( balances.next_marker) return return_balances @retry_auto_reconnect def _get_balances_recalculate(self, take, continuation=None, addresses=None): address_balances = collections.defaultdict( lambda: collections.defaultdict()) if not addresses: if continuation is not None: try: continuation_marker = json.loads(continuation) except TypeError: raise InputInvalidException() except JSONDecodeError: raise InputInvalidException() addresses = self._service.query_entities( self._azure_config["address_table"] + "balance", num_results=take, marker=continuation_marker) else: addresses = self._service.query_entities( self._azure_config["address_table"] + "balance", num_results=take) if addresses.next_marker: address_balances["continuation"] = json.dumps( addresses.next_marker) addresses = [x["address"] for x in addresses] if type(addresses) == str: addresses = [addresses] for address in addresses: addrs = split_unique_address(address) max_block_number = 0 for operation in self.get_operations_completed( filter_by={"customer_id": addrs["customer_id"]}): this_block_num = operation["block_num"] asset_id = operation["amount_asset_id"] if addrs["account_id"] == operation["from"]: # negative balance = address_balances[address].get(asset_id, 0) address_balances[address][asset_id] =\ balance - operation["amount_value"] # fee as well asset_id = operation["fee_asset_id"] balance = address_balances[address].get(asset_id, 0) address_balances[address][asset_id] =\ balance - operation["fee_value"] elif addrs["account_id"] == operation["to"]: # positive balance = address_balances[address].get(asset_id, 0) address_balances[address][asset_id] =\ balance + operation["amount_value"] else: raise InvalidOperationException() max_block_number = max(max_block_number, this_block_num) if max_block_number > 0: address_balances[address]["block_num"] = max_block_number # do not return default dicts for key, value in address_balances.items(): if type(value) == collections.defaultdict: address_balances[key] = dict(value) return dict(address_balances) def _parse_filter(self, filter_by): if filter_by: if filter_by.get("customer_id"): return {"customer_id": filter_by.pop("customer_id")} if filter_by.get("address"): addrs = split_unique_address(filter_by.pop("address")) return {"customer_id": addrs["customer_id"]} if filter_by.get("from"): addrs = split_unique_address(filter_by.pop("from")) return {"from": addrs["account_id"]} if filter_by.get("to"): addrs = split_unique_address(filter_by.pop("to")) return {"to": addrs["account_id"]} if filter_by: raise Exception("Filter not supported") return {} def _filter_dict_to_string(self, filter_dict, partition_key=None): filter_str = None for key, value in filter_dict.items(): if partition_key == key: key = "PartitionKey" if filter_str is not None: delimiter = " and " delimiter = "" filter_str = delimiter + key + " eq '" + value + "'" return filter_str @retry_auto_reconnect def get_operations_in_progress(self, filter_by=None): mapping = { "in_progress": "statusinprogress", "completed": "statuscompleted", "failed": "statusfailed" } filter_dict = {} filter_dict.update(self._parse_filter(filter_by)) filter_str = self._filter_dict_to_string(filter_dict, "status") return list( self._service.query_entities( self._operation_tables[mapping["in_progress"]], filter_str)) @retry_auto_reconnect def get_operations_completed(self, filter_by=None): mapping = { "in_progress": "statusinprogress", "completed": "statuscompleted", "failed": "statusfailed" } filter_dict = {} filter_dict.update(self._parse_filter(filter_by)) filter_str = self._filter_dict_to_string(filter_dict, "status") return list( self._service.query_entities( self._operation_tables[mapping["completed"]], filter_str)) @retry_auto_reconnect def get_operations_failed(self, filter_by=None): mapping = { "in_progress": "statusinprogress", "completed": "statuscompleted", "failed": "statusfailed" } filter_dict = {} filter_dict.update(self._parse_filter(filter_by)) filter_str = self._filter_dict_to_string(filter_dict, "status") return list( self._service.query_entities( self._operation_tables[mapping["failed"]], filter_str)) @retry_auto_reconnect def get_last_head_block_num(self): try: document = self._service.get_entity( self._azure_config["status_table"], "head_block_num", "last") return document["last_head_block_num"] except AzureMissingResourceHttpError: return 0 @retry_auto_reconnect def set_last_head_block_num(self, head_block_num): current_last = self.get_last_head_block_num() if current_last >= head_block_num: raise Exception("Marching backwards not supported! Last: " + str(current_last) + " New: " + str(head_block_num)) self._service.insert_or_replace_entity( self._azure_config["status_table"], { "PartitionKey": "head_block_num", "RowKey": "last", "last_head_block_num": head_block_num })
class AzureTableDatabase(object): def __init__(self): self.connection = TableService(account_name=storage_account, account_key=table_connection_string) self.table_name = table_name def _update_entity(self, record): record.LastModified = datetime.now() self.connection.update_entity(self.table_name, record) def create_table(self): if not self.connection.exists(self.table_name): self.connection.create_table(self.table_name) def raw_table(self, limit=100): """ Retrieve a list of rows in the table. """ calls = self.connection.query_entities(self.table_name, num_results=limit) return calls def list_calls(self, limit=100, select='PartitionKey'): """ Retrieve a set of records that need a phone call """ calls = self.connection.query_entities(self.table_name, num_results=limit, select=select) return [c.PartitionKey for c in calls] def reset_stale_calls(self, time_limit): """ Retrieve calls that are not done and whose last modified time was older than the limit. """ records = self.connection.query_entities(self.table_name, filter="LastModified lt datetime'{0}' and Status ne '{1}'".format(time_limit.date(), Statuses.extracting_done)) if not records.items: raise NoRecordsToProcessError() num_records = len(records.items) for record in records: if 'LastErrorStep' in record: record.Status = record.LastErrorStep del record.LastErrorStep record.Status = Statuses.reset_map.get(record.Status, record.Status) self._update_entity(record) return num_records def retrieve_next_record_for_call(self): """ Retrieve a set of records that need a phone call """ records = self.connection.query_entities(self.table_name, num_results=1, filter="Status eq '{0}'".format(Statuses.new)) if len(records.items) == 0: raise NoRecordsToProcessError() record = records.items[0] record.Status = Statuses.calling self._update_entity(record) return record.PartitionKey def set_error(self, partition_key, step): """ Reset a row from error state """ record = self.connection.get_entity(self.table_name, partition_key, partition_key) record.Status = Statuses.error record['LastErrorStep'] = step self._update_entity(record) def retrieve_next_record_for_transcribing(self): records = self.connection.query_entities( self.table_name, num_results=1, filter="Status eq '{0}'".format(Statuses.recording_ready), ) if not records.items: raise NoRecordsToProcessError() record = records.items[0] record.Status = Statuses.transcribing self._update_entity(record) return record.CallUploadUrl, record.PartitionKey def update_transcript(self, partition_key, transcript, status): record = self.connection.get_entity( self.table_name, partition_key, partition_key, ) if status == TranscriptionStatus.success: record.CallTranscript = transcript record.Status = Statuses.transcribing_done record.TranscribeTimestamp = datetime.now() self._update_entity(record) elif status == TranscriptionStatus.request_error: self.set_error(partition_key, Statuses.transcribing) else: record.Status = Statuses.transcribing_failed self._update_entity(record) def change_status(self, original_status, new_status): records = self.connection.query_entities( self.table_name, filter="Status eq '{0}'".format(original_status), ) if not records.items: return for record in records.items: record.Status = new_status self.connection.update_entity(self.table_name, record) def query(self, column, value, limit=1): records = self.connection.query_entities(self.table_name, num_results=limit, filter="{0} eq '{1}'".format(column, value)) return records def retrieve_next_record_for_extraction(self): records = self.connection.query_entities(self.table_name, num_results=1, filter="Status eq '{0}'".format(Statuses.transcribing_done)) if not records.items: raise NoRecordsToProcessError() record = records.items[0] record.Status = Statuses.extracting self._update_entity(record) return record.CallTranscript, record.PartitionKey def retrieve_next_record_for_extraction(self): records = self.connection.query_entities(self.table_name, num_results=1, filter="Status eq '{0}'".format(Statuses.transcribing_done)) if not records.items: raise NoRecordsToProcessError() record = records.items[0] record.Status = Statuses.extracting self.connection.update_entity(self.table_name, record) return record.CallTranscript, record.PartitionKey def update_location_date(self, case_number, city, location_confidence, state, zipcode, date): record = self.connection.get_entity(self.table_name, case_number, case_number) record.City = city record.LocationConfidence = location_confidence record.State = state record.Zipcode = zipcode record.CourtHearingDate = date record.Status = Statuses.extracting_done self.connection.update_entity(self.table_name, record) def upload_new_requests(self, request_ids): """ Upload new request ids to the database """ for request_id in request_ids: record = {'PartitionKey': request_id, 'RowKey': request_id, 'Status': Statuses.new, 'LastModified': datetime.now()} try: self.connection.insert_entity(self.table_name, record) except AzureConflictHttpError: pass # already exists. silently ignore. def update_call_id(self, alien_registration_id, call_id): record = self.connection.get_entity(self.table_name, alien_registration_id, alien_registration_id) record.CallID = call_id record.Status = Statuses.calling record.CallTimestamp = datetime.now() self._update_entity(record) def update_azure_path(self, alien_registration_id, azure_path): record = self.connection.get_entity(self.table_name, alien_registration_id, alien_registration_id) record.Status = Statuses.recording_ready record.CallUploadUrl = azure_path self._update_entity(record) def delete_ain(self, ain): return self.connection.delete_entity(self.table_name, ain, ain) def get_ain(self, ain): return self.connection.get_entity(self.table_name, ain, ain)
import random import time from ..SharedCode import calibrate # https://azure.microsoft.com/en-au/blog/managing-concurrency-in-microsoft-azure-storage-2/ # https://docs.microsoft.com/en-us/python/api/azure-cosmosdb-table/azure.cosmosdb.table.tableservice.tableservice?view=azure-python deviceStateTable = "DeviceState" calibrationTable = "Calibration" storageConnectionString = os.environ['StorageConnectionString'] partitionKey = os.environ.get('PartitionKey', 'Sydney') signalrUrl = os.environ['SignalrUrl'] table_service = TableService(connection_string=storageConnectionString) if not table_service.exists(deviceStateTable): table_service.create_table(deviceStateTable) calibrator = calibrate.Calibrate(table_service, calibrationTable, partitionKey) # Optimistic Concurrency Tuning Parameters occBase = 40 # 40 milliseconds occCap = 1000 # 1000 milliseconds def main(event: func.EventHubEvent): messages = json.loads(event.get_body().decode('utf-8')) signalrUpdates = {} # Batch calibrate telemetry