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
