Python Peewee LibraryPeewee is a Python library based on ORM (Object-Relational Mapping), which supports SQLite, MySQL, PostgreSQL, and Cockroach databases. In the following tutorial, we will be learning how to insert a new record, delete a record, create an index, and a lot more using the Peewee library in the Python programming language. Moreover, we will also understand connection management, text and binary fields, subqueries, filters, and more with respect to Peewee. So, let's get started. Understanding the Peewee library in Python
Setting Up the EnvironmentWe can install the latest version of Peewee as hosted on PyPI (short for Python Package Index), using the pip installer as shown below: Syntax: There are no other dependencies for this library to work. It works with SQLite without installing any other package since the sqlite3 module is bundled with the standard library. However, in order to work with MySQL and PostgreSQL, we may have to install DB-API compatible driver modules - pymysql and pyscopg2, respectively. The cockroach database is handled through a playhouse extension that is installed by default together with the Peewee library. Understanding the mapping using PeeweeA Model maps to the database table, a Field to the table column, and an instance to the table row. Peewee utilizes MySQLDatabase for MySQL, PostgresqlDatabase for PostgreSQL, and SqliteDatabase for SQLite. For the following tutorial, we will be working with the SQLite database. Understanding the Field Types in PeeweeField types in a Peewee model define the storage type of the model. They are translated to the corresponding database column types.
The above table lists the Peewee field types and the corresponding SQLite, PostgreSQL, and MySQL column types. Defining the model using PeeweeIn the first example, we will be creating a simple database table. Example: Output: sqlite> select * from notes; 1|Went to the Gym|2021-12-09 2|Went to the Cinema|2021-12-08 3|Watered the plants|2021-12-08 4|Listened to music|2021-12-09 Explanation: In the above snippet of code, we have imported the required library. We have then created a database as 'testSpace.db'. We have then created a class of the Peewee model and added some fields to it. We created the database table for the database and created the table. At last, we have inserted the entries in the table and saved the database. Dropping table using PeeweeWe can drop the table using the drop_table() function. Let us consider the following example demonstrating the same. Example: Explanation: In the above snippet of code, we have imported the required libraries. We have then created a database as 'testSpace.db'. We have then defined a class as 'Notes' for the Peewee model and created a table. We have then used the drop_table() function to drop the table. Peewee insert_many() methodThe insert_many() method of the Peewee library is used to perform bulk creates operation. Let us consider the following example demonstrating the same. Example: Output: sqlite> select * from notes; 1|Went to the Gym|2021-12-10 2|Went to the Cinema|2021-12-08 3|Watered the plants|2021-12-08 4|Listened to music|2021-12-10 5|Visited friends in the morning|2021-12-07 6|Worked on a Project|2021-12-10 7|Went to Shopping mall|2021-12-06 8|Listened to songs|2021-12-02 9|Watched Web series all day|2021-12-04 10|Watered the plants|2021-12-02 11|Walked for half an hour|2021-12-08 Explanation: In the above snippet of code, we have imported the required libraries. We have then created the database and the table. Later, we defined the data in a list of dictionaries that need to be filled in the table. We have then executed the bulk operation and used the atomic method to put the bulk operation in a transaction. Selecting all instances using PeeweeThe select method of Peewee is used to retrieve instances of the defined models. Let us consider the following example illustrating the same: Example: Output: $ python fetch_data.py SELECT "t1"."id", "t1"."text", "t1"."created" FROM "notes" AS "t1" Went to the Gym on 2021-12-10 Went to the Cinema on 2021-12-08 Watered the plants on 2021-12-08 Listened to music on 2021-12-10 Visited friends in the morning on 2021-12-07 Worked on a Project on 2021-12-10 Went to Shopping mall on 2021-12-06 Listened to songs on 2021-12-02 Watched Web series all day on 2021-12-04 Watered the plants on 2021-12-02 Walked for half an hour on 2021-12-08 Explanation: In the above snippet of code, we have imported the required libraries. We have then created the database and the table. We have then used the select() function to select columns from the tables. We have then used the for-loop to iterate through each row in the table and print them for the users. As a result, the program returned the entries for the users. Filtering data using Peewee where methodThe where method of Peewee allows programmers to filter data based on a specified condition. Let us consider the following example to understand the same: Example: Output: 5 Visited friends in the morning on 2021-12-07 6 Worked on a Project on 2021-12-10 7 Went to Shopping mall on 2021-12-06 8 Listened to songs on 2021-12-02 9 Watched Web series all day on 2021-12-04 10 Watered the plants on 2021-12-02 11 Walked for half an hour on 2021-12-08 Explanation: In the above snippet of code, we have imported the required libraries and created a database and the table. We have then used the where method along with the select() function to filter the data in the table. We have then used the for-loop to iterate through each row of the table and print the data for the users. Peewee multiple where expressionsWe can use the where method multiple times and combine them to produce a new expression and precise the filtration process. Let us consider the following example demonstrating the same: Example: Output: 4 Listened to music on 2021-12-10 5 Visited friends in the morning on 2021-12-07 6 Worked on a Project on 2021-12-10 7 Went to Shopping mall on 2021-12-06 8 Listened to songs on 2021-12-02 9 Watched Web series all day on 2021-12-04 Explanation: We have imported the required libraries and created the database and the table in the above snippet of code. We have now used the where method specifying the limits in order to filter the data in a specific manner. We have then used the for-loop to iterate through the rows and print them for the users. Retrieving Single instance using PeeweeThere are two methods that we can use to select a single instance; each of them utilizes a get() function. Let us consider the following example to understand the working of the same: Example: Output: 1 Went to the Gym 2021-12-10 6 Worked on a Project 2021-12-10 Explanation: We have imported the required libraries and created the database and the table in the above snippet of code. In order to retrieve a single instance, we can either the first way, where we can use the where method along with the get() function, or the second way using the get() function. Both methods are shown in the above example. Selecting column using PeeweeWe can specify the names of the columns inside the select method that has to be included within the query. Here is the example that demonstrates the same: Example: Output: [('Went to the Gym', datetime.date(2021, 12, 10)), ('Went to the Cinema', datetime.date(2021, 12, 8)), ('Watered the plants', datetime.date(2021, 12, 8))] Explanation: We have imported the required libraries and created the database and the table in the above snippet of code. We have then used the select() function specifying the names of the columns that have to be selected. We have also included the limit function to limit the number of entries to be printed. Counting Instances using PeeweeWe can use the count method in order to calculate the number of model instances in the table. Let us consider the following example that illustrates the same: Example: Output: 11 7 Explanation: In the above snippet of code, we have imported the required libraries and created the database and the table. We have then used the count() function to count the total number of entries. We have then used the where() function specifying the limits of the selection of the entries and used the count() function again to print the number of selected entries only. Displaying SQL statements using PeeweeThe Peewee library offers the sql method that allows programmers to generate SQL statements. Let us consider the following example that illustrates the same: Example: Output: ('SELECT "t1"."id", "t1"."text", "t1"."created" FROM "notes" AS "t1" WHERE ("t1"."id" = ?)', [4]) Explanation: We have imported the required libraries and created the database and the table in the above snippet of code. We then used the select() function and the where() to select the entry with id = 4. We have then used the sql function to print the SQL statement for that selection operation. Peewee offset and limit attributesPeewee also provides several attributes like offset and limit that allow programmers to define the initial skip of instances and the number of instances to be included in the select function. Let us consider the following example demonstrating the same: Example: Output: 4 Listened to music 2021-12-10 5 Visited friends in the morning 2021-12-07 6 Worked on a Project 2021-12-10 7 Went to Shopping mall 2021-12-06 Explanation: We have imported the required libraries and created the database and the table in the above snippet of code. We have then used the offset and limit attributes with the select() function in order to select the entries ranging from the 4th entry to the next three entries. Ordering using PeeweeWe can use the Peewee order_by function in order to retrieve instances. Let us consider the following example demonstrating the same: Example: Output: Ascending order ******************************** Listened to songs 2021-12-02 Watered the plants 2021-12-02 Watched Web series all day 2021-12-04 Went to Shopping mall 2021-12-06 Visited friends in the morning 2021-12-07 Went to the Cinema 2021-12-08 Watered the plants 2021-12-08 Walked for half an hour 2021-12-08 Went to the Gym 2021-12-10 Listened to music 2021-12-10 Worked on a Project 2021-12-10 Descending order ******************************** Went to the Gym 2021-12-10 Listened to music 2021-12-10 Worked on a Project 2021-12-10 Went to the Cinema 2021-12-08 Watered the plants 2021-12-08 Walked for half an hour 2021-12-08 Visited friends in the morning 2021-12-07 Went to Shopping mall 2021-12-06 Watched Web series all day 2021-12-04 Listened to songs 2021-12-02 Watered the plants 2021-12-02 Explanation: We have imported the required libraries and created the database and the table in the above snippet of code. We then used the select function and the order_by method to select the entries in the table and arrange them in ascending order. We have then used the for-loop to iterate through each row and print them. We have then used the select function along with the order_by method again. However, we have added the desc() function in order to arrange the entries in descending order and print them for the users with the help of the for-loop. Deleting instance using Peewee The Peewee library provides the delete_by_id method that allows programmers to delete an instance identified by its ID. This function returns the number of deleted instances. Let us consider the following example demonstrating the same: Example: Output: 1 - Went to the Gym on 2021-12-10 2 - Went to the Cinema on 2021-12-08 4 - Listened to music on 2021-12-10 5 - Visited friends in the morning on 2021-12-07 6 - Worked on a Project on 2021-12-10 7 - Went to Shopping mall on 2021-12-06 8 - Listened to songs on 2021-12-02 9 - Watched Web series all day on 2021-12-04 10 - Watered the plants on 2021-12-02 11 - Walked for half an hour on 2021-12-08 Explanation: We have imported the required libraries and created the database and the table in the above snippet of code. We have then used the delete_by_id() function to delete the entry at the ID = 3. We have then printed the entire table for the users. As a result, the entry at ID = 3 has been deleted from the table successfully. Deleting multiple instances using Peewee In order to delete more than one instance, we can call the Peewee delete method. This method will return the number of successfully deleted instances. Let us consider the following example illustrating the same: Example: Output: 7 instances deleted Explanation: We have imported the required libraries and created the database and the table in the above snippet of code. We have then used the delete() method and the where() method to delete the instances with the IDs above 3. As a result, seven instances are deleted from the table. Updating instance using Peewee We can use the Peewee update method in order to update an instance. It returns the number of successfully updated instances. Here is the example demonstrating the same: Example: Output: No. of rows updated: 1 Explanation: We have imported the required libraries and created the database and the table in the above snippet of code. We have then used the update() function to update the date of the instance at ID = 2. Next TopicSome Cryptocurrency Libraries for Python |