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='123456')
    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 = "qq@qq.com"  # 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/