sql_generator_for_joe.py

##### NOTE #####
#  THIS MAY NOT BE THE BEST OPTION.  I Would also look up subqueries.  https://www.techonthenet.com/oracle/subqueries.php
#  But this is an example of dynamically creating and executing queries with Python.  So, this is a Cadillac solution to a
#  Bicycle problem, but something that I think you should be aware of.  It can simplify life SO MUCH more than PL/SQL, but
#  as I was writing this, you might have better luck with a subquery..  oh well!  


import sqlite3

# Python DOES NOT have constants.  These are global variables, so don't copy this in super huge projects.
# the danger of doing this is that these variables are alterable, and if I alter it somewhere
# in my program then it breaks the program from that point on.  You CAN do this in smaller applications
# but it is discouraged when you get to bigger, hairier solutions to problems.  I do this in all my other
# programs because their languages have a CONST or a FINAL type of variable.  It is an EXCELLENT practice
# in those programs, and you do them in ALL_CAPS in snake_case (underscores between words).  that indicates to
# all other developers that these variables are not to be changed, or they can't be changed because of the CONST
# or FINAL declaration at the beginning of the program.
# But lets say that I decide to change the name of the DB_FILE, I can change it in one place and it changes everywhere
# at once.  This is awesome because it's a one line adjustment, even if I use it in 500 places.  I only have to change it in
# one place.
DB_FILE = "GE_INVENTORY_DB"
TABLE_NAME = "T_CHEMICAL"
DROP_CHEMICAL_TABLE_QUERY = "DROP TABLE IF EXISTS %s" % TABLE_NAME
SELECT_ALL_FROM_CHEMICAL_TABLE = "SELECT * FROM %s" % TABLE_NAME
CREATE_CHEMICAL_TABLE_QUERY = '''

        CREATE TABLE IF NOT EXISTS T_CHEMICAL
        (
          id integer primary key autoincrement,
          name text,
          description text
        )
        '''


# this is a helper function.  It's an example of 'Tight Coupling'.  that means that this function is
# extremely specific for it's function.  Not a good thing unless there is no other option.
# Usually, you want to make your functions very general rather than super specific so you can use it
# elsewhere.  IF you need to do strong coupling (I did it because I'm lazy), make sure to name your function
# as specifically as the function is programmed.  that's why I named it why I did.  This doesn't make insert
# statements for any other table, so I want to make sure I only use it when I'm working with the Chemical table
def generate_insert_for_chemical_table(name, description):
    return "INSERT INTO T_CHEMICAL (name, description) VALUES ('%s', '%s')" % (name, description)

def create_and_populate_chemical_table():
    # the string is the name of the sqlite file
    # if it doesn't exist, it creates one.
    # in SQL terms, you could consider the file a 'schema' of sorts
    conn = sqlite3.connect(DB_FILE)

    # all SQL languages works with cursors, but few expose them to the user.
    # its a 'Pointer' that points to one specific record at a time.
    # before pointing to it, it locks it, allows only us to see and modify it
    # and if we do, then it makes the modifications, and then we commit
    # and close it so others can see it.  This is part of a principle called
    # an ACID transaction, if you want to learn more about it.
    c = conn.cursor()

    # try and catch statements are extremely useful, especially in Python.
    # Java and C languages are PRE-compiled, which means that the program
    # will stop you during compliation and tell you where errors could arise
    # Python compiles as it goes, which means its fast, but you can have your program
    # running for hours and then randomly hit an error and fail.  That can undo a lot of
    # work.  In this small setting, there is a lot more forgiveness, but I put it in to show
    # you how you can use them in your work.
    # They are best used when allocating memory (using the 'new' keyword), working with APIs
    # (when they break, you want to be ready for it, and since they are out of your control,
    # they probably don't have any favors in the way of error handling).
    # BUT EXCEPTIONS ARE ONLY USED IN ... the exception case haha.  If you're planning on there
    # being a 'fork in the road' regularly, like a 404 error, then you would use an IF-ELSE statement.
    # save try and catch for the aforementioned cases.  I would look it up for more info.
    try:
        # when working in a small environment, we drop the table and recreate it to
        # avoid duplications in inserted rows.  Also, if we adjust the structure of the
        # table, we want to drop it so that we can create the new one that reflects the
        # changes.
        c.execute(DROP_CHEMICAL_TABLE_QUERY)
        c.execute(CREATE_CHEMICAL_TABLE_QUERY)
        c.execute(generate_insert_for_chemical_table('Hydrogen', 'Flamable, use caution'))
        c.execute(generate_insert_for_chemical_table('Oxygen', 'Flamable, use caution'))
        c.execute(generate_insert_for_chemical_table('Aluminum', 'Oft mispronounced by Englishmen'))

    except Exception:
        print(Exception.with_traceback())

    # this saves the transaction
    conn.commit()

    # this closes the transaction and the cursor.  This also makes our saves
    # permanent and visible to other users.
    conn.close()

    print("THE CONTENTS OF %s" % TABLE_NAME)
    print(get_sql_results_as_array(DB_FILE, SELECT_ALL_FROM_CHEMICAL_TABLE))
    print() # for formatting purposes



# this is just a helper function.  it makes it so we can see what we put into the database to make
# sure that they're there.  I have it save to array so they're easier to search and edit.  You can
# select by 'ID' easily by getting the array index (list[id])
# THIS IS AN EXAMPLE OF A WEAK COUPLING, which is a VERY GOOD THING.  I can use this function for any table
# with any query.  In fact, it is so weakly coupled, that I could use this in any project I do with SQL, and
# so I would save this in a special place where I could use it later.
def get_sql_results_as_array(db_file, query):

    results = []
    conn = sqlite3.connect(db_file)

    c = conn.cursor()
    for row in c.execute(query):
        results.append(row)

    conn.close()
    return results


# This is a fake way to get recipes.  I want you to have the adventure of making the recipe database.
# let me see what you can come up with.  If you need help, I'll attach a file with an example structure
# that I would use, just for reference.  But see if you can do it without looking.
def get_recipe_as_array_from_recipe_id(id):
    #TODO:  MAKE A TABLE OF RECIPES, QUERY IT FROM ID AND RETURN THE RESULTS AS AN ARRAY
    # I'll just do it from an if-then statemenet

    if id == 1:
        return [1, 2]
    elif id == 2:
        return [1, 3]
    elif id == 3:
        return [2, 3]
    else:
        print("ERROR: RECIPE NOT KNOWN")

#############################################
##THIS IS THE FUNCTION I WAS TALKING ABOUT ##
#############################################
#  Another strongly coupled function, but it's okay in this case.  I don't think you could
# get any less specific without getting ridiculous.
def make_select_query_from_chemcial_table_using_recipe_id(recipe_id):

    ingredients_list = get_recipe_as_array_from_recipe_id(recipe_id)

    # I'm starting with a basic 'SELECT ALL' query and then I'm going
    # to append an 'IN LIST' filter in the 'WHERE' clause.  The list
    # can get long, but it shouldn't be bad.
    recipe_query = "SELECT * FROM %s WHERE id in (" % TABLE_NAME

    # THIS PART IS TRICKY.  SQL needs a comma separated list.
    # this is a common way that programmers use to make a list with
    # commas.  The Logic is as follows: If not the first in line,
    # prepend a comma.
    first = True
    for id in ingredients_list:
        if first == True:
            recipe_query += str(id)
            first = False
        else:
            recipe_query += ", " + str(id);

    # the list needs to be parenthetically separated from the rest of the query.
    # this closes the paretheses.
    recipe_query += ")"

    print("RECIPE NUMBER PROVIDED: %d" % recipe_id)
    print("RECIPE QUERY: " + recipe_query)



# a simple driver function
if __name__ == "__main__":
    create_and_populate_chemical_table()
    make_select_query_from_chemcial_table_using_recipe_id(1)