Beginner's Guide to Building a Library Management System Using
Python and SQLite
Project Title: Library Management System
Objective: To teach you step-by-step how to create a simple Library Management System
using Python and SQLite, applying both DDL (Data Definition Language) and DML (Data
Manipulation Language).
What
️ You Need:
- A computer (Windows, Mac, or Linux)
- Python installed on your system
- VS Code (or any text editor)
- DB Browser for SQLite (to view your database visually)
Step 1: Set Up Your Project Folder
1. Create a new folder on your desktop called 'LibrarySystemPython'.
2. Inside that folder, create a new Python file: library.py
Step 2: Create the Database and Tables (DDL)
DDL means creating the structure of the database: tables, columns, data types, etc.
Tables we will create:
- Authors: ID, Name
- Books: ID, Title, AuthorID, Quantity
- Members: ID, Name, Contact
- Staff: ID, Name, Role
- Loans: ID, MemberID, BookID, LoanDate, ReturnDate
Example DDL Code:
import sqlite3
conn = sqlite3.connect('library.db')
cursor = conn.cursor()
cursor.execute('''CREATE TABLE IF NOT EXISTS Authors (
AuthorID INTEGER PRIMARY KEY,
Name TEXT NOT NULL
)''')
cursor.execute('''CREATE TABLE IF NOT EXISTS Books (
BookID INTEGER PRIMARY KEY,
Title TEXT NOT NULL,
AuthorID INTEGER,
Quantity INTEGER,
FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID)
)''')
# Repeat for Members, Staff, and Loans
conn.commit()
conn.close()
✍️
Step 3: Insert Sample Data (DML - INSERT)
DML is how we interact with data: add, update, delete.
conn = sqlite3.connect('library.db')
cursor = conn.cursor()
cursor.execute("INSERT INTO Authors (Name) VALUES ('George Orwell')")
cursor.execute("INSERT INTO Books (Title, AuthorID, Quantity) VALUES ('1984', 1, 3)")
conn.commit()
conn.close()
Step 4: Update or Replace Data (DML - UPDATE/REPLACE)
# UPDATE a book's quantity
cursor.execute("UPDATE Books SET Quantity = 5 WHERE BookID = 1")
# REPLACE a member's info
cursor.execute("REPLACE INTO Members (MemberID, Name, Contact) VALUES (1, 'John
Doe', '123456')")
❌ Step 5: Truncate or Clear Data
SQLite doesn't have TRUNCATE, so we use DELETE:
cursor.execute("DELETE FROM Books")
Step 6: View Your Data
To view your tables and data:
- Open DB Browser for SQLite
- Open 'library.db'
- Click 'Browse Data' tab to see your tables
You can also view it in Python:
cursor.execute("SELECT * FROM Books")
for row in cursor.fetchall():
print(row)
Extra Tips:
- Always call conn.commit() to save changes.
- Always call conn.close() at the end.
- Keep your code organized into sections (create, insert, update, delete).
Final Words
This project teaches:
- How to design and build a database using Python and SQLite
- How to apply SQL DDL and DML operations
- Basic logic to manage library systems (can also be extended to Bookstore, Inventory, etc.)
You now have the skills to build your own small database projects. Keep practicing and
don’t be afraid to explore more advanced concepts!
Prepared by: Abu Bakr E. Cesay
For academic use under the Database course Session 14 group project
