Ali's Photography Space…

The Data: In the Part-1 we Talk about the Entities that we will create and about the Data we will collect (Fields). So here we are writing them again and we will add classification for each book :
Books Entity: Book Name, Book Author, Date of publish, Edition Number, Book classification number.
Author Entity : Author name, Author Nationality, email, SMA (social media account).
From the Author information we can see that we need to define a new Entity to hold the SMA.
SMA Entity: SMA Name, SMA Link.
Class Entity: class Name.
Here is each entity and field :
book:

• b_id integer PK
• b_name text,
• b_a_id integer,
• b_isbn text,
• b_dop text,
• b_ed integer

author:

• a_id integer PK
• a_name text
• a_email text

sma:

• sma_id integer PK,
• a_id integer,
• sma_name text,
• sma_link text

class:

• class_id integer PK
• class_name text

Now we will write the code to create the Database and set a connection

# create the Database and set a connection

import sqlite3, os 

# Create the data-base & name it as LMS. 
db_conn = sqlite3.connect ("LMS.db") 

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

Now we will create the tables..

# Function to create the tables
 
def create_tables_() :
        # to create tables. 
    sql_books =    "CREATE TABLE if not exists books  (b_id INTEGER PRIMARY KEY AUTOINCREMENT, b_name text, b_a_id integer,b_isbn text, b_dop text, b_ed integer)" 
    
    sql_author =   "CREATE TABLE if not exists author (a_id INTEGER PRIMARY KEY AUTOINCREMENT ,a_ name text, a_email text     )" 
    
    sql_class  = "CREATE TABLE if not exists classifi_list (class_id INTEGER PRIMARY KEY AUTOINCREMENT, class_name text )"  



    sql_b_class  = "CREATE TABLE if not exists b_class (b_class_id INTEGER PRIMARY KEY AUTOINCREMENT, b_id integer, class_id integer )"  

    sql_sma =      "CREATE TABLE if not exists sma (sma_id INTEGER PRIMARY KEY AUTOINCREMENT, a_id integer,sma_name text, sma_link text)" 
    
    
    c.execute(sql_books) 
    db_conn.commit() 

    c.execute(sql_author) 
    db_conn.commit() 

    c.execute(sql_class)
    db_conn.commit()
    

    c.execute(sql_b_class) 
    db_conn.commit() 

    c.execute(sql_sma) 
    db_conn.commit() 

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

After creating the tables, and to make sure that AUTOINCREMENT of Primary key will run we need to have a number in each PK field, to do that we will add a 0 (Zero) record to each table. Here is the code to do it ..

#Function to Insert the Zero Record
 
#Function to Insert the Zero Record.  
def insert_record_0(): 

    c.execute ("INSERT INTO books (b_id) VALUES(:b_id)",{"b_id":0}) 
    c.execute ("INSERT INTO authors (a_id) VALUES(:a_id)",{"a_id":0}) 
    c.execute ("INSERT INTO classifi_list (class_id) VALUES(:class_id)",{"class_id":0})
   
    c.execute ("INSERT INTO b_class (class_id) VALUES(:class_id)",{"class_id":0}) 
    c.execute ("INSERT INTO sma (sma_id) VALUES(:sma_id)",{"sma_id":0}) 
    
    db_conn.commit() 
    
    input('\n ...Dummy records been Inserted .... Press any key .. ') 

The above functions create_tables_() and insert_record_0() will be run only one time to create the tables, and insert record number ZERO. During the programming and coding we may need to delete the DataBase and re-created again, in that case we run the both functions again.

Create the Menus
As we side, each Entities will have three Main Functions [Add, Edit, delete ad search]. Let’s start with Main Menu.

Main Menu Code	Main Menu Run-Time
Book Menu, Code	Book Menu, Run-Time
Author Menu, Code	Author Menu, Run-Time
Classification Menu, Code	Classification Menu, Run-Time
Search Menu, Code	Search Menu, Run-Time

What’s Coming In coming post, we will write the Functions to Manage the Authors and Classifications.

[ NOTE ]
1. I am using Galaxy Tab and QPython3 App.
2. All the above codes are available in the download section/page under the project name.
3. During the progress of the project, we may need to Add, Edit or delete any Tables, Fields, Menus or Functions that we had already finished.

:: Library Managment System ::




Part 1

Part 2

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