Category: peewee

Discuss Peewee ”; Previous Next Peewee is a Python ORM (Object-Relational Mapping) library which supports SQLite, MySQL, PostgreSQL and Cockroach databases. This tutorial will help you to understand how to insert a new record, delete a record, create an index, etc., with the help of Peewee. Moreover, you will gain knowledge about connection management, text and binary fields, subqueries, filters, etc., with regards to Peewee. Print Page Previous Next Advertisements ”;

Peewee – Using CockroachDB ”; Previous Next CockroachDB or Cockroach Database (CRDB) is developed by computer software company Cockroach Labs. It is a scalable, consistently-replicated, transactional datastore which is designed to store copies of data in multiple locations in order to deliver speedy access. Peewee provides support to this database by way of CockroachDatabase class defined in playhouse.cockroachdb extension module. The module contains definition of CockroachDatabase as subclass of PostgresqlDatabase class from the core module. Moreover, there is run_transaction() method which runs a function inside a transaction and provides automatic client-side retry logic. Field Classes The extension also has certain special field classes that are used as attribute in CRDB compatible model. UUIDKeyField – A primary-key field that uses CRDB’s UUID type with a default randomly-generated UUID. RowIDField – A primary-key field that uses CRDB’s INT type with a default unique_rowid(). JSONField – Same as the Postgres BinaryJSONField. ArrayField – Same as the Postgres extension, but does not support multi-dimensional arrays. Print Page Previous Next Advertisements ”;

Peewee – PostgreSQL and MySQL Extensions ”; Previous Next Additional PostgreSQL functionality is enabled by helpers which are defined in playhouse.postgres_ext module. This module defines PostgresqlExtDatabase class and provides the following additional field types to be exclusively used for declaration of model to be mapped against PostgreSQL database table. Features of PostgreSQL Extensions The features of PostgreSQL Extensions which are supported by Peewee are as follows − ArrayField field type, for storing arrays. HStoreField field type, for storing key/value pairs. IntervalField field type, for storing timedelta objects. JSONField field type, for storing JSON data. BinaryJSONField field type for the jsonb JSON data type. TSVectorField field type, for storing full-text search data. DateTimeTZField field type, a timezone-aware datetime field. Additional Postgres-specific features in this module are meant to provide. hstore support. server-side cursors. full-text search. Postgres hstore is a key:value store that can be embedded in a table as one of the fields of type HStoreField. To enable hstore support, create database instance with register_hstore=True parameter. db = PostgresqlExtDatabase(”mydatabase”, register_hstore=True) Define a model with one HStoreField. class Vehicles(BaseExtModel): type = CharField() features = HStoreField() Create a model instance as follows − v=Vechicle.create(type=”Car”, specs:{”mfg”:”Maruti”, ”Fuel”:”Petrol”, ”model”:”Alto”}) To access hstore values − obj=Vehicle.get(Vehicle.id=v.id) print (obj.features) MySQL Extensions Alternate implementation of MysqlDatabase class is provided by MySQLConnectorDatabase defined in playhouse.mysql_ext module. It uses Python’s DB-API compatible official mysql/python connector. from playhouse.mysql_ext import MySQLConnectorDatabase db = MySQLConnectorDatabase(”mydatabase”, host=”localhost”, user=”root”, password=””) Print Page Previous Next Advertisements ”;

Peewee – Counting and Aggregation ”; Previous Next We can find number of records reported in any SELECT query by attaching count() method. For example, following statement returns number of rows in Contacts table with City=’Nasik’. qry=Contacts.select().where (Contacts.City==”Nasik”).count() print (qry) Example SQL has GROUP BY clause in SELECT query. Peewee supports it in the form of group_by() method. Following code returns city wise count of names in Contacts table. from peewee import * db = SqliteDatabase(”mydatabase.db”) class Contacts(BaseModel): RollNo = IntegerField() Name = TextField() City = TextField() class Meta: database = db db.create_tables([Contacts]) qry=Contacts.select(Contacts.City, fn.Count(Contacts.City).alias(”count”)).group_by(Contacts.City) print (qry.sql()) for q in qry: print (q.City, q.count) The SELECT query emitted by Peewee will be as follows − (”SELECT “t1″.”City”, Count(“t1″.”City”) AS “count” FROM “contacts” AS “t1” GROUP BY “t1″.”City””, []) Output As per sample data in Contacts table, following output is displayed − Chennai 1 Delhi 2 Indore 1 Mumbai 1 Nagpur 1 Nasik 3 Pune 1 Print Page Previous Next Advertisements ”;

Peewee – Defining Database Dynamically ”; Previous Next If your database is scheduled to vary at run-time, use DatabaseProxy helper to have better control over how you initialise it. The DatabaseProxy object is a placeholder with the help of which database can be selected in run-time. In the following example, an appropriate database is selected depending on the application’s configuration setting. from peewee import * db_proxy = DatabaseProxy() # Create a proxy for our db. class MyUser (Model): name=TextField() city=TextField(constraints=[SQL(“DEFAULT ”Mumbai””)]) age=IntegerField() class Meta: database=db_proxy db_table=”MyUser” # Based on configuration, use a different database. if app.config[”TESTING”]: db = SqliteDatabase(”:memory:”) elif app.config[”DEBUG”]: db = SqliteDatabase(”mydatabase.db”) else: db = PostgresqlDatabase( ”mydatabase”, host=”localhost”, port=5432, user=”postgres”, password=”postgres” ) # Configure our proxy to use the db we specified in config. db_proxy.initialize(db) db.connect() db.create_tables([MyUser]) You can also associate models to any database object during run-time using bind() method declared in both database class and model class. Following example uses bind() method in database class. from peewee import * class MyUser (Model): name=TextField() city=TextField(constraints=[SQL(“DEFAULT ”Mumbai””)]) age=IntegerField() db = MySQLDatabase(”mydatabase”, host=”localhost”, port=3306, user=”root”, password=””) db.connect() db.bind([MyUser]) db.create_tables([MyUser]) The same bind() method is also defined in Model class. from peewee import * class MyUser (Model): name=TextField() city=TextField(constraints=[SQL(“DEFAULT ”Mumbai””)]) age=IntegerField() db = MySQLDatabase(”mydatabase”, host=”localhost”, port=3306, user=”root”, password=””) db.connect() MyUser.bind(db) db.create_tables([MyUser]) Print Page Previous Next Advertisements ”;

Peewee – SQLite Extensions ”; Previous Next Peewee comes with a Playhouse namespace. It is a collection of various extension modules. One of them is a playhouse.sqlite_ext module. It mainly defines SqliteExtDatabase class which inherits SqliteDatabase class, supports following additional features − Features of SQLite Extensions The features of SQLite Extensions which are supported by Peewee are as follows − Full-text search. JavaScript Object Notation (JSON) extension integration. Closure table extension support. LSM1 extension support. User-defined table functions. Support for online backups using backup API: backup_to_file(). BLOB API support, for efficient binary data storage. JSON data can be stored, if a special JSONField is declared as one of the field attributes. class MyModel(Model): json_data = JSONField(json_dumps=my_json_dumps) To activate full-text search, the model can have DocIdField to define primary key. class NoteIndex(FTSModel): docid = DocIDField() content = SearchField() class Meta: database = db FTSModel is a Subclass of VirtualModel which is available at http://docs.peewee-orm.com/en/latest/peewee/sqlite_ext.html#VirtualModel to be used with the FTS3 and FTS4 full-text search extensions. Sqlite will treat all column types as TEXT (although, you can store other data types, Sqlite will treat them as text). SearchField is a Field-class to be used for columns on models representing full-text search virtual tables. SqliteDatabase supports AutoField for increasing primary key. However, SqliteExtDatabase supports AutoIncrementField to ensure that primary always increases monotonically, irrespective of row deletions. SqliteQ module in playhouse namespace (playhouse.sqliteq) defines subclass of SqliteExeDatabase to handle serialised concurrent writes to a SQlite database. On the other hand, playhouse.apsw module carries support for apsw sqlite driver. Another Python SQLite Wrapper (APSW) is fast and can handle nested transactions, that are managed explicitly by you code. from apsw_ext import * db = APSWDatabase(”testdb”) class BaseModel(Model): class Meta: database = db class MyModel(BaseModel): field1 = CharField() field2 = DateTimeField() Print Page Previous Next Advertisements ”;

Peewee – Field Class ”; Previous Next Model class contains one or more attributes that are objects of Field class in Peewee. Base Field class is not directly instantiated. Peewee defines different sub classes for equivalent SQL data types. Constructor of Field class has following parameters− Sr.No Constructor & Description 1 column_name (str) Specify column name for field. 2 primary_key (bool) Field is the primary key. 3 constraints (list) List of constraints to apply to column 4 choices (list) An iterable of 2-tuples mapping column values to display labels. 5 null (bool) Field allows NULLs. 6 index (bool) Create an index on field. 7 unique (bool) Create an unique index on field. 8 Default Default value. 9 collation (str) Collation name for field. 10 help_text (str) Help-text for field, metadata purposes. 11 verbose_name (str) Verbose name for field, metadata purposes. Subclasses of Field class are mapped to corresponding data types in various databases, i.e. SQLite, PostgreSQL, MySQL, etc. Numeric Field classes The numeric field classes in Peewee are given below − Sr.No Field classes & Description 1 IntegerField Field class for storing integers. 2 BigIntegerField Field class for storing big integers (maps to integer, bigint, and bigint type in SQLite, PostegreSQL and MySQL respectively). 3 SmallIntegerField Field class for storing small integers (if supported by database). 4 FloatField Field class for storing floating-point numbers corresponds to real data types. 5 DoubleField Field class for storing double-precision floating-point numbers maps to equivalent data types in corresponding SQL databases. 6 DecimalField Field class for storing decimal numbers. The parameters are mentioned below − max_digits (int) – Maximum digits to store. decimal_places (int) – Maximum precision. auto_round (bool) – Automatically round values. Text fields The text fields which are available in Peewee are as follows − Sr.No Fields & Description 1 CharField Field class for storing strings. Max 255 characters. Equivalent SQL data type is varchar. 2 FixedCharField Field class for storing fixed-length strings. 3 TextField Field class for storing text. Maps to TEXT data type in SQLite and PostgreSQL, and longtext in MySQL. Binary fields The binary fields in Peewee are explained below − Sr.No Fields & Description 1 BlobField Field class for storing binary data. 2 BitField Field class for storing options in a 64-bit integer column. 3 BigBitField Field class for storing arbitrarily-large bitmaps in a Binary Large OBject (BLOB). The field will grow the underlying buffer as necessary. 4 UUIDField Field class for storing universally unique identifier (UUID) objects. Maps to UUID type in Postgres. SQLite and MySQL do not have a UUID type, it is stored as a VARCHAR. Date and Time fields The date and time fields in Peewee are as follows − Sr.No Fields & Description 1 DateTimeField Field class for storing datetime.datetime objects. Accepts a special parameter string formats, with which the datetime can be encoded. 2 DateField Field class for storing datetime.date objects. Accepts a special parameter string formats to encode date. 3 TimeField Field class for storing datetime.time objectsAccepts a special parameter formats to show encoded time. Since SQLite doesn’t have DateTime data types, this field is mapped as string. ForeignKeyField This class is used to establish foreign key relationship in two models and hence, the respective tables in database. This class in instantiated with following parameters − Sr.No Fields & Description 1 model (Model) Model to reference. If set to ‘self’, it is a self-referential foreign key. 2 field (Field) Field to reference on model (default is primary key). 3 backref (str) Accessor name for back-reference. “+” disables the back-reference accessor. 4 on_delete (str) ON DELETE action. 5 on_update (str) ON UPDATE action. 6 lazy_load (bool) Fetch the related object, when the foreign-key field attribute is accessed. If FALSE, accessing the foreign-key field will return the value stored in the foreign-key column. Example Here is an example of ForeignKeyField. from peewee import * db = SqliteDatabase(”mydatabase.db”) class Customer(Model): id=IntegerField(primary_key=True) name = TextField() address = TextField() phone = IntegerField() class Meta: database=db db_table=”Customers” class Invoice(Model): id=IntegerField(primary_key=True) invno=IntegerField() amount=IntegerField() custid=ForeignKeyField(Customer, backref=”Invoices”) class Meta: database=db db_table=”Invoices” db.create_tables([Customer, Invoice]) When above script is executed, following SQL queries are run − CREATE TABLE Customers ( id INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL, address TEXT NOT NULL, phone INTEGER NOT NULL ); CREATE TABLE Invoices ( id INTEGER NOT NULL PRIMARY KEY, invno INTEGER NOT NULL, amount INTEGER NOT NULL, custid_id INTEGER NOT NULL, FOREIGN KEY ( custid_id ) REFERENCES Customers (id) ); When verified in SQLiteStuidio GUI tool, the table structure appears as below − Other Field Types The other field types

Peewee – Using PostgreSQL ”; Previous Next Peewee supports PostgreSQL database as well. It has PostgresqlDatabase class for that purpose. In this chapter, we shall see how we can connect to Postgres database and create a table in it, with the help of Peewee model. As in case of MySQL, it is not possible to create database on Postgres server with Peewee’s functionality. The database has to be created manually using Postgres shell or PgAdmin tool. First, we need to install Postgres server. For windows OS, we can download https://get.enterprisedb.com/postgresql/postgresql-13.1-1-windows-x64.exe and install. Next, install Python driver for Postgres – Psycopg2 package using pip installer. pip install psycopg2 Then start the server, either from PgAdmin tool or psql shell. We are now in a position to create a database. Run following Python script to create mydatabase on Postgres server. import psycopg2 conn = psycopg2.connect(host=”localhost”, user=”postgres”, password=”postgres”) conn.cursor().execute(”CREATE DATABASE mydatabase”) conn.close() Check that the database is created. In psql shell, it can be verified with l command − To declare MyUser model and create a table of same name in above database, run following Python code − from peewee import * db = PostgresqlDatabase(”mydatabase”, host=”localhost”, port=5432, user=”postgres”, password=”postgres”) class MyUser (Model): name=TextField() city=TextField(constraints=[SQL(“DEFAULT ”Mumbai””)]) age=IntegerField() class Meta: database=db db_table=”MyUser” db.connect() db.create_tables([MyUser]) We can verify that table is created. Inside the shell, connect to mydatabase and get list of tables in it. To check structure of newly created MyUser database, run following query in the shell. Print Page Previous Next Advertisements ”;

Peewee – Database Class ”; Previous Next 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. Print Page Previous Next Advertisements ”;

Peewee – Integration with Web Frameworks ”; Previous Next Peewee can work seamlessly with most of the Python web framework APIs. Whenever the Web Server Gateway Interface (WSGI) server receives a connection request from client, the connection with database is established, and then the connection is closed upon delivering a response. While using in a Flask based web application, connection has an effect on @app.before_request decorator and is disconnected on @app.teardown_request. from flask import Flask from peewee import * db = SqliteDatabase(”mydatabase.db”) app = Flask(__name__) @app.before_request def _db_connect(): db.connect() @app.teardown_request def _db_close(exc): if not db.is_closed(): db.close() Peewee API can also be used in Django. To do so, add a middleware in Django app. def PeeweeConnectionMiddleware(get_response): def middleware(request): db.connect() try: response = get_response(request) finally: if not db.is_closed(): db.close() return response return middleware Middleware is added in Django’s settings module. # settings.py MIDDLEWARE_CLASSES = ( # Our custom middleware appears first in the list. ”my_blog.middleware.PeeweeConnectionMiddleware”, #followed by default middleware list. .. ) Peewee can be comfortably used with other frameworks such as Bottle, Pyramid and Tornado, etc. Print Page Previous Next Advertisements ”;