终于来到sora开发准备的最后部分
peewee 管理数据库
1.明确地管理数据库连接（也可以考虑使用连接池）
database = SqliteDatabase('my_app.db')
def before_request_handler():
database.connect()
def after_request_handler():
database.close()
务必明确地处理数据库连接
2.在表类中设置内部类，以使用数据库
class MyModel(Model):
some_field = CharField()
class Meta:
database = database
最佳实践：定义一个基类指定要连接的数据库，其他的子类继承该类定义表格
database = SqliteDatabase('my_app.db')
class BaseModel(Model):
class Meta:
database = database
class User(BaseModel):
username = CharField()
class Tweet(BaseModel):
user = ForeignKeyField(User, related_name='tweets')
message = TextField()
# etc, etc
注意：在建表时不用建BaseModel表，只需处理User和Tweet
3.连接数据库（不同的数据库用不同的类）
mysql：
mysql_db = MySQLDatabase('my_database')
或者：
db = MySQLDatabase('my_db',host='localhost',port=3306,user='root',password='root')
class BaseModel(Model):
"""A base model that will use our MySQL database"""
class Meta:
database = mysql_db
class User(BaseModel):
username = CharField()
# etc, etc
如果使用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
url范例：
mysql://user:passwd@ip:3306/my_db
4.运行时修改数据库配置（根据运行参数修改要连接的数据库）
database = SqliteDatabase(None) # Un-initialized database.
class SomeModel(Model):
class Meta:
database = database
>>> database.connect()
Exception: Error, database not properly initialized before opening connection
database_name = raw_input('What is the name of the db? ')
database.init(database_name, host='localhost', user='postgres')
使用init修改配置
5.动态修改数据库（使用proxy）
动态地换用不同的数据库（sqlite，mysql，postgreSQL）
database_proxy = Proxy() # Create a proxy for our db.
class BaseModel(Model):
class Meta:
database = database_proxy # Use proxy for our DB.
class User(BaseModel):
username = CharField()
# Based on configuration, use a different database.
if app.config['DEBUG']:
database = SqliteDatabase('local.db')
elif app.config['TESTING']:
database = SqliteDatabase(':memory:')
else:
database = PostgresqlDatabase('mega_production_db')
# Configure our proxy to use the db we specified in config.
database_proxy.initialize(database)
如果只是修改运行时的参数而非改变数据库类型，建议使用第四点的方法
6.连接池
连接池支持：
1、超时之后，连接将被回收
2、可以设置连接数的上限
from playhouse.pool import PooledPostgresqlExtDatabase
db = PooledPostgresqlExtDatabase(
'my_database',
max_connections=8,
stale_timeout=300,
user='postgres')
class BaseModel(Model):
class Meta:
database = db
Mysql相关的模块是：PooledMySQLDatabase
7.访问数据副本
使用ReadSlaveModel
from peewee import *
from playhouse.read_slave import ReadSlaveModel
# Declare a master and two read-replicas.
master = PostgresqlDatabase('master')
replica_1 = PostgresqlDatabase('replica', host='192.168.1.2')
replica_2 = PostgresqlDatabase('replica', host='192.168.1.3')
class BaseModel(ReadSlaveModel):
class Meta:
database = master
read_slaves = (replica_1, replica_2)
class User(BaseModel):
username = CharField()
注意：查询会在多个从数据库上以循环的方式执行
8.修改表的schema
通过playhouse的migrator
from playhouse.migrate import *
my_db = SqliteDatabase('my_database.db')
migrator = SqliteMigrator(my_db)
title_field = CharField(default='')
status_field = IntegerField(null=True)
with my_db.transaction():
migrate(
migrator.add_column('some_table', 'title', title_field),
migrator.add_column('some_table', 'status', status_field),
migrator.drop_column('some_table', 'old_column'),
)
更多内容要参考Schema Migrations部分的文档
9.从现有数据库生成相应的模块
使用pwiz实现
python -m pwiz -e mysql -H localhost -u root -P root user > md.py
生成的md.py如下
from peewee import *
database = MySQLDatabase('user', **{'host': 'localhost', 'password': 'root', 'user': 'root'})
class UnknownField(object):
pass
class BaseModel(Model):
class Meta:
database = database
class Infos(BaseModel):
name = CharField(null=True)
sex = CharField(null=True)
users = CharField(db_column='users_id', null=True)
class Meta:
db_table = 'infos'
class Usersinfo(BaseModel):
description = TextField(null=True)
user = IntegerField(db_column='user_id', null=True)
user_name = CharField(null=True)
user_password = CharField(null=True)
class Meta:
db_table = 'usersinfo'
该数据库的其中一个表：
mysql> desc infos;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| sex | varchar(20) | YES | | NULL | |
| name | char(20) | YES | | NULL | |
| users_id | varchar(40) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
10.model与fields
对应关系：
在peewee中，一个继承model类的类定义为某个数据库的一个表格
类中的feild，为数据库的列（字段）
一个model类实例则是一行
用例：
from peewee import *
db = SqliteDatabase('my_app.db')
class BaseModel(Model):
class Meta:
database = db
class User(BaseModel):
username = CharField(unique=True)
class Tweet(BaseModel):
user = ForeignKeyField(User, related_name='tweets')
message = TextField()
created_date = DateTimeField(default=datetime.datetime.now)
is_published = BooleanField(default=True)
不同的field与数据类型对照：
Field Type Sqlite Postgresql MySQL
CharField varchar varchar varchar
TextField text text longtext
DateTimeField datetime timestamp datetime
IntegerField integer integer integer
BooleanField smallint boolean bool
FloatField real real real
DoubleField real double precision double precision
BigIntegerField integer bigint bigint
DecimalField decimal numeric numeric
PrimaryKeyField integer serial integer
ForeignKeyField integer integer integer
DateField date date date
TimeField time time time
BlobField blob bytea blob
UUIDField not supported uuid not supported
field的初始化参数：
null = False – boolean indicating whether null values are allowed to be stored
index = False – boolean indicating whether to create an index on this column
unique = False – boolean indicating whether to create a unique index on this column. See also adding composite indexes.
verbose_name = None – string representing the “user-friendly” name of this field
help_text = None – string representing any helpful text for this field
db_column = None – string representing the underlying column to use if different, useful for legacy databases
default = None – any value to use as a default for uninitialized models
choices = None – an optional iterable containing 2-tuples of value, display
primary_key = False – whether this field is the primary key for the table
sequence = None – sequence to populate field (if backend supports it)
constraints = None - a list of one or more constraints, e.g. [Check('price > 0')]
schema = None – optional name of the schema to use, if your db supports this.
有关DateTimeField, DateField and TimeField：
DateField有如下特性：
year
month
day
TimeField：
hour
minute
second
而DateTimeField有以上全部特性
简单用例：
# Get the current time.
now = datetime.datetime.now()
# Get days that have events for the current month.
Event.select(Event.event_date.day.alias('day')).where(
(Event.event_date.year == now.year) &
(Event.event_date.month == now.month))
11.创建model tables
首次使用某类时，需要执行创建表格的操作：
# Connect to our database.
db.connect() #建议明确地控制连接
# Create the tables.
db.create_tables([User, Tweet])
可以有条件地创建表格：
# Only create the tables if they do not exist.
db.create_tables([User, Tweet], safe=True)
12.Model的options和Metadata
如果想访问某个表格类的meta，应该这样做：
>>> Person.Meta
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
AttributeError: type object 'Preson' has no attribute 'Meta'
>>> Person._meta
<peewee.ModelOptions object at 0x7f51a2f03790>
查看ModelOptions：
>>> Person._meta.fields
{'id': <peewee.PrimaryKeyField object at 0x7f51a2e92750>, 'name': <peewee.CharField object at 0x7f51a2f0a510>}
>>> Person._meta.primary_key
<peewee.PrimaryKeyField object at 0x7f51a2e92750>
>>> Person._meta.database
<peewee.SqliteDatabase object at 0x7f519bff6dd0>
ModelOptions有如下成分：
Option Meaning Inheritable?
database database for model yes
db_table name of the table to store data no
indexes a list of fields to index yes
order_by a list of fields to use for default ordering yes
primary_key a CompositeKey instance yes
table_alias an alias to use for the table in queries no
schema the database schema for the model yes
validate_backrefs ensure backrefs do not conflict with other attributes. yes
简单用例：
>>> db = SqliteDatabase(':memory:')
>>> class ModelOne(Model):
... class Meta:
... database = db
... db_table = 'model_one_tbl'
...
>>> class ModelTwo(ModelOne):
... pass
...
>>> ModelOne._meta.database is ModelTwo._meta.database
True
>>> ModelOne._meta.db_table == ModelTwo._meta.db_table
False
12.索引和唯一约束
简单用例：
class User(Model):
username = CharField(unique=True)
email = CharField(index=True)
或者通过Meta来设置（第二个布尔型参数决定了是否创建唯一约束）：
class Transaction(Model):
from_acct = CharField()
to_acct = CharField()
amount = DecimalField()
date = DateTimeField()
class Meta:
indexes = (
# create a unique on from/to/date
(('from_acct', 'to_acct', 'date'), True),
# create a non-unique on from/to
(('from_acct', 'to_acct'), False),
)
个人测试：
root@workgroup0:~# python
Python 2.7.6 (default, Mar 22 2014, 22:59:56)
[GCC 4.8.2] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> from peewee import *
>>> db = MySQLDatabase('vmrecord',host='localhost',port=3306,user='root',password='root')
>>> class Performance(Model):
... vm_id = IntegerField(unique=True)
... desc = CharField(index=True)
... class Meta:
... database = db
...
>>> db.connect()
>>> db.create_tables([Performance,])
>>> for i in Performance.select():
... print i.vm_id
...
123
124
>>> try:
... a.save
... except IntegrityError:
... print 'error'
...
<bound method Performance.save of <__main__.Performance object at 0x7f047311a750>>
>>> try:
... a.save()
... except IntegrityError as e:
... print e
...
(1062, "Duplicate entry '123' for key 'performance_vm_id'")
>>>
可以看到unique constraints已经起了作用
>>> b = Performance(vm_id='125',desc='use 10%')
>>> try:
... b.save()
... except IntegrityError as error:
... print error
... else:
... print 'success'
...
1
success
mysql> select * from performance;
+----+-------+---------+
| id | vm_id | desc |
+----+-------+---------+
| 1 | 123 | use 40% |
| 2 | 124 | use 40% |
| 5 | 125 | use 10% |
+----+-------+---------+
3 rows in set (0.01 sec)
13.复合主键
设置meta的primary key有多个，简单用例：
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')
14.手动指定主键
你可以设置Meta中（用class._meta访问）的auto_increment为false（一次性），用例
data = load_user_csv() # load up a bunch of data
User._meta.auto_increment = False # turn off auto incrementing IDs
with db.transaction():
for row in data:
u = User(id=row[0], username=row[1])
u.save(force_insert=True) # <-- force peewee to insert row
User._meta.auto_increment = True
如果你想彻底控制主键，可以指定id这个field（可以使用integerFiled取代PrimaryKeyField）：
class User(BaseModel):
id = IntegerField(primary_key=True)
username = CharField()
>>> u = User.create(id=999, username='somebody')
>>> u.id
999
>>> User.get(User.username == 'somebody').id
999
#此例中id字段不是自增的
注：如果不用PrimaryKeyField，创建表格时也会自动生成一个自增的id字段
个人测试：
>>> db.connect()
>>> db.create_tables([network,])
>>> class test1(Model):
... username = CharField()
... class Meta:
... database = db
...
>>> class test2(Model):
... id = IntegerField(primary_key=True)
... username = CharField()
... class Meta:
... database = db
...
>>> db.create_tables([test1,test2])
>>>
mysql> desc test1;
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(255) | NO | | NULL | |
+----------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> desc test2;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| username | varchar(255) | NO | | NULL | |
+----------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
15.Circular foreign key dependencies（我猜是两个表之间建立联系）
例子：
class User(Model):
username = CharField()
favorite_tweet = ForeignKeyField(Tweet, null=True) # NameError!!
class Tweet(Model):
message = TextField()
user = ForeignKeyField(User, related_name='tweets')
因为在定义User时，Tweet还没定义，会导致NameError
可以用这个简单地处理id：
class User(Model):
username = CharField()
favorite_tweet_id = IntegerField(null=True)
或者通过proxy绕过这个问题：
# Create a proxy object to stand in for our as-yet-undefined Tweet model.
TweetProxy = Proxy()
class User(Model):
username = CharField()
# Tweet has not been defined yet so use the proxy.
favorite_tweet = ForeignKeyField(TweetProxy, null=True)
class Tweet(Model):
message = TextField()
user = ForeignKeyField(User, related_name='tweets')
# Now that Tweet is defined, we can initialize the proxy object.
TweetProxy.initialize(Tweet)
但是建表时还要做出一些处理：
# Foreign key constraint from User -> Tweet will NOT be created because the
# Tweet table does not exist yet. `favorite_tweet` will just be a regular
# integer field:
User.create_table()
# Foreign key constraint from Tweet -> User will be created normally.
Tweet.create_table()
# Now that both tables exist, we can create the foreign key from User -> Tweet:
db.create_foreign_key(User, User.favorite_tweet)
