peewee操作MySQL

peewee 的简单使用

为什么要用 ORM

1. 隔离数据库之间的差异
2. 便于维护
3. ORM 会提供防 sql 注入的功能
4. 变量传递式的调用更加简单

为什么要使用 peewee

常见 python orm 有：

• Django ORM
• SQLAlchemy
• peewee

1. peewee 简单、灵活，声明方式和 Django ORM 接近
2. star 数量高，活跃度高
3. 文档质量高，peewee-async 活跃度高

安装 peewee

官网

pip install pymysql
pip install peewee

连接 mysql

module.py

from peewee import MySQLDatabase

database = 'test'
host='127.0.0.1'
user='root'
passwd=''
port=3306
db = MySQLDatabase(database, host, user, passwd, port, charset='utf8mb4')
# 查询数据库是连接
print(db.is_closed())  # 返回false未连接
# 连接数据库
db.connect()

创建表

module.py

from datetime import datetime
from peewee import MySQLDatabase, Model, CharField, DateTimeField, IntegerField， FixedCharField

class BaseModel(Model):
    class Meta:
    	# 将实体与数据库进行绑定
        database = db
        
# 继承自BaseModel，直接关联db，并且也继承了Model。Model有提供增删查改的函数
class CheckLog(BaseModel):
	# IntegerField->int
    uid = IntegerField(verbose_name='用户编号')
    action = CharField(verbose_name='行为', max_length=30)
    # DateTimeField->datetime
    time = DateTimeField(default=datetime.now())

class Users(BaseModel):
    # CharField->varchar   null->非空约束
    name = CharField(verbose_name='姓名', max_length=10, null=False)
    passwd = CharField(verbose_name='密码', max_length=20, null=False)
    phone = FixedCharField(verbose_name='手机号')

if __name__ == '__main__':
	# 创建表
    Users.create_table()
    CheckLog.create_table()

插入数据

sqltest.py

from models.model import Users, CheckLog

def insert_models():
    users = Users()
    users.name = "admin"
    users.passwd = "admin123"
    users.phone = "13996693254"
    users.save() # 一定要 save
    check_log = CheckLog()
    check_log.uid = users.id
    check_log.action = f"创建用户{users.name}"
    check_log.save() # 一定要 save

if __name__ == '__main__':
    insert_models()

查询数据

sqltest.py

def query_models():
# 获取某一条数据
    print(Users.get(Users.id == 1).name)
    # 或
    print(Users.get_by_id(1).name)
    # 或
    print(Users[1].name) # 这里的 1 是 id
# 获取全部数据
	users = Users.select()
    for user in users:
        print(user.name)

if __name__ == '__main__':
    query_models()

更新数据

sqltest.py

def update_models():
    user = Users.get_by_id(1)
    user.name = "test"
    user.save()
    check_log = CheckLog()
    check_log.uid = user.id
    check_log.action = f"修改用户名{user.name}"
    check_log.save()
    # 或
    Users.update(name="test").where(Users.id == 1).execute()
    
if __name__ == '__main__':
	update_models()

删除数据

sqltest.py

def del_models():
    # user = Users.get_by_id(1)
    # user.delete_instance()
    # 或
    Users.delete().where(Users.id == 1).execute()
    
if __name__ == '__main__':
	del_models()