Category: peewee

Peewee – User defined Operators ”; Previous Next Peewee has Expression class with the help of which we can add any customized operator in Peewee’s list of operators. Constructor for Expression requires three arguments, left operand, operator and right operand. op=Expression(left, operator, right) Using Expression class, we define a mod() function that accepts arguments for left and right and ‘%’ as operator. from peewee import Expression # the building block for expressions def mod(lhs, rhs): return Expression(lhs, ”%”, rhs) Example We can use it in a SELECT query to obtain list of records in Contacts table with even id. from peewee import * db = SqliteDatabase(”mydatabase.db”) class BaseModel(Model): class Meta: database = db class Contacts(BaseModel): RollNo = IntegerField() Name = TextField() City = TextField() db.create_tables([Contacts]) from peewee import Expression # the building block for expressions def mod(lhs, rhs): return Expression(lhs,”%”, rhs) qry=Contacts.select().where (mod(Contacts.id,2)==0) print (qry.sql()) for q in qry: print (q.id, q.Name, q.City) This code will emit following SQL query represented by the string − (”SELECT “t1″.”id”, “t1″.”RollNo”, “t1″.”Name”, “t1″.”City” FROM “contacts” AS “t1” WHERE ((“t1″.”id” % ?) = ?)”, [2, 0]) Output Therefore, the output is as follows − 2 Amar Delhi 4 Leena Nasik 6 Hema Nagpur 8 John Delhi 10 Raja Nasik Print Page Previous Next Advertisements ”;

Peewee – Constraints ”; Previous Next Constraints are restrictions imposed on the possible values that can be put in a field. One such constraint is primary key. When primary_key=True is specified in Field definition, each row can only store unique value – same value of the field cannot be repeated in another row. If a field is not a primary key, still it can be constrained to store unique values in table. Field constructor also has constraints parameter. Following example applies CHECK constraint on age field. class MyUser (Model): name=TextField() city=TextField() age=IntegerField(constraints=[Check(”name<10”)]) class Meta: database=db db_table=”MyUser” This will generate following Data Definition Language (DDL) expression − CREATE TABLE MyUser ( id INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL, age INTEGER NOT NULL CHECK (name < 10) ); As a result, if a new row with age<10 will result in error. MyUser.create(name=”Rajesh”, city=”Mumbai”,age=9) peewee.IntegrityError: CHECK constraint failed: MyUser In the field definition, we can also use DEFAULT constraint as in following definition of city field. city=TextField(constraints=[SQL(“DEFAULT ”Mumbai””)]) So, the model object can be constructed with or without explicit value of city. If not used, city field will be filled by default value – Mumbai. Print Page Previous Next Advertisements ”;

Peewee – Subqueries ”; Previous Next In SQL, a subquery is an embedded query in WHERE clause of another query. We can implement subquery as a model.select() as a parameter inside where attribute of outer model.select() statement. To demonstrate use of subquery in Peewee, let us use defined following models − from peewee import * db = SqliteDatabase(”mydatabase.db”) class BaseModel(Model): class Meta: database = db class Contacts(BaseModel): RollNo = IntegerField() Name = TextField() City = TextField() class Branches(BaseModel): RollNo = IntegerField() Faculty = TextField() db.create_tables([Contacts, Branches]) After tables are created, they are populated with following sample data − Contacts table The contacts table is given below − In order to display name and city from contact table only for RollNo registered for ETC faculty, following code generates a SELECT query with another SELECT query in its WHERE clause. #this query is used as subquery faculty=Branches.select(Branches.RollNo).where(Branches.Faculty==”ETC”) names=Contacts.select().where (Contacts.RollNo .in_(faculty)) print (“RollNo and City for Faculty=”ETC””) for name in names: print (“RollNo:{} City:{}”.format(name.RollNo, name.City)) db.close() Above code will display the following result: RollNo and City for Faculty=”ETC” RollNo:103 City:Indore RollNo:104 City:Nasik RollNo:108 City:Delhi RollNo:110 City:Nasik Print Page Previous Next Advertisements ”;

Peewee – Database Errors ”; Previous Next Python’s DB-API standard (recommended by PEP 249) specifies the types of Exception classes to be defined by any DB-API compliant module (such as pymysql, pyscopg2, etc.). Peewee API provides easy-to-use wrappers for these exceptions. PeeweeException is the base classes from which following Exception classes has been defined in Peewee API − DatabaseError DataError IntegrityError InterfaceError InternalError NotSupportedError OperationalError ProgrammingError Instead of DB-API specific exceptions to be tried, we can implement above ones from Peewee. Print Page Previous Next Advertisements ”;

Peewee – Primary and Composite Keys ”; Previous Next It is recommended that the table in a relational database, should have one of the columns applied with primary key constraint. Accordingly, Peewee Model class can also specify field attribute with primary-key argument set to True. However, if model class doesn’t have any primary key, Peewee automatically creates one with the name “id”. Note that the User model defined above doesn’t have any field explicitly defined as primary key. Hence, the mapped User table in our database has an id field. To define an auto-incrementing integer primary key, use AutoField object as one attribute in the model. class User (Model): user_id=AutoField() name=TextField() age=IntegerField() class Meta: database=db db_table=”User” This will translate into following CREATE TABLE query − CREATE TABLE User ( user_id INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL ); You can also assign any non-integer field as a primary key by setting primary_key parameter to True. Let us say we want to store certain alphanumeric value as user_id. class User (Model): user_id=TextField(primary_key=True) name=TextField() age=IntegerField() class Meta: database=db db_table=”User” However, when model contains non-integer field as primary key, the save() method of model instance doesn’t cause database driver to generate new ID automatically, hence we need to pass force_insert=True parameter. However, note that the create() method implicitly specifies force_insert parameter. User.create(user_id=”A001”,name=”Rajesh”, age=21) b=User(user_id=”A002”,name=”Amar”, age=20) b.save(force_insert=True) The save() method also updates an existing row in the table, at which time, force_insert primary is not necessary, as ID with unique primary key is already existing. Peewee allows feature of defining composite primary key. Object of CompositeKey class is defined as primary key in Meta class. In following example, a composite key consisting of name and city fields of User model has been assigned as composite key. class User (Model): name=TextField() city=TextField() age=IntegerField() class Meta: database=db db_table=”User” primary_key=CompositeKey(”name”, ”city”) This model translates in the following CREATE TABLE query. CREATE TABLE User ( name TEXT NOT NULL, city TEXT NOT NULL, age INTEGER NOT NULL, PRIMARY KEY ( name, city ) ); If you wish, the table should not have a primary key, then specify primary_key=False in model’s Meta class. Print Page Previous Next Advertisements ”;

Peewee – Quick Guide ”; Previous Next Peewee – Overview Peewee is a Python Object Relational Mapping (ORM) library which was developed by a U.S. based software engineer Charles Leifer in October 2010. Its latest version is 3.13.3. Peewee supports SQLite, MySQL, PostgreSQL and Cockroach databases. Object Relational Mapping is a programming technique for converting data between incompatible type systems in object-oriented programming languages. Class as defined in an Object Oriented (OO) programming language such as Python, is considered as non-scalar. It cannot be expressed as primitive types such as integers and strings. On the other hand, databases like Oracle, MySQL, SQLite and others can only store and manipulate scalar values such as integers and strings organised within tables. The programmer must either convert the object values into groups of scalar data types for storage in the database or convert them back upon retrieval, or only use simple scalar values within the program. In an ORM system, each class maps to a table in the underlying database. Instead of writing tedious database interfacing code yourself, an ORM takes care of these issues, while you can focus on programming the logics of the system. Environment setup To install latest version of Peewee as hosted on PyPI (Python Package Index), use pip installer. pip3 install peewee There are no other dependencies for Peewee to work. It works with SQLite without installing any other package as sqlite3 module is bundled with standard library. However, to work with MySQL and PostgreSQL, you may have to install DB-API compatible driver modules pymysql and pyscopg2 respectively. Cockroach database is handled through playhouse extension that is installed by default along with Peewee. Peewee is an open source project hosted on https://github.com/coleifer/peewee repository. Hence, it can be installed from here by using git. git clone https://github.com/coleifer/peewee.git cd peewee python setup.py install Peewee – Database Class An object of Database class from Peewee package represents connection to a database. Peewee provides out-of-box support for SQLite, PostgreSQL and MySQL databases through corresponding subclasses of Database class. Database class instance has all the information required to open connection with database engine, and is used to execute queries, manage transactions and perform introspection of tables, columns, etc. Database class has SqliteDatabase, PostgresqlDatabase and MySQLDatabase sub-classes. While DB-API driver for SQLite in the form of sqlite3 module is included in Python’s standard library, psycopg2 and pymysql modules will have to be installed first for using PostgreSql and MySQL databases with Peewee. Using Sqlite Database Python has built-in support for SQLite database in the form of sqlite3 module. Hence, it is very easy to connect. Object of SqliteDatabase class in Peewee represents connection object. con=SqliteDatabase(name, pragmas, timeout) Here, pragma is SQLite extension which is used to modify operation of SQLite library. This parameter is either a dictionary or a list of 2-tuples containing pragma key and value to set every time a connection is opened. Timeout parameter is specified in seconds to set busy-timeout of SQLite driver. Both the parameters are optional. Following statement creates a connection with a new SQLite database (if it doesn’t exist already). >>> db = peewee.SqliteDatabase(”mydatabase.db”) Pragma parameters are generally given for a new database connection. Typical attributes mentioned in pragmase dictionary are journal_mode, cache_size, locking_mode, foreign-keys, etc. >>> db = peewee.SqliteDatabase( ”test.db”, pragmas={”journal_mode”: ”wal”, ”cache_size”: 10000,”foreign_keys”: 1} ) Following pragma settings are ideal to be specified − Pragma attribute Recommended value Meaning journal_mode wal allow readers and writers to co-exist cache_size -1 * data_size_kb set page-cache size in KiB foreign_keys 1 enforce foreign-key constraints ignore_check_constraints 0 enforce CHECK constraints Synchronous 0 let OS handle fsync Peewee also has Another Python SQLite Wrapper (apsw), an advanced sqlite driver. It provides advanced features such as virtual tables and file systems, and shared connections. APSW is faster than the standard library sqlite3 module. Peewee – Model 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

Peewee – Update Existing Records ”; Previous Next Existing data can be modified by calling save() method on model instance as well as with update() class method. Following example fetches a row from User table with the help of get() method and updates it by changing the value of age field. row=User.get(User.name==”Amar”) print (“name: {} age: {}”.format(row.name, row.age)) row.age=25 row.save() The update() method of Method class generates UPDATE query. The query object’s execute() method is then invoked. Following example uses update() method to change the age column of rows in which it is >20. qry=User.update({User.age:25}).where(User.age>20) print (qry.sql()) qry.execute() The SQL query rendered by update() method is as follows − (”UPDATE “User” SET “age” = ? WHERE (“User”.”age” > ?)”, [25, 20]) Peewee also has a bulk_update() method to help update multiple model instance in a single query operation. The method requires model objects to be updated and list of fields to be updated. Following example updates the age field of specified rows by new value. rows=User.select() rows[0].age=25 rows[2].age=23 User.bulk_update([rows[0], rows[2]], fields=[User.age]) Print Page Previous Next Advertisements ”;

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 ”; 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 ”; 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 ”;