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!"