Archive

Posts Tagged ‘Language’

Excel : Form to Save Data P-1

March 31, 2020 Leave a comment


Learning : VBA Codes to Save Data
Subject: Create Form to collect Data

In a very fast and as simple as we can, we will design and write VBA codes to transform three fields of Data from an Excel sheet “Form” to another sheet “Data”.

To keep thing as simple as we can, we will not use any Validations on user inputs in this example.


First, we will re-name a sheet to “Form” and another one to “Data”. In sheet [Form] we will create a simple Form to collect Names, Company Name and Emails of our customers, also we add a button and call it Save. As in Image-1

Image-1


For companies Name, We will create a list then we will link it to Cell”D9″ so we can select a “Company Name” from a drop-down list. So to do this First in the Data sheet I will write a list of companies Name as (Comp Name 1,Comp Name 2,Comp Name 3, …. to Comp Name 10). I start my list from Cell “H8” [As in Image-2] you may start from any Cell you want.

Image-2


Now to link a Dropdown list of our companies Name to Cell “D9″ Do this:
1. Goto Cell”D9”.
2. From the menu we will select “Data” then “Data Validation” and select Data Validation. [Image-3]

Image-3


3. In the Allow [Select “List”], then in the Source we select the range of Companies we write [Range Cells H8:H17] or just write this: =$H$8:$H$17. Then click OK. See Image-4

Image-4

Now if we try to click on Cell “D9” we will have a list as in Image-5.

Image-5

In the “Data” Sheet we will just create the Table Header as in Image-6, and will go-back to “Form” Sheet.

Image-6


Now we will write the VBA codes and link it to to a “Save” Button we create. To open the Visual-Basic window we select “Developer” From the Top menu, then Press Visual Basic.

Then we write this code and link it to the Save Button. ..

 # VBA Macro code to Save Data to Data sheet 

Sub Save_data()
'
' Save_data Macro
' Macro recorded 2020-03-29 by HP
'

'
    ' Set Variables
    Name = Range("D7").Value
    comp_name = Range("D9").Value
    Email = Range("D11").Value


    ' Goto data sheet
    Sheets("Data").Select
    ' This line will get the Next empty Row in the Data sheet.
    emp_row = Range("A" & Rows.Count).End(xlUp).Offset(1).Row
    Range("A" & emp_row).Value = Name
    Range("B" & emp_row).Value = comp_name
    Range("C" & emp_row).Value = Email
   
    'Go to Form Sheet.
    Sheets("Form").Select
   
    ' Clear data cells.
    Range("D7").Value = ""
    Range("D9").Value = ""
    Range("D11").Value = ""
   
    Range("D7").Select
   
End Sub



Now if we enter some data [as we said: No Validation on the data] and press the Save button, the data will be coped to next empty row in the Data Sheet.



Enhancement: In some cases as in our coming project, it’s better to create a sheet and call it “Setting”, then we can have our Lists (such as Company-Name), Colors, Filters all to be in the Setting sheet. [We will see this in the Next Project.]



Follow me on Twitter..




By: Ali Radwani




Python and Excel P1

March 24, 2020 Leave a comment


Learning : Python and Excel Part-1
Subject: Read Excel file

In the coming four or five Lessons we will work on reading and writing to Excel file using Python code.

First we need to import os and Pandas as in coming code block, I will use os later to fetch file information. Also we will set the file_name variable to our file assuming it is in the same .py directory and we will call [read] the file in to df [dataframe]. Here is the code

 # Import and call the File into DataFrame.
 
  import os, pandas as pd
file_name = 'python_sheet_4.xlsx'
df = pd.read_excel(file_name, sheet_name='Sheet1')

Now let’s talk about my excel file “python_sheet_4.xlsx” has two sheets named “sheet1 and sheet2” both contin same table sheet1 the table start from cell A1 and in sheet2 the table start from cell C7. So first we will work on sheet1 that’s way we call sheet1 in our df setting statement.

Now we have the df (dataframe), and if we run the this code:

 # To print out the data from df DataFrame.
 
print(df)

The Output..



Here is the original shot for our excel table



Here is a list of commands that we can use..

sheets_name = (pd.ExcelFile(file_name)).sheet_names
print(‘\n Number of Sheets in the file: ‘,len(sheets_name))
print(‘\n Sheets Name :’,*sheets_name,sep=’,’)

Command Action
print(df) Print all the data in the table
print(df.head()) Print the first 5 record in the table
print(df.head(10)) Print the first 10 records in the table, and
print(df.head(x)) will will print the first x record.
print(df.columns) Print out the table header as a list along with data type.
print(*df.columns,sep=’,’) This command will printout only the table header separated by (,)
print(df.shape) This will print the size of the table, in our case the
out-put will be (17,3) so we have 17 records and 3 columns.


If we wanr only the number of records we shall use print(df.shape[0])
sheets_name = (pd.ExcelFile(file_name)).sheet_names This command will return the Number and name of sheets in the file, then we
can print it out like this:

print(‘\n Number of Sheets in the file: ‘,len(sheets_name))
print(‘\n Sheets Name :’,*sheets_name,sep=’,’)



To make things in a professional way, we will write a function so we can use it with other applications, here is it..

The Code
[Out-Put]:



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



Follow me on Twitter..




By: Ali Radwani




Python: Cooking App P-7



Learning : Python, Data-Base, SQlite
Subject: Writing a Cooking Application

RECIPES MENU: Delete Recipe To Delete a recipe we need to point on it, we will do this by it’s ID. So we can show all recipes by names, then we select the one we want to delete and enter it’s ID, the system will ask to confirm this action by typing (y,Y) then it we will call the delete function. In Delete function “def del_recipe()” we need to delete all the data regarding this ID form three DB Tables, recipes, recipes_steps, rec_ingredient and photo. Although we did not use the photo table and and we just create it for future use, but er will write its code.

Future Plan: In coming weeks we will convert this application “Cooking Application” to GUI “Graphical User Interface” application using tkinter library (Tkinter: is a Python binding to the Tk GUI toolkit) .


Coding: So to Delete a recipe we will show all Recipes and there ID’s sorted by it’s Name and ask the user to enter the ID of the Recipe he/she want to delete.

 # Code to Delete a Recipe

def del_recipe():
    os.system("clear")
    print('\n ====== Delete a Recipe =====')

    # Start to list down all the Recipes Name.
    print("\n\n  List of ALL Recipes we have.")
    c.execute("select r_id,r_name from recipes where r_id > 0")
    for each_r_name in c.fetchall():
        print('   ID:',each_r_name[0], 'Name:',each_r_name[1])

    # Now we ask the user to Enter the Recipe ID. 
    rec_del = input('\n\n   Enter an Recipe ID to be Deleted: ')
    
    # Now we ask the user to Confirm Deleting Recipe.
    sure_remove = input('\n The Recipe will be DELETE and can''t be Rolled-Back.
Are you sure you want to Remove it [Y,N] ') if sure_remove in ['y','Y']: c.execute ("Delete from recipes_steps where r_id = {}".format(rec_del)) db_conn.commit() c.execute ("Delete from recipes where r_id = {}".format(rec_del)) db_conn.commit() c.execute ("Delete from rec_ingredient where r_id = {}".format(rec_del)) db_conn.commit() c.execute ("Delete from photo where r_id = {}".format(rec_del)) db_conn.commit() elif sure_remove in ['n','N']: # If the user decide not to delete and select N print('\n You select NOT to remove the Recipe.') else :# If the user enter anything else than Y or N print('\n You must select (Y or N).') input('\n .. One Recipe Removed .. Press any key .. ')




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




Follow me on Twitter..




By: Ali Radwani




Python: Cooking App P-6



Learning : Python, Data-Base, SQlite
Subject: Writing a Cooking Application

RECIPES MENU: Adding a Recipe.To Add a recipe we need to work on three tables, the user will not know that, the user dose’t care what happening in the application, he/she want the cooking information to be saved and retrieved whenever required.
To do this we First will ask the user to Enter some basic information about the Recipe, such as Name, Date selecting it’s type like is it a Lunch or Dinner and other thing we will see it later while writing the code, then we need to enter the Ingredients of each Recipes, the Ingredients are store in another DB-Table based on our DB-design, so we will list down all the Ingredients and ask the user to select from the list by typing the ID’s of those they want, and if an ingredient does not exist in the list the user will write it.Finally we will ask the user to write the cooking steps.
This is not easy way to do it using Terminal-screen or console (Command line application CLIs), we will convert this application to GUI (Graphical User Interface) later after we finish the application.

Now, let’s start with the basic information about the Recipes. In Basic Information we have:
Recipe Name: To give a Name to the Recipe.
Recipe Date: The user can Write a date.
Recipe Type: The user will select one of: [Breakfast, Dinner, Lunch, Snack, Sweet]
Recipe Details: Other Details such as the time needed for cooking, other notes.
Recipe Tags: Tags will not be available in this version, but we will work on it when we start doing the GUI version.

So let’s start writing the code..

 # Adding New Recipe

def add_recipe():

    os.system("clear")
    print('\n ====== Adding New Recipe =====')

    print('\n First let''s Enter some Information about the Recipe..')
    r_name = input('\n\n   Enter The Recipe Name: ').capitalize()
    r_date = input('\n\n   Enter The Date in dd.mm.yyyy format: ')
    
    # Now to select the Recipe Type.
    print('\n   A Recipe Type can be:\n    1.Breakfast.\n    2.Dinner.\n 
3.Lunch.\n 4.Snack.\n 5.Sweet. ') r_type = False while r_type == False : rec_type = input('\n\n Select a Type [1,2,3,4,5]: ') if rec_type =='1' : rec_type ='Breakfast' r_type = True elif rec_type =='2' : rec_type ='Dinner' r_type = True elif rec_type =='3' : rec_type ='Lunch' r_type = True elif rec_type =='4' : rec_type ='Snack' r_type = True elif rec_type =='5' : rec_type ='Sweet' r_type = True else : print('\n You have to select from the List') r_details = input ('\n Enter Short information for the Recipe, like \n
Time for cooking,\n Country of this Meal,\n
Other short details.\n [ USE (,) to SEPARATE EACH INFO ]\n\n Enter here: ') c.execute ("INSERT INTO recipes (r_name, r_date, r_type, r_details )
VALUES(:r_name,:r_date,:r_type, :r_details)",{"r_name":r_name,"r_date":r_date,"r_type":rec_type, "r_details":r_details}) db_conn.commit()



Now, we save the Basic Recipe Information and we have the Recipe ID (PK) and we will use it as a Foreign Key in Recipe-Ingredient and Recipe-Steps. With Recipe-Ingredient we will list down all the recipes and let the user to enter the Id’s of the one’s they will use with there recipe. If the needed Ingredient is not in the list, the user will insert it and it will be added to the Ingredient list. Here is the code for this section. .

 #This part of code continuing the above part. 


    os.system("clear")
    # Select the Ingredient for the Recipe.
    print('\n Now, Select the Ingredients of your Recipe:')
    print("\n If the Ingredient is not in the List you will Add it
shortly, FIRST select the exist ones\n write it''s ID Separated
by ( , ) ") print('\n List of Ingredients:') list_of_ing() ing_l = input('\n Enter the Ingredients ID Seperated by ( , ) ') if input ('\n Is there any more Ingredients you want to add? [Y,N]: ') in ['y','Y'] : new_ing = input('\n Enter the Ingredients Seperated by ( , ): ') new_ing = new_ing.split(",") # To get max ingredients ID. c.execute ("select max(i_l_id) from ingredients_list") max_ing_id = c.fetchall()[0][0] ing_l2 =[] # To add the new ingredients to the ingredients_list . for each in range(len(new_ing)) : c.execute ("INSERT INTO ingredients_list (i_name) VALUES(:i_name)",{"i_name":new_ing[each].capitalize() })#ing_name}) db_conn.commit() ing_l2.append (str(max_ing_id+(each+1))) ing_l=ing_l.split(",") ing_l2=ing_l + ing_l2 # To link the ingredients to the recipe. for each in range (len(ing_l2)): c.execute ("INSERT INTO rec_ingredient (i_l_id, r_id) VALUES(:i_l_id, :r_id)",{"i_l_id":ing_l2[each],"r_id":rec_max }) db_conn.commit()


Now, we have the all Ingredients of the Recipe, also we have the ID or the Recipe, so we can link the Recipe information to the cooking Steeps.
Here the user will write the cooking steps until the user press (Q,q) and Enter.

 #This part of code continuing the above part. 

print('\n  Now we can Write the Cooking Steps, Press Enter After each Step. When you finish just enter (q). ')

    stop = False
    while stop == False :
        c_step = input('\n Enter a Cooking Step: [Press Enter After each Step. q to Finish] :\n   Write Here : ')
        if c_step not in ['q','Q'] :
            c.execute("INSERT Into recipes_steps (r_id , rec_steps) values (:r_id, :rec_steps)",{"r_id":rec_max,"rec_steps":c_step})
            db_conn.commit()
        else :
            stop = True


    input('\n   .. One Recipe Added ..   Press any key .. ')




Here are some screen shots of the code and running out-put.

In Next Part: In the next coming part we will work on the Deleting Recipe.



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




Follow me on Twitter..




By: Ali Radwani




Python: Cooking App P-5

February 13, 2020 Leave a comment


Learning : Python, Data-Base, SQlite
Subject: Writing a Cooking Application

RECIPES MENU: Once I start reading the Net (blogs and Cooking Books) to see how cookers talking about foods-recipes, I realise that I may need to add something to the DataBase, then I start writing the codes for the “Recipe Menu” and immediately I find the we need to add a new Table; I call it “recipes_steps” it will hold several steps of how to cook a recipe.

DataBase Diagram

Here is the code to create the new Table and adding the record 0 (Zero).
# To create the Table “recipes_steps” .
sql_recipes_steps =”CREATE TABLE if not exists recipes_steps (st_id INTEGER PRIMARY KEY AUTOINCREMENT,r_id integer, rec_steps text )”
c.execute(sql_recipes_steps)
db_conn.commit()

# To add the Record Zero.
c.execute (“INSERT INTO recipes_steps (st_id) VALUES(:st_id)”,{“st_id”:0})
db_conn.commit()

First let’s see the “Recipe Menu” and from there we will write the codes for each item in the menu. As we saw in Ingredients Menu, same functionality should be here to, Listing all Recipes, Adding new one, Editing and Deleting a recipes. We will write the code for each of them, and here is the code for the “Recipes Menu”

 # Recipes Menu - Function
 
def recipes_menu ():
    while True:
        os.system("clear")
        print('\n ===========[ RECIPES MENU ]=============')
        print('  --------------------------------------')
        print(' 1. Show Recipes.')
        print(' 2. Add Recipe.')
        print(' 3. Delete Recipe. ')
        print(' 4. Edit Recipe.')
        print(' 9. Exit')
        uinp= input('\n Enter your Selection: ')

        if uinp == '1':
            show_recipes()
        elif uinp == '2':
            add_recipe()
        elif uinp == '3':
            del_recipe()
        elif uinp == '4':
            edit_recipe()
        elif uinp =='9':
            return
        else:
            print('\n  Please select from the Menu.')


So first thing we will work on “Show Recipes”, once the user select Show Recipes another Menu will be display to give more option as in the screen shot..

To display the Recipes based on the options we have, we will write a function that take a parameter based on the user selection so the user can search for a recipe based on it’s Name, Date or other details. Let’s see the code ..



NOTE THAT: We did nothing with Tags option, after we finish the application we will assume that the Business owner ask to add a new requirement to the application, and we will see the impact of this and who to solve it.


In Next Post we will work on the Adding New Recipe to our Cooking Application.



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




Follow me on Twitter..




By: Ali Radwani




Python: Cooking App P-4

February 11, 2020 Leave a comment


Learning : Python, Data-Base, SQlite
Subject: Writing a Cooking Application

INGREDIENT MENU: In this post we will write the Python code for the Ingredient Menu Functions. We have four main Functions :

1. Show all Ingredient.
2. Add Ingredient.
3. Delete Ingredient.
4. Edit Ingredient.

We need the Ingredient List so the user can select an Ingredient for the Recipes they want to write. First we will list down all the Ingredient in the Table.

show_ingredient is a function to list all the ingredient in “ingredients_list” Table, here is the code ..

 # Show Ingredient Function
 
def show_ingredient():

    os.system("clear")
    print('\n ====== Show All Ingredient =====')
    show_sql = c.execute('select * from ingredients_list where i_l_id > 0 order by i_name')
    for each in c.fetchall() :
        print ('  ',each[0],'....',each[1])

    input('\n   ..   Press any key .. ') 


add_ingredient Function will let the user to add more Ingredients to the list.

 # Add New Ingredients Function
 
def add_ingredient():

    os.system("clear")
    print('\n ====== Adding New Ingredient =====')
    ing_name = input('\n\n   Enter an Ingredient you want to add: ').capitalize()
    c.execute ("INSERT INTO ingredients_list (i_name) VALUES(:i_name)",{"i_name":ing_name})
    db_conn.commit()

    input('\n   .. One Ingredient Added ..   Press any key .. ')


del_ingredient To Delete an Ingredients we need to enter it’s ID, and If we Delete one and that one was used in any recipes, then once we view the recipe the Ingredients will not show there.

 # To Delete an Ingredient 

def del_ingredient():

    os.system("clear")
    print('\n ====== Delete an Ingredient =====\n')
    c.execute('select * from ingredients_list where i_l_id > 0 order by i_name')
    for each in c.fetchall() :
        print ('  ',each[0],'....',each[1])

    ing_del = input('\n\n   Enter an Ingredient ID to Delete: ')

    if ing_del.isnumeric():
        sure_remove = input('\n Please Note that if you Delete this Ingredient it will not show in any recipes that use it.\n   Are you sure you want to Remove it [Y,N] ')

        if sure_remove in ['y','Y']:
            c.execute ("Delete from ingredients_list where i_l_id = {}".format(ing_del))
            db_conn.commit()
        elif sure_remove in ['n','N']:
            print('\n You select NOT to remove the Ingredient.')
        else :
            print('\n You must select (Y or N).')

        input('\n   .. One Ingredient Deleted ..   Press any key .. ')
    else:
        print('\n   You must Enter a Numeric Ingredient ID.')
        input('\n   ..  Press any key .. ')
    


edit_ingredient To Edit an Ingredients we need to enter it’s ID, so first we will list down all Ingredients we have then the user will select the one to be edit. Here is the code to do so..

 # To Edit an Ingredients. 

def edit_ingredient():
   
    os.system("clear")
    print('\n ====== Edit an Ingredient =====')
    print('\n\n :: List of Ingredients.\n')

    c.execute('select * from ingredients_list where i_l_id > 0 order by i_name')
    for each in c.fetchall() :
        print ('  ',each[0],'....',each[1])
    ing_edit = input('\n\n   Enter an Ingredient ID you want to Change: ')

    if ing_edit.isnumeric():
        c.execute('select i_name from ingredients_list where i_l_id  = {}'.format(ing_edit))
        print('  You select to change the: ',c.fetchall())
        new_ing = input('\n  Enter the New Update for it: ')

        c.execute("update ingredients_list set i_name = '{}' where i_l_id  = {}".format(new_ing.capitalize(),int(ing_edit)))
        db_conn.commit()
        input('\n   .. One Ingredient Updated ..   Press any key .. ')

    else:
        print('\n   You must Enter a Numeric Ingredient ID.')
        input('\n   ..  Press any key .. ')


Last thing we will Insert some Ingredients in the “ingredients_list” Table so we can test the functions, for this purpose I will write this piece of code that we can run to Insert some Ingredients.

 # To Insert sample ing_list

ing_list =["Onion","Spinach","Mushroom","Tomatoes","Lime","Turnip","Snake Beans",
"Jalapeno","Baking powder","sugar","Eggs","Vanilla extract","Salt","Flour","Butter",
"Oil","Whole milk","Orange juice","Baking soda","Ground cinnamon","Dark chocolate",
 "Cocoa","Cherry","Cherry jam"]

for item in range (len(ing_list)):
    ing_name = ing_list[item].capitalize()
    c.execute ("INSERT INTO ingredients_list (i_name) VALUES(:i_name)",{"i_name":ing_name})
    db_conn.commit()


Now, we can use the Delete, Edit and Add functions to test the “Ingredients Manager” from the Main Menu, here is some screen shots.

Show Ingredients
Add Ingredients
Delete Ingredients
Edit Ingredients

Note: The Screen-Shots above was before I add Order by clause to the SQL statement.

In Next post we will write the codes for the functions in the ” Recipe Menu”.



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




Follow me on Twitter..




By: Ali Radwani




Python: Cooking App P-3

February 9, 2020 Leave a comment


Learning : Python, Data-Base, SQlite
Subject: Writing a Cooking Application

Over View: In last post (Part-2) we wrote the codes for Main-Menu and other sub-Menus, today in this post we will work on “Setting Menu”.
Setting Menu has three items:
1. Create the needed Tables. [To create all tables we need in the database]
2. Drop Tables. [If any things goes wrong then we can Drop (Delete) a Table (or more) ]
3. Insert Dummy Data. [To Insert ONE Record in the tables. (Record 0)]

First thing we need to create a database and set a connection to it, also we will import the [sqlite3 and os], to do so we will write this code:


  import sqlite3, os

  # Create the data-base & name it.
  db_conn = sqlite3.connect ("cooking.db")    
  
   # set the connection.
  c = db_conn.cursor() 


Now we need to write three Functions for the Setting Menu, we will start with Table creation code and here I am posting the Data-Base Tables Diagram ..

Click to Enlarge

 # Function to Create the Tables


def create_tables_() :    # to create tables.

    sql_recipes = "CREATE TABLE if not exists recipes (r_id INTEGER PRIMARY KEY AUTOINCREMENT, r_name text, r_date text, r_type text, r_details text, r_tags text )"

    sql_photo = "CREATE TABLE if not exists photo (p_id  PRIMARY KEY AUTOINCREMENT ,r_id integer , p_name text)"

    sql_ingredients_list = "CREATE TABLE if not exists ingredients_list (i_l_id PRIMARY KEY AUTOINCREMENT, i_name text )"

    sql_rec_ingredient ="CREATE TABLE if not exists rec_ingredient (ing_id INTEGER PRIMARY KEY AUTOINCREMENT, i_l_id integer, r_id integer)"

    c.execute(sql_recipes)
    db_conn.commit()

    c.execute(sql_photo)
    db_conn.commit()

    c.execute(sql_ingredients_list)
    db_conn.commit()

    c.execute(sql_rec_ingredient)
    db_conn.commit()

    input('\n   .. Cooking  Tables created..  Press any key .. ')



So now if we call this function “create_tables_() “, Tables will be created. Another function in the Setting Menu is Drop a Table, as we mention above we need this function in case we need to Delete a Table and create it again, in this function first we will list all the tables in the database and ask the user to Enter the Table need to be delete. Here is the code behind this function:

 # Drop a Table.

def drop_table():

    try:
        # First we will print-out the list of Tables.
        c.execute("SELECT name FROM sqlite_master  WHERE type = 'table'")
        db_tables =[]
        for each in c.fetchall() :
            for t in each :
                db_tables.append(t)
        print('\n  Table List .. ')
        for x in range (len(db_tables)) :
            print((x+1),db_tables[x])
        t = int(input('\n  Enter the number next to Table you want to Drop:  '))
        c.execute("DROP TABLE {}".format(db_tables[t-1]))
        db_conn.commit()
        print('\n ... One Table been Dropped')

    except:
        print('\n  No Tables with this name .. ')

    input('\n  . . . Press any key  . . ')



Last code to write in this post is to Insert One-Record in the tables, the reason of doing this manually (Hard-Coded) is that we have some “Primary key AUTOINCREMENT” fields in our tables, and to make it AUTOINCREMENT there MUST be a number first so we will enter the ZERO record (0).

 # Function to Insert the Zero Record

def insert_rec_0():

    c.execute ("INSERT INTO recipes (r_id) VALUES(:r_id)",{"r_id":0})
    
    c.execute ("INSERT INTO ingredients_list (i_l_id) VALUES(:i_l_id)",{"i_l_id":0})
    
    c.execute ("INSERT INTO rec_ingredient (ing_id) VALUES(:ing_id)",{"ing_id":0})
    
    c.execute ("INSERT INTO photo (p_id) VALUES(:p_id)",{"p_id":0})

    db_conn.commit()
   
    input('\n ...Dummy records been Inserted ....  Press any key  .. ')


In Next post we will write the codes for the functions in the “INGREDIENT MENU” to Add, Delete and Edit Ingredient List.



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




Follow me on Twitter..




By: Ali Radwani