One problem people face when they attempt to use databases is that they have a hard time writing SQL queries. This makes it hard to connect to a database and perform actions on it. Peewee helps you solve this problem by being an ORM (Object Relational Mapper). Instead of writing SQL queries, Peewee uses python-like syntax to give you a better feel for writing code. This can help you write code faster and more efficiently. ORMs are an important tool for structuring data in an application and keeping an organized database. In Python, it is essential to use an ORM when working with a database. An ORM, or Object Relational Mapper, is a tool for translating a database into a type of Python object. ORMs let you work with databases by way of a programming language’s native metaphors, instead of by constructing raw database commands as strings.
In this tutorial we’ll look at a simple ORM for Python called Peewee and how it uses Python objects, like classes, to represent and work with an SQlite database. We’ll build a simple model which represents a table of people. Then we’ll explore this data in various ways, demonstrating some of the advanced features of Peewee.
Prerequisites
Before proceeding further with the tutorial, we’ll first need the get some prerequisites to run it. First, we’ll need to install Peewee itself, which can be done with the following pip command:
pip install peeweeAfter successfully installing Peewee, we’ll need a database on which we will conduct our experiments. Peewee provides support for SQLite, MySQL, and Postgres. Each database class has some basic configuration options specific to the type of database used. I’ll be using an SQLite database in this tutorial for simplicity’s sake.
If you don’t already have any database installed in your system, you can install SQLite from here.
Creating a database
Creating a database with peewee is extremely easy. All you need to do is call peewee’s Database method of any supported database and pass it the path of the database file or “:memory:” if you want an in-memory database.
#models.py
import peewee
db = peewee.SqliteDatabase('test.db')You can also connect to a lot of different databases at once. For example like this:
schools_db = SqliteDatabase('schools.db')
students_db = SqliteDatabase('students.db')
crime_db = SqliteDatabase('crime.db')Once we have this, we can start defining the model. Working with database models in Peewee is very similar to working with SQLAlchemy models. Let’s start by setting up a table definition. A model maps to a database table, a field to a table column, and an instance to a table row. Here we have created some classes, which would hold information about some students and schools.
import peewee
import datetime
database = peewee.SqliteDatabase("test.db")
class Students(peewee.Model):
"""
ORM model of the Students table
"""
name = peewee.CharField()
admission = peewee.DateField(default=datetime.date.today)
class Meta:
database = database
class Schools(peewee.Model):
"""
ORM model of Schools table
"""
school = peewee.CharField()
location = peewee.CharField()
class Meta:
database = database
if __name__ == "__main__":
"""
Handling exceptions
"""
try:
Students.create_table()
except peewee.OperationalError:
print ("Students table already exists!")
try:
Schools.create_table()
except peewee.OperationalError:
print ("Schools table already exists!")Note: this is the main code that we’ll be working on for the rest of this tutorial.
In this code, we see that all we’re doing is creating two classes that each define a table in our database. We are defining the fields or columns used to take care of the data and then connecting the database to the tables via nested models. And then we call the class directly to create the table schema.
Now to check if it works, you can run this like any other python file:
python models.py
# or
python3 models.py If it ran successfully it should create a test.db file in your project directory. And If you have the tools or extensions (SQLite Viewer for example) to look into this file, you would see a table and columns created inside it. Now that everything is set up and ready to go, we can learn how to populate our database with data!
Populating the Database
Inserting data into our database is also very easy with Peewee, the insert_many method allows us to do bulk creates. Let’s take a look:
#add_data.py
data = [
{ 'name': 'Sandhya', 'admission': datetime.date(2018, 10, 20) },
{ 'name': 'Aarav', 'admission': datetime.date(2018, 10, 12) },
{ 'name': 'Mohammed', 'admission': datetime.date(2018, 10, 5) },
{ 'name': 'Vihaan', 'admission': datetime.date(2018, 10, 28) },
{ 'name': 'Aditya', 'admission': datetime.date(2018, 10, 14) },
{ 'name': 'Sai', 'admission': datetime.date(2018, 10, 22) },
{ 'name': 'Dev', 'admission': datetime.date(2018, 10, 28) }
]The code example recreates the Student’s table in one bulk create operation. It consists of defining the data as a list of dictionaries. This can be put to the bottom of your code when used.
with database.atomic():
query = Students.insert_many(data)
query.execute()The atomic method executes a bulk operation. The atomic method puts the bulk operation in a transaction.
And now if you execute the code() and check your database, you can see the stored data.
Similarly, you can add data to the other tables as well.
Querying the Database
Now, this is arguably one of the most used applications of Peewee. Peewee offers a select method that can be used to retrieve instances of the defined models easily. For example:
#quering_database.py
students = Students.select()
for student in students:
print('{} on {}'.format(student.name, student.admission))Each of these students‘ records has their information filled out on the Students table. This example shows how to query data from this database and display it. The `select` function invokes a SELECT command and returns a list containing records from the Students table. If no fields are explicitly provided, the query will by default select all fields defined on the model.
This can again be put below all the main code and run with this command.
python models.py
# or
python3 models.py And you should get a result like this:
You can also filter the data based on a given condition. For example, if we add an id variable in the format method, we can define it to load specific results.
notes = Students.select().where(Students.id > 3)
for note in notes:
print('{} {} on {}'.format(note.id, note.name, note.admission))Like this, the where methods apply a filtering condition on the query and it retrieves all rows with Id greater than three.
Modifying Records
Editing your data is easy to do with Peewee. Peewee lets you modify your databases pretty easily with its ORM tool that allows you to manipulate your data. Here’s a quick example of how to do it:
#modify_database_records.py
query = Students.update(admission=datetime.date(2018, 10, 27)).where(Students.id == 1)
n = query.execute()
print('# of rows updated: {}'.format(n))Here we are modifying the creation date of the Students with Id 1. We are using the update method which also returns the number of successfully updated instances as (n) and that is the last line.
Again, put this code at the bottom of the main code and run it to see the difference.
This is the pre-stored instance:
And this is after running the update command:
Final Words
Peewee is a small and expressive ORM that currently supports Postgres and MySQL. It’s not the first Python ORM, but it is the most lightweight and has many features that are necessary for modern applications. In addition to out-of-the-box support for common tasks like SQL abstraction, object mapping, and result set iteration, Peewee also includes other Pythonic goodies such as convenient keyword arguments, intelligent defaults, explicit interface signatures, and some nice documentation just to name a few!
Hopefully, this introduction will give you a good idea of what Peewee can do as well as get you excited to try using it in your next application. You can also download the entire source code for this tutorial at this Github repo.
