Archive

Posts Tagged ‘sql’

Python: Coffee Consumption – P7

September 8, 2021 Leave a comment

Learning : Python, SQlite3, Dataset, Pandas,
Subject: Create Coffee Consumption Application.

[NOTE: To keep the code as simple as we can, We WILL NOT ADD any user input Varevecations. Assuming that our user will Enter the right inputs.]

[ IF THE IS FIRST TIME DOWNLOADING THE CODE FILE, SELECT OPTION 7 FROM MAIN-MENU TO CREATE THE DATABASE]


In this part (Part-7) of Coffee Consumption App, we will write a Function to display all Records in the data-base in a table on the screen.

Beginning with formating the table header, we will have the following columns: record Id, Date_time, Coffee_name, Coffee_type, coffee_size, customer_Gender and Rank. Here is the code..

print(‘ ‘*6,’ID’,’ ‘*5,’ Date & Time’,’ ‘*3,’ Coffee Name’,’ ‘*4,’Type’,’ ‘*9,’Size’,’ ‘*4,’ Gender’,’ ‘*4,’ Rank’)
print(‘ ‘*4,’-‘*90)

Next we will execute an SQLITE command to fetch all the records. Here is the commands..
c.execute (f”select * from coffee_con where c_id > 1 “)
dataset = c.fetchall()

Also we will using the lookup tables to get the names using the id keys in the coffee_name, coffee_type, coffee_size columns.

Now, Using the for loop to access all the records and print them on the screen. Here is apart of the code ..


print(‘ ‘*6,'{}’.format(data_id),end=””)
print(‘ ‘*(10 – 5),'{}’.format(data_date),end=””)
print(‘ ‘*(21 – 16),'{}’.format(data_name[0]),end=””)
print(‘ ‘*(14 – len(data_name[0])),'{}’.format(data_type[0]),end=””)
print(‘ ‘*(13 – len(data_type[0])),'{}’.format(data_size[0]),end=””)
print(‘ ‘*(13 – len(data_size[0])),'{}’.format(data_sex),end=””)
print(‘ ‘*(12 – 1),'{}’.format(data_rank))

Here is a screen shot of the code..

python project code coffee consumption ali radwani
OutPut Screen
python project code coffee consumption ali radwani



What’s Coming: In Part-8 we will write the Function to Edit the records on the Main Data-Base.


..:: Have Fun with Coding ::.. 🙂


Part 1 Part 2 Part 3 Part 4 Part 5
Part 6 Part 7 Part – Part – Part –


To Download my Python code (.py) files Click-Here



ali radwani ahradwani.com python projects codeFollow me on Twitter..

By: Ali Radwani

Python: Coffee Consumption – P6

September 5, 2021 1 comment

Learning : Python, SQlite3, Dataset, Pandas,
Subject: Create Coffee Consumption Application.

[NOTE: To keep the code as simple as we can, We WILL NOT ADD any user input Varevecations. Assuming that our user will Enter the right inputs.]

[ IF THE IS FIRST TIME DOWNLOADING THE CODE FILE, SELECT OPTION 7 FROM MAIN-MENU TO CREATE THE DATABASE]


In this part (Part-6) of Coffee Consumption App, we will write First Function of the Main Application to ADD New Record. In this Function we will do the following:

  • Get the Current Date and Time.
  • The user will Select the Coffee Name, Type and Size from the list in lookup tables.
  • Selecting the Gender of the user.
  • Entring the Rank for the order [If the customer rank it].

Beginning with adding New Record in the Coffee Consumption System. We will use the datatime.now() function get the current data and time here is the code..

now = datetime.now()
date_t = now.strftime(“%d/%m/%Y %H:%M”)
Then we will show each of the lookup table we have and the user will select the ID of the Coffee Name,Type and Size, the, we will use a simple Validation process to control the user inut. Here is the code for Entering the Coffee Name


   # Select a Coffee Name 
    print('\n     List of Coffee Names:\n')
    show_list('coffee_name','cn_id')
     # check the availability of the input 
    while True:
        cn_id = input('\n     Enter the ID of a Coffee Name. [E to Exit]. > ')
        if cn_id in ['e','E']:
            input('     You Select [E to Exit].. Press any Key. > ')    
            return
            
        elif (cn_id ==" ") or (cn_id =="") or (check_availabilty('coffee_name','cn_id', int(cn_id)) != None):
            break

The same code will be apply for the Coffee Type and Coffee Size.
[NOTE] All the codes will be in the file to be Download.

Next part of the code will be to select the customer Gender [M,F] here also we will use a simple Validation that will accept M or F Not anything else. Here is the code..


# Select Customer Gender [Only M,F]
    while True:
        sex = input('\n     Enter the Gender of the customer. [M / F] [E to Exit]. > ')
        
        if sex in ['e','E']:
            input('     You Select [E to Exit].. Press any Key. > ')    
            return
        elif sex in ['m','M','f','F']:
            break
        else: 
            print('     You Must input [M,F]')

Last variable we will collect will be the Rank, the Customer will ask the user to Rank this Order, the range will be from 1 to 10, 0 will be for Not Available.. Here is the code…


# Enter the user Rank
    print('     If the User can Rank the Coffee.. 1 to 10 [1 is Bad, 10 is Great]')
    while True :
        rank = input('\n     Enter User Rank.[1 is Bad, 10 is Great] [0 is NOT Available] [E to Exit]. > ')
        if int(rank) in range (0,11):
            break
        else:
            print('\n     Rank Should be in Range of 0-10.'

After that we will display all the Entries on the screen and ask the customer to press [S] to save the Record, and then we will use IINSERT SQL command to Insert the record. Here is the code for all the function….



What’s Coming: In Part-7 we will do the Follwing:

Writing three Function to Show / Display the Records in the main Table of the Coffee Consumption Application.


..:: Have Fun with Coding ::.. 🙂


Part 1 Part 2 Part 3 Part 4 Part 5
Part 6 Part – Part – Part – Part –


To Download my Python code (.py) files Click-Here



ali radwani ahradwani.com python projects codeFollow me on Twitter..

By: Ali Radwani

Python: Coffee Consumption – P5

August 29, 2021 2 comments

Learning : Python, SQlite3, Dataset, Pandas,
Subject: Create Coffee Consumption Application.

[NOTE: To keep the code as simple as we can, We WILL NOT ADD any user input Varevecations. Assuming that our user will Enter the right inputs.]

[ IF THE IS FIRST TIME DOWNLOADING THE CODE FILE, SELECT OPTION 7 FROM MAIN-MENU TO CREATE THE DATABASE]


In this part (Part-5) of Coffee Consumption App, we will write all three Function to manage the Coffee Size (Mug Size).

  • Create Function: Add New Coffee Size.
  • Create Function: Edit a Coffee Size.
  • Create Function: Delete a Coffee Size.

Beginning with adding new Coffee Size, we will ask the user to enter a New Coffee Size, then we simply added to the databasde using SQL Insert command. … Here is the Code ..

python project sql coffee consumption code ali radwani


The second Function in this part is to Edit a selected Coffee Size, so we will list-down all Sizes we have in the database, the user will select one (ID) then we will ask to enter the new one and update the database. Here is the code..

python project sql coffee consumption code ali radwani

Last Function in this part will be to Delete a selected Coffee Size, so again we will list all Coffee Sizes we have and will ask the user to select the one to be Deleted, then we execute the SQL command to Delete the record from the database… here is the code..

python project sql coffee consumption code ali radwani




What’s Coming: In Part-6 we will Start writing the Main Function to Add a Record of Coffee Consumption using the lookup tables data.


..:: Have Fun with Coding ::.. 🙂


Part 1 Part 2 Part 3 Part 4 Part 5
Part 6 Part – Part – Part – Part –


To Download my Python code (.py) files Click-Here



ali radwani ahradwani.com python projects codeFollow me on Twitter..

By: Ali Radwani

Python: Coffee Consumption – P4

August 26, 2021 3 comments

Learning : Python, SQlite3, Dataset, Pandas,
Subject: Create Coffee Consumption Application.

[NOTE: To keep the code as simple as we can, We WILL NOT ADD any user input Varevecations. Assuming that our user will Enter the right inputs.]

[ IF THE IS FIRST TIME DOWNLOADING THE CODE FILE, SELECT OPTION 7 FROM MAIN-MENU TO CREATE THE DATABASE]


In this part (Part-4) of Coffee Consumption App, we will write all three Function to manage the Coffee types.

  • Create Function: Add New Coffee Type.
  • Create Function: Edit a Coffee Type.
  • Create Function: Delete a Coffee Type.

Beginning with adding new Coffee Type, we will ask the user to enter a new Coffee Type, then we simply added to the databasde. … Here is the Code ..

python project code coffee consumption ali radwani



Tip: To Capitalize user input we are using one line code
# Capitalize user input
user_input = ” “.join([word.capitalize() for word in user_input.split(” “)])

The second Function in this part is to Edit a selected Coffee Type, so we will list-down all Types we have in the database, the user will select one (ID) then we will ask to enter the new one and update the database. Here is the code..

python project code coffee consumption ali radwani

Last Function in this part will be to Delete a selected Coffee Type, so again we will list all Coffee Types we have and will ask the user to select the one to be Deleted, then we execute the SQL command to Delete the record from the database… Here is the code..

python project code coffee consumption ali radwani




What’s Coming: In Part-5 we will do the Follwing:

Writing three Function to Manage the Coffee Size, Functions are: Add New Coffee Size, Edit Coffee Size and Delete a Coffee Size.


..:: Have Fun with Coding ::.. 🙂



::.Coffee Consumption Parts.::

Part 1 Part 2 Part 3 Part 4 Part 5
Part 6 Part – Part – Part – Part –


To Download my Python code (.py) files Click-Here



ali radwani ahradwani.com python projects codeFollow me on Twitter..

By: Ali Radwani

Python: Coffee Consumption – P3

August 22, 2021 4 comments

Learning : Python, SQlite3, Dataset, Pandas,
Subject: Create Coffee Consumption Application.

[NOTE: To keep the code as simple as we can, We WILL NOT ADD any user input Varevecations. Assuming that our user will Enter the right inputs.]


[ IF THE IS FIRST TIME DOWNLOADING THE CODE FILE, SELECT OPTION 7 FROM MAIN-MENU TO CREATE THE DATABASE. ]


In this part (Part-3) of Coffee Consumption App, we will fill in some code into selected Functions. We will do the following:

  • Create Function: Show list.
  • Create Function: is_available.
  • Create Function: Manager Menu.
  • Create Manager Coffee Name Functions: Add, Edit, Delete

So, let’s start with writing the Manager Menu Function and a while loop to take the user selection and trigger the corresponding Function… Here is the Code ..

python project sql code coffee consumption ali radwani

We have two functions to help us in this application, one of them called def show_list(dt,d_id) is to Display the Data we have in the Lookup Tables based on the user selection function. The Function will take two arguments:
dt: data-table (Coffee Name, Coffee Type, Coffee Size)
d_id: id column name, and returning nothing.

Fisrt, let’s look at this Function:

python project sql code coffee consumption ali radwani


The second Function will be used to check if the user selection is available in the database, we will call it def is_available (dt, d_id, check_id) this Function is to check if the passed ID available in the data-set or not. The Function will take three Arguments as:
dt: Data-Table, d_id : Name of id column, check_id : The id we want to search for, and it will Return the data-set. After return, if dataset is empty that’s mean selected id is not available.

Now let’s see the function code..



Now we will start writing first three functions to manage the Coffee Name, and will start with Adding New Coffee Name to the lookup Table.
In coming code first we will call the header then show_list(‘coffee_name’,’cn_id’) passing Table Name:’coffee_name’ and id column:’cn_id’ to display the Coffee Names we have on the sccreen. Then we will ask the user to enter the New Name to be added to the database. Here is the Full code..

python project sql sqlite code coffee consumption ali radwani



Next we will write the Edit Function, and again after the header and show_list(‘coffee_name’,’cn_id’) we will ask the user to enter the ID of the Coffee Name to be change, here we will do a simple validation on user input. After that we will update the record that the user select. Here is the code..

python project sql code coffee consumption ali radwani



Last Function in this part is to Delete a selected Coffee Name by selection it’s ID, as in the Edit Fnction, the user will select an Id, we will check the availability the will execute the Delete command. Here is the code..


What’s Coming: In Part-4 we will do the Follwing:

Writing three Function to Manage the Coffee Type, Functions are: Add New Coffee Type, Edit Coffee Type and Delete a Coffee Type.


..:: Have Fun with Coding ::.. 🙂


Part 1 Part 2 Part 3 Part 4 Part 5
Part 6 Part – Part – Part – Part –


To Download my Python code (.py) files Click-Here



ali radwani ahradwani.com python projects codeFollow me on Twitter..

By: Ali Radwani

Python: Coffee Consumption Part-1

August 12, 2021 6 comments

Learning : Python, SQlite3, Dataset, Pandas,
Subject: Create Coffee Consumption Application.

[NOTE: To keep the code as simple as we can, We WILL NOT ADD any user input Varevecations. Assuming that our user will Enter the right inputs.]

Last week I post an articale about generating Fake Data-Set about Coffee Consumption, and using that dataset in ML and DeepLearning. [Read the Story]. I’ve got a request from someone asking to build a reall Full Application that can be used in a Coffee Shop and collect/track his Coffee orders (consumption). So here is the application.

Road Map

Brain Storming
In this python project will collent some informatin based on a friend requerment. Our Task is:

  • Data Gathering.
  • Designing the Database and Tables.
  • Writing python Functions to do the Following:
    • Create the Main-Menu. [Add, Edit, Delete for main Table]
    • Create the Manager Menu. [Add, Edit, Delete for each lookup Tables]
    • Create the database and the tables:
      • Main: coffee_con.
      • Lookup: coffee_name.
      • Lookup: coffee_type.
      • Lookup: coffee_size.
    • Inserting Zero record.
    • Inserting Basic Data in all Lookup tables.
    • Creating all Functions we need such as:
      • Add, Edit, Delete Coffee Names.
      • Add, Edit, Delete Coffee Types.
      • Add, Edit, Delete Coffee Sizes.

    ..:: In Part -1 ::..
    In this part, we will create a python file, and will write all the functions header [we think we may need] so late we can add the codes inside, of course we may add other functions later [if we miss it here].
    So, starting from the Main-Menu, we will have
    – Add New Order.
    – Edit an Order.
    – Delete an Order.
    – Show Orders.
    [… SETTING …]
    – Coffee Names, Type and Size Manager.
    – Create the database.
    – Back-up Data. (as CSV)
    – Exit.

    Here is just a sample of the code, in this part no much coding of the Functoins, as we said only Functions Names, the full code is available in the Download Page.[ Click Here ]

    # Sample of Functions Names in our Application 
    
    import os
    import sqlite3
    
    
    def header():
        pass
    
    def create_data_base () :
        pass
            
    def main_menu():
        pass    
    
    def new_record():
        pass
                
    def edit_record():
        pass
        
    def delete_record():
        pass
            
    def backup_dataset():
        pass
    
    
    

    … The above are just a sample of Functions that we may have in the application, you can download and read the Project file.

    What’s Coming: In Part-2 we will do the Follwing:

    • Writing the Main Menu Function.
    • Creating the DataBase.
    • Writing the Function to Create the Tables.
    • Inserting the Zero Records.

    ..:: Have Fun with Coding ::.. 🙂

    Part 1 Part 2 Part 3 Part 4

    To Download my Python code (.py) files Click-Here



    ali radwani ahradwani.com python projects codeFollow me on Twitter..

    By: Ali Radwani

Python: Sorting Algorithm (1.Quick Sort)

May 23, 2021 8 comments

Learning : SQL, Python, sqlite, DataBase
Subject: Testing the SQL Join commands using Python.

Sorting Algorithm is a way to sort a given list of numbers, there are several sorting Algorithm as follow:
Type of Sorting Algorithm
Quick Sort.
Bubble Sort.
Merge Sort.
Insertion Sort.
Selection Sort.
Heap Sort.
Radix Sort.
Bucket Sort.

Here in this post we will write a function to take a given list and sort it then pass it back. We assume the user will enter a serial of numbers, that he want to sort, our function will sort it and print out the original numbers and the sorted one.

Quick Sort Steps of Quick Sorting Algorithm are:
1 – Save the first element of the list as pivot. We will call it as pv .
2 – Define Two variables i and j. We will call them as fc, lc fc will be 0 (first element position in the list) and lc will be the length of the list.(last element position in the list) .
3 – Increment fc until the number in the list in fc position is smaller or equal to pv (the first element).
4 – Decrement lc until the number in the list in lc position smaller than pv.

until list[j] < pivot then stop.
5 – If fc less than lc then we swap the two elements in the location of fc and lc. (SWAP list[fc] and list[lc]).

7 – Exchange the pivot element with list[j] element.

Coding First we will write a sort Menu for the project, we will have tree items to select from, Quick Sort Algorithm – Fast Run and Quick Sort Algorithm – Step By Step This will show sorting details.

 # Main Menu

def main_menu ():
    os.system('clear')
    
    print('\n\n',' '*5,'******************************')
    print(' '*5,' ***','  Sorting Algorithm ',' '*1,'***')
    print(' '*5,' ***','     Quick Sort     ',' '*1,'***')
    print(' '*5,' ***',' '*22,'***')
    print(' '*5,' ******************************\n\n')
    
    print(' '*7,'1. Quick Sort Algorithm - Fast Run.')
    print(' '*7,'2. Quick Sort Algorithm - Step By Step.')
    print(' '*7,'9. Exit.')
    
    user_choice = input('\n   Select your choice.  > ')
    return user_choice 



And this is the main code body that will call the menu and check the user selection ..

 # The Main application Body

while True:
    
    user_select = main_menu()

    if user_select == '1' :
        user_list = create_list()
        
        fpos = 0  # first position index
        lpos = len(user_list)-1  # last position index
        
        original_list = user_list 
        print('\n   The original List is: ',original_list)

        user_sorted_list = quick_sort(user_list,fpos,lpos)
        
        print('\n   DONE .. We Finish Sorting .. ')
        print('   The Sorted List is: > ',user_sorted_list)
        input('\n   ...Press any key to continue.  ')
    
    if user_select == '2' :
        user_list = create_list()
        
        print('\n   We will show the Quick Sorting Step By Step... \n')
        fpos = 0  # first position index
        lpos = len(user_list)-1  # last position index
        original_list = user_list

        print('\n   The Original List is: ',original_list)
        user_list = quick_sort_details(user_list,fpos,lpos)
        
        print('\n   DONE .. We Finish Sorting .. ')
        print('   The Sorted List is: > ',user_list)
        input('\n   ...Press any key to continue.  ')

    if user_select == '9' :
        break



Also we will have a Function to take the List of Elements from the user, the user input will be as a string, we will convert it to an integer List and will return it back.. Here is the code ..

 # Create the List

def create_list():
    print('\n   Enter the List Elements separated by SPACE, when Finish just Press Enter.')
    the_list = input('\n   Start Entering the Numbers in the List. >  ')
    
    # Convert user input to List
    the_list = the_list.split()        
    
    # Convert str list to int list  
    the_list = [int(each) for each in the_list]

    return the_list



Now let’s write the Quick_sort function, then we will duplicaet it and add some print statements to show sorting steps. So first the code for Quick Sort Algorithm – Fast Run. Here is the code ..

Screen shot of the Quick Sort Algorithm – Fast Run.
python project code Quick Sort Algorithm ali radwani



Running this Function will return the sorted list and display it on the screen, I thought it will be nice if we show the sorting process Step by Step, so I copy the same Function with adding some print-statement in-between .. here is the code and the run-output..

Screen shot of the Quick Sort Algorithm – Detail Run.
python project code Quick Sort Algorithm ali radwani



End of Sorting Algorithm (1.Quick Sort)

To Download my Python code (.py) files Click-Here



Follow me on Twitter..

By: Ali Radwani

Python: Testing the SQL joines Commands


Learning : SQL Commands, DataBase, Python
Subject: Testing the SQL Join Commands


Introduction In this post we will apply several SQL Join Commands to test them, here are them:

Left Join:
All the Data in the Category
ali radwani learning python sql joins commands
Right Join: All the Data in the Products ali radwani learning python sql joins commands
Inner Join:
All the Data that in Both Tables.
ali radwani learning python sql joins commands
Left Join
Only Data in Category Table and NOT in Product Table.
ali radwani learning python sql joins commands
Right Join:
Only data in Product Table and NOT in Category Table.
ali radwani learning python sql joins commands
Full Outre:
All the Records in both Tables
ali radwani learning python sql joins commands
Full Outre:
All the data from the Category Table that are NOT linked to any Product, AND all the data in Product Table that has NO Category.
ali radwani learning python sql joins commands


First: let’s talk about the Tables,we will create two Tables (Category and Products) each will be very simple, the Category will have two fields [c_id, and cat_name],
the Products will have [p_id, pro_name, cat_id]. (cat_id is the foreign key that connicting the two tables). Some test data will be inserted in both Tables so we can demonstrate the SQL Commands. I create a Dictionary storing information/summary about the project named about_the_project the user can read it in run time.

Coding: We will do the fowlloing:
1. Set a Database for the Project.
2. Writing the codes to Create the Tables.
3. Writing the codes to insert some test Data in the Tables.
4. Creating a Menu for the Project.
5. Writing the Functions to Apply the SQL Join commmands.

NOTE: All the code will be avilable in the DOWNLOAD page.

Let’s import some lybraries, we will need this code:

import sqlite3, os

And here is the code to set the data base and create the connection.
# Create the data-base and name it as Share_S_System.
db_conn = sqlite3.connect (“sql_join_test.db”)

# set the connection.
c = db_conn.cursor()

First function is to create the Tables:

 # Function to create the Tables.

def create_tables ():
    
    # CREATE TABLE:
    category_t = "CREATE TABLE if not exists category_t (c_id INTEGER PRIMARY KEY AUTOINCREMENT, cat_name text)" 
    c.execute(category_t) 
    db_conn.commit()     
    
    product_t = "CREATE TABLE if not exists product_t (p_id INTEGER PRIMARY KEY AUTOINCREMENT, p_name text, cat_id integer)" 
    c.execute(product_t) 
    db_conn.commit()     


Now we will insert some Test data.

 # Function to insert some Data into the Tables

def insert_sample_data():
    
    #First we will call the Function to Crete the Table.
    create_tables ()
    print('\n   Category Table has been Created. ')
    print('\n   Product Table has been Created. ')
    
    # Insert into Category Table
    c.execute ("INSERT INTO category_t (cat_name) VALUES(:cat_name )",{"cat_name":"Mobile Phone"})
    c.execute ("INSERT INTO category_t (cat_name) VALUES(:cat_name )",{"cat_name":"Labtop"}) 
    c.execute ("INSERT INTO category_t (cat_name) VALUES(:cat_name )",{"cat_name":"Ext. HD"}) 
    c.execute ("INSERT INTO category_t (cat_name) VALUES(:cat_name )",{"cat_name":"CCTV Camera"})     
    db_conn.commit()

    # Insert into Product Table.
    c.execute ("INSERT INTO product_t (p_name, cat_id) VALUES(:p_name, :cat_id )",{"p_name":"Note 9","cat_id":"1"})
    c.execute ("INSERT INTO product_t (p_name, cat_id) VALUES(:p_name, :cat_id )",{"p_name":"iPhone 12 pro","cat_id":"1"})
    c.execute ("INSERT INTO product_t (p_name, cat_id) VALUES(:p_name, :cat_id )",{"p_name":"HP","cat_id":"2"})
    c.execute ("INSERT INTO product_t (p_name, cat_id) VALUES(:p_name, :cat_id )",{"p_name":"Toshiba 1T HD","cat_id":"3"})
    c.execute ("INSERT INTO product_t (p_name, cat_id) VALUES(:p_name, :cat_id )",{"p_name":"iMac","cat_id":None})
    db_conn.commit()
    
    print('\n   Sample Date Inserted in both tables.\n   Select 9 from the Main Menu to see the Data')
    
    input('\n      ... Press any key to continue.  > ')


Now, let’s write the function for the Main Menu, it will return the user_choice.. here is the code..

 # Main Menu Function.

def main_menu():
    os.system('clear')
    print("\n==========[ Main Menu ]==========")    
    print(' 1. About this Project.')
    print(' 2. All Categories. (Left Join: All in Category Only).')
    print(' 3. All Products. (Right Join: All in Product Only).')
    print(' 4. Only Categories that linked to a Products. (Inner Join: Only if in Ta AND Tb).')
    print(' 5. All Catigories that are NOT linked to a Product. (Left Join: Only in Category AND NOT in Product .')
    print(' 6. All Products that has NO Category. (Right Join: Only in Product AND NOT in Category.')
    print(' 7. All Categories and Products. (Full Outer Join in Both Table)' )
    print(' 8. All Categories that are NOT linked to any Product, and All Products that has NO Categotry. (Full Outer Join NOT in Both Table)')
    print(' 9. Show the Data') 
    print(' 11. Setting: Create the Tables and Insert Some Sample Data. Run Only One Time.')
    print(' 99. Exit.')

    user_choice = input("\n Select from the Menu: > ") 
    return user_choice
ahradwani.com python code SQL join


The coming part of the code is calling the functions and running the SQL commands based on the user choice. Here is the Main-code-body..

 # The Main section of the Application..

while True :
    user_select = main_menu()

    if user_select == '1' :
        show_data('cat') 

    if user_select == '2' :
        show_data('prod') 

    if user_select == '3' :
        os.system('clear')
        print('\n   First: All the Data in Category and Product ')
        show_data('both','inside')
        print("\n==========[ Show Date: INNER JOIN ]==========\n")
        print('   Inner Join: Only the Data that exist in Category AND Product).')
        print('\n   The SQL Statment:\n    select category_t.c_id, product_t.p_name from category_t inner join product_t on category_t.c_id == product_t.cat_id\n\n ')
        c.execute ("select category_t.c_id, category_t.cat_name, product_t.p_name from category_t inner join product_t on category_t.c_id == product_t.cat_id  ") 
        innerJ_list = c.fetchall() 
     
        print('   [OUTPUT]:\n       Categories Name      |',' '*2,'Products Name    ')
        for innerJ_data in (innerJ_list): 
            print('       ',innerJ_data[1],'.'*(25 - len(str(innerJ_data[1]))),innerJ_data[2]) 
            
        print('\n   We can see that the INNER JOIN Command fetched the records of Products that has a Category.\n   iMac Not listed Because it has No Category Data.')         
        input('\n\n      ...Press any key to continue.  > ') 
    
   if user_select == '4' :
        os.system('clear')
        print('\n   List of All the Data in Category and Product ')
        show_data('both','inside')
        print("\n==========[ Show Date: LEFT JOIN, Only Data in Categoty Table ]==========\n")
        print('   The SQL Statment:\n     select category_t.c_id, category_t.cat_name, product_t.p_name from category_t\n     left join product_t on category_t.c_id == product_t.cat_id\n     where product_t.cat_id is null\n')
        c.execute ("select category_t.c_id, category_t.cat_name, product_t.p_name from category_t left join product_t on category_t.c_id == product_t.cat_id where product_t.cat_id is null") 
        leftJ_list = c.fetchall()     
        print('   [OUTPUT]:\n       Categories Name      |',' '*2,'Products Name    ')
        for leftJ_data in (leftJ_list): 
            print('         ',leftJ_data[1],'.'*(25 - len(str(leftJ_data[1]))),leftJ_data[2]) 
              
        print('\n   We can see that the LEFT JOIN Command fetched all Categories that are not used/linked to/with any Product.\n   CCTV Camera is Not linked. So We can Say: We Do Not have any CCTV Camera Products.')         
        input('\n\n      ...Press any key to continue.  > ') 

    if user_select == '5' :
    ....
# Just to save the time, i will not post all the code here, you can see all the code and download
# the .py source code from the download page.


Here are some screen shot of the output ..

The Main Menu

ahradwani.com python code SQL join functions commands

Output Screen for Option 3: Only Categories that linked to a Products.
(Inner Join: Only if Data in Category AND Product)
ahradwani.com python code SQL join functions commands



End of the Post, and all the code is available to download from the Download page.


To Download my Python code (.py) files Click-Here



Follow me on Twitter..

By: Ali Radwani