廖雪峰python教程实战 Day 4 - 编写Model

Day 4 - 编写Model

在上一节的基础上，可以把三个表：User，Blog，Comment用Model表示出来

import time, uuid

from orm import Model, StringField, BooleanField, FloatField, TextField

def next_id(): ##随机创建唯一id，作为主键缺省值
    return '%015d%s000' % (int(time.time() * 1000), uuid.uuid4().hex)

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)

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)

初始化数据库表

1.可以选择手写创建sql脚本

-- schema.sql

drop database if exists awesome;

create database awesome;

use awesome;

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

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;

进入mysql，运行source 【sql脚本文件路径】

mysql> source e:/awesome-python3-webapp/www/schema.sql

2.也可以选择python的mysql模块

使用MySQL

import mysql.connector

conn = mysql.connector.connect(user='root', password='password', database='awesome')
cursor = conn.cursor()
cursor.execute('create table users (id varchar(50) primary key,email varchar(50),passwd varchar(50),name varchar(50),image varchar(500),admin boolean,create_at real)')
cursor.execute('create table blogs (id varchar(50) primary key,user_id varchar(50),user_name varchar(50),user_image varchar(500),name varchar(50),summary varchar(200),content text,create_at real)')
cursor.execute('create table comments (id varchar(50) primary key,blog_id varchar(50),user_id varchar(50),user_name varchar(50),user_image varchar(500),content text,create_at real)')
cursor.close()
conn.commit()
conn.close()

编写数据访问代码

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

async def test(loop):

    await orm.create_pool(loop=loop,user='www-data', password='www-data', db='awesome')
    u = User(name='Summous', email='Summous@example.com', passwd='123456', image='about:blank')
    await u.save()

loop = asyncio.get_event_loop()
loop.run_until_complete(test(loop))
loop.close()

执行后，可以在mysql客户端命令查询，看看数据是否存到数据库里了。

>mysql>select * from users;

出现了Event loop is closed，修改orm.py的execute，加上关闭

await cur.close()   

finally:
    conn.close()

async def execute(sql, args, autocommit=True):
    '''
    Insert、Update、Delete操作的公共执行函数
    :param sql:sql语句
    :param args:sql参数
    :param autocommit:自动提交事务
    :return:
    '''
    # logging.log(sql,args)
    async with __pool.get() as conn:
        if not autocommit:
            await conn.begin()
        try:
            # 创建一个结果为字典的游标
            async with conn.cursor(aiomysql.DictCursor) as cur:
                # 执行sql语句
                await cur.execute(sql.replace('?','%s'),args or ())
                # 获取操作的记录数
                affected = cur.rowcount
                await cur.close()
            if not autocommit:
                await conn.commit()
        except BaseException as e:
            if not autocommit:
                await conn.rollback() #数据回滚
            raise
        finally:
            conn.close()
        return affected #返回的结果数