database_interaction.py

#this tutorial was followed as a guide on connecting and using databases in python: https://www.python-course.eu/sql_python.php
import sqlite3
import json

#define constants
DATABASE_DIRECTORY = 'Databases/chatbot_database.db'

def SetLastMessages(username, messages):
    """
 Parameters: string: username
 list: messages
 """
    jsonStr = json.dumps(messages)
    sqlCommand = '''
 UPDATE UserData
 SET LastMessages='{messages}'
 WHERE `Username`='{username}';
 '''
    sqlCommand = sqlCommand.format(messages = jsonStr, username = username)
    cursor.execute(sqlCommand)
    connection.commit()

def GetLastMessages(username):
    sqlCommand = '''
 SELECT LastMessages FROM UserData 
WHERE `Username` = "{username}";
 '''
    sqlCommand = sqlCommand.format(username = username)
    cursor.execute(sqlCommand)
    result = cursor.fetchone()
    messages = json.loads(result[0])
    return messages

def CreateUser(username, password):
    sqlCommand = '''
 INSERT INTO UserData (Username, Password, LastMessages)
 VALUES ("{username}", "{password}", "[]");
 '''
    sqlCommand = sqlCommand.format(username = username, password = password)
    cursor.execute(sqlCommand)
    connection.commit()

def CheckUsernamePasswordMatch(username, password):
    """
 Returns: bool: username exists
 bool: password matches
 """
    sqlCommand = '''
 SELECT Password FROM UserData 
WHERE `Username` = "{username}";
 '''
    sqlCommand = sqlCommand.format(username = username)
    cursor.execute(sqlCommand)
    result = cursor.fetchone()
    if result == None:
        #username not found
        return False, False
    else:
        #username found
        actualPassword = result[0]
        if actualPassword == password:
            return True, True
        else:
            return True, False

def CheckUsernameExists(username):
    sqlCommand = '''
 SELECT Username FROM UserData 
WHERE `Username` = "{username}";
 '''
    sqlCommand = sqlCommand.format(username = username)
    cursor.execute(sqlCommand)
    result = cursor.fetchone()
    if result == None:
        #username doesn't exist
        return False
    else:
        return True

def InsertWordIntoDictionary(word, tags):
    """
 Parameters: string: word
 string: tags (seperating each with comma e.g. tag1,football team,city)
 Returns: None
 """
    sqlCommand = '''
 INSERT INTO WordsDictionary (Word, Tags)
 VALUES ("{word}", "{tags}");
 '''
    sqlCommand = sqlCommand.format(word = word, tags = tags)
    cursor.execute(sqlCommand)
    connection.commit()

def DeleteWordFromDictionary(word):
    sqlCommand = '''
 DELETE FROM WordsDictionary
 WHERE `Word` = "{word}";
 '''
    sqlCommand = sqlCommand.format(word = word)
    cursor.execute(sqlCommand)
    connection.commit()

def AddWordTags(word, newTags):
    exists, existingTags = GetWordTags(word)
    if exists:
        if newTags[0] != ',':
            newTags = ',' + newTags
        allTags = existingTags + newTags
        sqlCommand = '''
 UPDATE WordsDictionary
 SET Tags='{tags}'
 WHERE `Word`='{word}';
 '''
        sqlCommand = sqlCommand.format(word = word, tags = allTags)
        cursor.execute(sqlCommand)
        connection.commit()

def GetWordTags(word):
    """
 Parameters: string: word
 Returns: bool: whether word was found in db table, True for yes
 string: string list of tags, comma seperated (if word not found or has no tags an empty string is returned)
 """
    sqlCommand = '''
 SELECT Tags FROM WordsDictionary
 WHERE `Word` = "{word}";
 '''
    sqlCommand = sqlCommand.format(word = word)
    cursor.execute(sqlCommand)
    result = cursor.fetchone()
    try:
        result = result[0]
        return True, result
    except TypeError:
        return False, ''

def GetWordExists(word):
    """
 Parameters: string: word to check
 Returns: bool: True if it exists, False otherwise
 """
    sqlCommand = '''
 SELECT * FROM WordsDictionary
 WHERE `Word` = "{word}";
 '''
    sqlCommand = sqlCommand.format(word = word)
    cursor.execute(sqlCommand)
    result = cursor.fetchone()
    if result == None:
        return False
    else:
        return True

def SelectFirstRecord(table):
    sqlCommand = '''
 SELECT * FROM {table}
 '''
    sqlCommand = sqlCommand.format(table = table)
    cursor.execute(sqlCommand)
    result = cursor.fetchone()
    return result

connection = sqlite3.connect(DATABASE_DIRECTORY)
cursor = connection.cursor()

if __name__ == '__main__':
    print('-Database Control Panel-')
    print('What do you want to do? All changes are final and cannot be undone!')
    choices = ['1: Create WordsDictionary table', '2: Insert new word into WordsDictionary', '3: See first records (for an example)',
    '4: Delete word from WordsDictionary', '5: Check whether word exists', '6: Add word tags', '7: Create UserData table']
    for each in choices:
        print(each)
    
    choice = int(input('Enter number >'))
    while not ( choice - 1 >= 0  and choice - 1 < len(choices) ):
        print('Invalid number')
        choice = int(input('Enter number >'))
    
    if choice == 1:
        #create words dictionary table
        sqlCommand = '''
 CREATE TABLE WordsDictionary ( 
Word VARCHAR(20) PRIMARY KEY, 
Tags TEXT);
 '''
        try:
            cursor.execute(sqlCommand)
        except sqlite3.OperationalError as e:
            print(e)

    elif choice == 2:
        #insert new word with tags into WordsDictionary table
        confirm = ''
        while confirm.lower() != 'y':
            word = input('Enter word >').lower()
            tags = input('Enter tags (seperating each with comma e.g. tag1,football team,city) >').lower()
            tags = tags.replace(', ', ',')

            print('Word Tags')
            print(word + ' | ' + tags)
            print('Is this ok?')
            confirm = input("Enter 'y' for yes, anything to re-enter >")
        InsertWordIntoDictionary(word, tags)

    elif choice == 3:
        #view first record from all tables
        #get all table names in database
        #sql code snippet from https://www.sqlitetutorial.net/sqlite-tutorial/sqlite-show-tables/
        sqlCommand = '''
 SELECT name
 FROM sqlite_master 
WHERE type ='table' AND name NOT LIKE 'sqlite_%';
 '''
        #end of code snippet
        cursor.execute(sqlCommand)
        tables = cursor.fetchone()
        for table in tables:
            print(table)

            record = SelectFirstRecord(table)
            recordStr = '| '
            for value in record:
                recordStr += str(value) + ' | '
            print(recordStr)

    elif choice == 4:
        #delete word from WordsDictionary table
        wordToDelete = input('Enter word to delete (case sensitive) >')
        DeleteWordFromDictionary(wordToDelete)
        print('Word successfully deleted if it existed.')

    elif choice == 5:
        #check whether word exists
        wordToCheck = input('Enter word to check (case sensitive) >')
        exists = GetWordExists(wordToCheck)
        if exists:
            print('Yes / True')
        else:
            print('No / False')

    elif choice == 6:
        #add word tags
        wordToUse = input('Enter word to update (case sensitive) >')
        exists, existingTags = GetWordTags(wordToUse)
        if exists:
            print('Existing tags: ' + existingTags)
            confirm = ''
            while confirm.lower() != 'y':
                newTags = input('Enter tags to add (seperating each with comma e.g. tag1,football team,city) >').lower()
                newTags = newTags.replace(', ', ',')
                if newTags[0] != ',':
                    newTags = ',' + newTags
                allTags = existingTags + newTags

                print('Word Tags')
                print(wordToUse + ' | ' + allTags)
                print('Is this ok?')
                confirm = input("Enter 'y' for yes, anything to re-enter >")
            AddWordTags(wordToUse, newTags)
        else:
            print('Word does not exist in WordsDictionary')

    elif choice == 7:
        #create UserData table
        sqlCommand = '''
 CREATE TABLE UserData ( 
Username VARCHAR(20) PRIMARY KEY, 
Password VARCHAR(20), 
LastMessages TEXT);
 '''
        try:
            cursor.execute(sqlCommand)
        except sqlite3.OperationalError as e:
            print(e)

    connection.commit()
    connection.close()
    input()