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.

Advertisements