数据分析师：2.1peewee模块写入数据库

初始化数据库并设计模型

class 模型名称(Model) -> 一个class就是一张表

from peewee import SqliteDatabase
from peewee import Model
from peewee import CharField,DateField,ForeignKeyField

db = SqliteDatabase('people.db')

class Person(Model):
    name = CharField()
    birthday = DateField()

    class Meta:
        database = db
           

设计两个表：Person和Pet

from peewee import SqliteDatabase
from peewee import Model
from peewee import CharField,DateField,ForeignKeyField

db = SqliteDatabase('people.db')

class Person(Model):
    name = CharField()
    birthday = DateField()

    class Meta:
        database = db

class Pet(Model):
    owner = ForeignKeyField(Person,backref='pets')
    name = CharField()
    animal_type = CharField()

    class Meta:
        database = db

           

生成数据库文件

db.connect() -> 连接数据库

db.create_tables([类1, 类2, …]) -> 创建表

from peewee import SqliteDatabase
from peewee import Model
from peewee import CharField,DateField,ForeignKeyField

db = SqliteDatabase('people.db')

class Person(Model):
    name = CharField()
    birthday = DateField()

    class Meta:
        database = db

class Pet(Model):
    owner = ForeignKeyField(Person,backref='pets')
    name = CharField()
    animal_type = CharField()

    class Meta:
        database = db
db.connect()
db.create_tables([Person,Pet])

           

添加数据

实例化一个类，然后.save()

from peewee import SqliteDatabase
from peewee import Model
from peewee import CharField,DateField,ForeignKeyField
from datetime import date

db = SqliteDatabase('people.db')

class Person(Model):
    name = CharField()
    birthday = DateField()

    class Meta:
        database = db

class Pet(Model):
    owner = ForeignKeyField(Person,backref='pets')
    name = CharField()
    animal_type = CharField()

    class Meta:
        database = db
# db.connect()
# db.create_tables([Person,Pet])

uncle_bob = Person(name='Bob', birthday=date(1960, 1, 15))
uncle_bob.save()
           

直接使用类的.create()方法，不用再save()

from peewee import SqliteDatabase
from peewee import Model
from peewee import CharField,DateField,ForeignKeyField
from datetime import date

db = SqliteDatabase('people.db')

class Person(Model):
    name = CharField()
    birthday = DateField()

    class Meta:
        database = db

class Pet(Model):
    owner = ForeignKeyField(Person,backref='pets')
    name = CharField()
    animal_type = CharField()

    class Meta:
        database = db
# db.connect()
# db.create_tables([Person,Pet])

# uncle_bob = Person(name='Bob', birthday=date(1960, 1, 15))
# uncle_bob.save()

sam = Person.create(name='Sam', birthday=date(1970, 2, 25))
           

获取一条数据，如果没有就添加一条数据

. get_or_create()

返回一个实例以及是否有创建新数据

from peewee import SqliteDatabase
from peewee import Model
from peewee import CharField,DateField,ForeignKeyField
from datetime import date

db = SqliteDatabase('people.db')

class Person(Model):
    name = CharField()
    birthday = DateField()

    class Meta:
        database = db

class Pet(Model):
    owner = ForeignKeyField(Person,backref='pets')
    name = CharField()
    animal_type = CharField()

    class Meta:
        database = db
# db.connect()
# db.create_tables([Person,Pet])

# uncle_bob = Person(name='Bob', birthday=date(1960, 1, 15))
# uncle_bob.save()
# sam = Person.create(name='Sam', birthday=date(1970, 2, 25))
user,created = Person.get_or_create(name = 'Sam')
print(user.name, user.birthday, created)
           

输出结果：

user,created = Person.get_or_create(name = 'Kate', birthday=date(1980,2,2))
print(user.name, user.birthday, created)
           

输出结果：

修改数据

获取一个实例并修改，用.save()保存

from peewee import SqliteDatabase
from peewee import Model
from peewee import CharField,DateField,ForeignKeyField
from datetime import date

db = SqliteDatabase('people.db')

class Person(Model):
    name = CharField()
    birthday = DateField()

    class Meta:
        database = db

class Pet(Model):
    owner = ForeignKeyField(Person,backref='pets')
    name = CharField()
    animal_type = CharField()

    class Meta:
        database = db
# db.connect()
# db.create_tables([Person,Pet])

# uncle_bob = Person(name='Bob', birthday=date(1960, 1, 15))
# uncle_bob.save()
# sam = Person.create(name='Sam', birthday=date(1970, 2, 25))
# user,created = Person.get_or_create(name = 'Kate', birthday=date(1980,2,2))
# print(user.name, user.birthday, created)
uncle_bob = Person.get(name='Bob')
uncle_bob.birthday = date(1961, 1, 15)
uncle_bob.save()

           

正确的原位修改方法

query = Person.update(name='Mr.' + Person.name)
query.execute()
           

删除一条数据

Kate = Person.get(Person.name.endswith('Kate'))
Kate.delete_instance()
           

删除多条数据

from peewee import SqliteDatabase
from peewee import Model
from peewee import CharField,DateField,ForeignKeyField
from datetime import date

db = SqliteDatabase('people.db')

class Person(Model):
    name = CharField()
    birthday = DateField()

    class Meta:
        database = db

class Pet(Model):
    owner = ForeignKeyField(Person,backref='pets')
    name = CharField()
    animal_type = CharField()

    class Meta:
        database = db
# db.connect()
# db.create_tables([Person,Pet])

# uncle_bob = Person(name='Bob', birthday=date(1960, 1, 15))
# uncle_bob.save()
# sam = Person.create(name='Sam', birthday=date(1970, 2, 25))
# user,created = Person.get_or_create(name = 'Kate', birthday=date(1980,2,2))
# print(user.name, user.birthday, created)
# uncle_bob = Person.get(name='Bob')
# uncle_bob.birthday = date(1961, 1, 15)
# uncle_bob.save()
# query = Person.update(name='Mr.' + Person.name)
# query.execute()
# Kate = Person.get(Person.name.endswith('Kate'))
# Kate.delete_instance()
query = Person.delete().where(Person.name.endswith('Bob'))
query.execute()