Peewee使用笔记

1、python文件进入交互模式的方法

import IPython
IPython.embed()

---

2、peewee模块的安装参考官网

---

3、peewee模型简单的结构：

Model class --> Database table
Field class --> Column table
Model instance --> Row in a database table 

from peewee import *
db = SqliteDatabase('people.db')
class Person(Model):
    name = CharField()
    birthday = DateField() is_relative = BooleanField()
    class Meta:
        database = db

class Pet(Model):
    owner = ForeignKeyField(Person, related_name='pets') name = CharField()
    animal_type = CharField()
    class Meta:
        database = db

---

4、以sqlite3为例，先创建对应模型的数据表

>>> db.connect()
>>> db.create_tables([Person, Pet])

---

5、新添模型数据

方法一：
>>> from datetime import date
>>> uncle_bob = Person(name='Bob', birthday=date(1960, 1, 15), is_relative=True)
>>> uncle_bob.save()
方法二：
>>> p1 = Person.create(name='p1', birthday=date(1999, 10, 10), is_relative=True)

---

6、删除数据

>>> p1.delete_instance()

---

7.1、得到单一数据 get()

方法一：
>>> uncle_bob = Person.select().where(Person.name == 'Bob').get()
方法二：
>>> uncle_bob = Person.get(Person.name == 'Bob')

7.2、得到数据列表 select()

>>> query = Pet.select()
>>> query = Pet.select().where(Pet.animal_type == 'cat')

7.3、使用 join()

query = (Pet.select(Pet, Person).join(Person).where(Person.name == 'Bob'))

7.4、数据列表排序 order_by()

>>> query = Pet.select().where(Pet.owner == uncle_bob).order_by(Pet.name)

7.5、模型间数据关联

获取到属于person的pet的数目
>>> for person in Person.select():
...     print(person.name, person.pets.count())

>>> subquery = Pet.select(fn.COUNT(Pet.id)).where(Pet.owner == Person.id)
>>> query = (Person.select(Person, Pet, subquery.alias('pet_count')).join(Pet, JOIN.LEFT_OUTER).order_by(Person.name))

7.6、获取条件范围的数据

>>> d1940 = date(1940, 1, 1) 
>>> d1960 = date(1960, 1, 1) 
>>> query = (Person.select().where((Person.birthday < d1940) | (Person.birthday > d1960)))
注：或条件使用 | ，且条件使用 &

7.7、获取Person的name首字母为g或G的数据

>>> expression = (fn.Lower(fn.Substr(Person.name, 1, 1)) == 'g')
>>> query = Person.select().where(expression)

7.8、关闭数据库

>>> db.close()

---

以下为简单的应用

---

8、模型数据结构和关系

database = SqliteDatabase(DATABASE)

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

class User(BaseModel):
    username = CharField(unique=True) password = CharField()
    email = CharField()
    join_date = DateTimeField()

    class Meta:
        order_by = ('username',)

class Relationship(BaseModel):
    from_user = ForeignKeyField(User, related_name='relationships') 
    to_user = ForeignKeyField(User, related_name='       ')

    class Meta: 
        indexes = (
            # Specify a unique multi-column index on from/to-user.
            (('from_user', 'to_user'), True),
        )

class Message(BaseModel):
    user = ForeignKeyField(User)
    content = TextField()
    pub_date = DateTimeField()

    class Meta:
        order_by = ('-pub_date',)

peewee中可使用的数据类型：
String、Integer、float、Decimal、Boolean、Date、time、datetime、None、Binary

---

9、创建表

def create_tables():
    database.connect()
    database.create_tables([User, Relationship, Message])
注：peewee内置有create_tables()方法，但是考虑到模型字段的变更，基本上不用，而是使用sqitch进行数据库版本变更管理。

---

10、flask框架中一般在请求前开启，响应后关闭数据库

@app.before_request
def before_request(): 
    database.connect()

@app.after_request
def after_request(response): 
    database.close()
    return response

---

11、查询

不是重点：
def following(self):
    # query other users through the "relationship" table 
    return (User
            .select()
            .join(Relationship, on=Relationship.to_user)
            .where(Relationship.from_user == self))
def followers(self): 
    return (User
            .select()
            .join(Relationship, on=Relationship.from_user)
            .where(Relationship.to_user == self))

---

12、创建一个新对象

try:
    with database.transaction():
        # Attempt to create the user. If the username is taken, due to the
        # unique constraint, the database will raise an IntegrityError.
        user = User.create(username=request.form['username'],
            password=md5(request.form['password']).hexdigest(),
            email=request.form['email'],
            join_date=datetime.datetime.now()
)
    # mark the user as being 'authenticated' by setting the session vars
    auth_user(user)
    return redirect(url_for('homepage'))
except IntegrityError:
    flash('That username is already taken')

---

13、<<为包含在的意思

messages = Message.select().where(Message.user << user.following())