- Installing and Testing
- Quickstart
- Example app
- Using Peewee Interactively
- Contributing
- Database
- Initializing a Database
- Using Postgresql
- Using SQLite
- Using MariaDB
- Using MySQL
- Connecting using a Database URL
- Run-time database configuration
- Dynamically defining a database
- Setting the database at run-time
- Thread-Safety and Multiple Databases
- Connection Management
- Connection Pooling
- Testing Peewee Applications
- Async with Gevent
- Framework Integration
- Executing Queries
- Managing Transactions
- Database Errors
- Logging queries
- Adding a new Database Driver
- Models and Fields
- Querying
- Creating a new record
- Bulk inserts
- Updating existing records
- Atomic updates
- Deleting records
- Selecting a single record
- Create or get
- Selecting multiple records
- Filtering records
- Sorting records
- Getting random records
- Paginating records
- Counting records
- Aggregating records
- Retrieving Scalar Values
- Window functions
- Retrieving row tuples / dictionaries / namedtuples
- Returning Clause
- Common Table Expressions
- Foreign Keys and Joins
- Query operators
- Relationships and Joins
- API Documentation
- Database
Database
Database.init()
Database.__enter__()
Database.connection_context()
Database.connect()
Database.close()
Database.is_closed()
Database.connection()
Database.cursor()
Database.execute_sql()
Database.execute()
Database.last_insert_id()
Database.rows_affected()
Database.in_transaction()
Database.atomic()
Database.manual_commit()
Database.session_start()
Database.session_commit()
Database.session_rollback()
Database.transaction()
Database.savepoint()
Database.begin()
Database.commit()
Database.rollback()
Database.batch_commit()
Database.table_exists()
Database.get_tables()
Database.get_indexes()
Database.get_columns()
Database.get_primary_keys()
Database.get_foreign_keys()
Database.get_views()
Database.sequence_exists()
Database.create_tables()
Database.drop_tables()
Database.bind()
Database.bind_ctx()
Database.extract_date()
Database.truncate_date()
Database.random()
SqliteDatabase
SqliteDatabase.pragma()
SqliteDatabase.cache_size
SqliteDatabase.foreign_keys
SqliteDatabase.journal_mode
SqliteDatabase.journal_size_limit
SqliteDatabase.mmap_size
SqliteDatabase.page_size
SqliteDatabase.read_uncommitted
SqliteDatabase.synchronous
SqliteDatabase.wal_autocheckpoint
SqliteDatabase.timeout
SqliteDatabase.register_aggregate()
SqliteDatabase.aggregate()
SqliteDatabase.register_collation()
SqliteDatabase.collation()
SqliteDatabase.register_function()
SqliteDatabase.func()
SqliteDatabase.register_window_function()
SqliteDatabase.window_function()
SqliteDatabase.table_function()
SqliteDatabase.unregister_aggregate()
SqliteDatabase.unregister_collation()
SqliteDatabase.unregister_function()
SqliteDatabase.unregister_table_function()
SqliteDatabase.load_extension()
SqliteDatabase.attach()
SqliteDatabase.detach()
SqliteDatabase.atomic()
SqliteDatabase.transaction()
PostgresqlDatabase
MySQLDatabase
- Query-builder
Node
Source
BaseTable
Table
Join
ValuesList
CTE
ColumnBase
Column
Alias
Negated
Value
AsIs()
Cast
Ordering
Asc()
Desc()
Expression
Entity
SQL
Check()
Function
fn()
Window
Case()
NodeList
CommaNodeList()
EnclosedNodeList()
DQ
Tuple
OnConflict
EXCLUDED
BaseQuery
RawQuery
Query
SelectQuery
SelectBase
CompoundSelectQuery
Select
_WriteQuery
Update
Insert
Delete
Index
ModelIndex
- Fields
Field
IntegerField
BigIntegerField
SmallIntegerField
AutoField
BigAutoField
IdentityField
FloatField
DoubleField
DecimalField
CharField
FixedCharField
TextField
BlobField
BitField
BigBitField
UUIDField
BinaryUUIDField
DateTimeField
DateField
TimeField
TimestampField
IPField
BooleanField
BareField
ForeignKeyField
DeferredForeignKey
ManyToManyField
DeferredThroughModel
CompositeKey
- Schema Manager
- Model
Metadata
SubclassAwareMetadata
Model
Model.alias()
Model.select()
Model.update()
Model.insert()
Model.insert_many()
Model.insert_from()
Model.replace()
Model.replace_many()
Model.raw()
Model.delete()
Model.create()
Model.bulk_create()
Model.bulk_update()
Model.get()
Model.get_or_none()
Model.get_by_id()
Model.set_by_id()
Model.delete_by_id()
Model.get_or_create()
Model.filter()
Model.get_id()
Model.save()
Model.dirty_fields
Model.is_dirty()
Model.delete_instance()
Model.bind()
Model.bind_ctx()
Model.table_exists()
Model.create_table()
Model.drop_table()
Model.truncate_table()
Model.index()
Model.add_index()
Model.dependencies()
Model.__iter__()
Model.__len__()
ModelAlias
ModelSelect
prefetch()
- Query-builder Internals
- Constants and Helpers
- Database
- SQLite Extensions
- Playhouse, extensions to Peewee
- Sqlite Extensions
- SqliteQ
- Sqlite User-Defined Functions
- Functions, listed by collection name
if_then_else()
strip_tz()
humandelta()
mintdiff()
avgtdiff()
duration()
date_series()
file_ext()
file_read()
gzip()
gunzip()
hostname()
toggle()
setting()
clear_toggles()
clear_settings()
randomrange()
gauss_distribution()
sqrt()
tonumber()
mode()
minrange()
avgrange()
range()
median()
substr_count()
strip_chars()
damerau_levenshtein_dist()
levenshtein_dist()
str_dist()
regex_search()
- Functions, listed by collection name
- apsw, an advanced sqlite driver
- Sqlcipher backend
- Postgresql Extensions
- Cockroach Database
- MySQL Extensions
- DataSet
- Fields
- Hybrid Attributes
- Key/Value Store
- Shortcuts
- Signal support
- pwiz, a model generator
- Schema Migrations
- Example usage
- Supported Operations
- Migrations API
migrate()
SchemaMigrator
SchemaMigrator.add_column()
SchemaMigrator.drop_column()
SchemaMigrator.rename_column()
SchemaMigrator.add_not_null()
SchemaMigrator.drop_not_null()
SchemaMigrator.add_column_default()
SchemaMigrator.drop_column_default()
SchemaMigrator.alter_column_type()
SchemaMigrator.rename_table()
SchemaMigrator.add_index()
SchemaMigrator.drop_index()
SchemaMigrator.add_constraint()
SchemaMigrator.drop_constraint()
SchemaMigrator.add_unique()
PostgresqlMigrator
SqliteMigrator
MySQLMigrator
- Reflection
- Database URL
- Connection pool
- Test Utils
- Flask Utils
- Query Examples
- Model Definitions
- Schema Creation
- Basic Exercises
- Retrieve everything
- Retrieve specific columns from a table
- Control which rows are retrieved
- Control which rows are retrieved - part 2
- Basic string searches
- Matching against multiple possible values
- Classify results into buckets
- Working with dates
- Removing duplicates, and ordering results
- Combining results from multiple queries
- Simple aggregation
- More aggregation
- Joins and Subqueries
- Retrieve the start times of members’ bookings
- Work out the start times of bookings for tennis courts
- Produce a list of all members who have recommended another member
- Produce a list of all members, along with their recommender
- Produce a list of all members who have used a tennis court
- Produce a list of costly bookings
- Produce a list of all members, along with their recommender, using no joins.
- Produce a list of costly bookings, using a subquery
- Modifying Data
- Insert some data into a table
- Insert multiple rows of data into a table
- Insert calculated data into a table
- Update some existing data
- Update multiple rows and columns at the same time
- Update a row based on the contents of another row
- Delete all bookings
- Delete a member from the cd.members table
- Delete based on a subquery
- Aggregation
- Count the number of facilities
- Count the number of expensive facilities
- Count the number of recommendations each member makes.
- List the total slots booked per facility
- List the total slots booked per facility in a given month
- List the total slots booked per facility per month
- Find the count of members who have made at least one booking
- List facilities with more than 1000 slots booked
- Find the total revenue of each facility
- Find facilities with a total revenue less than 1000
- Output the facility id that has the highest number of slots booked
- List the total slots booked per facility per month, part 2
- List the total hours booked per named facility
- List each member’s first booking after September 1st 2012
- Produce a list of member names, with each row containing the total member count
- Produce a numbered list of members
- Output the facility id that has the highest number of slots booked, again
- Rank members by (rounded) hours used
- Find the top three revenue generating facilities
- Classify facilities by value
- Recursion
- Query Builder
- Hacks
- Changes in 3.0
Installing and Testing
Most users will want to simply install the latest version, hosted on PyPI:
pip install peewee
Peewee comes with a couple C extensions that will be built if Cython is available.
Sqlite extensions, which includes Cython implementations of the SQLite date manipulation functions, the REGEXP operator, and full-text search result ranking algorithms.
Installing with git
The project is hosted at https://github.com/coleifer/peewee and can be installed using git:
git clone https://github.com/coleifer/peewee.git
cd peewee
python setup.py install
Note
On some systems you may need to use sudo python setup.py install
to
install peewee system-wide.
If you would like to build the SQLite extension in a git checkout, you can run:
# Build the C extension and place shared libraries alongside other modules.
python setup.py build_ext -i
Running tests
You can test your installation by running the test suite.
python runtests.py
You can test specific features or specific database drivers using the
runtests.py
script. To view the available test runner options, use:
python runtests.py --help
Note
To run tests against Postgres or MySQL you need to create a database named “peewee_test”. To test the Postgres extension module, you will also want to install the HStore extension in the postgres test database:
-- install the hstore extension on the peewee_test postgres db.
CREATE EXTENSION hstore;
Optional dependencies
Note
To use Peewee, you typically won’t need anything outside the standard
library, since most Python distributions are compiled with SQLite support.
You can test by running import sqlite3
in the Python console. If you
wish to use another database, there are many DB-API 2.0-compatible drivers
out there, such as pymysql
or psycopg2
for MySQL and Postgres
respectively.
Cython: used to expose additional functionality when using SQLite and to implement things like search result ranking in a performant manner. Since the generated C files are included with the package distribution, Cython is no longer required to use the C extensions.
apsw: an optional 3rd-party SQLite binding offering greater performance and comprehensive support for SQLite’s C APIs. Use with
APSWDatabase
.gevent is an optional dependency for
SqliteQueueDatabase
(though it works withthreading
just fine).BerkeleyDB can be compiled with a SQLite frontend, which works with Peewee. Compiling can be tricky so here are instructions.
Lastly, if you use the Flask framework, there are helper extension modules available.
Note on the SQLite extensions
Peewee includes two SQLite-specific C extensions which provide additional functionality and improved performance for SQLite database users. Peewee will attempt to determine ahead-of-time if SQLite3 is installed, and only build the SQLite extensions if the SQLite shared-library is available on your system.
If, however, you receive errors like the following when attempting to install
Peewee, you can explicitly disable the compilation of the SQLite C extensions
by settings the NO_SQLITE
environment variable.
fatal error: sqlite3.h: No such file or directory
Here is how to install Peewee with the SQLite extensions explicitly disabled:
$ NO_SQLITE=1 python setup.py install