Home > Learning, Lesson, Problem, Projects/Experiments, Python > Python: Library Managment System -P2

Python: Library Managment System -P2

Learning : Python, DataBase, SQlite
Subject: Create Simple Library Managment System

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 :

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


  • a_id integer PK
  • a_name text
  • a_email text


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


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






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


    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

Follow me on Twitter..

By: Ali Radwani

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: