peewee基本使用

PEEWEE基本使用

Content 
Ⅰ  安装
Ⅱ  链接数据库
Ⅲ  建表
Ⅳ  增删改
Ⅴ  基础查询
Ⅵ  ForeignKey
Ⅷ  事务

参考官方文档：http://docs.peewee-orm.com/en/latest/index.html

1. 安装

pip install peewee

2. 链接数据库

以mysql 为例（Peewee提供mysql,postgresql,sqllite）的支持

import peewee
settings = {'host': 'localhost', 'password': '', 'port': 3306, 'user': 'root'}
db = peewee.MySQLDatabase("test",**settings)

3. 建表

3.1 表关系

class Person(Model):
    name = CharField(verbose_name='姓名', max_length=10, null=False, index=True)
    passwd = CharField(verbose_name='密码', max_length=20, null=False, default='')
    email = CharField(verbose_name='邮件', max_length=50, null=True, unique=True)
    gender = IntegerField(verbose_name='姓别', null=False, default=1)
    birthday = DateField(verbose_name='生日', null=True, default=None)
    is_admin = BooleanField(verbose_name='是否是管理员', default=True)
    class Meta:
        database = db  # 这里是数据库链接，为了方便建立多个表，可以把这个部分提炼出来形成一个新的类
　　　　 table_name = 'persons'  # 这里可以自定义表名

# 例如：
　　　　class BaseModel(Model):
    　　   class Meta:
        　　  database = db
       class Person(BaseModel):
    　　    name = CharField()

全部数据类型

Field Type	Sqlite	Postgresql	MySQL
IntegerField	integer	integer	integer
BigIntegerField	integer	bigint	bigint
SmallIntegerField	integer	smallint	smallint
AutoField	integer	serial	integer
FloatField	real	real	real
DoubleField	real	double precision	double precision
DecimalField	decimal	numeric	numeric
CharField	varchar	varchar	varchar
FixedCharField	char	char	char
TextField	text	text	longtext
BlobField	blob	bytea	blob
BitField	integer	bigint	bigint
BigBitField	blob	bytea	blob
UUIDField	text	uuid	varchar(40)
DateTimeField	datetime	timestamp	datetime
DateField	date	date	date
TimeField	time	time	time
TimestampField	integer	integer	integer
IPField	integer	bigint	bigint
BooleanField	integer	boolean	bool
BareField	untyped	not supported	not supported
ForeignKeyField	integer	integer	integer

null = False – 可否为空
index = False – index索引
unique = False – unique索引
column_name = None – string representing the underlying column to use if different, useful for legacy databases
default = None – 默认值，如果callable, 会调用生成！
primary_key = False – 主键
constraints = None - a list of one or more constraints, e.g. [Check('price > 0')]
sequence = None – sequence to populate field (if backend supports it)
collation = None – collation to use for ordering the field / index
unindexed = False – indicate field on virtual table should be unindexed (SQLite-only)
choices = None – an optional iterable containing 2-tuples of value, display
help_text = None – string representing any helpful text for this field
verbose_name = None – string representing the “user-friendly” name of this field

所有属性

3.2 链接数据库

db.is_closed()  # 判断数据库是不是链接
db.connect()   # 数据库链接

3.3 建库

db.create_tables([Person,]) #建库

#   如果数据表已经存在，执行create_table的时候，将会抛出异常。

3.4 主键和约束

class Person(Model):
    first = CharField()
    last = CharField()

    class Meta:
        primary_key = CompositeKey('first', 'last')

class Pet(Model):
    owner_first = CharField()
    owner_last = CharField()
    pet_name = CharField()

    class Meta:
        constraints = [SQL('FOREIGN KEY(owner_first, owner_last) '
                           'REFERENCES person(first, last)')]

主键和约束

class BlogToTag(Model):
    """A simple "through" table for many-to-many relationship."""
    blog = ForeignKeyField(Blog)
    tag = ForeignKeyField(Tag)

    class Meta:
        primary_key = CompositeKey('blog', 'tag')

复合主键

User._meta.auto_increment = False # turn off auto incrementing IDs

关自增

4. 插&改&删

4.1 插

p = Person.create(name='master')
####### 或 #########
p = Person(name="master")
p.save()

data = [
    {'facid': 9, 'name': 'Spa', 'membercost': 20, 'guestcost': 30,
     'initialoutlay': 100000, 'monthlymaintenance': 800},
    {'facid': 10, 'name': 'Squash Court 2', 'membercost': 3.5,
     'guestcost': 17.5, 'initialoutlay': 5000, 'monthlymaintenance': 80}]
query = Facility.insert_many(data) # 插入了多个

with db.atomic():  # 一次链接
    for data_dict in data_source:
        MyModel.create(**data_dict)

User.insert(username='Mickey').execute()  # >>> 返回主键

# insert_from 是指从一个表查数据快速差到另一个表
query = (TweetArchive
         .insert_from(
             Tweet.select(Tweet.user, Tweet.message),
             fields=[Tweet.user, Tweet.message])
         .execute())

insert_from

4.2 改

p.email = "[e-mail]"  # p 为记录对象
p.save()  # 会调用update  但是不要这么写！

query = (Facility
         .update(membercost=6, guestcost=30)
         .where(Facility.name.startswith('Tennis'))) 

query.execute()  # 元子操作

4.3 注意

插入的时候，如果字段设置了default值，则会按照default指定的值插入，如果没有指定，同时字段可以为null，则数据库自动初始化值为null，如果字段不能为null，则数据库自动初始化为其零值。 如果字段为非Null，最好设置default值，同时数据库schema也设置其default值，如果字段为可以为null，那么初始值就设置为null即可。

4.4 数据删除

p.delete_instance()

query = Member.delete().where(Member.memid == 37)

# DELETE FROM members WHERE NOT EXISTS (SELECT * FROM bookings WHERE bookings.memid = members.memid);

subq = Booking.select().where(Booking.member == Member.memid)
query = Member.delete().where(~fn.EXISTS(subq)) # 同样这样的操作是被推荐的

5. 基础查询

　　基本和sql语句相同

User.get(User.id == 1)

User.get_by_id(1)  # Same as above.

User[1]  # Also same as above.

g = Person.select().where(Person.name == 'Grandma L.').get()   # where是查询一个集合, select是查询字段

g = Person.get(Person.name == 'fff.')   # get是得到第一个

g = Person.select().where(Person.age > 23).get()

# select 代表sql语句中select后面的语句表示要展示的字段 # where 代表where条件语句 得到一个数据集合，用for循环遍历 # get()代表找第一个

5.1 复合条件

query1 = Person.select().where((Person.name == "fff0") | (Person.name == "sss1"))

query2 = Person.select().where((Person.name == "fff") & (Person.is_relative == True))

==    x equals y
<    x is less than y
<=    x is less than or equal to y
>    x is greater than y
>=    x is greater than or equal to y
!=    x is not equal to y
<<    x IN y, where y is a list or query
>>    x IS y, where y is None/NULL
%    x LIKE y where y may contain wildcards
**    x ILIKE y where y may contain wildcards
^    x XOR y
~    Unary negation (e.g., NOT x)

操作符

5.2 模糊查询

#SELECT * FROM person WHERE name ILIKE '%tennis%';

Person.select().where(Person.name ** "%fff%").get()
######### OR ##############
query = Facility.select().where(Facility.name.contains('tennis'))

5.3 In 查询

# SELECT * FROM facilities WHERE facid IN (1, 5);
query = Facility.select().where(Facility.facid.in_([1, 5]))

person, created = Person.get_or_create(
    first_name=first_name,
    last_name=last_name,
    defaults={'dob': dob, 'favorite_color': 'green'})

# defaults dictionary, which will be used to populate values on newly-created instances.

get_or_create

query = User.select().dicts()
for row in query:
    print(row)
# row 是什么字典

字典展示(还可以tuples(),namedtuples(),objects())

5.4 Order by; Limit; Distinct; Group by; Having

query = (Person.select(Person.name).order_by(Person.name).limit(10).distinct())  # 几乎和sql一模一样
Person.select().order_by(Person.birthday.desc())  # 日期排序

query = (Booking
         .select(Booking.facid, fn.SUM(Booking.slots))
         .group_by(Booking.facid)   # group_by
         .order_by(Booking.facid))

query = (Booking
         .select(Booking.facility, fn.SUM(Booking.slots))
         .group_by(Booking.facility)
         .having(fn.SUM(Booking.slots) > 1000)   # having
         .order_by(Booking.facility))

Tweet.select().order_by(-Tweet.created_date) 
# Similarly you can use "+" to indicate ascending order, though ascending

5.5 聚合函数

# SELECT MAX(birthday) FROM person;
query = Person.select(fn.MAX(Person.birthday))

# SELECT name, is_relative FROM person WHERE birthday = (SELECT MAX(birthday) FROM person);

MemberAlias = Member.alias()  # 如果一个查询中用了两个表，需要这个Alias作为影子
subq = MemberAlias.select(fn.MAX(MemberAlias.joindate))
query = (Member.select(Person.is_relative, Person.name, ).where(Person.birthday == subq))

5.6 分页&计数

for tweet in Tweet.select().order_by(Tweet.id).paginate(2, 10):
    print(tweet.message)

# paginate两个参数：page_number 和 items_per_page

分页

# 返回查到了多少条记录
Tweet.select().where(Tweet.id > 50).count()

count

5.7 SQL

query = MyModel.raw('SELECT * FROM my_table WHERE data = %s', user_data)
query = MyModel.select().where(SQL('Some SQL expression %s' % user_data))
# sql 注入: ensure that any user-defined data is passed in as a query parameter and not part of the actual SQL query

原生sql

6. ForeignKey

6.1 表关系

class Pet(peewee.Model):
    name = peewee.CharField()
    owner = peewee.ForeignKeyField(Person,related_name="pets",backref="petties")   
　　# backref是反查的字段，如果有related_name用related_name反查，如果没有直接用petties反查 e.g. [i.name for i in Person.get(name="aaa").petties] 

class Meta: database = db

class Category(Model):
    name = CharField()
    parent = ForeignKeyField('self', null=True, backref='children')
   # 注意自关联永远是null = True

自关联

6.2 插

g2 = Person.get(tablesinfo.Person.is_relative == False)
d2 = Pet.create(name="dog2",owner=g2)

6.3 正反查

#正查
dog1 = Pet.get(name="dog1")
dog1.owner.name

# 反查
aaa = Person.get(name="aaa").pets  # pets为related_name字段，如果没写用backref字段
for a in aaa:
    print(i.name)

6.4 连表查询

g1 = Person.select().join(Pet).where(Pet.name == "dog2")

6.5 复杂连表

# SELECT DISTINCT m.firstname, m.surname FROM members AS m2
INNER JOIN members AS m ON (m.memid = m2.recommendedby)
ORDER BY m.surname, m.firstname;

MA = Member.alias()
query = (Member
         .select(Member.firstname, Member.surname)
         .join(MA, on=(MA.recommendedby == Member.memid))  # join中用on表示链接方法
         .order_by(Member.surname, Member.firstname))

6.6 连多表

User.select().join(Tweet).join(Comment)

Artist.select().join(Album).switch(Artist).join(Genre) # 如果连一表多次

连多表

6.7 多对多

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

class Student(BaseModel):
    name = CharField()

class Course(BaseModel):
    name = CharField()
    students = ManyToManyField(Student, backref='courses')

StudentCourse = Course.students.get_through_model()  # 中间表

huey = Student.get(Student.name == 'Huey')

# We can add all the objects represented by a query.
english_courses = Course.select().where(
    Course.name.contains('english'))
huey.courses.add(english_courses)

add

huey = Student.get(Student.name == 'Huey')
for course in huey.courses.order_by(Course.name):
    print(course.name)

查

huey.courses.remove(Course.select().where(Course.name.startswith('CS')))
engl_101.students.remove(huey)

remove

7 事务

with db.atomic() as transaction:  # Opens new transaction.
    try:
        save_some_objects()
    except ErrorSavingData:
        # Because this block of code is wrapped with "atomic", a
        # new transaction will begin automatically after the call
        # to rollback().
        transaction.rollback()
        error_saving = True

atomic和rollback

db = SqliteDatabase(':memory:')

with db.atomic() as txn:
    # This is the outer-most level, so this block corresponds to
    # a transaction.
    User.create(username='charlie')

    with db.atomic() as nested_txn:
        # This block corresponds to a savepoint.
        User.create(username='huey')

        # This will roll back the above create() query.
        nested_txn.rollback()

    User.create(username='mickey')

# When the block ends, the transaction is committed (assuming no error
# occurs). At that point there will be two users, "charlie" and "mickey".

atomic甚至可以多层嵌套

@db.atomic()
def create_user(username):
    # This statement will run in a transaction. If the caller is already
    # running in an `atomic` block, then a savepoint will be used instead.
    return User.create(username=username)

create_user('charlie')

装饰器模式

# 纯手动用commit()提交，用rollback回滚
with db.transaction() as txn:
    User.create(username='mickey')
    txn.commit()  # Changes are saved and a new transaction begins.
    User.create(username='huey')

    # Roll back. "huey" will not be saved, but since "mickey" was already
    # committed, that row will remain in the database.
    txn.rollback()

# 半自动用savepoint上下文管理
with db.transaction() as txn:
    with db.savepoint() as sp:
        User.create(username='mickey')

    with db.savepoint() as sp2:
        User.create(username='zaizee')
        sp2.rollback()  # "zaizee" will not be saved, but "mickey" will be.

手动、半自动

8 其他

import uuid

class UUIDField(Field):
    field_type = 'uuid'

    def db_value(self, value):
        return str(value) # convert UUID to str

    def python_value(self, value):
        return uuid.UUID(value) # convert str to UUID

自定义类型！

Person._meta.fields   # 用访问_meta访问定义的meta
Person._meta.primary_key
Person._meta.database

# 属性有：database， table_name， table_function， indexes， primary_key， constraints， schema， only_save_dirty， options， table_alias， depends_on， without_rowid

数据表信息的访问

class Article(Model):
    name = TextField()
    timestamp = TimestampField()
    status = IntegerField()
    flags = IntegerField()

# Add an index on "name" and "timestamp" columns.
Article.add_index(Article.name, Article.timestamp)

# Add a partial index on name and timestamp where status = 1.
Article.add_index(Article.name, Article.timestamp,
                  where=(Article.status == 1))

# Create a unique index on timestamp desc, status & 4.
idx = Article.index(
    Article.timestamp.desc(),
    Article.flags.bin_and(4),
    unique=True)
Article.add_index(idx)

索引增加

事务autocommit模式(不推荐)

Employee.select(fn.Min(Employee.salary), fn.Max(Employee.salary) ).scalar(as_tuple=True)
# You can retrieve multiple scalar values by passing as_tuple=True

# scalar 表示结果的第一行第一列

scalar

注：原生sql，推荐 https://pypi.python.org/pypi/records/