Posts Tagged ‘sqlite’

Python: Cooking App P-8

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

RECIPES MENU: Show Recipe During writing all functions of the application I notes that my “Show Recipe” need to be updated, so in this post we will do some changes on “def show_all_rec(opt)” function.

First, we will change the if opt ==”name” to list all the Recipes Name with ID’s and ask the user to Enter a Recipe ID to show its information.
Here is the code ..

 # Changes on show_all_rec(opt) - if opt =="name"

if opt =="name" :
        #First we will list down all Recipes Names.
        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])
        get_rec_id = input ('\n Enter the Recipe ID you want to read: ')
        # Get the Recipe information based on ID.
        c.execute("select * from recipes where r_id ={}".format(get_rec_id))
        for each_basic in c.fetchall():
            print('\n  ID: ',each_basic[0])
            print('  Name: ',each_basic[1])
            print('  Date: ',each_basic[2])
            print('  Type: ',each_basic[3])
            print('  Other: ',each_basic[4])

            print('\n  The Recipe Ingredients: ')
            # Get all Recipe Ingredients.
            get_sql = '''


                    INNER JOIN rec_ingredient ON ingredients_list.i_l_id = rec_ingredient.i_l_id
                    where rec_ingredient.r_id = {}


            st = 1
            for each in c.fetchall():
                print('  ',st,': ',each[0])
                st = st + 1

            print('\n\n  Cooking Steps: ')
            # Get the Cooking Steps. 
            c.execute("select * from recipes_steps where recipes_steps.r_id = {}".format(each_basic[0]))
            st = 1
            for each in c.fetchall():
                print('\n    Step-',st,': ',each[2])
                st = st + 1

            print('\n ---------------------------------------------')

Here is the out-put screen :

First we have the Menus, we select 1,1,1 to
get “Show Recipe By Name”

Here is the List of All Recipes and we will enter
the ID of the one we want to see.

Here is the Recipes Information.

Notes That 1. We don’t have any Ingredient (we did not add any) so in coming post we will develop the Edit function to add some Ingredient and change/Update the Recipe Information.
2. We are not using any Validations, so if we enter any dummy data for Date the system will accept it.
3. We are not using any (avoiding Error code) such as Try…except.

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)"





    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():

        # 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 :
        print('\n  Table List .. ')
        for x in range (len(db_tables)) :
        t = int(input('\n  Enter the number next to Table you want to Drop:  '))
        c.execute("DROP TABLE {}".format(db_tables[t-1]))
        print('\n ... One Table been Dropped')

        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})

    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

Python: SQlite Project – P2

January 23, 2020 Leave a comment

Learning : Python and Sqlite3
Subject: Sqlite3, Database functions ” Employee App” P2

In Part1 of this project (Click to Read) we create the database and set the connection, also we create an Employee table with very basic fields and also we wrote a dummy_data() function to Insert some records into the table. And to make the application usable we wrote the Main-Men function and we test it with selecting to display the records that we have.

Today we will write other functions from our Menu. INSERT NEW EMPLOYEE: To Insert new employee we will ask the user to input or to fill the fields we have in our table such as First Name, Last Name and the Salary. .Let’s see the code ..

 # Insert Function

def insert_emp ():
    print("\n\n ======== INSERT NEW RECORD ========")
    if input("\n Do you want to enter new employee data press. (y,n) ") in ["Y","y"] :
        f_name = input("    Enter the first name: ")
        l_name = input("    Enter the last name: ")
        p_pay = input("    Enter the salary: ")

        c.execute ("INSERT INTO emp (fname,lname,pay) VALUES(:fname,:lname, :pay)",{"fname":f_name,"lname":l_name, "pay":p_pay})
        print(input ("\n  One record has been Inserted. .. Press any key .. ."))
    else :
        print(input ("\n  Ok .. you don't want to enter any data. .. Press any key .. .")) 

So, if we select to Insert a new Employee and we Enter First name as : Jacob Last Name as: Noha also we we set the salary to 3200 and press Enter, as in this page ..

Then if we select to show all data we have in the database, we can see the new record added..

DELETE AN EMPLOYEE: To Delete or remove an employee from the database, First we will print-out all the records on the screen and ask the user to enter the ID_ number of the employee he want to delete. As shown here ..

In the above example we select ID number 3 to be deleted and press enter, the system will show the record and ask to confirm the deletion and wait for ‘Y’ to be pressed, then the record will be deleted.
..Here is the code..

In this post we cover the INSERT AND DELETE of the records from the database, in the next post we will cover the SEARCH AND EDIT Functions also some search conditions like salary range and group-by command.

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

Follow me on Twitter..

By: Ali Radwani

Python: SQlite Project – P1

January 21, 2020 1 comment

Learning : Python and Sqlite3
Subject: Sqlite3, Database functions ” Employee App” P1

One of the most important thing about writing applications is to save or store some Data for later use. This data could be simple as setting information or basic user information or can be a full records about something you need to keep, such as health data, or employee contacts or other projects data. To store a huge data we use a Data-Base, some databases are free and open to be downloaded from the internet, one of them is SQLITE3, in Python we can use the Sqlite for small and personal projects. In this post We will use the Sqlite to write a simple project for Employees.

As that our goal is to learn how to use Sqlite and python code to track our employees, and to keep every thing as simple as we can, we will collect only three piece of information that’s will be First Name, Last Name and the Salary.

Functions: In any application there must be several functions to complete our works and tasks that we need to perform, in our Employee System we need to perform these tasks:

1. Show the Data we have.
2. Insert New Employee.
3. Delete an Employee.
4. Editing Employee information.
5. Search for Employee.

This is the most important functions in any application, we will start working on the system and see how things goes on.

First we MUST Creating the data base and set the connection, here is the code to do this and we will call our database as test.db.

 # Create the database. 

import sqlite3, os

db_conn = sqlite3.connect ("test.db")    # set the data-base name

c = db_conn.cursor()  # set the connection

To create the Employee table we will write this code and run it only ONE Time.

 # Create the Employee Table. 

def create_tabels_() :    # to create tables.

    # employee table
    sql_s= "CREATE TABLE if not exists emp (emp_id INTEGER PRIMARY KEY AUTOINCREMENT,
fname text,lname text, pay integer)" c.execute(sql_s) db_conn.commit() print(input('\n .. Employee TABLE created.. Press any key .. '))

Since we are learning and playing with our code, we may need to drop the table for some reasons, so here is the code to Drop the table we will re-call the function if we need-so.

 # Function to DROP a Table. 

def drop_table(tname):

    c.execute("DROP TABLE {}".format(tname))

Now after the creating of the Table we need to feed it with some data so we can see the records. To do so we will run a function called dummy_data.

 # Function to INSERT Dummy data into the Employee Table. 

def dummy_data():
        This Function will Insert 4 Dummy rows in the temp table, first record will set the emp_id to 1, the other
        record the emp_id will be AUTOINCREMENT.

        This Function to be run one time only.

# First record will have the emp_id set as 1, other records will be AUTOINCREMENT.
    c.execute ("INSERT INTO emp (emp_id, fname,lname,pay) VALUES(:emp_id, :fname,
:lname, :pay)",{"emp_id":1,"fname":"James","lname":"Max", "pay":"2000"}) c.execute ("INSERT INTO emp (fname,lname,pay) VALUES(:fname,:lname, :pay)",
{"fname":"Robert","lname":"Ethan", "pay":"1500"}) c.execute ("INSERT INTO emp (fname,lname,pay) VALUES(:fname,:lname, :pay)",
{"fname":"Jack","lname":"Leo", "pay":"890"}) c.execute ("INSERT INTO emp (fname,lname,pay) VALUES(:fname,:lname, :pay)",
{"fname":"Sophia","lname":"Jack", "pay":"320"}) db_conn.commit() print(input('\n Dummy Data has been INSERTED\n\n .. Press any key .. '))

Main Menu To use the application we need a Menu to jump between the tasks in the app. Here is the Main-Menu, it will return the user selection.

 # The Main Menu. 

def menu():
    print("\n\n      ::: The Menu :::")

    print("   1. Show the Data.")
    print("   2. Insert a New  Employee.")
    print("   3. Delete an Employee.")
    print("   4. Edit/Change employee data. ")
    print("   5. Search.")
    print("   6. Setting.")
    print("   7. Data-Base Information.")
    print("   9. Exit. ")

    uinput = input("\n     Enter a selection: ")

    return uinput

Here is the loop for the Menu and the user selection until (9. Exit) will be selected.

 # The Main Menu. 

while True :

    uinput = menu()

    if uinput == '1' :

    elif uinput =='2' :
        insert_emp ()

    elif uinput =='3' :

    elif uinput =='4' :

    elif uinput =='5' :

    elif uinput =='6' :

    elif uinput =='7' :
        #print("DataBase Information.")

    elif uinput =='9' :

    else: # If the user select something out of the menu (Numbers or Character)
        print("  You need to select from the list")

Now we remember that we run the dummy_data() function (above) so we have four records in our Employee Table, so if we want to see the records we will select first option in the Main Menu: 1. Show the data. this will call a function called show_data() as in this screen shot.

The screen prompt will wait for an input of the number that present the task we want. So if we select No. 2 then we will get all the records in the table as this .. .

And here is the code behind this function..

Done with Part 1, in part 2 we will cover more functions to Search and Add records to the Table.

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

Follow me on Twitter..

By: Ali Radwani

Python: My Fake Data Generator P-7

January 19, 2020 Leave a comment

Learning : Python: Functions, Procedures and documentation
Subject: About fake data P-7: (Fake File Name)

In this post we will write a function to generate a file name. Each file name consist of two part, first one is the name which present (or should present) a meaning part, then there is a dot (.) then mostly three characters showing the file type or extension.

Scope of work: Our files names will have 4 syllables all to gather will be one file name. Each syllables will be loaded in a variable as shown ..
1. fext: for Files extensions such as: (doc, jpeg, pdf, bmp …. and so on)
2. name_p1: Is a noun such as (customers, visitors, players .. . and so on )
3. name_p2: will be an nouns, adjective or characteristics such as (name, index, table .. .. and so on)
4. Then we will add a random integer number between (1,30) to give the file a version, or a number.

All parts or syllables will be as one file name.

Let’s Work: First we need to load the File name syllables from the json file called : file_dict.json

 # Loading the json from a url 

import json , requests, random

fname = ""

def call_json_url(fname):
    Function to load the json file from URL.
    Argument: str :fname
    Return : dict: data
    req = requests.get(fname)
    cont = req.content
    data = json.loads(cont)
    return data

fdict = call_json_url(fname)

# Save each syllables into variable. 
f_ext = fdict["fext"]
f_p1_name = fdict["name_p1"]
f_p2_name = fdict["name_p2"]

Now we will write the function that will generate the file name:

 # Function to generate the file name 

def generate_fname():
    Function to generate a Fake files name.
    File Name consist of four syllables, Two names, a random number and an extension.
    First two syllables of the file name will be selected randomly from a dictuenary stored in a json       file.
    Return : str : f_file_name

    To read the information key in the json file use this code.
    ------ CODE TO READ DATA-SET INFORMATION --------------
     	for each in fdict["information"]:

    ---END OF CODE ------------------------------------------
    fp1 = (random.choice (f_p1_name)["n_p1"])
    fp2 = (random.choice (f_p2_name)["n_p2"])
    fp3 = (random.choice (f_ext)["ext"])

    f_file_name = (fp1 + "_" + fp2 + "_" + str(random.randint(1,30)) + "." + fp3)

    return f_file_name

Last thing we just will call the function for X numbers of files Name we want.

 # Generate 15 file Name. 

for x in range (15):



:: Fake Function List ::

Function Name Description
Color To return a random color code in RGB or Hex.
Date To return a random date.
Mobile To return a mobile number.
Country To return a random country name.
City To return a random City name.
ID To return X random dig as ID.
Time To return random time.
Car’s Brand
file_name file name: list for fake file names.
Creatures Random animal names of a certain type: Mammals, Birds, Insect, Reptiles
Foods To return a random list of foods

Follow me on Twitter..

By: Ali Radwani