# Getting Started with Peewee ORM for Python

[Peewee](https://github.com/coleifer/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:  

```command
mkdir peewee-tutorial && cd peewee-tutorial
```
Next, create a Python virtual environment to keep our project dependencies isolated:

```command
python3 -m venv venv
```

Activate the virtual environment:

```command
source venv/bin/activate
```

With the virtual environment active, install Peewee using pip:

```command
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:


```python
[label database.py]
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`:

```python
[label 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`:

```python
[label 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:

```command
python create_tables.py
```

You should see the output:

```text
[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:

```python
[label add_books.py]
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.

```command
python add_books.py
```

You should see output similar to:

```text
[output]
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:  

```python
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:  

```python
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:


```python
[label query_books.py]
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:


```command
python query_books.py
```

You should see output like:

```text
[output]
==== 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:

```python
# 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()`:

```python
# 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()`:

```python
# 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:

```python
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:

```python
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:

```python
# 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:

```python
[label update_books.py]
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:  

```command
python update_books.py
```

You should see output like:

```text
[output]

=== 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:

```python
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:  

```python
# 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:  


```python
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:

```python
[label delete_books.py]
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:  

```command
python delete_books.py
```

You should see output like:

```text
[output]
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:

```python
# 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:  

```python
# 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](https://docs.peewee-orm.com/).