Python - MySQL database using Peewee ORM

Discover the synergy of Python and MySQL databases via Peewee ORM. Learn to harness Python's capabilities for efficient data handling, merging the simplicity of MySQL with Peewee's seamless integration.

• Python 3
• Python 3.12

What is ORM?

ORM stands for Object-Relational Mapping. It is a programming technique that enables you to convert data between incompatible systems in object-oriented programming languages. In the context of databases, ORM is used to map objects to tables in a relational database management system.

Why use ORM in Python?

ORM in Python provides a convenient way to interact with databases by abstracting the underlying SQL statements and allowing developers to work with objects directly in their code. This simplifies database operations, enhances code portability, improves security by preventing SQL injection attacks, facilitates code maintenance by providing a clear and concise syntax for database queries, and enables easier unit testing by allowing objects to be manipulated in memory without the need for separate database setups.

What is Peewee ?

Peewee is a lightweight, open-source ORM library for Python. It simplifies database interactions, enabling the mapping of Python objects to SQL data, thus facilitating the handling of databases in Python applications. Its intuitive design makes database operations straightforward and efficient.

Tutorial Python - MySQL database using Peewee ORM

Install the MySQL database.

Install the required packages.

In our example, we installed it on a computer running Linux.

Access the MySQL service as the ROOT user.

Create a database.

Create a user account.

Grant privileges over the database created to this user account.

The MY_PYTHON_USER account will be allowed to access the DRAGONBALLZ database.

Test the MySQL connection to the database using the new account.

On your desktop, install Peewee using PIP.

Install the required Python package to connect to the MySQL server.

Create a Python file.

Import modules and features from Peewee ORM and MySQL.

Create a model class for the table.

The model class does not explicitly specify that ID is a primary key or that it is auto-incremental. However, with Peewee, by default the ID field is automatically treated as a primary key and is auto-incremental.

Establish a connection to the database.

Create the table in the database.

This creates a table in the connected MySQL database. It defines the table's structure with five columns: ID as the primary key set to auto-increment, NAME, SPECIAL_MOVE, SS_LEVEL and EYE_COLOR.

Insert data into the table.

In Peewee, the changes made to the database are automatically committed for basic operations such as inserts, updates, and deletes.

Query all information stored in the table.

Display the information retrieved.

Here is the command output.

Display the information retrieved as a formatted string.

Here is the command output.

Optionally, query only for specific fields.

Here is the command output.

Close the connection to the database.

Here is the complete Python script.

Conclusion

Incorporating Peewee ORM simplifies MySQL database management in Python, enhancing data manipulation efficiency and offering a seamless object-oriented experience. Embrace robust data operations with ease.