python web py入门（4）-从数据库读取数据显示到网页

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
-- 配置MySQL连接为utf-8
SET NAMES 'utf8';
SET CHARSET 'utf8';

-- 创建数据库
CREATE DATABASE IF NOT EXISTS forum DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
USE forum;

-- 创建表users
CREATE TABLE IF NOT EXISTS users
(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    email VARCHAR(100) NOT NULL,
    name VARCHAR(100) NOT NULL,
    password TEXT NOT NULL,
    picture TEXT NOT NULL,
    description TEXT,
    time TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY(id),
    UNIQUE KEY(email),
    UNIQUE KEY(name)
);

-- 创建表posts
CREATE TABLE IF NOT EXISTS posts
(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    time TIMESTAMP DEFAULT NOW(),
    user_id INT UNSIGNED,
    PRIMARY KEY(id),
    FOREIGN KEY(user_id) REFERENCES users(id)
);

-- 创建表comments
CREATE TABLE IF NOT EXISTS comments
(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    content TEXT NOT NULL,
    time TIMESTAMP DEFAULT NOW(),
    user_id INT UNSIGNED,
    parent_id INT UNSIGNED,
    quote_id INT UNSIGNED,
    PRIMARY KEY(id),
    FOREIGN KEY(user_id) REFERENCES users(id),
    FOREIGN KEY(parent_id) REFERENCES posts(id),
    FOREIGN KEY(quote_id) REFERENCES comments(id)
);

-- 设置时区为北京时间
-- 1.以下命令仅在当前会话期间有效
/*SET time_zone = '+8:00';*/
-- 2.以下命令则全局有效
SET GLOBAL time_zone = '+8:00';

1
2
3
4
5
6
7
8
9
10
CREATE TABLE IF NOT EXISTS mintest
(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    email VARCHAR(100) NOT NULL,
    NAME VARCHAR(100) NOT NULL,    
    TIME TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY(id),
    UNIQUE KEY(email),
    UNIQUE KEY(NAME)
);

1
INSERT INTO `forum`.`mintest` (`email`, `NAME`) VALUES ('90732041@qq.com', 'caimouse1'); 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
#python 3.6    
#蔡军生     
#http://blog.csdn.net/caimouse/article/details/51749579    
#
import web

urls = (
    '/', 'index'
)


app = web.application(urls, globals())
render = web.template.render('templates/')
db = web.database(dbn='mysql', host='127.0.0.1', port=3308,
                  db='forum', user='root', pw='12345678')

class index:
    def GET(self):
       email = db.select('mintest')
       return render.index(email)        
    

if __name__ == "__main__":
    app.run()