from peewee import *
# test為數(shù)據(jù)庫(kù)名，并需安裝mysql庫(kù)
db = MySQLDatabase("test", **{'charset': 'utf8', 'use_unicode': True, 'host': 'localhost', 'user': 'root', 'password': '***'})
# 基類
class BaseModel(Model):
    class Meta:
        database = db
# 表類
class Test(BaseModel):
    id = IntegerField(primary_key=True)
    name = CharField()
    gender = CharField()
    class Meta:
    	# 表名
        table_name = 'Test'
# 創(chuàng)建表
def create_table(table):
    if not table.table_exists():
        table.create_table()
# 刪除表
def drop_table(table):
    if table.table_exists():
        table.drop_table()
# 創(chuàng)建一張Test表
create_table(Test)

#  數(shù)據(jù)庫(kù)文件路徑
db_path = "../database/test.db"
db = SqliteDatabase(db_path)
# PostgreSQL數(shù)據(jù)庫(kù)則調(diào)用PostgresqlDatabase方法即可
# db = PostgresqlDatabase('test', user='postgres')

t = Tset(name="test", gender="男")
t.save()
# 或者
t = Test()
t.name = "test"
t.gender = "男"
t.save()
# insert() 只插入數(shù)據(jù)，不返回對(duì)象實(shí)例
Test.insert(name="test", gender="男").excute()
# insert_many() 插入多行，rows為元組或字典列表，fields為需要插入的字段名list列表
rows = [
	{"name": "test1", "gender": "男"},
	{"name": "test2", "gender": "女"}
]
insert_many(rows, fields=None).execute()
# 或
rows = [
	{"test1", "男"},
	{"test2", "女"}
]
fields = ["name", "gender"]
insert_many(rows, fields=fields).execute()

# where()是條件，execute()是執(zhí)行
Test.delete().where(Test.name == "test").execute()
# 已經(jīng)實(shí)例化的數(shù)據(jù), 使用delete_instance()
t = Tset(name="test", gender="男").save()
t.delete_instance()

# 查詢單條數(shù)據(jù)，多條件用逗號(hào)或邏輯運(yùn)算符隔開(kāi)，也支持其它比較符（大于小于等）
t = Test.get(Test.name == "test")
# 使用where().get()查詢
t = Test.select().where(Test.name == "test").get()
# 查詢多條數(shù)據(jù)
tests = Test.select().where(Test.gender == "男")
# 迭代結(jié)果
for t in tests:
    t.save()
# in操作
tests = Test.select().where(Test.gender.in_(["男"]))
# 通過(guò)id獲取
t = Test.get_by_id(1)
# 嘗試獲取，不存在則創(chuàng)建，t為實(shí)例，created為是否創(chuàng)建
# 參數(shù)name為自定義查詢條件，dafaults并非查詢條件，而是創(chuàng)建字段
t, created = Test.get_or_create(name="test", defaults={"gender": "男"})
# {"gender": "男"}也可以寫作dict(gender="男")
# 查詢結(jié)果轉(zhuǎn)字典dicts
t = Test.select().dicts()
# 只返回元組迭代器tuples
t = Test.select().tuples()

tests = Test.select().where(Test.name.contains("t"))

# 已經(jīng)實(shí)例化的數(shù)據(jù)，直接修改對(duì)象屬性并save()
t = Tset(name="test", gender="男").save()
t.gender = "女"
t.save()
# 查詢后并用update()更新
t = Tset.update({Tset.gender: "女"}).where(Person.name == "test")
t.execute()
# 查詢出對(duì)象并修改屬性
t = Test.get(Test.name == 'test')
t.gender = "女"
t.save()

# 使用order_by()進(jìn)行排序，多個(gè)字段用分號(hào)隔開(kāi)
tests = Test.select().order_by(Test.id)
# 升序（默認(rèn)）
tests = Test.select().order_by(Test.id.asc())
tests = Test.select().order_by(+Test.id)
# 降序
tests = Test.select().order_by(Test.id.desc())
tests = Test.select().order_by(-Test.id)
# 使用count()進(jìn)行統(tǒng)計(jì)
n = Test.select().count()
# 執(zhí)行原生語(yǔ)句
name = "test"
gender = "男"
Test.raw('SELECT * FROM test WHERE name= %s and gender = %s', name, gender)
# fn操作
# 1.Avg 平均值
# 2.Sum 和
# 3.Max 最大值
# 4.Min 最小值
# 5.Count 合計(jì)
# 6.Substr 切片
t= Test.select(fn.MAX(Test.id))