python peewee

创建连接

1. 直接连接

db = MySQLDatabase(database="peewee", user="root", host="127.0.0.1", port=3306, passwd="123456", charset='utf8')

2. 连接池

db_pool = PooledMySQLDatabase(
    database="peewee",
    user="root",
    host="127.0.0.1",
    port=3306,
    passwd="123456",
    max_connections=30,
    autoconnect=True,  # 启动自动连接，只有复杂条件下，才手动 connect 和 close
    charset='utf8',
)

模型定义

class BaseModel(Model):
    class Meta:
        database = db_pool  # 使用连接池连接


class Student(BaseModel):
    name = CharField()  # 名字为 varchar 类型
    age = IntegerField()  # 年龄为 int 类型
    is_graduate = BooleanField()  # 是否毕业，bool 类型

    class Meta:
        # 表的命名一般都是单数
        db_table = "student"

注意: mysql 数据库中的表，命名都是使用单数

查询

1.单条记录查询

def get_data():
    # 根据 id 快捷查询单个
    name = Student.get_by_id(2).name
    print(f"name is {name}")

    # 根据名字查询
    st = Student.get_or_none(Student.name == "张三")
    if st:
        print(f"用户名:{st.name}")
    else:
        print("未查询该用户")

    # 如果查询不到就返回 null
    st = Student.get_or_none(Student.name == "张三")
    print(st)

2.批量查询

def get_data_in_batch():
    # 条件查询并排序
    st = Student.select().where(Student.age < 30).order_by(Student.age.desc())
    for s in st:
        print(f"根据年龄范围迭代查询到的名字 {s.name},age is {s.age}")

    # 直接用 - 号表示倒排
    st = Student.select().where(Student.age < 30).order_by(-Student.age)
    for s in st:
        print(f"根据年龄范围迭代查询到的名字 {s.name},age is {s.age}")

    st = Student.select().where(Student.age.in_([25, 26]))
    for s in st:
        print(f"in 迭代查询到的名字 {s.name}")

新增

1.单条插入

def insert_data():
    Student.create(name="张三", age=18, is_graduate=True)
    Student.create(name="李四", age=18, is_graduate=True)

2.批量插入

def insert_data_batch():
    students = []
    for i in range(20):
        age = random.randint(1, 82)
        students.append({"name": fake.name(), "age": age, "is_graduate": age > 22})

    # 原子操作
    with db.atomic():
        Student.insert_many(students).execute()

更新

def update_student():
    # 方法1：先查询，再更新
    zs = Student.get(Student.name == "张三")  # 获取单条记录
    zs.age = 26
    zs.save()  # 创建用户不需要 save ，但是更新一定要 save

    # 方法2，直接更新
    Student.update(age=20).where(Student.name == "李四").execute()

删除

def delete_student():
    # 删除单个实例
    st = Student.get(Student.id == 22)
    st.delete_instance()

    # 删除任意行级
    sql = Student.delete().where(Student.name == "李四")
    sql.execute()

连接查询

def get_join():
    q = (
        Student.select(Student.name, Person.addr)
        .join(Person, on=(Person.name == Student.name))
        .where(Student.age < 100)
        .dicts() # join 查询需要添加 dicts 方法
    )

    for c in q:
        print(c)

指定使用左连接

# 指定为左连接
def get_join_2():
    q = Student.select(Student, Person.addr).join(Person, JOIN.LEFT_OUTER, on=(Person.name == Student.name)).where(Student.age < 100).dicts()

    for c in q:
        print(c)

参考文献

https://www.osgeo.cn/peewee/peewee/relationships.html
https://geek-docs.com/python/python-tutorial/python-peewee.html#ftoc-heading-9
https://docs.peewee-orm.com/en/latest/peewee/query_operators.html