Try to operate the database using Python's ORM Peewee (August 2019 version)

Overview

peewee is an ORM for Python. You can operate the following databases. ・ SQLite ・ MySQL ・ Postgres ・ AWSP ・ Berkeley Database (unverified)

Please refer to the following document for details. https://peewee.readthedocs.org/en/latest/index.html

Change log

2019.08.03 Changed sample code Python2.7 → Python3.7 & Peewee 3.9.6

environment

Windows10 Python 3.7.4(32bit) Peewee 3.9.6

Installation method

pip install peewee

Simple example

Here is a simple sample with SQLite.

from peewee import *
from datetime import date

db = SqliteDatabase(':memory:')

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

    class Meta:
        database = db # This model uses the "people.db" database.


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

    class Meta:
        database = db # this model uses the people database



try:
    db.create_tables([Person, Pet])
    with db.transaction():
        #INSERT by creating an object and saving
        uncle_bob = Person(name='Bob', birthday=date(1960, 1, 15), is_relative=True)
        uncle_bob.save() # bob is now stored in the database

        #INSERT with create
        grandma = Person.create(name='Grandma', birthday=date(1935, 3, 1), is_relative=True)
        herb = Person.create(name='Herb', birthday=date(1950, 5, 5), is_relative=False)

        bob_kitty = Pet.create(owner=uncle_bob, name='Kitty', animal_type='cat')
        herb_fido = Pet.create(owner=herb, name='Fido', animal_type='dog')
        herb_mittens = Pet.create(owner=herb, name='Mittens', animal_type='cat')
        herb_mittens_jr = Pet.create(owner=herb, name='Mittens Jr', animal_type='cat')

        print ("Get all-----------------")
        for person in Person.select():
            print(person.name, person.is_relative)

        print("Get only cat-----------------")
        query = Pet.select().where(Pet.animal_type == 'cat')
        for pet in query:
            print(pet.name, pet.owner.name)

        print("Join example-----------------")
        query = (Pet
                 .select(Pet, Person)
                 .join(Person)
                 .where(Pet.animal_type == 'cat'))
        for pet in query:
            print(pet.name, pet.owner.name)

        print("Update example-----------------")
        update_pet = Pet.get(Pet.name=='Kitty')
        update_pet.name = 'Kitty(updated)'
        update_pet.save() 

        query = (Pet
                 .select(Pet, Person)
                 .join(Person)
                 .where(Pet.animal_type == 'cat'))
        for pet in query:
            print(pet.name, pet.owner.name)


        print("Deletion example-----------------")
        del_pet = Pet.get(Pet.name=='Mittens Jr')
        del_pet.delete_instance() 

        query = (Pet
                 .select(Pet, Person)
                 .join(Person)
                 .where(Pet.animal_type == 'cat'))
        for pet in query:
            print(pet.name, pet.owner.name)

        db.commit()


except IntegrityError as ex:
    print (ex)
    db.rollback()

If you use peewee as in this example, you can operate the database without writing SQL statements. For other grouping and queries using max and min functions, refer to the following.

https://peewee.readthedocs.org/en/latest/peewee/querying.html

When using a function such as SUBSTR

If you want to use a function such as SUBSTR, use SQL ().

        print ('SUBSTR example')
        for r in Pet.select(SQL('SUBSTR(name,1,1)').alias('a1')):
            print (r.a1)

In this example, the first character of the name column is acquired by the SUBSTR function, and the column name a1 is given to it.

When using MAX to get the maximum value

Specify a function in fn.MAX and use scalar () to get the value from the query.

max = LiveChatMessage.select(
    fn.MAX(LiveChatMessage.offset_time_msec)
).where(LiveChatMessage.video_id  == video_id).scalar()
print(max)

Creating large amounts of data

If you want to create a lot of data at once, use insert_many.

from peewee import *
from datetime import date

db = SqliteDatabase(':memory:')

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

    class Meta:
        database = db

data_source = [
    {'name' : 'test1' , 'birthday' : date(1960, 1, 15), 'is_relative' : True},
    {'name' : 'test2' , 'birthday' : date(1960, 2, 15), 'is_relative' : True},
    {'name' : 'test3' , 'birthday' : date(1960, 3, 15), 'is_relative' : True},
    {'name' : 'test4' , 'birthday' : date(1960, 4, 15), 'is_relative' : True},
    {'name' : 'test5' , 'birthday' : date(1960, 5, 15), 'is_relative' : True},
]

db.create_tables([Person])
try:
    with db.transaction():
        Person.insert_many(data_source).execute()

    print ("Get all-----------------")
    for person in Person.select():
        print (person.name, person.birthday, person.is_relative)

except IntegrityError as ex:
    print (ex)
    db.rollback()

How to use DISTINCT

DISTINCT is used as follows.

    q = Person.select(Person.name).distinct()
    print (q.sql())
    for person in q:
        print (person.name, person.birthday, person.is_relative)

The created SQL will be as follows.

'('SELECT DISTINCT "t1"."name" FROM "person" AS "t1"', [])

Dynamically assemble conditions

In the example below, the query is fetched based on operation_company and railway_line.

def get_dynamic_sql(name = None, is_relative = None):
    ret = []
    query = Person.select()
    cond = None
    if not name is None:
        cond = (Person.name == name)
    if not is_relative is None:
        if cond:
            cond = cond & (Person.is_relative == is_relative)
        else:
            cond = (Person.is_relative == is_relative)
    rows = query.where(cond)
    for r in rows: #Issue SQL here
        ret.append(r.name)
    return ret

In this example, 4 types of SQL are created depending on the parameter specification method.

Also, the time when the created SQL is actually issued is when to use it. By using this behavior, you can dynamically construct queries with complex conditions.

About JOIN

JOIN that can be used

RIGHT and FULL JOIN are not available in peewee 2.4.5. Only INNER JOIN or LEFT OUTER JOIN can be used.

query = Curve.select(Curve, RailRoadSection).join(RailRoadSection, JOIN_FULL)

If you specify JOIN_FULL, an exception will be thrown.

peewee.OperationalError: RIGHT and FULL OUTER JOINs are not currently supported
```


### LEFT OUTER JOIN example
 The retrieved record has the name of the table being joined. It is a good idea to use the dir function to do the contents of the record.

```py
#model
from peewee import *
from datetime import date

db = SqliteDatabase(':memory:')

class Group(Model):
    name = CharField()

    class Meta:
        database = db # This model uses the "people.db" database.


class Person(Model):
    name = CharField()
    birthday = DateField()
    is_relative = BooleanField()
    group = ForeignKeyField(Group, related_name='group')

    class Meta:
        database = db # This model uses the "people.db" database.


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

    class Meta:
        database = db # this model uses the people database




try:
    db.create_tables([Person, Pet, Group])
    with db.transaction():
        # 
        grp1 = Group(name='Flower Troupe')
        grp1.save()
        grp2 = Group(name='Kimengumi')
        grp2.save()

        #INSERT by creating an object and saving
        uncle_bob = Person(name='Bob', birthday=date(1960, 1, 15), is_relative=True, group=grp1)
        uncle_bob.save() # bob is now stored in the database

        #INSERT with create
        grandma = Person.create(name='Grandma', birthday=date(1935, 3, 1), is_relative=True, group=grp2)
        herb = Person.create(name='Herb', birthday=date(1950, 5, 5), is_relative=False, group=grp1)

        bob_kitty = Pet.create(owner=herb, name='Kitty', animal_type='cat')
        herb_fido = Pet.create(owner=herb, name='Fido', animal_type='dog')
        herb_mittens = Pet.create(owner=herb, name='Mittens', animal_type='cat')
        herb_mittens_jr = Pet.create(owner=herb, name='Mittens Jr', animal_type='cat')
        ginga = Pet.create(owner=None, name='Mittens Jr', animal_type='cat')


        print ("Get all-----------------")
        for group in Group.select():
            print(group.name)
        for person in Person.select():
            print(person.name, person.is_relative, person.group)
        for pet in Pet.select():
            print(pet.owner, pet.name, pet.animal_type)


        print("Inner Join example-----------------")
        query = (Pet
                 .select(Pet, Person)
                 .join(Person))
        for pet in query:
            print(pet.name, pet.owner.name)


        print("left outer Join example-----------------")
        query = (Pet
                 .select(Pet, Person)
                 .join(Person, JOIN.LEFT_OUTER))
        for pet in query:
            print(pet.name, pet.owner)

        #print("example of right outer Join-----------------")
        #query = (Pet
        #         .select(Pet, Person)
        #         .join(Person, JOIN.FULL))
        #for pet in query:
        #    print(pet.name, pet.owner)

except IntegrityError as ex:
    print (ex)
    db.rollback()


```

### When joining multiple tables
 If you join multiple tables, you must use a switch to specify which table to join.
http://stackoverflow.com/questions/22016778/python-peewee-joins-multiple-tables

```py
#http://stackoverflow.com/questions/22016778/python-peewee-joins-multiple-tables
query = (TimeTableItem
    .select(TimeTableItem, TimeTable, BusStop)
    .join(TimeTable, on = (TimeTableItem.timeTable << list(timetableids.keys())))
    .switch(TimeTableItem)
    .join(BusStop, on=(TimeTableItem.busStop == BusStop.id))
)
for r in query:
    print (r.busStop.stopName)

```
### Self-bonding
 When performing self-join, create an object with an alias with alias and use it

```py
    fromBusStop = BusStopOrder.alias()
    toBusStop = BusStopOrder.alias()
    query = (fromBusStop
        .select(fromBusStop, toBusStop, BusStop)
        .join(
            toBusStop,
            on=((toBusStop.route == fromBusStop.route) & (toBusStop.stopOrder > fromBusStop.stopOrder))
            .alias('toBusStopOrder')
        )
        .switch(toBusStop)
        .join(BusStop, on=(toBusStop.busStop==BusStop.id))
        .where((fromBusStop.busStop==from_bus_stop))
    )
    for r in query:
        print (r.toBusStopOrder.busStop.id)
```

## Creating a model
 This section describes how to create a model.
 See below for details.
https://peewee.readthedocs.org/en/latest/peewee/models.html

### Column type
 Fields can be set by specifying DateField and CharField in the Model class as in the previous example.
 The fields that can be used here are as follows.

 |Field Type |Sqlite |Postgresql |MySQL|
 |:---------:|:-----:|:---------:|:---:|
 |CharField |varchar |varchar |varchar|
 |TextField |text |text |longtext|
 |DateTimeField |datetime |timestamp |datetime|
 |IntegerField |integer |integer |integer|
 |BooleanField |smallint |boolean |bool|
 |FloatField |real |real |real|
 |DoubleField |real |double precision |double precision|
 |BigIntegerField |integer |bigint |bigint|
 |DecimalField |decimal |numeric |numeric|
 |PrimaryKeyField |integer |serial |integer|
 |ForeignKeyField |integer |integer |integer|
 |DateField |date |date |date|
 |TimeField |time |time |time|
 |BlobField |blob |bytea |blob|
 |UUIDField |not supported |uuid |not supported|

 You can specify the default value and the presence or absence of duplication by the parameter when creating the field.
 See below for details.

https://peewee.readthedocs.org/en/latest/peewee/api.html#fields

## Specifying the primary key
 The method of specifying the primary key with peewee is explained below.

### When not setting the primary key
 If you do not specify the PrimaryKey, create a primary key called id for auto-increment.


```py
class Test1(Model):
    name = CharField()
    birthday = DateField()
    is_relative = BooleanField()

    class Meta:
        database = db
``` 


 SQLite database created

```sql
CREATE TABLE IF NOT EXISTS "test1" ("id" INTEGER NOT NULL PRIMARY KEY, "name" VARCHAR(255) NOT NULL, "birthday" DATE NOT NULL, "is_relative" INTEGER NOT NULL
```

### When specifying a specific field as the primary key
 By specifying primary_key = True when creating a field, that field will be the primary key.

```py
class Test2(Model):
    name = CharField(primary_key=True)
    birthday = DateField()
    is_relative = BooleanField()

    class Meta:
        database = db
```


 SQLite database created

```sql
CREATE TABLE IF NOT EXISTS "test2" ("name" VARCHAR(255) NOT NULL PRIMARY KEY, "birthday" DATE NOT NULL, "is_relative" INTEGER NOT NULL)
```

### Make multiple fields the primary key
 Multiple fields can be used as the primary key by using CompositeKey.


```py
class Test3(Model):
    name = CharField()
    birthday = DateField()
    is_relative = BooleanField()

    class Meta:
        database = db
        primary_key = CompositeKey('name', 'birthday')
```

 SQLite database created

```sql
CREATE TABLE IF NOT EXISTS "test3" ("name" VARCHAR(255) NOT NULL, "birthday" DATE NOT NULL, "is_relative" INTEGER NOT NULL, PRIMARY KEY ("name", "birthday"))
```

## Specifying the index
 The method of specifying the index with peewee is explained below.

### Index a specified single field
 When creating a field, you can index it by setting index = True

```py
class Test4(Model):
    name = CharField(index=True)
    birthday = DateField()
    is_relative = BooleanField()

    class Meta:
        database = db
```

 Index created

```sql
CREATE TABLE IF NOT EXISTS "test4" ("id" INTEGER NOT NULL PRIMARY KEY, "name" VARCHAR(255) NOT NULL, "birthday" DATE NOT NULL, "is_relative" INTEGER NOT NULL)
CREATE INDEX IF NOT EXISTS "test4_name" ON "test4" ("name")
```

### Combine specified fields to index
 By specifying indexes in the Meta class, you can create indexes by combining multiple keys.

```py
class Test5(Model):
    name = CharField()
    birthday = DateField()
    is_relative = BooleanField()

    class Meta:
        database = db
        indexes = (
            #At the end,If there is no error
            #Multiple specifications are possible
            (('name', 'birthday'), False),
        )
```

 Index created

```sql
CREATE TABLE IF NOT EXISTS "test5" ("id" INTEGER NOT NULL PRIMARY KEY, "name" VARCHAR(255) NOT NULL, "birthday" DATE NOT NULL, "is_relative" INTEGER NOT NULL)

CREATE INDEX IF NOT EXISTS "test5_name_birthday" ON "test5" ("name", "birthday")
```

### Make an index that prohibits duplication
 You can create an index that prohibits duplication by specifying unique = True when creating the field, or by specifying True in the second argument of indexes of the Meta class.

```py
class Test6(Model):
    name = CharField()
    birthday = DateField()
    is_relative = BooleanField()

    class Meta:
        database = db
        indexes = (
            #At the end,If there is no error
            #Multiple specifications are possible
            (('name', 'birthday'), True),
        )
```

 Index created

```sql
CREATE TABLE IF NOT EXISTS "test6" ("id" INTEGER NOT NULL PRIMARY KEY, "name" VARCHAR(255) NOT NULL, "birthday" DATE NOT NULL, "is_relative" INTEGER NOT NULL)
CREATE UNIQUE INDEX IF NOT EXISTS "test6_name_birthday" ON "test6" ("name", "birthday")
```

### About foreign keys
 You can specify a foreign key using ForeignKeyField ().

 You can specify something other than a primary key with to_field, but this key must be one of the primary keys or have a unique constraint.

http://peewee.readthedocs.org/en/latest/peewee/api.html#ForeignKeyField

## How to connect to various databases
 You can use various databases with peewee.
 See below for details.
https://peewee.readthedocs.org/en/latest/peewee/database.html

### SQLite connection example
 You can connect by specifying memory or a file.

```py
from peewee import *
from datetime import date

db = SqliteDatabase(':memory:')

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

    class Meta:
        database = db

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

    class Meta:
        database = db

db.create_tables([Person, Pet], True)
```

### APSW connection example
 See below for more information on APSW and how to install it.

 ** Try using APSW, a Python library that SQLite can take seriously **
https://qiita.com/mima_ita/items/711f4324da14cbd7741c

 You can install it below.

```
pip install --user https://github.com/rogerbinns/apsw/releases/download/3.28.0-r1/apsw-3.28.0-r1.zip --global-option=fetch --global-option=--version --global-option=3.28.0 --global-option=--all --global-option=build --global-option=--enable-all-extensions
```

 You can connect by specifying memory or a file.

 Also, since Commit is executed when exiting with, the last commit is commented out (otherwise an error will occur).

```py
from peewee import *
from datetime import date
from playhouse.apsw_ext import APSWDatabase
from playhouse.apsw_ext import DateField

db = APSWDatabase('apswdatabase.sqlite')

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

    class Meta:
        database = db 

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

    class Meta:
        database = db 

db.create_tables([Person, Pet])

#db.set_autocommit(False)
with db.transaction():
    # birthday=date(1960, 1, 15) ...
    uncle_bob = Person(name='Bob', birthday=date(1960, 1, 15), is_relative=True)
    uncle_bob.save() # bob is now stored in the database

    grandma = Person.create(name='Grandma', birthday=date(1960, 1, 5), is_relative=True)
    herb = Person.create(name='Herb', birthday='1950-05-05', is_relative=False)

    bob_kitty = Pet.create(owner=uncle_bob, name='Kitty', animal_type='cat')
    herb_fido = Pet.create(owner=herb, name='Fido', animal_type='dog')
    herb_mittens = Pet.create(owner=herb, name='Mittens', animal_type='cat')
    herb_mittens_jr = Pet.create(owner=herb, name='Mittens Jr', animal_type='cat')

    herb_mittens.delete_instance() 
    print ("-----------------")
    for person in Person.select():
        print (person.name, person.is_relative)

    print ("-----------------")
    query = Pet.select().where(Pet.animal_type == 'cat')
    for pet in query:
        print (pet.name, pet.owner.name)

    print ("-----------------")
    query = (Pet
             .select(Pet, Person)
             .join(Person)
             .where(Pet.animal_type == 'cat'))
    for pet in query:
        print (pet.name, pet.owner.name)
    #db.commit() # Not required for APSWDatabase 
 
```

### MySQL connection example
 You can connect by specifying the database, user, password, host, and port.

```py
from peewee import *
from datetime import date



db = MySQLDatabase(
    database='testdb',
    user='test',
    password="test",
    host="192.168.80.131",
    port=3306)

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

    class Meta:
        database = db # This model uses the "people.db" database.

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

    class Meta:
        database = db # this model uses the people database

db.create_tables([Person, Pet])
```

#### If you get a connection error
 If you get the following error, please install the mysql driver.

```
peewee.ImproperlyConfigured: MySQL driver not installed!
```

 ** Installation example: **
```
pip3 install mysqlclient
```

https://github.com/coleifer/peewee/issues/1569

 If you are using windows and get an error, see below.
https://stackoverflow.com/questions/51294268/pip-install-mysqlclient-returns-fatal-error-c1083-cannot-open-file-mysql-h

 Download the whl file for your environment and specify it with pip install.

### Postgres connection example
 You can connect by specifying the database, user, password, host, and port.

```py
from peewee import *
from datetime import date
from playhouse.postgres_ext import PostgresqlExtDatabase

#By default, peewee uses a feature called hstore.
db = PostgresqlExtDatabase(
    database='peewee_test',
    user='postgres',
    password="",
    host="192.168.80.131",
    port=5432,
    register_hstore=False)

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

    class Meta:
        database = db # This model uses the "people.db" database.

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

    class Meta:
        database = db # this model uses the people database

db.create_tables([Person, Pet])
```
#### In case of connection error

```
 ERROR: Could not find a version that satisfies the requirement psqlclient (from versions: none)
ERROR: No matching distribution found for psqlclient
```

 Install psycopg2

```
pip install psycopg2
```

### How to connect to Spatia Lite SQL
 Describes the connection to SpatiaLite, which is an extension of SQLite that handles spatial information.
http://qiita.com/mima_ita/items/64f6c2b8bb47c4b5b391

 To do this, use SqliteExtDatabase in playhouse.sqlite_ext and do the following:

```py
import os
from peewee import *
from playhouse.sqlite_ext import SqliteExtDatabase

# mod_Add the folder with spatialite to your PATH
os.environ["PATH"] = os.environ["PATH"] + ';C:\\tool\\spatialite\\mod_spatialite-NG-win-x86'
db = SqliteExtDatabase('testspatialite.sqlite')

class PolygonField(Field):
    db_field = 'polygon'
db.field_overrides = {'polygon': 'POLYGON'}


# mod_Loading spatialite
db.load_extension('mod_spatialite.dll')


class GeometryTable(Model):
  pk_uid  = PrimaryKeyField()
  n03_001 = CharField()
  n03_002 = CharField()
  n03_003 = CharField()
  n03_004 = CharField()
  n03_007 = CharField()
  Geometry = PolygonField()

  class Meta:
      database = db


for r in GeometryTable.select(GeometryTable.n03_001 ,  SQL('AsText(Geometry)').alias('Geo')).limit(10):
    print (r.n03_001, r.Geo)


```

 The points are as follows.
 -Call mod_spatialite.dll / so using load_extension.
 -Spatialite columns such as POINT and POLYGON are defined by inheriting the Field class, and the db_field specified in the code in db.field_overrides is associated with the DB type name.
 -Use spatiaLite-specific functions such as AsText using R ()

 If you access the RTreeIndex table, you will get the following error.

```
TypeError: 'idx_Station_geometry' object does not support indexing
```

 In this case, execute the SQL directly. (I couldn't use APSW Database)

```py
    rows = database_proxy.connection().execute("""
        SELECT 
          statValue.value,
          AsGeoJson(MapArea.Geometry)
        FROM 
          MapArea 
          inner join idx_MapArea_Geometry ON pkid = MapArea.id AND xmin > ? AND ymin > ? AND xmax < ? AND ymax < ?
          inner join statValueAttr ON MapArea.stat_val_attr_id = statValueAttr.id 
          inner join statValueAttr AS b ON b.stat_value_id = statValueAttr.stat_value_id AND b.attr_value = ?
          inner join statValue ON statValue.id = b.stat_value_id
        WHERE 
          MapArea.stat_id like ?;
    """,(xmin, ymin, xmax, ymax, attr_value, stat_id_start_str + '%'))
```


### How to select a connection destination at runtime
 With the conventional method, it is not possible to specify the connection destination at runtime.
 Therefore, you can use Proxy () to create a connection later that matches the information in the configuration file.

```py
from peewee import *
from datetime import date

database_proxy = Proxy()  # Create a proxy for our db.


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

    class Meta:
        database = database_proxy

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

    class Meta:
        database = database_proxy

#You can specify the actual database later
db = SqliteDatabase(':memory:', autocommit=False)
database_proxy.initialize(db)

db.create_tables([Person, Pet])

```

### Read Slaves and connection pool
 Depending on the Database, you can specify Read Slaves to access the read-only DB, or use a connection pool to handle multiple connections.

https://peewee.readthedocs.org/en/latest/peewee/database.html#read-slaves
https://peewee.readthedocs.org/en/latest/peewee/database.html#connection-pooling


## Schema migration
 Peewee supports schema migration.
 Unlike other schema migration tools, it does not have version control, but it does provide helper functions for migration.

```py
from peewee import *
from datetime import date
from playhouse.migrate import *

db = SqliteDatabase('mig.sqlite')

##Creating the original DB
class Person(Model):
    name = CharField()
    birthday = DateField()
    is_relative = BooleanField()

    class Meta:
        database = db

db.create_tables([Person])
data_source = [
    {'name' : 'test1' , 'birthday' : date(1960, 1, 15), 'is_relative' : True},
    {'name' : 'test2' , 'birthday' : date(1960, 2, 15), 'is_relative' : True},
    {'name' : 'test3' , 'birthday' : date(1960, 3, 15), 'is_relative' : True},
    {'name' : 'test4' , 'birthday' : date(1960, 4, 15), 'is_relative' : True},
    {'name' : 'test5' , 'birthday' : date(1960, 5, 15), 'is_relative' : True},
    {'name' : 'test1' , 'birthday' : date(1960, 1, 15), 'is_relative' : True},
]
Person.insert_many(data_source).execute()

##Schema migration
migrator = SqliteMigrator(db)


title_field = CharField(default='')
status_field = IntegerField(null=True)

with db.transaction():
    migrate(
        migrator.add_column('Person', 'title', title_field),
        migrator.add_column('Person', 'status', status_field),
        migrator.drop_column('Person', 'is_relative'),
    )


```

 Please refer to the following for details.
https://peewee.readthedocs.org/en/latest/peewee/playhouse.html#migrate

## How to create an object from an existing database
 You can use pwiz to create objects from an existing database.

```
python -m pwiz --engine=sqlite mig.sqlite
```

 By executing this command, the object will be output as standard from people.db.

 Output example

```python
from peewee import *

database = SqliteDatabase('mig.sqlite')

class UnknownField(object):
    def __init__(self, *_, **__): pass

class BaseModel(Model):
    class Meta:
        database = database

class Person(BaseModel):
    birthday = DateField()
    name = CharField()
    status = IntegerField(null=True)
    title = CharField()

    class Meta:
        table_name = 'person'
```


## SQL logging
 To log the SQL issued by peewee:


```py
import logging
logger = logging.getLogger('peewee')
logger.setLevel(logging.DEBUG)
logger.addHandler(logging.StreamHandler())
```

 This will output the following log to stderr every time you issue SQL to peewee.

```
('SELECT name FROM sqlite_master WHERE type = ? ORDER BY name;', ('table',))
('CREATE TABLE "test1" ("id" INTEGER NOT NULL PRIMARY KEY, "name" VARCHAR(255) NOT NULL, "birthday" DATE NOT NULL, "is_relative" SMALLINT NOT NULL)', [])
```

## Summary
 As mentioned above, it was confirmed that various DB operations can be performed from Python without writing SQL by using Peewee.