Peewee – Delete Records

Peewee – Delete Records ”; Previous Next Running delete_instance() method on a model instance delete corresponding row from the mapped table. obj=User.get(User.name==”Amar”) obj.delete_instance() On the other hand, delete() is a class method defined in model class, which generates DELETE query. Executing it effectively deletes rows from the table. db.create_tables([User]) qry=User.delete().where (User.age==25) qry.execute() Concerned table in database shows effect of DELETE query as follows − (”DELETE FROM “User” WHERE (“User”.”age” = ?)”, [25]) Print Page Previous Next Advertisements ”;

Peewee – Create Index

Peewee – Create Index ”; Previous Next By using Peewee ORM, it is possible to define a model which will create a table with index on single column as well as multiple columns. As per the Field attribute definition, setting unique constraint to True will create an index on the mapped field. Similarly, passing index=True parameter to field constructor also create index on the specified field. In following example, we have two fields in MyUser model, with username field having unique parameter set to True and email field has index=True. class MyUser(Model): username = CharField(unique=True) email = CharField(index=True) class Meta: database=db db_table=”MyUser” As a result, SQLiteStudio graphical user interface (GUI) shows indexes created as follows − In order to define a multi-column index, we need to add indexes attribute in Meta class inside definition of our model class. It is a tuple of 2-item tuples, one tuple for one index definition. Inside each 2-element tuple, the first part of which is a tuple of the names of the fields, the second part is set to True to make it unique, and otherwise is False. We define MyUser model with a two-column unique index as follows − class MyUser (Model): name=TextField() city=TextField() age=IntegerField() class Meta: database=db db_table=”MyUser” indexes=( ((”name”, ”city”), True), ) Accordingly, SQLiteStudio shows index definition as in the following figure − Index can be built outside model definition as well. You can also create index by manually providing SQL helper statement as parameter to add_index() method. MyUser.add_index(SQL(”CREATE INDEX idx on MyUser(name);”)) Above method is particularly required when using SQLite. For MySQL and PostgreSQL, we can obtain Index object and use it with add_index() method. ind=MyUser.index(MyUser.name) MyUser.add_index(ind) Print Page Previous Next Advertisements ”;

Peewee – Model

Peewee – Model ”; Previous Next An object of Model sub class in Peewee API corresponds to a table in the database with which connection has been established. It allows performing database table operations with the help of methods defined in the Model class. A user defined Model has one or more class attributes, each of them is an object of Field class. Peewee has a number of subclasses for holding data of different types. Examples are TextField, DatetimeField, etc. They correspond to the fields or columns in the database table. Reference of associated database and table and model configuration is mentioned in Meta class. Following attributes are used to specify configuration − Meta class Attributes The meta class attributes are explained below − Sr.No Attribute & Description 1 Database Database for model. 2 db_table Name of the table to store data. By default, it is name of model class. 3 Indexes A list of fields to index. 4 primary_key A composite key instance. 5 Constraints A list of table constraints. 6 Schema The database schema for the model. 7 Temporary Indicate temporary table. 8 depends_on Indicate this table depends on another for creation. 9 without_rowid Indicate that table should not have rowid (SQLite only). Following code defines Model class for User table in mydatabase.db − from peewee import * db = SqliteDatabase(”mydatabase.db”) class User (Model): name=TextField() age=IntegerField() class Meta: database=db db_table=”User” User.create_table() The create_table() method is a classmethod of Model class that performs equivalent CREATE TABLE query. Another instance method save() adds a row corresponding to object. from peewee import * db = SqliteDatabase(”mydatabase.db”) class User (Model): name=TextField() age=IntegerField() class Meta: database=db db_table=”User” User.create_table() rec1=User(name=”Rajesh”, age=21) rec1.save() Methods in Model class Other methods in Model class are as follows − Sr.No Model Class & Description 1 Classmethod alias() Create an alias to the model-class. It allows the same Model to any referred multiple times in a query. 2 Classmethod select() Performs a SELECT query operation. If no fields are explicitly provided as argument, the query will by default SELECT * equivalent. 3 Classmethod update() Performs an UPDATE query function. 4 classmethod insert() Inserts a new row in the underlying table mapped to model. 5 classmethod delete() Executes delete query and is usually associated with a filter by where clause. 6 classmethod get() Retrieve a single row from mapped table matching the given filters. 7 get_id() Instance method returns primary key of a row. 8 save() Save the data of object as a new row. If primary-key value is already present, it will cause an UPDATE query to be executed. 9 classmethod bind() Bind the model to the given database. Print Page Previous Next Advertisements ”;

Peewee – Select Records

Peewee – Select Records ”; Previous Next Simplest and the most obvious way to retrieve data from tables is to call select() method of corresponding model. Inside select() method, we can specify one or more field attributes. However, if none is specified, all columns are selected. Model.select() returns a list of model instances corresponding to rows. This is similar to the result set returned by SELECT query, which can be traversed by a for loop. from peewee import * db = SqliteDatabase(”mydatabase.db”) class User (Model): name=TextField() age=IntegerField() class Meta: database=db db_table=”User” rows=User.select() print (rows.sql()) for row in rows: print (“name: {} age: {}”.format(row.name, row.age)) db.close() The above script displays the following output − (”SELECT “t1″.”id”, “t1″.”name”, “t1″.”age” FROM “User” AS “t1″”, []) name: Rajesh age: 21 name: Amar age : 20 name: Kiran age : 19 name: Lata age : 20 Print Page Previous Next Advertisements ”;