Track your physical books with shelf locations using Python and SQLite
Welcome to another official UVF IT build where we create a minimal, self-hosted book collection tracker inspired by Vaultisse. This project focuses on the core utility: logging physical books and assigning them to specific shelf locations, without the complexity of a full production database.
We will use a lightweight Python stack with Flask and SQLite to ensure the entire application runs locally on your machine in under 45 minutes. By the end, you will have a working web interface to add, view, and organize your personal library by physical location.
pip is installed.First, we need to set up a clean workspace for our application. By creating a virtual environment, we ensure that the libraries we install for this project do not conflict with global Python packages on your system. This is a standard practice in Python development to keep projects isolated and reproducible.
Once the environment is active, we will install Flask. Flask is a micro-framework that provides the essential tools for building web applications, such as routing and request handling, without imposing a specific project structure. We will also create the necessary directory structure, specifically the templates folder, which Flask expects to find for HTML rendering.
mkdir shelflife
cd shelflife
python3 -m venv venv
source venv/bin/activate # On Windows: venv\Scripts\activate
pip install flask
mkdir templatesNext, we define how our data will be stored. We will use Python's built-in sqlite3 module to interact with the database directly, avoiding the need for an Object-Relational Mapper (ORM) like SQLAlchemy to keep the dependencies minimal. The Book model will consist of a unique identifier, the title, the author, and the shelf location.
We also include an initialization function that creates the database table if it does not already exist. To verify that our database connection works correctly, we will insert a sample record. This ensures that when we later query the database, we have data to display and can confirm the SQL syntax is valid.
models.py
import sqlite3
DB_NAME = 'books.db'
def get_db_connection():
conn = sqlite3.connect(DB_NAME)
conn.row_factory = sqlite3.Row
return conn
def init_db():
conn = get_db_connection()
cursor = conn.cursor()
cursor.execute('''
CREATE TABLE IF NOT EXISTS books (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL,
author TEXT NOT NULL,
shelf_location TEXT NOT NULL
)
''')
# Insert sample data if the table is empty
cursor.execute('SELECT COUNT(*) FROM books')
count = cursor.fetchone()[0]
if count == 0:
cursor.execute(
'INSERT INTO books (title, author, shelf_location) VALUES (?, ?, ?)',
('The Pragmatic Programmer', 'Andrew Hunt & David Thomas', 'Living Room Shelf A')
)
conn.commit()
conn.close()
def get_all_books():
conn = get_db_connection()
books = conn.execute('SELECT * FROM books ORDER BY id DESC').fetchall()
conn.close()
return books
def add_book(title, author, shelf_location):
conn = get_db_connection()
conn.execute(
'INSERT INTO books (title, author, shelf_location) VALUES (?, ?, ?)',
(title, author, shelf_location)
)
conn.commit()
conn.close()Now we build the application logic in app.py. We instantiate the Flask app and configure the secret key, which is required for handling sessions and CSRF protection in forms, even though we are not using full authentication in this minimal clone.
We define two main routes: a GET request to the root path that fetches all books from the database and renders the main template, and a POST request that handles the form submission to add a new book. After adding a book, we redirect the user back to the main page to follow the Post/Redirect/Get pattern, which prevents duplicate submissions if the user refreshes the page.
app.py
from flask import Flask, render_template, request, redirect, url_for
from models import init_db, get_all_books, add_book
app = Flask(__name__)
app.secret_key = 'dev_secret_key_change_in_production'
# Initialize the database on startup
init_db()
@app.route('/', methods=['GET'])
def index():
books = get_all_books()
return render_template('index.html', books=books)
@app.route('/add', methods=['POST'])
def add_book_route():
title = request.form.get('title', '').strip()
author = request.form.get('author', '').strip()
shelf_location = request.form.get('shelf_location', '').strip()
if title and author and shelf_location:
add_book(title, author, shelf_location)
return redirect(url_for('index'))
if __name__ == '__main__':
app.run(debug=True)Now we need a way to actually see and interact with our data. Create a file named index.html inside the templates directory. This file will serve as the single-page interface for your book collection, combining both the display of existing books and the form for adding new ones.
We will use Jinja2 templating syntax to loop through the books list passed from our Flask route. This ensures that every time a new book is added, the table automatically updates without needing to reload the entire page structure manually. Keep the CSS minimal to focus on functionality rather than aesthetics.
templates/index.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>ShelfLife - Book Tracker</title>
<style>
body { font-family: Arial, sans-serif; max-width: 800px; margin: 0 auto; padding: 20px; background-color: #f4f4f9; }
h1 { color: #333; }
.section { background: white; padding: 20px; border-radius: 8px; margin-bottom: 20px; box-shadow: 0 2px 4px rgba(0,0,0,0.1); }
table { width: 100%; border-collapse: collapse; }
th, td { padding: 10px; text-align: left; border-bottom: 1px solid #ddd; }
th { background-color: #f2f2f2; }
form { display: flex; flex-wrap: wrap; gap: 10px; }
input { padding: 8px; flex: 1; min-width: 150px; }
button { padding: 8px 15px; background-color: #0056b3; color: white; border: none; cursor: pointer; }
button:hover { background-color: #004494; }
</style>
</head>
<body>
<h1>ShelfLife</h1>
<div class="section">
<h2>Add New Book</h2>
<form action="/add" method="POST">
<input type="text" name="title" placeholder="Title" required>
<input type="text" name="author" placeholder="Author" required>
<input type="text" name="shelf_location" placeholder="Shelf Location (e.g., A1-3)" required>
<button type="submit">Add Book</button>
</form>
</div>
<div class="section">
<h2>My Collection</h2>
<table>
<thead>
<tr>
<th>Title</th>
<th>Author</th>
<th>Shelf Location</th>
</tr>
</thead>
<tbody>
{% for book in books %}
<tr>
<td>{{ book.title }}</td>
<td>{{ book.author }}</td>
<td>{{ book.shelf_location }}</td>
</tr>
{% endfor %}
</tbody>
</table>
</div>
</body>
</html>With the backend logic and frontend template ready, it's time to bring the application to life. We will start the Flask development server, which will listen for incoming HTTP requests on your local machine.
Once the server is running, you can navigate to the application in your web browser. You should immediately see the sample book we added in Phase 2. Try adding a new book using the form to ensure that the data flow from the browser, through the Flask route, into SQLite, and back to the template is working correctly.
python app.py| Check | Command / Why it matters |
|---|---|
| Server starts without errors | Run python app.py and observe 'Running on http://127.0.0.1:5000' in the terminal. |
| Sample data is visible | Visit http://localhost:5000 in a browser and confirm 'The Pragmatic Programmer' appears in the table. |
| New book persists | Submit the form with a new title/author/location, refresh the page, and verify the new entry appears in the list. |
| Database file exists | Check the project directory for the presence of the books.db file. |
You now have a functional self-hosted book tracker. To extend this, consider adding search functionality, editing/deleting books, or integrating with a public API to auto-fill metadata.