Peewee
Python中数据库与ORM主要做这几件事:
- 数据库方面由程序员设计表关系,主要是1v1,1vN,NvN;
- ORM做数据类型映射,将数据库表示的char/int等类型映射成Python对象,将数据映射到Python类对象,将数据表关系映射到类关系中;
- 实现CRUD和事务语句转换,将转化的SQL语句传递给数据库引擎,并将结果返回转为类对象;
- 缓存优化,数据以对象的形式存储在内存中。
创建
from peewee import *
from datetime import date
db = SqliteDatabase('people.db')
class Person(Model):
name = CharField()
birthday = DateField()
class Meta:
database = db
with db:
Person.create_table()
bob = Person(name='bob', birthday=date(1990,1,1))
bob.save()
bob = Person.get(name == 'bob')
print(bob.birthday)
# 其它数据库创建
from peewee import *
# SQLite database using WAL journal mode and 64MB cache.
sqlite_db = SqliteDatabase('/path/to/app.db', pragmas={
'journal_mode': 'wal',
'cache_size': -1024 * 64})
# Connect to a MySQL database on network.
mysql_db = MySQLDatabase('my_app', user='app', password='db_password',
host='10.1.0.8', port=3316)
# Connect to a Postgres database.
pg_db = PostgresqlDatabase('my_app', user='postgres', password='secret',
host='10.1.0.9', port=5432)
get方法只能获取唯一一条,获取多条通过filter()方法
查询
# 官方查询示例,返回的是迭代器
tweets = (Tweet
.select(Tweet, User)
.join(User)
.order_by(Tweet.create_date.desc()))
插入
# 单条数据
res = (Facility
.insert(facid=9, name='Spa', membercost=20, guestcost=30,
initialoutlay=100000, monthlymaintenance=800)
.execute())
# 上面插入单条等效于
res = Facility(facid=9, name='Spa', membercost=20, guestcost=30,
initialoutlay=100000, monthlymaintenance=800)
res.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}]
res = Facility.insert_many(data).execute()
更新数据
# 修改单列
res = (Facility
.update(initialoutlay=10000)
.where(Facility.name == 'Tennis Court 2')
.execute())
# 修改多列
nrows = (Facility
.update(membercost=6, guestcost=30)
.where(Facility.name.startswith('Tennis'))
.execute())
删除数据
nrows = Member.delete().where(Member.memid == 37).execute()
修改表定义
from playhouse.migrate import *
# 通过Migrator实例来修改
# Postgres example:
my_db = PostgresqlDatabase(...)
migrator = PostgresqlMigrator(my_db)
# SQLite example:
my_db = SqliteDatabase('my_database.db')
migrator = SqliteMigrator(my_db)
migrate(
migrator.add_column('some_table', 'title', title_field),
migrator.rename_column('story', 'mod_date', 'modified_date'),
migrator.drop_column('some_table', 'old_column'),
migrator.drop_not_null('story', 'pub_date'),
migrator.rename_table('story', 'stories_tbl'),
migrator.add_index('story', ('pub_date',), False),
migrator.drop_index('story', 'story_pub_date_status'),
migrator.create_index('some_table', ('new_column',)
)
playhouse提供方法用于将对象转换成字典
from playhouse.shortcuts import model_to_dict, dict_to_model
d = model_to_dict(bob)
print(d)