Flask数据库

from flask import Flask
from flask_sqlalchemy import SQLAlchemy


app = Flask(__name__)


class Config(object):
    """配置参数"""
    # sqlalchemy的配置参数
    SQLALCHEMY_DATABASE_URI = "mysql://root:123456@127.0.0.1:3306/db_flask"
    # 设置成 True，SQLAlchemy 将会追踪对象的修改并且发送信号。这需要额外的内存， 如果不必要的可以禁用它。
    SQLALCHEMY_TRACK_MODIFICATioNS = True

# 实例化SQLAlchemy对象
app.config.from_object(Config)

# 创建数据库sqlalchemy工具对象
db = SQLAlchemy(app)

# 表名常见规范
# ihome --> ih_user     数据库缩写_表名
# tbl_user  -->  tbl_表名

# 创建数据库模型类

class Role(db.Model):
    """用户表"""
    __tablename__ = "tbl_roles"

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), unique=True)
    users = db.relationship("User", backref="role")


class User(db.Model):
    """用户表"""
    __tablename__ = "tbl_users"     # 指明数据库的表名

    id = db.Column(db.Integer, primary_key=True)    # 整型的主键， 会默认设置为自增主键
    name = db.Column(db.String(64), unique=True)
    email = db.Column(db.String(128))
    passWord = db.Column(db.String(128))
    role_id = db.Column(db.Integer, db.ForeignKey("tbl_roles.id"))

  # 创建对象
    ro1 = Role(name='admin')
    # session记录对象任务
    db.session.add(ro1)
    # 提交任务到数据库
    db.session.commit()

    ro2 = Role(name='user')
    db.session.add(ro2)
    db.session.commit()

    us1 = User(name='wang', email='wang@163.com', password='123456', role_id=ro1.id)
    us2 = User(name='zhang', email='zhang@163.com', password='201512', role_id=ro2.id)
    us3 = User(name='chen', email='chen@126.com', password='987654', role_id=ro2.id)
    us4 = User(name='zhou', email='zhou@163.com', password='456789', role_id=ro1.id)

    # 一次保存多条数据
    db.session.add_all([us1, us2, us3, us4])
    db.session.commit()

>>> User.query.filter_by(name="wang").all()
[<User 1>]
>>> user = User.query.filter_by(name="wang").all()
>>> user[0].name
'wang'

>>> user = User.query.first()
>>> user
<User 1>
>>> user.name
'wang'

>>> User.query.all()
[<User 1>, <User 2>, <User 3>, <User 4>]

>>> users = User.query.filter(User.name.endswith("g")).all()
>>> users
[<User 1>, <User 2>]
>>> users[0].name
'wang'

>>> User.query.get(1)
<User 1>
>>> user = User.query.get(1)
>>> user.name
'wang'

>>> user.query.filter(User.name!="wang").all()
[<User 2>, <User 3>, <User 4>]

>>> from sqlalchemy import and_
>>> user = User.query.filter(and_(User.name!="wang",User.email.endswith("163.com"))).all()
>>> user
[<User 2>, <User 4>]

>>> from sqlalchemy import or_
>>> User.query.filter(or_(User.name!="wang", User.email.endswith("163.com"))).all()
[<User 1>, <User 2>, <User 3>, <User 4>]

>>> from sqlalchemy import not_
>>> User.query.filter(not_(User.name=="chen")).all()
[<User 1>, <User 2>, <User 4>]

>>> db.session.query(Role).all()
[<Role 1>, <Role 2>]
>>> db.session.query(Role).get(2)
<Role 2>
>>> db.session.query(Role).first()
<Role 1>

>>> user= User.query.get(5)
>>> user
>>> type(user)
<class 'NoneType'>

>>> users = User.query.offset(2).all()
>>> users[0].name
'chen'

>>> users = User.query.offset(1).limit(2).all()
>>> users[0].name
'zhang'
>>> users[1].name
'chen'

>>> users = User.query.order_by(User.id.desc()).all()
>>> users
[<User 4>, <User 3>, <User 2>, <User 1>]
>>> users[0].name
'zhou'

>>> from sqlalchemy import func
>>> db.session.query(User.role_id, func.count(User.role_id)).group_by(User.role_id)
<flask_sqlalchemy.BaseQuery object at 0x0000025DBA5B0CC0>
>>> db.session.query(User.role_id, func.count(User.role_id)).group_by(User.role_id).all()
[(1, 2), (2, 2)]

class Role(db.Model):
    """用户表"""
    __tablename__ = "tbl_roles"

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), unique=True)
    users = db.relationship("User", backref="role")

    def __repr__(self):
        return "Role object:%s" % self.name


class User(db.Model):
    """用户表"""
    __tablename__ = "tbl_users"     # 指明数据库的表名

    id = db.Column(db.Integer, primary_key=True)    # 整型的主键， 会默认设置为自增主键
    name = db.Column(db.String(64), unique=True)
    email = db.Column(db.String(128))
    password = db.Column(db.String(128))
    role_id = db.Column(db.Integer, db.ForeignKey("tbl_roles.id"))

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

>>> User.query.get(1)
User object:wang

>>> ro = Role.query.get(1)
>>> type(ro)
<class 'db_demo.Role'>
>>> ro.users
[User object:wang, User object:zhou]

>>> user = User.query.get(1)
>>> user
User object:wang
>>> user.role_id
1
>>> Role.query.get(user.role_id)
Role object:admin
>>> user.role
Role object:admin

>>> user = User.query.get(4)
>>> user
User object:zhou
>>> user.name = "test"
>>> db.session.add(user)
>>> db.session.commit()
>>> User.query.get(4)
User object:test

>>> user = User.query.filter_by(id=1).update({"name":"test1"})
>>> db.session.commit()
>>> User.query.get(1)
User object:test1

>>> user =User.query.get(1)
>>> db.session.delete(user)
>>> db.session.commit()

from flask import Flask, render_template, request, redirect, url_for
from flask_sqlalchemy import SQLAlchemy
from flask_wtf import FlaskForm
from wtforms import StringField, SubmitField
from wtforms.validators import DataRequired

app = Flask(__name__)

class Config(object):
    SQLALCHEMY_DATABASE_URI = "mysql://root:123456@127.0.0.1:3306/db_flask"
    SQLALCHEMY_TRACK_MODIFICATIONS = True
    SECRET_KEY = "sahq28y1qhihsd0-121ewq"

# 实例化SQLAlchemy对象
app.config.from_object(Config)

# 创建数据库sqlalchemy工具对象
db = SQLAlchemy(app)

# 定义数据库的模型
class Author(db.Model):
    """作者"""
    __tablename__ = "tbl_authors"

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(32), unique=True)
    books = db.relationship("Book", backref="author")


class Book(db.Model):
    """书籍"""
    __tablename__ = "tbl_books"

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), unique=True)
    author_id = db.Column(db.Integer, db.ForeignKey("tbl_authors.id"))

# 创建表单模型类
class AuthorBookForm(FlaskForm):
    """作者数据表单模型类"""
    author_name = StringField(label="作者", validators=[DataRequired("作者必填")])
    book_name = StringField(label="书籍", validators=[DataRequired("书籍必填")])
    submit = SubmitField(label="保存")

@app.route("/index", methods=["POST", "GET"])
def index():
    form = AuthorBookForm()
    if form.validate_on_submit():
        author_name = form.author_name.data
        book_name = form.book_name.data

        author = Author(name=author_name)
        db.session.add(author)
        db.session.commit()

        book = Book(name=book_name, author_id=author.id)
        # book = Book(name=book_name, author=author)
        db.session.add(book)
        db.session.commit()

    authors = Author.query.all()
    return render_template("author_book.html", form=form, authors=authors)

@app.route("/delete_book")
def delete_book():
    book_id = request.args.get("book_id")
    book = Book.query.get(book_id)
    author_id = book.id
    author = Author.query.get(author_id)
    db.session.delete(book)
    db.session.commit()

    db.session.delete(author)
    db.session.commit()
    return redirect(url_for("index"))

if __name__ == '__main__':
    # db.drop_all()
    # db.create_all()
    # au_xi = Author(name='我吃西红柿')
    # au_qian = Author(name='萧潜')
    # au_san = Author(name='唐家三少')
    # db.session.add_all([au_xi, au_qian, au_san])
    # db.session.commit()
    #
    # bk_xi = Book(name='吞噬星空', author_id=au_xi.id)
    # bk_xi2 = Book(name='寸芒', author_id=au_qian.id)
    # bk_qian = Book(name='飘渺之旅', author_id=au_qian.id)
    # bk_san = Book(name='冰火魔厨', author_id=au_san.id)
    # db.session.add_all([bk_xi, bk_xi2, bk_qian, bk_san])
    db.session.commit()

    app.run(debug=True)

<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
    <meta Http-equiv='Content-type' content='text/htm'>
</head>
<body>
<form method="post">
<p>添加作者和书籍</p>
{{ form.csrf_token }}
{{ form.author_name.label }}
{{ form.author_name }}
{{ form.author_name.errors.0 }}
<br/>
{{ form.book_name.label }}
{{ form.book_name }}
{{ form.book_name.errors.0 }}
<br/>
{{ form.submit }}
<br/>
</form>
<hr/>

{% for author in authors %}
{{ author.name }}
    <ul>
    {% for book in author.books %} <a href="/delete_book?book_id={{ book.id }}">删除</a>
    <li>{{ book.name }}</li>
    {% endfor %}
    </ul>
{% endfor %}


</body>
</html>

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_script import Manager
from flask_migrate import Migrate, MigrateCommand

app = Flask(__name__)


class Config(object):
    SQLALCHEMY_DATABASE_URI = "mysql://root:123456@127.0.0.1:3306/db_flask"
    SQLALCHEMY_TRACK_MODIFICATIONS = True

app.config.from_object(Config)

# 创建sqlalchemy的数据库连接对象
db = SQLAlchemy(app)

# 创建flask脚本管理工具对象
manager = Manager(app)

# 创建数据库迁移工具对象
Migrate(app, db)

# 向manager对象中添加数据库的操作命令
manager.add_command("db", MigrateCommand)


# 定义模型Role
class Role(db.Model):
    # 定义表名
    __tablename__ = 'roles'
    # 定义列对象
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), unique=True)

    def __repr__(self):
        return 'Role:'.format(self.name)


# 定义用户
class User(db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(64), unique=True, index=True)

    def __repr__(self):
        return 'User:'.format(self.username)


if __name__ == '__main__':
    manager.run()

from flask import Flask
from flask_mail import Mail, Message

app = Flask(__name__)
#配置邮件：服务器／端口／传输层安全协议／邮箱名／密码
app.config.update(
    DEBUG = True,
    MAIL_SERVER='smtp.qq.com',
    MAIL_PROT=465,
    MAIL_USE_TLS = True,
    MAIL_USERNAME = 'xxxxxxxx@qq.com',
    MAIL_PASSWORD = 'xxxxxx',
)

mail = Mail(app)

@app.route('/')
def index():
 # sender 发送方，recipients 接收方列表
    msg = Message("This is a test ",sender='11111@qq.com', recipients=['aaaaaa@163.com','11111@qq.com'])
    #邮件内容
    msg.body = "Flask test mail"
    #发送邮件
    mail.send(msg)
    print "Mail sent"
    return "Sent　Succeed"

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