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

Peewee – Using MySQL ”; Previous Next As mentioned earlier, Peewee supports MySQL database through MySQLDatabase class. However, unlike SQLite database, Peewee can’t create a MySql database. You need to create it manually or using functionality of DB-API compliant module such as pymysql. First, you should have MySQL server installed in your machine. It can be a standalone MySQL server installed from https://dev.mysql.com/downloads/installer/. You can also work on Apache bundled with MySQL (such as XAMPP downloaded and installed from https://www.apachefriends.org/download.html ). Next, we install pymysql module, DB-API compatible Python driver. pip install pymysql The create a new database named mydatabase. We shall use phpmyadmin interface available in XAMPP. If you choose to create database programmatically, use following Python script − import pymysql conn = pymysql.connect(host=”localhost”, user=”root”, password=””) conn.cursor().execute(”CREATE DATABASE mydatabase”) conn.close() Once a database is created on the server, we can now declare a model and thereby, create a mapped table in it. The MySQLDatabase object requires server credentials such as host, port, user name and password. from peewee import * db = MySQLDatabase(”mydatabase”, host=”localhost”, port=3306, user=”root”, password=””) 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]) The Phpmyadmin web interface now shows myuser table created. Print Page Previous Next Advertisements ”;