Getting Started with Peewee ORM for Python

Peewee is a minimalist yet powerful Python ORM designed with simplicity in mind.

Its straightforward syntax and small footprint make it perfect for smaller applications, scripts, and projects where you want database functionality without excessive overhead.

This tutorial walks you through building a simple library management app using Peewee and SQLite. You'll learn how to use this lightweight ORM to manage database operations efficiently with minimal code.

Prerequisites

Before starting this tutorial, you should have:

• Python 3.13 or newer installed on your system
• Basic Python programming knowledge
• Basic knowledge of databases (helpful but not necessary)

Step 1 — Setting up your Peewee project

Peewee is designed to be simple and set it up quickly and easily. In this section, you'll prepare your development environment and install the required packages.

Start by creating a project folder and navigating into it:

mkdir peewee-tutorial && cd peewee-tutorial

Next, create a Python virtual environment to keep our project dependencies isolated:

python3 -m venv venv

Activate the virtual environment:

source venv/bin/activate

With the virtual environment active, install Peewee using pip:

pip install peewee

This command installs Peewee, a compact ORM with no additional dependencies required. Peewee works with various database backends, but we'll use SQLite for this tutorial since it's included with Python and doesn't require a separate server setup.

Now that you have Peewee installed, you can begin building your database application.

Step 2 — Understanding Peewee components and creating your first model

Peewee is designed to be simple and intuitive. In this section, you'll explore its key components and define your first model.

Peewee's core components include:

• Database: Manages the connection to your database.
  
• Model: Acts as a base class for defining database tables.
  
• Fields: Specify column types and constraints.
  
• Query: Provides methods for retrieving and modifying data.
  

First, create a file named database.py in the root directory to set up the database connection:

from peewee import SqliteDatabase

# Initialize the SQLite database
# The database file will be created automatically if it doesn't exist
db = SqliteDatabase('library.db')

This code initializes a connection to an SQLite database named library.db. If the file doesn't exist, SQLite will automatically create it when the application runs.

Now, define a model to represent books in the library system. Create a file named models.py:

from peewee import Model, CharField, IntegerField, FloatField, DateField
from database import db

class BaseModel(Model):
    """Base model class that should be subclassed by all models in the app.
    Connects models to the database and implements shared functionality."""
    class Meta:
        database = db  # Database to use for all models

class Book(BaseModel):
    """Represents a book in the library system."""
    title = CharField(max_length=200, null=False)
    author = CharField(max_length=100, null=False)
    pages = IntegerField(null=True)
    publication_date = DateField(null=True)
    price = FloatField(null=True)

    def __str__(self):
        return f"<Book: {self.title} by {self.author}>"

Unlike more complex ORMs, Peewee keeps model definitions simple and readable. First, a BaseModel class is created with a Meta inner class to link models to the database. Then, a Book model is defined with different field types that match database columns.

The field types used include:

• CharField: Stores text with a maximum length.
  
• IntegerField: Stores whole numbers.
  
• DateField: Stores dates.
  
• FloatField: Stores decimal numbers.
  

Constraints like null=False ensure that specific fields must have a value and cannot be left empty.

Now, create the database tables by adding a new file named create_tables.py:

from database import db
from models import Book

# Connect to the database
db.connect()

# Create the tables
# Peewee will check if tables exist before creating them
db.create_tables([Book])

print("Database tables created!")

# Close the connection
db.close()

Peewee simplifies table creation with the create_tables() method. It takes a list of model classes and automatically generates the necessary CREATE TABLE SQL statements.

Run this script to create your database:

python create_tables.py

You should see the output:

Database tables created!

Peewee has set up the database schema with just a few lines of code. The library.db file is now in your project directory, and the table is ready to store book records.

Step 3 — Adding data to your database

Peewee makes inserting data simple, offering multiple ways to create records. In this section, you'll add sample books to the database using Peewee's model API.

Now, create a file named add_books.py to insert books into the library database:

from datetime import date
from database import db
from models import Book

# Connect to the database
db.connect()

# Create and save books
books_data = [
    {
        "title": "The Hobbit",
        "author": "J.R.R. Tolkien",
        "pages": 295,
        "publication_date": date(1937, 9, 21),
        "price": 19.99
    },
    {
        "title": "1984",
        "author": "George Orwell",
        "pages": 328,
        "publication_date": date(1949, 6, 8),
        "price": 14.95
    },
    {
        "title": "To Kill a Mockingbird",
        "author": "Harper Lee",
        "pages": 281,
        "publication_date": date(1960, 7, 11),
        "price": 12.99
    }
]

# Add books to the database
for book_data in books_data:
    book = Book.create(**book_data)
    print(f"Added: {book} with ID: {book.id}")

# Close the connection
db.close()

Peewee provides multiple ways to insert records, but the create() method is one of the simplest. It takes keyword arguments matching the model’s fields, creates a record, saves it to the database, and returns the saved instance in a single step.

The create() method is a shortcut for manually instantiating a model, setting attributes, and calling save(). This makes it an efficient choice for straightforward insertions.

Now, run the script to add these books to your database.

python add_books.py

You should see output similar to:

Added: <Book: The Hobbit by J.R.R. Tolkien> with ID: 1
Added: <Book: 1984 by George Orwell> with ID: 2
Added: <Book: To Kill a Mockingbird by Harper Lee> with ID: 3

Peewee automatically generates and executes the necessary INSERT SQL statements for each book, with SQLite assigning a primary key (ID) to each record. The ID is then accessible on the returned model instance.

Records can also be inserted by creating an instance of the model and calling save(), which explicitly saves the data to the database:

book = Book(title="Dune", author="Frank Herbert", pages=412)
book.save()

Another option for handling multiple records at once is insert_many(), which efficiently inserts a batch of data in a single operation:

data = [
    {"title": "Book 1", "author": "Author 1"},
    {"title": "Book 2", "author": "Author 2"}
]
Book.insert_many(data).execute()

Now that the database is populated with sample books, it's time to query the data.

Step 4 — Querying data from your database

Peewee offers an intuitive and Pythonic query API, making retrieving and filtering data easy. In this section, you'll explore different ways to query the database.

Now, create a file named query_books.py to demonstrate Peewee's querying capabilities:

from database import db
from models import Book

# Connect to the database
db.connect()

# Get all books
print("==== All Books ====")
all_books = Book.select()
for book in all_books:
    print(f"{book.title} by {book.author}, {book.pages} pages, ${book.price:.2f}")

# Close the connection
db.close()

The select() method is the main way to query data in Peewee. It returns a ModelSelect object, which acts as a query builder but doesn’t execute the query immediately. This lazy evaluation improves performance by only running the query when the results are accessed, such as during iteration or when calling a method that fetches data.

Now, run the script to display all the books in your database:

python query_books.py

You should see output like:

==== All Books ====
The Hobbit by J.R.R. Tolkien, 295 pages, $19.99
1984 by George Orwell, 328 pages, $14.95
To Kill a Mockingbird by Harper Lee, 281 pages, $12.99

One of Peewee’s key strengths is its flexible and expressive query methods.

A common way to filter data is by using the where() method, which allows you to apply conditions to queries:

# Find books by a specific author
tolkien_books = Book.select().where(Book.author == "J.R.R. Tolkien")
for book in tolkien_books:
    print(f"{book.title}, published on {book.publication_date}")

# Find books with more than 300 pages
long_books = Book.select().where(Book.pages > 300)
for book in long_books:
    print(f"{book.title}: {book.pages} pages")

# Combine multiple conditions
specific_books = Book.select().where(
    (Book.price < 20) & (Book.pages > 250)
)

To order query results, use order_by():

# Sort by price (ascending)
cheap_books = Book.select().order_by(Book.price)

# Sort by price (descending)
expensive_books = Book.select().order_by(Book.price.desc())

# Sort by multiple fields
sorted_books = Book.select().order_by(Book.author, Book.title)

To retrieve a limited number of records, use limit() and offset():

# Get only two books
two_books = Book.select().limit(2)

# Get two books, skipping the first one
page_two = Book.select().offset(1).limit(2)

When you need only one record, get() is a convenient method:

try:
    # Get the first book that matches the condition
    book = Book.get(Book.title == "The Hobbit")
    print(f"Found: {book.title}")
except Book.DoesNotExist:
    print("Book not found")

Unlike select().where(), which returns an empty result set if no records match, get() raises a DoesNotExist exception if the record isn’t found.

For retrieving by primary key, you can use the shorthand get_by_id() method:

try:
    book = Book.get_by_id(1)  # Get book with ID 1
    print(f"Book #1 is {book.title}")
except Book.DoesNotExist:
    print("Book not found")

Peewee also supports aggregate queries:

# Count books
book_count = Book.select().count()

# Get average price
avg_price = Book.select(fn.AVG(Book.price)).scalar()

# Get total pages
total_pages = Book.select(fn.SUM(Book.pages)).scalar()

These querying features provide a solid foundation for retrieving data with Peewee. Next, you’ll update existing records in the database.

Step 5 — Updating records in your database

Peewee simplifies updating records with both instance-level and bulk update methods. In this section, you'll explore different ways to modify existing data.

Create a file named update_books.py to demonstrate Peewee's update capabilities:

from database import db
from models import Book

# Connect to the database
db.connect()

# Find the book to update
print("=== Before update ===")
try:
    book = Book.get(Book.title == "1984")
    print(f"{book.title} current price: ${book.price:.2f}")

    # Update the book's price
    book.price = 16.99
    book.save()  # Save the changes to the database

    # Verify the update
    updated_book = Book.get(Book.title == "1984")
    print(f"=== After update ===")
    print(f"{updated_book.title} new price: ${updated_book.price:.2f}")
except Book.DoesNotExist:
    print("Book not found")

# Close the connection
db.close()

Peewee provides a simple, object-oriented way to update records. First, retrieve the record using get(), then modify its attributes like a regular Python object, and finally, call save() to store the changes in the database.

To update a book's price, run the following script:

python update_books.py

You should see output like:

=== Before update ===
1984 current price: $14.95
=== After update ===
1984 new price: $16.99

The instance-based approach is excellent for updating single records with multiple field changes:

try:
    hobbit = Book.get(Book.title == "The Hobbit")

    # Make multiple changes
    hobbit.pages = 300  # Update page count
    hobbit.price = 21.99  # Update price
    hobbit.author = "J.R.R. Tolkien (Illustrated Edition)"  # Update author

    # Save all changes at once
    hobbit.save()
    print("Updated The Hobbit with multiple changes")
except Book.DoesNotExist:
    print("Book not found")

When calling save(), Peewee generates a single UPDATE statement that only includes the modified fields, ensuring efficiency.

For updating multiple records at once based on specific conditions, use Peewee’s update() method:

# Increase the price of all books by 10%
query = Book.update(price=Book.price * 1.1)
rows_updated = query.execute()
print(f"Updated prices for {rows_updated} books")

# Discount books with more than 300 pages
discount_query = Book.update(price=Book.price * 0.9).where(Book.pages > 300)
discount_count = discount_query.execute()
print(f"Applied discount to {discount_count} books")

The update() method returns a query object, which you execute with execute(). This approach is highly efficient for bulk updates since it generates a single UPDATE SQL statement instead of retrieving and modifying each record individually.

For more complex updates, you can use update expressions:

import peewee

# Mark all books as "On Sale" if their price is below 15.00
Book.update(title=peewee.fn.CONCAT(Book.title, " (On Sale)"))\
    .where(Book.price < 15.00)\
    .execute()

With these update techniques, you now have efficient ways to modify your database records. Next, you’ll delete records from the database.

Step 6 — Deleting records from your database

Peewee offers flexible methods for deleting records, whether removing a single entry or performing bulk deletions. This section covers both approaches.

Now, create a file named delete_books.py to explore Peewee's deletion capabilities:

from database import db
from models import Book

# Connect to the database
db.connect()

# Count books before deletion
count_before = Book.select().count()
print(f"Total books before deletion: {count_before}")

# Find and delete a book
try:
    book_to_delete = Book.get(Book.title == "To Kill a Mockingbird")
    print(f"Found book to delete: {book_to_delete.title} by {book_to_delete.author}")

    # Delete the book
    rows_deleted = book_to_delete.delete_instance()
    print(f"Book deleted successfully ({rows_deleted} row)")
except Book.DoesNotExist:
    print("Book not found")

# Count books after deletion
count_after = Book.select().count()
print(f"Total books after deletion: {count_after}")

# List remaining books
remaining_books = Book.select()
print("\nRemaining books:")
for book in remaining_books:
    print(f"- {book.title} by {book.author}")

# Close the connection
db.close()

This script demonstrates how to delete a specific book using the instance-based approach. After retrieving the book with get(), call the delete_instance() method to remove it from the database. The method returns the number of rows deleted, which is usually 1 for a single record deletion.

Run the following script to delete a book:

python delete_books.py

You should see output like:

Total books before deletion: 3
Found book to delete: To Kill a Mockingbird by Harper Lee
Book deleted successfully (1 row)
Total books after deletion: 2

Remaining books:
- The Hobbit by J.R.R. Tolkien
- 1984 by George Orwell

For deleting records by their primary key without retrieving them first, Peewee provides a convenient shortcut using the delete_by_id() method:

# Delete a book by its ID
rows_deleted = Book.delete_by_id(2)
if rows_deleted:
    print(f"Deleted book with ID 2")
else:
    print("No book with ID 2 found")

The delete_by_id() method removes the record with the specified primary key and returns the number of rows deleted. If no matching record is found, it returns 0.

For deleting multiple records based on specific conditions, Peewee provides a query-based approach similar to bulk updates:

# Delete all books with prices under $15
delete_query = Book.delete().where(Book.price < 15.00)
rows_deleted = delete_query.execute()
print(f"Deleted {rows_deleted} inexpensive books")

# Delete all books by a particular author
author_delete = Book.delete().where(Book.author.contains("Tolkien"))
tolkien_deleted = author_delete.execute()
print(f"Deleted {tolkien_deleted} books by Tolkien")

The query-based approach is ideal for bulk deletions since it generates a single DELETE SQL statement with a WHERE clause, rather than deleting records individually. This improves performance and reduces database overhead.

Final thoughts

This tutorial covered building a library management system using Peewee ORM with SQLite. Peewee’s simple API and minimal overhead are great for small to medium-sized projects.

You learned essential database operations—creating tables, inserting, querying, updating, and deleting records—while leveraging Peewee’s intuitive syntax. Despite being lightweight, it supports advanced features like relationships, transactions, and complex queries.

Explore ForeignKeyField, migrations, joins, and transactions to expand your skills. For more details, visit the official Peewee documentation.

Article by

Stanley Ulili

Stanley Ulili is a technical educator at Better Stack based in Malawi. He specializes in backend development and has freelanced for platforms like DigitalOcean, LogRocket, and AppSignal. Stanley is passionate about making complex topics accessible to developers.

Got an article suggestion? Let us know

Next article

DuckDB for Python: A beginner's guide

Learn how to use DuckDB, a fast in-process SQL database for Python, to build powerful data analysis workflows. This guide covers setup, querying CSV and Parquet files, joins, aggregation, and integration with pandas—no server required.

→

This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.