createTable.py

import sqlite3
from sqlite3 import Error
 
 
def create_connection(db_file):
    """ create a database connection to the SQLite database
        specified by db_file
    :param db_file: database file
    :return: Connection object or None
    """
    conn = None
    try:
        conn = sqlite3.connect(db_file)
        return conn
    except Error as e:
        print(e)
 
    return conn
 
 
def create_table(conn, create_table_sql):
    """ create a table from the create_table_sql statement
    :param conn: Connection object
    :param create_table_sql: a CREATE TABLE statement
    :return:
    """
    try:
        c = conn.cursor()
        c.execute(create_table_sql)
    except Error as e:
        print(e)
 

def table_restart(conn):
    delete = """ DROP TABLE marvel"""
    delete2 = """ DROP TABLE comics"""
    delete3 = """ DROP TABLE user"""
    cur = conn.cursor()
    cur.execute(delete)
    cur.execute(delete2)
    cur.execute(delete3)
    

def createMain():
    database = r"C:\Users\fgust\OneDrive\Documents\chatbot/marvelDatabase.db"
 
    sql_create_marvel = """ CREATE TABLE IF NOT EXISTS marvel (
                                        id integer PRIMARY KEY,
                                        name text NOT NULL,
                                        description text,
                                        comics text,
                                        FOREIGN KEY (comics) REFERENCES comics (name)
                                ); """

    

    sql_create_comics = """CREATE TABLE IF NOT EXISTS comics (
                                    id integer PRIMARY KEY,
                                    name text NOT NULL,
                                    characters text,
                                    FOREIGN KEY (characters) REFERENCES marvel (name)
                                );"""

    
    sql_create_user = """CREATE TABLE IF NOT EXISTS user (
                                    id integer PRIMARY KEY,
                                    userName text NOT NULL,
                                    character text,
                                    comic text,
                                    FOREIGN KEY (character) REFERENCES marvel (name),
                                    FOREIGN KEY (comic) REFERENCES comics (name)
                                );"""
    
 
    # create a database connection
    conn = create_connection(database)
 
    # create tables
    if conn is not None:
        # create projects table
        ##table_restart(conn) #removed to not cause errors
        create_table(conn, sql_create_marvel)
        create_table(conn, sql_create_comics)
        create_table(conn, sql_create_user)
    else:
        print("Error! cannot create the database connection.")
 
 
if __name__ == '__main__':
    createMain()