Snake's Home

flask之mysql数据库连接

發表於 | 分類於

结构

1
2
3
4
5
6
7
8
9
10
├── __init__.py
├── application
│   ├── __init__.py
│   ├── app.py
│   ├── models.py
│   └── templates
│       ├── index.html
│       ├── layout.html
│       └── show.html
├── manage.py

安装

1
2
3
4
flask
flask-Script
flask-SQLAlchemy
MySQL-python

manage.py

1
2
3
4
5
6
7
8
# encoding=utf-8

from flask_script import Manager
from application.app import app
manager = Manager(app)
app.config['DEBUG'] = True
if __name__ == '__main__':
    manager.run()

app.py

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
#encoding=utf-8
from flask import Flask
from flask import render_template
from flask import url_for
from flask import redirect
from flask import request,flash
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
track_modifications = app.config.setdefault('SQLALCHEMY_TRACK_MODIFICATIONS', True)
app.config['SQLALCHEMY_DATABASE_URI']="mysql://root:@localhost:3306/message"
db = SQLAlchemy(app)
SECERT_KEY="a secret key"
app.config.from_object(__name__)
app.secret_key=app.config['SECERT_KEY']
from models import Message

@app.route('/',methods=['POST','GET'])
def index():
        if request.method == 'POST':
                name = request.form['name']
                email = request.form['email']
                content = request.form['content']
                title = request.form['title']
                mess = Message(name=name,email=email,content=content,title=title)
                db.session.add(mess)
                db.session.commit()
                flash("Add Message Sucess!!")
                #return name+email+content+title
                return redirect(url_for("index"))
        else:
                mess = Message.query.all()
                return render_template("index.html",message=mess)

@app.route('/show/<int:id>')
def show(id):
        mess = Message.query.filter_by(id=id).first()
        if mess !=None:
                return render_template("show.html",message=mess)
        else:
                return redirect(url_for("index"))

models.py

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
#encoding=utf-8
from app import db
from datetime import datetime

class Message(db.Model):
        __tablename__ ="message"
        id = db.Column(db.Integer,primary_key=True)
        name = db.Column(db.String(200))
        time = db.Column(db.DateTime,default=datetime.now())
        content = db.Column(db.String(255))
        email = db.Column(db.String(100))
        title = db.Column(db.String(255))

        def __init__(self,name,content,email,title):
                self.name = name
                self.content = content
                self.email = email
                self.title = title

        def __repr__(self):
                return "<User:%s>" %self.name


if __name__=="__main__":
        db.create_all()

layout.html

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>留言板</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<link href={{ url_for('static',filename="css/bootstrap.min.css") }} rel="stylesheet" media="screen">
<script src={{ url_for('static',filename="js/jquery.js") }}></script>
<script src={{ url_for('static',filename="js/bootstrap.min.js") }}></script>
</head>
<body>
        {% block body %}
        {% endblock %}
</body>
<html>

index.html

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
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
{% extends "layout.html" %}
{% block body %}
<div class="row">
        <div class="span6 offset6">
        {% for mess in get_flashed_messages() %}
 
                <div class="alert alert-info">
                        {{ mess }}
                </div>
        {% endfor %}
        </div>
 
</div>
<script language="javascript">
        function checkinput()
{
        var oName=document.getElementById('name');
        if(oName.value == ""){
                alert("姓名不能为空");
                return false;
        }
        var ocontent=document.getElementById('content');
        if(ocontent.value == ""){
                alert("内容不能为空");
                return false;   
        }
        var otitle=document.getElementById('title');
        if(otitle.value == ""){
                alert("标题不能为空");
                return false;
        }
        var oemail=document.getElementById('email');
        if(oemail.value == ""){
                alert("邮箱不能为空");
                return false;
        }
        return true;
}
</script>
<div class="container">
<div class="span8 offset2">
                <h2>留言板</h2>
<table class="table table-hover">
                <tr>
                        <th>姓名</th>
                        <th>留言主题</th>
                        <th>留言时间</th>
                        <th>留言邮箱</th>
                <tr>
        {% for i in message %}
                <tr>
                        <td>{{ i.name }}</td>
                        <td><a href="/show/{{ i.id }}">{{ i.title}}</a></td>
                        <td>{{ i.time }}</td>
                        <td>{{ i.email }}</td>
                </tr>
        {% endfor %}
</table>
</div>
</div>
<div class="container">
<div class="span12">
        <hr>
</div>
</div>
<div class="container">
<div class="span6 offset3">
<form class="form-horizontal" role="form" action="{{ url_for("index") }}" method=post onsubmit="return checkinput();">
<div class="form-group">
        <label class="col-sm-4 control-label">姓名</label>
<div class="col-sm-8">
<input type=text name="name" id="name" />
</div>
</div>
<div class="form-group">
        <label class="col-sm-4 control-label">留言主题</label>
<div class="col-sm-8">
<input type=text name="title" id="title" />
</div>
</div>
 
<div class="form-group">
        <label class="col-sm-4 control-label">留言内容</label>
<div class="col-sm-8">
<input type=text name="content" id="content" />
</div>
</div>
 
<div class="form-group">
        <label class="col-sm-4 control-label">留言信箱</label>
<div class="col-sm-8">
<input type=text name="email" id="email" />
</div>
</div>
        <div class="span6 offset3">
        <input type=submit value="提交" name="sub"/>
        </div>
</div>
</form>
</div>
</div>
{% endblock %}

show.html

1
2
3
4
5
{% extends "layout.html" %}
{% block body %}
        <h3>{{ message.title }}<h3>
        <p class="less">{{ message.content }}</p>
{% endblock %}

数据库操作：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
$ mysql.server start
$ mysql -h localhost -u root -p
show databases;
crate database Message;
use message;
show tables;


#mysql> CREATE TABLE message(
#   -> id INT NOT NULL AUTO_INCREMENT,
#   -> name VARCHAR(200) NOT NULL,
#   -> time DATE,
#   -> content VARCHAR(255) NOT NULL,
#   -> email VARCHAR(100) NOT NULL,
#   -> title VARCHAR(255) NOT NULL,
#   -> PRIMARY KEY ( id )
#   -> );

mysql查看表结构命令，如下:

desc 表名;
show columns from 表名;
describe 表名;
show create table 表名;

then run:

➜ testdb python manage.py runserver