Introduction

In this blog post, we will explore how to use Peewee, a lightweight Object-Relational Mapping (ORM) library, with MySQL in Python. Peewee makes it easy to interact with a MySQL database by providing a simple and intuitive API.

Prerequisites

Before we begin, make sure you have the following installed:

• Python: version 3.x
• MySQL: version 5.x or above
• Peewee: you can install it using pip with the command pip install peewee

Setting up the MySQL Connection

To start using Peewee with MySQL, we first need to establish a connection to our MySQL database. We can do this by creating an instance of the MySQLDatabase class provided by Peewee and passing the necessary configuration parameters:

from peewee import MySQLDatabase

db = MySQLDatabase(
    database='my_database',
    user='my_user',
    password='my_password',
    host='localhost',
    port=3306
)

Replace 'my_database', 'my_user', 'my_password', 'localhost', and 3306 with your specific database details.

Defining Models

Next, we can define our database models using Peewee’s Model class. Each model represents a table in our MySQL database and contains fields that map to the table’s columns. For example, let’s define a User model with name and email fields:

from peewee import Model, CharField

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

    class Meta:
        database = db

In the above code, we import the necessary classes from Peewee and define a User model with CharField fields for name and email. We also specify the database attribute as our previously defined db instance.

Note that we can specify various field types provided by Peewee to match the column types in our MySQL database.

Creating Tables

Once our models are defined, we can create the corresponding tables in our MySQL database using Peewee’s create_tables() method:

db.create_tables([User])

The create_tables() method takes a list of models as an argument. In the above example, we pass [User] to create the User table.

Interacting with the Database

With our tables created, we can now perform various operations on our MySQL database using Peewee. Here are some common operations:

Inserting Rows

To insert a new row into a table, we can create an instance of the corresponding model and use the save() method:

new_user = User(name='John Doe', email='john@example.com')
new_user.save()

Querying Rows

We can query rows from a table using the select() method, followed by various filter conditions:

users = User.select().where(User.name.contains('Doe'))

The above code selects all users whose name contains ‘Doe’.

Updating Rows

To update rows in a table, we can first retrieve the desired rows and then modify the corresponding fields:

user = User.get(User.email == 'john@example.com')
user.name = 'Jane Doe'
user.save()

Deleting Rows

To delete rows from a table, we can use the delete_instance() method on the desired model instances:

user = User.get(User.email == 'john@example.com')
user.delete_instance()

Conclusion

In this blog post, we’ve learned how to use Peewee with MySQL in Python. We explored establishing a MySQL connection, defining models, creating tables, and performing common database operations. Peewee’s simplicity and intuitive API make it a powerful tool for interacting with MySQL databases in Python.

Feel free to explore the Peewee documentation for more advanced features and functionalities. Happy coding!