def transcation():
with db.transaction():
db.table('user').where('id', 26).update({'name': 'hehe'})
db.table('user').insert([
{'uid': 111, 'name': '111', 'email': '*****@*****.**'},
{'uid': 222, 'name': '222', 'email': '*****@*****.**'}
])
def update_logs(card_id, terminal_id):
logs = db.table('logs')
person = db.table('people').search(where('card_id') == card_id)
person_id = person[0]['id']
found_log = logs.search((where('login_time') == '')
| (where('logout_time') == '')
& (where('card_id') == card_id))
if not found_log:
login_time = datetime.now().strftime(datetime_format)
logs.insert({
'person_id': person_id,
'card_id': card_id,
'terminal_id': terminal_id,
'login_time': str(login_time),
'logout_time': ''
})
return f"Person with ID {person_id} logged in"
elif logs.search((where('login_time') != '') & (where('logout_time') == '')
& (where('card_id') == card_id)):
logout_time = datetime.now().strftime(datetime_format)
logs.update({'logout_time': str(logout_time)},
(where('card_id') == card_id) &
(where('logout_time') == ''))
return f"Person with ID {person_id} logged out!"
return "Unable to login or logout!"
def on_message(client, userdata, msg):
data = json.loads(msg.payload)
if not data['card_id'] or not data['terminal_id']:
return
time = datetime.now().strftime(datetime_format)
found = db.table('terminals').search(where('id') == data['terminal_id'])
if not found:
print(
f"Connection attempt from unknown terminal at time: {time}. TerminalID: {data['terminal_id']} "
)
return
found_card = db.table('cards').search(where('id') == data['card_id'])
if not found_card:
db.table('unknown_logs').insert({
'card_id': data['card_id'],
'terminal_id': data['terminal_id'],
'time': time
})
print(f"Login attempt by unknown card. CardID: {data['card_id']}")
elif found_card[0]['assigned']:
print(update_logs(data['card_id'], data['terminal_id']))
else:
print(f"Login attempt by unassigned card. CardID: {data['card_id']}")
def on_message(client, userdata, msg):
data = json.loads(msg.payload)
if not data["terminal_id"] or not data["card_id"]:
return
terminals = db.table('terminals')
search = terminals.search(where('id') == data["terminal_id"])
if not search:
print("Unpermitted connection from", data["terminal_id"], "terminal.")
return
cards = db.table('cards')
search = cards.search(where('id') == data["card_id"])
if not search:
cards.insert({'id': data["card_id"]})
print("New card registered with id ", data["card_id"])
logins = db.table('logins')
t = time.time()
logins.insert({
'time': t,
'card_id': data["card_id"],
'terminal_id': data["terminal_id"]
})
print("New", data["card_id"], "login from", data["terminal_id"], "at", t)
def login(data):
logins = db.table('logins')
if len(data) == 1:
return "\n\t".join(("Available subcommands:", "list <person-id>", "save <person-id>"))
if data[1] == "list":
if len(data) != 3:
return "Incorrect argument number! Expected `login list <person-id>`"
person_id = data[2]
search = db.table('people').search(where('id') == person_id)
if not search:
return "No person with such id!"
card_id = search[0]["card_id"]
if len(card_id) == 0:
return "The person doesn't have any card assigned!"
login_search = logins.search(where('card_id') == card_id)
string = "time" + "\t\t\t" + "terminal id" + "\n"
for l in login_search:
string += datetime.utcfromtimestamp(l["time"]).strftime(datetime_format)
string += "\t" + l["terminal_id"] + "\n"
return string
if data[1] == "save":
if len(data) != 3:
return "Incorrect argument number! Expected `login save <person-id>`"
person_id = data[2]
search = db.table('people').search(where('id') == person_id)
if not search:
return "No person with such id!"
card_id = search[0]["card_id"]
if len(card_id) == 0:
return "The person doesn't have any card assigned!"
login_search = logins.search(where('card_id') == card_id)
timeInMs = int(round(time.time() * 1000))
location = file_location("_".join(("logins", person_id, str(timeInMs))) + '.csv')
f = open(location, "w+")
f.write("time" + "," + "terminal id" + "\n")
for l in login_search:
string = datetime.utcfromtimestamp(l["time"]).strftime(datetime_format)
string += "," + l["terminal_id"] + "\n"
f.write(string)
f.close()
return "File saved to " + location
return "No such command!"
def transcation2():
db.begin_transaction()
try:
db.table('user').where('id', 26).update({'name': 'hehe'})
db.table('user').insert([
{'uid': 111, 'name': '111', 'email': '*****@*****.**'},
{'uid': 222, 'name': '222', 'email': '*****@*****.**'}
])
db.commit()
except:
db.rollback()
raise
def insert1():
uid = uuid.uuid4().hex
# db.table('user').insert(uid=uid, name='a', email='*****@*****.**')
# db.table('user').insert({
# 'uid': uid,
# 'name': 'b',
# 'email': '*****@*****.**'
# })
db.table('user').insert([
{'uid': 111, 'name': '111', 'email': '*****@*****.**'},
{'uid': 222, 'name': '222', 'email': '*****@*****.**'}
])
def cards(input_data):
cards = db.table('cards')
if len(input_data) == 1:
return "\n\t".join(('Available subcommands:', 'list', 'add <card-id>',
'remove <card-id>'))
if input_data[1] == 'list':
print("{:<17}".format("CardID") + "Assigned")
return "\n".join(f"{c['id']} \t {c['assigned']}" for c in cards)
if input_data[1] == 'add':
if len(input_data) != 3:
return "Incorrect argument number! Expected `card add <card-id>`"
card_id = input_data[2]
found = cards.search(where('id') == card_id)
if not found:
cards.insert({'id': card_id, 'assigned': False})
return 'Card added successfully !'
return 'Card already exists!'
if input_data[1] == 'remove':
if len(input_data) != 3:
return "Incorrect argument number! Expected `card remove <card-id>`"
card_id = input_data[2]
found = cards.search(where('id') == card_id)
if found:
if not found[0]['assigned']:
cards.remove(where('id') == card_id)
return 'Card removed successfully!'
return 'Unable to remove card assigned to a person!'
return 'No such card exists!'
return "No such command"
def add_article_to_db(table: str, article_title: str, article_url: str,
article_date: str) -> None:
""" Add a new article title and date to the database
Args:
table (str): current table
article_title (str): The title of an article
article_url (str): The url of an article
article_date (str): The publication date of an article
"""
db.table(table).insert({
"title": article_title,
"link": article_url,
"published": article_date
})
logger.debug(f"Article '{article_title}' inserted")
def terminal(data):
terminals = db.table('terminals')
if len(data) == 1:
return "\n\t".join(("Available subcommands:", "list", "add <terminal-id>", "remove <terminal-id>"))
if data[1] == "list":
return "\n".join(t['id'] for t in terminals)
if data[1] == "add":
if len(data) != 3:
return "Incorrect argument number! Expected `terminal add <terminal-id>`"
terminal_id = data[2]
search = terminals.search(where('id') == terminal_id)
if not search:
terminals.insert({'id': terminal_id})
return "Terminal added successfully!"
return "Terminal already added!"
if data[1] == "remove":
if len(data) != 3:
return "Incorrect argument number! Expected `terminal remove <terminal-id>`"
terminal_id = data[2]
search = terminals.search(where('id') == terminal_id)
if search:
terminals.remove(where('id') == terminal_id)
return "Terminal removed successfully!"
return "No such terminal exists!"
return "No such command!"
def query1():
# retrieving all row from a table
users = db.table('user').get()
# users = db.table('user').select('name', 'email').get()
# users = db.table('user').distinct().get()
# users = db.table('user').select('name as user_name').get()
# users = db.table('user').where('id', '>', 10).get()
for user in users:
print user
def cards(data):
cards = db.table('cards')
if len(data) == 1:
return "\n\t".join(("Available subcommands:", "list"))
if len(data) == 2 and data[1] == "list":
return '\n'.join(c['id'] for c in cards)
return "No such command!"
def terminal(input_data):
terminals = db.table('terminals')
if len(input_data) == 1:
return "\n\t".join(('Available subcommands:', 'list',
'add <terminal-id>', 'remove <terminal-id>'))
if input_data[1] == 'list':
print("ID")
list = "\n".join(t['id'] for t in terminals)
return list
if input_data[1] == 'add':
if len(input_data) != 3:
return 'Passed command is incorrect ! Expected `terminal add <terminal-id>` '
terminal_id = input_data[2]
found = terminals.search(where('id') == terminal_id)
if not found:
terminals.insert({'id': terminal_id})
return "Terminal added successfully !"
else:
return "Terminal already added !"
if input_data[1] == 'remove':
if len(input_data) != 3:
return 'Passed command is incorrect! Expected `terminal remove <terminal-id>` '
terminal_id = input_data[2]
found = terminals.search(where('id') == terminal_id)
if found:
terminals.remove(where('id') == terminal_id)
return "Terminal removed successfully !"
else:
return "Terminal not found !"
return "No such command"
def article_is_not_db(table: str, article_title: str, article_url: str,
article_date: str) -> bool:
""" Check if a given pair of article title and date
is in the database.
Args:
article_title (str): The title of an article
article_url (str): The url of an article
article_date (str): The publication date of an article
Return:
True if the article is not in the database
False if the article is already present in the database
"""
result = (db.table(table).where("title", article_title).where(
"link", article_url).where("published",
article_date).get().serialize())
if result:
logger.debug(
f"Article '{article_title}' not found. Needs to be imported.")
return False
logger.debug(f"Article '{article_title}' found. Needs not be imported.")
return True
def person(data):
people = db.table('people')
if len(data) == 1:
return "\n\t".join(("Available subcommands:", "list", "add <person-id> <name>", "remove <person-id>", "assign <person-id> <card-id>", "clear <person-id>"))
if data[1] == "list":
return '\n'.join(p['id'] + "\t" + p['name'] + "\t" + p['card_id'] for p in people)
if data[1] == "add":
if len(data) > 4:
return "Incorrect argument number! Expected `person add <person-id> <name>`"
person_id = data[2]
search = people.search(where('id') == person_id)
if not search:
people.insert({'id': person_id, 'name': data[3], 'card_id': ''})
return "Person added successfully!"
return "Person already exists! Aborted."
if data[1] == "remove":
if len(data) != 3:
return "Incorrect argument number! Expected `person remove <person-id>`"
person_id = data[2]
search = people.search(where('id') == person_id)
if search:
people.remove(where('id') == person_id)
return "Person removed successfully!"
return "No such person exists!"
if data[1] == "assign":
if len(data) != 4:
return "Incorrect argument number! Expected `person assign <person-id> <card-id>`"
person_id = data[2]
search = people.search(where('id') == person_id)
if search:
card_id = data[3]
card_search = db.table('cards').search(where('id') == card_id)
if card_search:
people.update({ 'card_id': card_id }, where('id') == person_id)
return "Person got the card assigned successfully!"
return "No such card exists!"
return "No such person exists!"
if data[1] == "clear":
if len(data) != 3:
return "Incorrect argument number! Expected `person clear <person-id>`"
person_id = data[2]
search = people.search(where('id') == person_id)
if search:
people.update({ 'card_id': '' }, where('id') == person_id)
return "Person card cleared successfully!"
return "No such person exists!"
return "No such command!"
def log(data):
logs = db.table('logs')
if len(data) == 1:
return "\n\t".join(("Available subcommands:", "list <person-id>",
"gencsv <person-id>"))
if data[1] == 'list':
if len(data) != 3:
return "Incorrect argument number! Expected `log list <person-id>`"
person_id = data[2]
found = db.table('people').search(where('id') == person_id)
if not found:
return "No such person exists!"
found_log = logs.search(where('person_id') == person_id)
if found_log:
print(f"{'login_time':<19} \t logout_time")
return "\n".join(f"{l['login_time']} \t {l['logout_time']}"
for l in found_log)
if data[1] == 'gencsv':
if len(data) != 3:
return "Incorrect argument number! Expected `log gencsv <person-id>`"
person_id = data[2]
found = db.table('people').search(where('id') == person_id)
if found:
found_log = logs.search(where('person_id') == person_id)
if found_log:
filename = '_'.join(('logs', person_id)) + '.csv'
with open(filename, 'w', newline='') as csv_file:
csv_writer = DictWriter(csv_file,
fieldnames=fieldnames,
delimiter=',')
csv_writer.writeheader()
for log in found_log:
login_time = datetime.strptime(log['login_time'],
datetime_format)
working_time = ''
if log['logout_time']:
logout_time = datetime.strptime(
log['logout_time'], datetime_format)
working_time = ':'.join(
(str(logout_time.hour - login_time.hour) + 'h',
str(logout_time.minute - login_time.minute) +
'm',
str(logout_time.second - login_time.second) +
's'))
csv_row = {
'terminal id': log['terminal_id'],
'login time': log['login_time'],
'logout time': log['logout_time'],
'working time': working_time
}
csv_writer.writerow(csv_row)
return f"{filename} got created."
return "Person has no logs yet."
return "No such person exsists!"
return "No such command"
def delete1():
db.table('user').where('id', '=', 29).delete()
def update1():
# db.table('user').where('id', 6).update(name='123')
db.table('user').where('id', 6).update({'name': '234'})
def person(data):
people = db.table('people')
if len(data) == 1:
return "\n\t".join(("Available subcommands:", "list",
"add <person-id> <name>", "remove <person-id>",
"remove_card <person-id>", "assign <person-id>"))
if data[1] == "list":
print("{:<8}".format("ID") + "\t" + "{:<15}".format("Name") + "\t" +
"CardID")
return '\n'.join("{:<8}".format(p['id']) + "\t" +
"{:<15}".format(p['name']) + "\t" + p['card_id']
for p in people)
if data[1] == "add":
if len(data) != 4:
return "Incorrect argument number! Expected `person add <person-id> <name>`"
person_id = data[2]
found = people.search(where('id') == person_id)
if not found:
people.insert({'id': person_id, 'name': data[3], 'card_id': ''})
return "Person added successfully!"
return "Person already exists! Aborted."
if data[1] == "remove":
if len(data) != 3:
return "Incorrect argument number! Expected `person remove <person-id>`"
person_id = data[2]
found = people.search(where('id') == person_id)
if found:
person_card_id = found[0]['card_id']
if person_card_id != '':
db.table('cards').update({'assigned': False},
where('id') == person_card_id)
people.remove(where('id') == person_id)
return "Person removed successfully!"
return "No such person exists!"
if data[1] == "remove_card":
if len(data) != 3:
return "Incorrect argument number! Expected `person remove <person-id>`"
person_id = data[2]
found = people.search(where('id') == person_id)
if found:
person_card_id = found[0]['card_id']
if person_card_id != '':
db.table('cards').update({'assigned': False},
where('id') == person_card_id)
people.update({'card_id': ''}, where('id') == person_id)
return "Card removed from person successfully!"
return "No such person exists!"
if data[1] == "assign":
if len(data) != 3:
return "Incorrect argument number! Expected `person assign <person-id>`"
person_id = data[2]
found = people.search(where('id') == person_id)
if found:
card_search = db.table('cards').search(where('assigned') == False)
if card_search[0]:
card_id = card_search[0]['id']
people.update({'card_id': card_id}, where('id') == person_id)
db.table('cards').update({'assigned': True},
where('id') == card_id)
return "Person got the card assigned successfully!"
elif len(card_search) == 0:
return "There are no more cards to assign"
return "No such card exists!"
return "No such person exists!"
return "No such command!"
