Write Model

After orm.py is written, the three tables (user, blog and comment) required by the website application can be represented by Model. In the www directory, create models.py:

import time, uuid
# Importing modules in orm
from orm import Model, StringField, BooleanField, FloatField, TextField
# Define a function to randomly generate a string as the value of the primary key id
# The bracketed run function returns a string. The first 15 bits of the string are the timestamp multiplied by 1000, and then less than 15 bits are filled with 0
# This is followed by a string randomly generated using uuid.uuid4().hex
# Last supplement: 000
def next_id():
    return '%015d%s000' % (int(time.time() * 1000), uuid.uuid4().hex)

# User module
class User(Model):
    __table__ = 'users'

    id = StringField(primary_key=True, default=next_id, ddl='varchar(50)')
    email = StringField(ddl='varchar(50)')
    passwd = StringField(ddl='varchar(50)')
    admin = BooleanField()
    name = StringField(ddl='varchar(50)')
    image = StringField(ddl='varchar(500)')
    created_at = FloatField(default=time.time)

# Blog module
class Blog(Model):
    __table__ = 'blogs'

    id = StringField(primary_key=True, default=next_id, ddl='varchar(50)')
    user_id = StringField(ddl='varchar(50)')
    user_name = StringField(ddl='varchar(50)')
    user_image = StringField(ddl='varchar(500)')
    name = StringField(ddl='varchar(50)')
    summary = StringField(ddl='varchar(200)')
    content = TextField()
    created_at = FloatField(default=time.time)

class Comment(Model):
    __table__ = 'comments'

    id = StringField(primary_key=True, default=next_id, ddl='varchar(50)')
    blog_id = StringField(ddl='varchar(50)')
    user_id = StringField(ddl='varchar(50)')
    user_name = StringField(ddl='varchar(50)')
    user_image = StringField(ddl='varchar(500)')
    content = TextField()
    created_at = FloatField(default=time.time)

When writing the ORM, adding a default parameter to the Field allows the ORM to fill in the default value by itself, which is very convenient. Moreover, the default value can be passed in as a function object and calculated automatically when calling save(). For example, the default value of the primary key id is the function next_id, creation time created_ The default value of at is the function time.time, which can automatically set the current date and time. The date and time are stored in the database with float type instead of datetime type. The advantage of this is that you don't have to care about the time zone and time zone conversion of the database. The sorting is very simple. When displaying, you only need to convert float to str, which is also very easy.

As for the default values, we talked about id and created in the previous section from the split parsing process_ The default value of at is a function name, and then when calling save(), the function uses callable to determine whether it is a callable object. If so, the plus () operation assigns the return value to the corresponding field. For example, the return value obtained by the id field is a whole random string composed of a random string preceded by a timestamp multiplied by 1000 and a uuid, createad_ The return value obtained by at is time.time(), that is, the current timestamp.

Initialize database tables

Due to the small number of site tables, you can manually create the SQL script schema.sql to the root directory:

drop database if exists awesome;
drop user if exists 'www-data'@'localhost';

create database awesome;

use awesome;

create user 'www-data'@'localhost' identified by 'www-data';
alter user 'www-data'@'localhost' identified with mysql_native_password by 'www-data';
grant select, insert, update, delete on awesome.* to 'www-data'@'localhost';

create table users (
    `id` varchar(50) not null,
    `email` varchar(50) not null,
    `passwd` varchar(50) not null,
    `admin` bool not null,
    `name` varchar(50) not null,
    `image` varchar(500) not null,
    `created_at` real not null,
    unique key `idx_email` (`email`),
    key `idx_created_at` (`created_at`),
    primary key (`id`)
) engine=innodb default charset=utf8;

create table blogs (
    `id` varchar(50) not null,
    `user_id` varchar(50) not null,
    `user_name` varchar(50) not null,
    `user_image` varchar(500) not null,
    `name` varchar(50) not null,
    `summary` varchar(200) not null,
    `content` mediumtext not null,
    `created_at` real not null,
    key `idx_created_at` (`created_at`),
    primary key (`id`)
) engine=innodb default charset=utf8;

create table comments (
    `id` varchar(50) not null,
    `blog_id` varchar(50) not null,
    `user_id` varchar(50) not null,
    `user_name` varchar(50) not null,
    `user_image` varchar(500) not null,
    `content` mediumtext not null,
    `created_at` real not null,
    key `idx_created_at` (`created_at`),
    primary key (`id`)
) engine=innodb default charset=utf8;

SQL script   schema.sql is executed on the MySQL command line to complete the initialization of database tables:

mysql -u root -p < schema.sql

Then we can write the data access code test.py to test. To create a new User object:

import orm
import asyncio
from models import User, Blog, Comment

# Define the coprocessor function test
async def test(loop):
    # Create connection pool
    await orm.create_pool(loop=loop,user='www-data', password='www-data', db='awesome')
    # Instantiating an object with class User is actually equivalent to creating a dictionary
    # id and created_at is not passed in. The default value next is used_ id and time.time
    # If id and created_ When at is initialized, the value passed in during initialization will be obtained first, and the default value will not be used
    u = User(name='Test', email='test@qq.com', passwd='1234567890', image='about:blank')
    # print(dir(u))
    # print(u.__fields__)
    await u.save()
    # print(u.__fields__)
    ## Netizens pointed out that the connection pool needs to be closed after adding to the database, otherwise an error RuntimeError: Event loop is closed will be reported
    orm.__pool.close()
    await orm.__pool.wait_closed()
if __name__ == '__main__':
    loop = asyncio.get_event_loop()
    loop.run_until_complete(test(loop))
    loop.close()

After running test.py, you can query on the MySQL client command line to see if the test data is normally stored in MySQL.

mysql> select * from users;
+----------------------------------------------------+-------------+------------+-------+------+-------------+------------------+
| id                                                 | email       | passwd     | admin | name | image       | created_at       |
+----------------------------------------------------+-------------+------------+-------+------+-------------+------------------+
| 00163780190327232747957103e40eda717c9c7c5e057b2000 | test@qq.com | 1234567890 |     0 | Test | about:blank | 1637801903.27281 |
+----------------------------------------------------+-------------+------------+-------+------+-------------+------------------+
1 row in set (0.00 sec)