Peewee is a popular lightweight ORM (Object-Relational Mapper) library for Python. It provides a simple and expressive way to interact with databases. One of the useful features of Peewee is its JSONField, which allows you to store JSON data in a database column.

In this blog post, we will explore how to use the Peewee JSONField to store and retrieve JSON data in a Python application.

Installing Peewee

To get started, you need to install Peewee. Open your command line interface and run the following command:

pip install peewee

Setting up the Database

Let’s assume you have a PostgreSQL database and want to store JSON data in a table called posts. First, you need to create a model using Peewee that will represent the posts table. Here’s an example model that includes a JSONField:

from peewee import *

db = PostgresqlDatabase('my_database', user='my_user', password='my_password')

class Post(Model):
    title = CharField()
    content = TextField()
    metadata = JSONField()

    class Meta:
        database = db

# Create the table
db.create_tables([Post])

Storing JSON Data

To store JSON data in the metadata field, you can simply assign a Python dictionary to it. Here’s an example:

post_data = {
    "title": "Peewee JSONField",
    "content": "Using JSONField in Peewee",
    "metadata": {
        "author": "John Doe",
        "tags": ["ORM", "Peewee", "JSON"],
        "views": 100
    }
}

post = Post.create(**post_data)

Peewee will automatically convert the dictionary to JSON format and store it in the metadata column of the posts table.

Querying JSON Data

Now let’s see how to retrieve and query the JSON data stored in the metadata field. Peewee provides a set of convenient methods to work with JSON.

To retrieve all posts that have an author named “John Doe”, you can use the fn function along with the MATCH operator:

from peewee import fn

author = "John Doe"
query = Post.select().where(fn.json_extract_path_text(Post.metadata, 'author') == author)

for post in query:
    print(post.title)

You can also perform more complex queries using the JSON functions provided by your database backend. For example, if you want to find posts with more than 100 views, you can use the >, <, >=, <= operators:

views_threshold = 100
query = Post.select().where(fn.json_extract_path_text(Post.metadata, 'views').cast('integer') > views_threshold)

for post in query:
    print(post.title)

Conclusion

The Peewee JSONField is a powerful feature that allows you to store and query JSON data in your Python application. It provides a convenient way to work with structured data and leverage the benefits of JSON in your database schema.

If you are working with JSON data and need an ORM for your Python application, give Peewee a try. It’s easy to use, lightweight, and provides excellent support for working with various database backends.

Happy coding!