table_service = TableService(account_name='acc_name',
account_key='acc_key')
print(
table_service.get_entity('testAPPs',
'ComputerVision',
'instruct',
select='action').action)
# 無窮迴圈
while True:
# 如果 Table 的 action 欄位是 open
if table_service.get_entity('testAPPs',
'ComputerVision',
'instruct',
select='action').action == 'open':
# 開門
if openDoor == False:
openDoor = True
memoryTime = time.time()
controlDoor('open')
# 三秒後關門，並把 Table 的 action 欄位改為 close
if time.time() - memoryTime > 3:
openDoor = False
controlDoor('close')
taskU = {
'PartitionKey': 'ComputerVision',
'RowKey': 'instruct',
'action': 'close'
}
table_service.merge_entity('testAPPs', taskU)
class Storage_az_table():
##################################################################
# BEGIN - Common methods to implement storage interface
def __init__(self):
connection_string = os.environ['SHKOLA_AZ_TABLE_CONN_STR']
self.table_service = TableService(connection_string=connection_string)
self.default_partition_key = "USER"
self.users_table_name = 'users'
self.responses_table_name = 'responses'
self.sessions_table_name = 'sessions'
self.feedbacks_table_name = 'feedbacks'
tables = [
self.users_table_name, self.responses_table_name,
self.sessions_table_name, self.feedbacks_table_name
]
existing_tables = list(
map(lambda x: x.name, self.table_service.list_tables()))
for table in tables:
if table not in existing_tables:
self.table_service.create_table(table)
def get_user(self, user_id):
partition_key = self.default_partition_key
try:
entity = self.table_service.get_entity(self.users_table_name,
partition_key, user_id)
except AzureMissingResourceHttpError:
return None
entity["user_id"] = user_id
return entity
def update_user(self,
user_id,
name=None,
remote_ip=None,
user_agent=None,
picture=None,
user_language=None,
selected_language=None,
last_accessed=None):
properties = dict()
# Nothing better at the moment:
properties['PartitionKey'] = self.default_partition_key
properties['RowKey'] = user_id
properties['user_id'] = user_id
properties["name"] = name
properties["remote_ip"] = remote_ip
properties["user_agent"] = user_agent
properties["picture"] = picture
properties["user_language"] = user_language
if selected_language:
properties["selected_language"] = selected_language
properties["last_accessed"] = last_accessed
logging.debug("azure table update_user %s: %s", str(name),
str(properties))
try:
self.table_service.insert_or_merge_entity(self.users_table_name,
properties)
except Exception:
logging.exception('Error adding to table ' +
self.users_table_name +
' record: {}'.format(properties))
def update_selected_language(self, user_id, selected_language):
properties = dict()
# Nothing better at the moment:
properties['PartitionKey'] = self.default_partition_key
properties['RowKey'] = user_id
properties['user_id'] = user_id
properties["selected_language"] = selected_language
logging.debug("azure table update_user_langauge %s: %s", str(user_id),
str(properties))
try:
self.table_service.insert_or_merge_entity(self.users_table_name,
properties)
except Exception:
logging.exception('Error adding to table ' +
self.users_table_name +
' record: {}'.format(properties))
def insert_user_id(self, user_id):
self.update_user(user_id)
return user_id
# Get all user responses from the response table
def get_all_user_results(self, u_id, from_date=None):
req = "(PartitionKey eq '{}')".format(u_id)
if from_date:
if len(req) > 0:
req = req + " and "
req = req + "(Timestamp ge datetime'{}')".format(from_date)
entries = self.table_service.query_entities(self.responses_table_name,
req)
result = []
for row in entries:
result.append(row)
return result
# Update user info with the latest <stats>, and with
# <stats_time> being the time of the latest stats
def update_user_stats(self, user_id, stats, stats_time):
properties = dict()
# Nothing better at the moment:
properties['PartitionKey'] = self.default_partition_key
properties['RowKey'] = user_id
properties['user_id'] = user_id
properties['stats'] = encode_dict(stats)
properties['stats_time'] = stats_time
try:
self.table_service.merge_entity(self.users_table_name, properties)
except Exception:
logging.exception('Error updating results for user ' + user_id +
' record: {}'.format(properties))
@timer_section("storage.record_response")
def record_response(self, response):
fb_time = int(time.time() * 1000)
response['PartitionKey'] = response['user_id']
response['RowKey'] = response['question_id'] + "|" + \
str(response['attempt']) + "|" + \
str(fb_time) + "|" + \
str(response['duration'])
# Remove special characters not allowed in Azure PartitionKey and RowKey
response['PartitionKey'] = re.sub("[\ /?#]", "_",
response['PartitionKey'])
response['RowKey'] = re.sub("[\ /?#]", "_", response['RowKey'])
#logging.debug("*** record response: {}".format(response))
try:
self.table_service.insert_entity(self.responses_table_name,
response)
except Exception as err:
logging.exception('Error adding response: {}\n\n{}'.format(
response, err))
@timer_section("storage.record_feedback")
def record_feedback(self, response):
fb_time = int(time.time() * 1000)
response['PartitionKey'] = response['question_id']
response['RowKey'] = response['type'] + "|" + response[
'list_id'] + "|" + str(fb_time)
# Remove special characters not allowed in Azure PartitionKey and RowKey
response['PartitionKey'] = re.sub("[\ /?#]", "_",
response['PartitionKey'])
response['RowKey'] = re.sub("[\ /?#]", "_", response['RowKey'])
logging.debug("*** record feedback: {}".format(response))
try:
self.table_service.insert_entity(self.feedbacks_table_name,
response)
except Exception as err:
logging.exception('Error adding feedback: ' + str(err))
@timer_section("storage.update_session")
def update_session(self, session_id, data={}):
assert session_id is not None
assert data['state_id'] is not None
properties = {
'PartitionKey': session_id,
'RowKey': "",
'data': encode_dict(data['data']),
'user_id': data['user_id'],
'state_id': data['state_id'],
'valid': data['valid']
}
logging.debug(
"storage: updating session: {}, valid={}, state_id={}".format(
session_id, data['valid'], data['state_id']))
try:
self.table_service.insert_or_replace_entity(
self.sessions_table_name, properties)
except Exception:
logging.exception('Error adding to table ' +
self.sessions_table_name +
' record: {}'.format(properties))
@timer_section("get_session")
def get_session(self, session_id):
try:
entity = self.table_service.get_entity(self.sessions_table_name,
session_id, "")
except AzureMissingResourceHttpError:
return None
# Azurite simulator returns an empty entity instead of exception, so check here
if "user_id" not in entity.keys():
return None
logging.debug(
"storage: loaded session: {}, valid={}, state_id={}".format(
session_id, entity.get('valid'), entity.get('state_id')))
# Compatibility: old records don't have state_id, valid
if not "state_id" in entity:
entity['state_id'] = None
if not "valid" in entity:
entity["valid"] = True
return {
# Convert "None" to None, see above
# "user_id": entity["user_id"] if (not entity["user_id"] == "None") else None,
"user_id": entity["user_id"],
"data": decode_dict(entity["data"]),
"state_id": entity["state_id"],
"valid": entity["valid"]
}
# Get all user responses from the response table
def get_all_user_sessions(self, u_id, from_date=None):
req = "(user_id eq '{}')".format(u_id)
if from_date:
if len(req) > 0:
req = req + " and "
req = req + "(Timestamp ge datetime'{}')".format(
from_date.isoformat())
entries = self.table_service.query_entities(self.sessions_table_name,
req)
result = []
for row in entries:
row["data"] = decode_dict(row["data"])
result.append(row)
return result
# Get direct user feedback before given date
def get_all_user_feedback(self, from_date=None):
# Ignore JS and Google errors, only return specific user feedback
req = "(type ne 'JS_ERROR') and (type ne 'GOOGLE_ERROR')"
if from_date:
if len(req) > 0:
req = req + " and "
req = req + "(Timestamp ge datetime'{}')".format(
from_date.isoformat())
entries = self.table_service.query_entities(self.feedbacks_table_name,
req)
result = []
for row in entries:
result.append(row)
return result
# END - Common methods to implement storage interface
##################################################################
# Doesn't really work
# def wipe_tables(self):
# try:
# self.table_service.delete_entity(self.users_table_name, "", "")
# except Exception as err:
# print('Error wiping table, ' + self.users_table_name + ': ' + str(err))
# try:
# self.table_service.delete_entity(self.responses_table_name, "", "")
# except Exception as err:
# print('Error wiping table, ' + self.responses_table_name + ':' + str(err))
# def delete_all_tables(self):
# try:
# self.table_service.delete_table(self.users_table_name)
# except Exception as err:
# logging.exception('Error deleting table, ' + self.users_table_name + ': ' + str(err))
# try:
# self.table_service.delete_table(self.responses_table_name)
# except Exception as err:
# logging.exception('Error deleting table, ' + self.responses_table_name + ':' + str(err))
def get_all_responses(self, user_id=None):
if user_id is None:
req = ""
else:
req = "PartitionKey = {}".format(user_id)
entries = self.table_service.query_entities(self.responses_table_name,
req)
return entries
def get_all_users(self):
entries = self.table_service.query_entities(self.users_table_name, "")
return entries
def print_all_responses(self, user_id=None):
entries = self.get_all_responses(user_id)
if user_id is None:
print(" USER_ID ", end='')
else:
print("USER_ID = {}\n".format(user_id))
print(
" QUESTION_ID LIST_ID RESPONSE_TYPE TIME DURATION CORRECT INCORRECT QUESTIONS"
)
for response in entries:
if user_id is None:
print("{:^30} ".format(response['question_id']), end='') # UID
print("{:^20} ".format(response['user_id']), end='') # QID
print("{:^16} ".format(response['list_id']), end='') # List ID
print("{:^12} ".format(response['response_type']),
end='') # RESPONSE TYPE
print("{:^26} ".format(
time.strftime("%d-%m-%y %H:%M:%S",
time.localtime(int(response['time'])))),
end='') # TIME
print("{:^16} ".format(response['duration']), end='') # DURATION
print("{:^10} ".format(response['correct']), end='') # CORRECT
print("{:^10} ".format(response['incorrect']), end='') # INCORRECT
print("{:^38} ".format(response['questions'])) # QUESTIONS
print("\n")
def print_all_users(self):
entries = self.get_all_users()
print(
" USER ID NAME EMAIL LAST ACCESSED REMOTE IP USER AGENT USER LANGUAGE"
)
for row in entries:
print("{:^30} {:^20} {:^20} {:^20} {:^40} {:^40}".format(
row['user_id'], row['name'],
time.strftime("%d-%m-%y %H:%M:%S",
time.localtime(row['last_accessed'])),
row['remote_ip'] if 'remote_ip' in row.keys() else "",
row['user_agent'] if 'user_agent' in row.keys() else "",
row['user_language'] if 'user_language' in row.keys() else ""))
print("\n")
def get_question_stats(self, q_id=None, from_date=None):
req = ""
if q_id:
# Remove / from q_id
# mq_id = ("".join("fractions/q00022".split("/")))
mq_id = ("".join(q_id.split("/")))
req = req + "((RowKey ge '{}|') and (RowKey lt '{}{}'))".format(
mq_id, mq_id, chr(255))
if from_date:
if len(req) > 0:
req = req + " and "
req = req + "(Timestamp ge datetime'{}')".format(from_date)
entries = self.table_service.query_entities(self.responses_table_name,
req)
result = []
for row in entries:
if "user_id" not in row.keys() or \
"UNKNOWN" in row["user_id"]:
continue
result.append(row)
return result
