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模块
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 #返回的结果数