class - peewee

由 datahunter 在日, 27/04/2014 - 23:18 發表

介紹

peewee 中文解作 n. 矮小的人, 小東西

simple and small ORM

built-in support for sqlite, mysql and postgresql

Install

pip install peewee

version: peewee 2.2.3 <-- sqlite3

事前要理解

Model Definition

Model class    Database table
Field instance    Column on a table
Model instance    Row in a database table

Field:

CharField varchar
TextField text
BooleanField smallint
IntegerField integer
FloatField real
DateTimeField datetime
DateField date
TimeField time
PrimaryKeyField integer
BlobField blob

# Model Definition

from peewee import *

# Create an instance of a Database
db = SqliteDatabase('cms.db')


# In order not to pollute the model namespace,
# model-specific configuration is placed in a special class called Meta

class BaseModel(Model):
    class Meta:
        database = db     # 指定用那個 DB. 當沒有指定 DB 時, 那 peewee 會建立 peewee.db


# Define a model class

class Person(BaseModel):
    name = CharField()
    birthday = DateField()
    is_relative = BooleanField(default = True)

class Pet(BaseModel):
    owner = ForeignKeyField(Person, related_name='pets')
    name = CharField()
    animal_type = CharField()

# 建立 Table

# connect to the database

>>> db.connect()

# 做完所有野後, 可以 Close 它 "db.close()"

# Connecting using a Database URL

import os

from peewee import *
from playhouse.db_url import connect

# Connect to the database URL defined in the environment, falling
# back to a local Sqlite database if no database URL is specified.
db = connect(os.environ.get('DATABASE') or 'sqlite:///default.db')

class BaseModel(Model):
    class Meta:
        database = db

# creating the tables in the database

>>> Person.create_table()
>>> Pet.create_table()

再行一次 create 會有

peewee.OperationalError: table "person" already exists

在未有 tables 時會見到

peewee.OperationalError: no such table: person

# 另一個寫法

# db.create_tables([Person, Pet])

建立的 table schema 如下:

CREATE TABLE "person" ("id" INTEGER NOT NULL PRIMARY KEY,
                        "name" VARCHAR(255) NOT NULL,
                        "birthday" DATE NOT NULL,
                        "is_relative" SMALLINT NOT NULL);

CREATE TABLE "pet" ("id" INTEGER NOT NULL PRIMARY KEY,
                    "owner_id" INTEGER NOT NULL,
                    "name" VARCHAR(255) NOT NULL,
                    "animal_type" VARCHAR(255) NOT NULL,
                    FOREIGN KEY ("owner_id") REFERENCES "person" ("id"));
    
CREATE INDEX "pet_owner_id" ON "pet" ("owner_id");

class Field

Field class is used to describe the mapping of Model attributes to database columns.

class Field(null=False, index=False, unique=False, verbose_name=None, help_text=None, db_column=None, default=None, choices=None, *args, **kwargs)

null (bool) – whether this column can accept None or NULL values
index (bool) – whether to create an index for this column when creating the table
unique (bool) – whether to create a unique index for this column when creating the table
help_text (string) – specify some instruction text for the usage/meaning of this field
choices – an iterable of 2-tuples mapping value to display
primary_key (bool) – whether to use this as the primary key for the table

Field initialization

null = False
index = False
unique = False
help_text = None
primary_key = False

class ForeignKeyField

usage:

class ForeignKeyField(rel_model, related_name=???)

related_name (string) – attribute to expose on related model

# Storing data

>>> from datetime import date

>>> uncle_bob = Person(name='Bob', birthday=date(1960, 1, 15), is_relative=True)
>>> num_of_record = uncle_bob.save() # bob is now stored in the database

# return model

>>> grandma = Person.create(name='Grandma', birthday=date(1935, 3, 1), is_relative=True)

# 建立一個人擁有的 pet

>>> grandma_kitty = Pet.create(owner=grandma, name='Mittens', animal_type='cat')

# Change

>>> grandma.name = 'Grandma L.'

>>> grandma.save()

Delete

>>> herb_mittens.delete_instance() # he had a great life

Getting single records

# 相當於 grandma = Person.select().where(Person.name == 'Grandma L.').get()

grandma = Person.get(Person.name == 'Grandma L.')

# Lists of records

for person in Person.select():
     print person.name, person.is_relative

order_by()

>>> for pet in Pet.select().where(Pet.owner == uncle_bob).order_by(Pet.name):
... print pet.name

>>> for person in Person.select().order_by(Person.birthday.desc()):
... print person.name

where()

>>> d1940 = date(1940, 1, 1)
>>> d1960 = date(1960, 1, 1)
>>> for person in Person.select().where((Person.birthday < d1940) | (Person.birthday > d1960)):
... print person.name

limit(num)

Parameters: num (int) – limit results to num rows

i.e.

.limit(5)

offset(num)

Parameters: num (int) – offset results by num rows

having(*expressions)

sq = (User
    .select(User, fn.Count(Tweet.id).alias('count'))
    .join(Tweet)
    .group_by(User)
    .having(fn.Count(Tweet.id) > 100))

join(model, join_type=None, on=None)

join_type – allows the type of JOIN used to be specified explicitly, one of JOIN_INNER(default), JOIN_LEFT_OUTER, JOIN_FULL

sq = SelectQuery(Tweet).join(User).where( (User.is_editor == True) | (User.is_admin == True))

Counting records

>>> Tweet.select().where(Tweet.id > 50).count()

Logging queries

# All queries are logged to the "peewee" namespace

# Queries are logged using the DEBUG level.

# Print all queries to stderr.
import logging
logger = logging.getLogger('peewee')
logger.setLevel(logging.DEBUG)
logger.addHandler(logging.StreamHandler())

Work with webpy

Code

db = SqliteDatabase('my_webpy_app.db')

def connection_processor(handler):
    db.connect()
    try:
        return handler()
    finally:
        if not db.is_closed():
            db.close()

app.add_processor(connection_processor)

Generate peewee model definitions for an existing database

python -m pwiz --engine=sqlite test.db

playhouse - pskel

print the skeleton code to stdout

# -e <db_driver>, Default: sqlite
# -d db_to_connect, Defalt: :memory:
# Log all queries to stdout.
pskel [-e] [-d][-l] model1 model2 ...

Example:

pskel -d cms User Game

DL:
https://github.com/coleifer/peewee/blob/master/playhouse/pskel

DOC

瀏覽次數： 4130

夢想家