peewee

a small, expressive orm -- supports postgresql, mysql, sqlite and cockroachdb

11,053

1,369

11,053

View on GitHub

Top Related Projects

django

79,643

The Web framework for perfectionists with deadlines.

tortoise-orm

4,609

Familiar asyncio ORM for python, built with relations in mind

Quick Overview

Peewee is a simple and expressive ORM (Object-Relational Mapping) library for Python. It provides a lightweight and intuitive interface for working with relational databases, supporting SQLite, MySQL, and PostgreSQL. Peewee aims to make database operations easy and pythonic while maintaining flexibility and performance.

Pros

Simple and intuitive API, easy to learn and use
Supports multiple database backends (SQLite, MySQL, PostgreSQL)
Lightweight with minimal dependencies
Extensive documentation and active community support

Cons

Limited support for complex queries compared to some larger ORMs
May not be suitable for very large-scale applications with complex database requirements
Lacks some advanced features found in more comprehensive ORMs like SQLAlchemy

Code Examples

Defining a model:

from peewee import *

db = SqliteDatabase('my_app.db')

class User(Model):
    username = CharField(unique=True)
    email = CharField(unique=True)
    is_active = BooleanField(default=True)

    class Meta:
        database = db

Creating and saving a record:

user = User.create(username='john_doe', email='john@example.com')
# or
user = User(username='jane_doe', email='jane@example.com')
user.save()

Querying the database:

# Fetch a single user
user = User.get(User.username == 'john_doe')

# Fetch multiple users
active_users = User.select().where(User.is_active == True)

# Iterate through results
for user in active_users:
    print(user.username, user.email)

Getting Started

Install Peewee:

pip install peewee

Create a database connection and define your models:

from peewee import *

db = SqliteDatabase('my_app.db')

class BaseModel(Model):
    class Meta:
        database = db

class User(BaseModel):
    username = CharField(unique=True)
    email = CharField(unique=True)

db.connect()
db.create_tables([User])

Use your models to interact with the database:

# Create a user
User.create(username='alice', email='alice@example.com')

# Query users
users = User.select()
for user in users:
    print(user.username)

Competitor Comparisons

sqlalchemy

9,550

The Database Toolkit for Python

Pros of SQLAlchemy

More comprehensive ORM with advanced features like relationship management and complex querying
Supports a wider range of databases and SQL dialects
Larger community and ecosystem with more third-party extensions

Cons of SQLAlchemy

Steeper learning curve due to its complexity and extensive feature set
Generally slower performance compared to lighter-weight ORMs
More verbose syntax for simple operations

Code Comparison

SQLAlchemy:

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)

Peewee:

from peewee import *

db = SqliteDatabase('my_app.db')
class User(Model):
    name = CharField()
    class Meta:
        database = db

SQLAlchemy offers more flexibility and power but requires more setup, while Peewee provides a simpler and more concise syntax for basic operations. SQLAlchemy is better suited for complex applications with intricate database relationships, whereas Peewee excels in smaller projects where simplicity and ease of use are prioritized.

django

79,643

The Web framework for perfectionists with deadlines.

Pros of Django

Full-featured web framework with built-in admin interface, authentication, and ORM
Large ecosystem with extensive third-party packages and community support
Batteries-included approach, providing tools for most common web development tasks

Cons of Django

Steeper learning curve due to its comprehensive nature
Can be overkill for small projects or microservices
Less flexibility in database schema design compared to Peewee's simplicity

Code Comparison

Django ORM example:

from django.db import models

class Person(models.Model):
    name = models.CharField(max_length=100)
    age = models.IntegerField()

Peewee ORM example:

from peewee import *

db = SqliteDatabase('people.db')

class Person(Model):
    name = CharField()
    age = IntegerField()
    
    class Meta:
        database = db

Both Django and Peewee provide Object-Relational Mapping (ORM) capabilities, but Django's ORM is part of a larger framework, while Peewee is a standalone ORM library. Django's approach is more structured and integrated with its framework, while Peewee offers a simpler and more lightweight solution for database interactions.

orm

1,780

An async ORM. 🗃

Pros of ORM

Built specifically for async Python, integrating well with FastAPI and other async frameworks
Supports multiple database backends including SQLite, PostgreSQL, and MySQL
Offers a clean, intuitive API design with type hints for better IDE support

Cons of ORM

Less mature and battle-tested compared to Peewee
Smaller community and ecosystem, potentially leading to fewer resources and third-party extensions
May have a steeper learning curve for developers not familiar with async programming concepts

Code Comparison

ORM:

class User(Model):
    id = Integer(primary_key=True)
    name = String(max_length=100)
    email = String(max_length=100)

async def create_user():
    user = await User.objects.create(name="John", email="john@example.com")

Peewee:

class User(Model):
    name = CharField(max_length=100)
    email = CharField(max_length=100)

def create_user():
    user = User.create(name="John", email="john@example.com")

Both ORMs offer similar syntax for defining models and creating objects. However, ORM's async nature is evident in its use of async/await, while Peewee uses a synchronous approach. ORM also explicitly defines the primary key, whereas Peewee automatically adds an id field by default.

pony

3,606

Pony Object Relational Mapper

Pros of Pony

More intuitive and Pythonic query syntax using generator expressions
Automatic query optimization and caching for improved performance
Built-in support for composite keys and multi-column unique constraints

Cons of Pony

Smaller community and fewer third-party extensions compared to Peewee
Less flexible for raw SQL queries and complex database operations
Steeper learning curve for developers familiar with traditional ORM patterns

Code Comparison

Pony:

select(c for c in Customer if c.age > 18 and c.orders)

Peewee:

Customer.select().where(Customer.age > 18).join(Order)

Both Pony and Peewee are Python ORMs, but they differ in their approach to database interactions. Pony offers a more Pythonic syntax and automatic query optimization, while Peewee provides a more traditional ORM experience with greater flexibility for complex operations. The choice between them depends on project requirements and developer preferences.

tortoise-orm

4,609

Familiar asyncio ORM for python, built with relations in mind

Pros of Tortoise-ORM

Asynchronous: Built for async/await, ideal for high-concurrency applications
Type hinting: Provides better IDE support and code completion
Supports multiple databases: Works with SQLite, PostgreSQL, and MySQL

Cons of Tortoise-ORM

Less mature: Newer project with potentially fewer resources and community support
Limited raw SQL support: More complex queries may be challenging to implement
Steeper learning curve: Async nature may be unfamiliar to some developers

Code Comparison

Peewee example:

class User(Model):
    name = CharField()
    email = CharField(unique=True)

user = User.create(name='John', email='john@example.com')
users = User.select().where(User.name.contains('oh'))

Tortoise-ORM example:

class User(Model):
    name = fields.CharField(max_length=50)
    email = fields.CharField(max_length=50, unique=True)

user = await User.create(name='John', email='john@example.com')
users = await User.filter(name__contains='oh')

Both ORMs offer similar functionality, but Tortoise-ORM's async nature is evident in its code structure. Peewee's syntax is more concise, while Tortoise-ORM leverages Python's type hinting for better IDE support. The choice between them often depends on whether async capabilities are required and the developer's familiarity with async programming.

Convert designs to code with AI

Introducing Visual Copilot: A new AI model to turn Figma designs to high quality code using your components.

Try Visual Copilot

README

.. image:: https://media.charlesleifer.com/blog/photos/peewee3-logo.png

peewee

Peewee is a simple and small ORM. It has few (but expressive) concepts, making it easy to learn and intuitive to use.

a small, expressive ORM
python 2.7+ and 3.4+
supports sqlite, mysql, mariadb, postgresql and cockroachdb
tons of extensions <http://docs.peewee-orm.com/en/latest/peewee/playhouse.html>_

New to peewee? These may help:

Quickstart <http://docs.peewee-orm.com/en/latest/peewee/quickstart.html#quickstart>_
Example twitter app <http://docs.peewee-orm.com/en/latest/peewee/example.html>_
Using peewee interactively <http://docs.peewee-orm.com/en/latest/peewee/interactive.html>_
Models and fields <http://docs.peewee-orm.com/en/latest/peewee/models.html>_
Querying <http://docs.peewee-orm.com/en/latest/peewee/querying.html>_
Relationships and joins <http://docs.peewee-orm.com/en/latest/peewee/relationships.html>_

Examples

Defining models is similar to Django or SQLAlchemy:

.. code-block:: python

from peewee import *
import datetime


db = SqliteDatabase('my_database.db')

class BaseModel(Model):
    class Meta:
        database = db

class User(BaseModel):
    username = CharField(unique=True)

class Tweet(BaseModel):
    user = ForeignKeyField(User, backref='tweets')
    message = TextField()
    created_date = DateTimeField(default=datetime.datetime.now)
    is_published = BooleanField(default=True)

Connect to the database and create tables:

.. code-block:: python

db.connect()
db.create_tables([User, Tweet])

Create a few rows:

.. code-block:: python

charlie = User.create(username='charlie')
huey = User(username='huey')
huey.save()

# No need to set `is_published` or `created_date` since they
# will just use the default values we specified.
Tweet.create(user=charlie, message='My first tweet')

Queries are expressive and composable:

.. code-block:: python

# A simple query selecting a user.
User.get(User.username == 'charlie')

# Get tweets created by one of several users.
usernames = ['charlie', 'huey', 'mickey']
users = User.select().where(User.username.in_(usernames))
tweets = Tweet.select().where(Tweet.user.in_(users))

# We could accomplish the same using a JOIN:
tweets = (Tweet
          .select()
          .join(User)
          .where(User.username.in_(usernames)))

# How many tweets were published today?
tweets_today = (Tweet
                .select()
                .where(
                    (Tweet.created_date >= datetime.date.today()) &
                    (Tweet.is_published == True))
                .count())

# Paginate the user table and show me page 3 (users 41-60).
User.select().order_by(User.username).paginate(3, 20)

# Order users by the number of tweets they've created:
tweet_ct = fn.Count(Tweet.id)
users = (User
         .select(User, tweet_ct.alias('ct'))
         .join(Tweet, JOIN.LEFT_OUTER)
         .group_by(User)
         .order_by(tweet_ct.desc()))

# Do an atomic update (for illustrative purposes only, imagine a simple
# table for tracking a "count" associated with each URL). We don't want to
# naively get the save in two separate steps since this is prone to race
# conditions.
Counter.update(count=Counter.count + 1).where(Counter.url == request.url)

Check out the example twitter app <http://docs.peewee-orm.com/en/latest/peewee/example.html>_.

Learning more

Check the documentation <http://docs.peewee-orm.com/>_ for more examples.

Specific question? Come hang out in the #peewee channel on irc.libera.chat, or post to the mailing list, http://groups.google.com/group/peewee-orm . If you would like to report a bug, create a new issue <https://github.com/coleifer/peewee/issues/new>_ on GitHub.

Still want more info?

.. image:: https://media.charlesleifer.com/blog/photos/wat.jpg

I've written a number of blog posts about building applications and web-services with peewee (and usually Flask). If you'd like to see some real-life applications that use peewee, the following resources may be useful:

Building a note-taking app with Flask and Peewee <https://charlesleifer.com/blog/saturday-morning-hack-a-little-note-taking-app-with-flask/>_ as well as Part 2 <https://charlesleifer.com/blog/saturday-morning-hacks-revisiting-the-notes-app/>_ and Part 3 <https://charlesleifer.com/blog/saturday-morning-hacks-adding-full-text-search-to-the-flask-note-taking-app/>_.
Analytics web service built with Flask and Peewee <https://charlesleifer.com/blog/saturday-morning-hacks-building-an-analytics-app-with-flask/>_.
Personalized news digest (with a boolean query parser!) <https://charlesleifer.com/blog/saturday-morning-hack-personalized-news-digest-with-boolean-query-parser/>_.
Structuring Flask apps with Peewee <https://charlesleifer.com/blog/structuring-flask-apps-a-how-to-for-those-coming-from-django/>_.
Creating a lastpass clone with Flask and Peewee <https://charlesleifer.com/blog/creating-a-personal-password-manager/>_.
Creating a bookmarking web-service that takes screenshots of your bookmarks <https://charlesleifer.com/blog/building-bookmarking-service-python-and-phantomjs/>_.
Building a pastebin, wiki and a bookmarking service using Flask and Peewee <https://charlesleifer.com/blog/dont-sweat-small-stuff-use-flask-blueprints/>_.
Encrypted databases with Python and SQLCipher <https://charlesleifer.com/blog/encrypted-sqlite-databases-with-python-and-sqlcipher/>_.
Dear Diary: An Encrypted, Command-Line Diary with Peewee <https://charlesleifer.com/blog/dear-diary-an-encrypted-command-line-diary-with-python/>_.
Query Tree Structures in SQLite using Peewee and the Transitive Closure Extension <https://charlesleifer.com/blog/querying-tree-structures-in-sqlite-using-python-and-the-transitive-closure-extension/>_.

Top Related Projects

Convert designs to code with AI

Introducing Visual Copilot: A new AI model to turn Figma designs to high quality code using your components.

Try Visual Copilot